2008人阅读
转载请注明出处:
正确答案:D
Oracle 11g新特性之统计信息收集
&& 在以前的数据库版本中，当收集到新的优化器统计信息时会自动发布。在11g中，这仍然是默认的动作，但你多了一个选择，
你可以将最新的统计信息挂起，直到人为地发布它们。DBMS_STATS.GET_PREFS函数运行你检查“PUBLISH”属性，查看统计信息
是否已经自动发布。默认返回的值为TRUE，意味着已经自动发布了，而FALSE表示它还处于挂起状态，等待发布。
& 检查当前数据库的PUBLISH”属性:
gyj@OCM& col get_prefs for a50
gyj@OCM& SELECT DBMS_STATS.get_prefs('PUBLISH','SH','CUSTOMERS') get_prefs FROM
--------------------------------------------------
使用存储过程DBMS_STATS.SET_TABLE_PREFS可以重新设置“PUBLISH”属性,设为FALSE表示它还处于挂起状态，等待发布:
gyj@OCM& BEGIN
DBMS_STATS.SET_TABLE_PREFS('SH', 'CUSTOMERS', 'PUBLISH', 'false');
PL/SQL procedure successfully completed.
gyj@OCM& SELECT DBMS_STATS.get_prefs('PUBLISH','SH','CUSTOMERS') get_prefs FROM
--------------------------------------------------
参考官方文档:
SET_TABLE_PREFS Procedure
This procedure isused to set the statistics preferences of the specified table in the specifiedschema.
DBMS_STATS.SET_TABLE_PREFS (
&&& ownname&&& IN& VARCHAR2,
&&& tabname&&& IN& VARCHAR2,
&&& pname&&&&& IN& VARCHAR2,
& &&pvalue&&&& IN& VARCHAR2);
Parameters
Table 141-93SET_TABLE_PREFS Procedure Parameters
Description
Owner name
Table name
Preference name. The default value for following preferences can be set:
·&&&&&&&& CASCADE
·&&&&&&&& DEGREE
·&&&&&&&& ESTIMATE_PERCENT
·&&&&&&&& METHOD_OPT
·&&&&&&&& NO_INVALIDATE
·&&&&&&&& GRANULARITY
·&&&&&&&& PUBLISH
·&&&&&&&& INCREMENTAL
·&&&&&&&& STALE_PERCENT
CASCADE&- Determines whether or not index statistics are collected as part of gathering table statistics.
DEGREE&- Determines degree of parallelism used for gathering statistics.
ESTIMATE_PERCENT&- Determines the percentage of rows to estimate. The valid range is [0.]. Use the constantDBMS_STATS.AUTO_SAMPLE_SIZE&to have Oracle determine the appropriate sample size for good statistics.
This is the default.
METHOD_OPT&- Controls column statistics collection and histogram creation. It accepts either of the following options, or both in combination:
·&&&&&&&& FOR ALL [INDEXED | HIDDEN] COLUMNS&[size_clause]
·&&&&&&&& FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]
size_clause&is defined as&size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column&is defined as&column := column_name | extension name | extension
- integer&: Number of histogram buckets. Must be in the range [1,254].
- REPEAT&: Collects histograms only on the columns that already have histograms
- AUTO&: Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY&: Oracle determines the columns to collect histograms based on the data distribution of the columns.
-&column_name&: Name of a column
-&extension :&Can be either a column group in the format of&(column_name, colume_name [, ...])&or an expression
The default is&FOR ALL COLUMNS SIZE AUTO.
NO_INVALIDATE&- The value controls the invalidation of dependent cursors of the tables for which statistics are being gathered. Does not invalidate the dependent cursors if set to&TRUE. The procedure invalidates the dependent cursors
immediately if set to&FALSE. Use&DBMS_STATS.AUTO_INVALIDATE&to have Oracle decide when to invalidate dependent cursors. This is the default.
GRANULARITY&- Determines granularity of statistics to collect (only pertinent if the table is partitioned).
'ALL'&- Gathers all (subpartition, partition, and global) statistics
'AUTO'- Determines the granularity based on the partitioning type. This is the default value.
'DEFAULT'&- Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality.
Note that the default value is now 'AUTO'.
'GLOBAL'&- Gathers global statistics
'GLOBAL AND PARTITION' - Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.
'PARTITION&'- Gathers partition-level statistics
'SUBPARTITION'&- Gathers subpartition-level statistics.
PUBLISH&- Determines whether or not newly gathered statistics will be published once the gather job has completed. Prior to Oracle Database 11g, Release 1 (11.1), once a statistic gathering job completed the new statistics were automatically
published into the dictionary tables. The user now has the ability to gather statistics but not publish them immediately. This allows the DBA to test the new statistics before publishing them.
INCREMENTAL&- Determines whether or not the global statistics of a partitioned table will be maintained without doing a full table scan. With partitioned tables it is very common to load new data into a new partition. As new partitions are added
and data loaded, the global table statistics need to be kept up to date. Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:
·&&&&&&&& INCREMENTAL&value for the partitioned table is set to&TRUE;
·&&&&&&&& PUBLISH&value for the partitioned table is set to&TRUE;
·&&&&&&&& User specifies&AUTO_SAMPLE_SIZE&for&ESTIMATE_PERCENT&and&AUTO&for&GRANULARITY&when gathering statistics on the table.
If the&INCREMENTAL&value for the partitioned table was set to&FALSE&(default value), a full table scan is used to maintain the global statistics which is a much more resource intensive and time-consuming operation for large tables.
STALE_PERCENT&- Determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The default value is 10%.
Preference value. If&NULL&is specified, it will set the Oracle default value.
Exceptions
: Object doesnot exist or insufficient privileges
: Invalid orillegal input values
Usage Notes
·&&&&&&&&To run this procedure, you need to connect as owner of the tableor should have the&ANALYZE&ANY&systemprivilege.
·&&&&&&&&All arguments are of type&VARCHAR2&and values are enclosed in quotes, even when they representnumbers.
DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'CASCADE', 'DBMS_STATS.AUTO_CASCADE');
DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES','ESTIMATE_PERCENT','9');
DBMS_STATS.SET_TABLE_PREFS('SH', 'SALES', 'DEGREE','99');
&QQ：技术交流QQ群：
DSI&Core Search &Ⅰ 群：（2000人技术群:未满）
DSI&Core Search &Ⅱ 群：（1000人技术群:未满）
DSI&Core Search &Ⅲ 群：（500人技术群:未满）
DSI&Core Search &Ⅳ 群：（500人技术群:未满）
DSI&Core Search &Ⅴ 群：（500人闲聊群:未满）
MAIL：dbathink@BLOG：&WEIBO：ITPUB：&OCM： &&
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：328265次
积分：6312
积分：6312
排名：第1304名
原创：242篇
转载：15篇
译文：10篇
评论：93条
OCP认证群：
OCM认证群：
SQL优化群：
JAVA实战群：
Hadoop群：
技术分享群：
文章：97篇
阅读：122603
(1)(2)(1)(1)(2)(2)(10)(6)(1)(1)(5)(5)(1)(3)(24)(30)(31)(23)(5)(5)(10)(20)(78)推荐到广播
255450 人聚集在这个小组
(夜猫神的多巴胺)
(zz怪兽麻麻)
手机扫描二维码，把小组装进口袋
第三方登录：..................._官道之1976吧_百度贴吧
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&签到排名：今日本吧第个签到，本吧因你更精彩，明天继续来努力！
本吧签到人数：0成为超级会员，使用一键签到本月漏签0次！成为超级会员，赠送8张补签卡连续签到：天&&累计签到：天超级会员单次开通12个月以上，赠送连续签到卡3张
关注：6,360贴子：
...................收藏
There*s a fire starting in my heart 我怒火中烧Reaching a fever pitch and it*s bringing me out the dark 熊熊烈焰带我[]http://[/url], I can see you crystal clear 最终 我将你看得一清二楚Go ahead and sell me out and I*ll lay your ship bare 去吧 出卖我 我会让你一无所有See how I*ll leave with every piece of you 看我怎么离你而去 带走你的一切Don*t underestimate the things that I will do 不要低估我将来的所作所为There*s a fire starting in my heart 我怒火中烧Reaching a fever pitch and it*s bring me out the dark 熊熊烈焰带我[]http://[/url]The scars of your love remind me of us 你的爱情伤痕让我想起了我们曾经的甜蜜They keep me thinking that we almost had it all 它们总在提醒我 我们几乎拥有了一切The scars of your love, they leave me breathless 你的爱情伤痕 让我窒息I can*t help feeling 我不禁心生感触We could have had it all 我们本该拥有一切(You*re gonna wish you never had met me) （你会祈祷 要是从未遇见我该有多好）Rolling in the deep 内心深处[]http://[/url](Tears are gonna fall, rolling in the deep) （眼泪快要掉下来，内心深处[]http://[/url]）You had my heart inside your hand 你俘虏了我的芳心(You*re gonna wish you never had met me) （你会祈祷 要是从未遇见我该有多好）And you played it to the beat 但是你玩弄它 伴着[]http://[/url](Tears are gonna fall, rolling in the deep) （眼泪快要掉下来，内心深处[]http://[/url]）Baby, I have no story to be told 宝贝 我没有故事可讲But I*ve heard one on you and I*m gonna make your head burn 但是我听说了一件有关你的事情 我会让你焦头烂额Think of me in the depths of your despair 在绝望的深渊中想起我Making a home down there as mine sure won*t be shared 就在那里安家吧 因为[]http://[/url]绝对没你的份The scars of your love remind me of us 你的爱情伤痕让我想起了我们曾经的甜蜜(You*re gonna wish you never had met me) （你会祈祷 要是从未遇见我该有多好）They keep me thinking that we almost had it all 它们总在提醒我 我们几乎拥有了一切(Tears are gonna fall, rolling in the deep) （眼泪快要掉下来，内心深处[]http://[/url]）The scars of your love, they leave me breathless 你的爱情伤痕 让我窒息(You*re gonna wish you never had met me) （你会祈祷 要是从未遇见我该有多好）I can*t help feeling 我不禁心生感触(Tears are gonna fall, rolling in the deep) （眼泪快要掉下来，内心深处[]http://[/url]）We could have had it all 我们本该拥有一切(You*re gonna wish you never had met me) （你会祈祷 要是从未遇见我该有多好）Rolling in the deep 内心深处[]http://[/url](Tears are gonna fall, rolling in the deep) （眼泪快要掉下来，内心深处[]http://[/url]）You had my heart inside your hand 你俘虏了我的芳心(You*re gonna wish you never had met me) （你会祈祷 要是从未遇见我该有多好）And you played it to the beat 但是你玩弄它 伴着[]http://[/url](Tears are gonna fall, rolling in the deep) （眼泪快要掉下来，内心深处[]http://[/url]）Could have had it all 本该拥有一切Rolling in the deep 内心深处[]http://[/url]You had my heart inside your hand 你俘虏了我的芳心But you played it with a beating 但是你玩弄它 还一顿打击Throw your soul through every open door 打开每一扇门 将你的灵魂驱赶出去Count your blessings to find what you look for 细数你的祷告 知道你在寻找什么Turn my sorrow into treasured gold 把我的悲痛化作珍宝You*ll pay me back in kind and reap just what you*ve sown 我要让你付出同样的代价 你自作自受 自食其果(You*re gonna wish you never had met me) （你会祈祷 要是从未遇见我该有多好）We could have had it all 我们本该拥有一切(Tears are gonna fall, rolling in the deep) （眼泪快要掉下来，内心深处[]http://[/url]）We could have had it all 我们本该拥有一切(You*re gonna wish you never had met me) （你会祈祷 要是从未遇见我该有多好）It all, it all, it all 一切 一切 一切(Tears are gonna fall, rolling in the deep) （眼泪快要掉下来，内心深处[]http://[/url]）We could have had it all 我们本该拥有一切(You*re gonna wish you never had met me) （你会祈祷 要是从未遇见我该有多好）Rolling in the deep 内心深处[]http://[/url](Tears are gonna fall, rolling in the deep) （眼泪快要掉下来，内心深处[]http://[/url]）You had my heart inside your hand 你俘虏了我的芳心(You*re gonna wish you never had met me) （你会祈祷 要是从未遇见我该有多好）And you played it to the beat 但是你玩弄它 伴着[]http://[/url](Tears are gonna fall, rolling in the deep) （眼泪快要掉下来，内心深处[]http://[/url]）Could have had it all 本该拥有一切(You*re gonna wish you never had met me) （你会祈祷 要是从未遇见我该有多好）Rolling in the deep 内心深处[]http://[/url](Tears are gonna fall, rolling in the deep) （眼泪快要掉下来，内心深处[]http://[/url]）You had my heart inside your hand 你俘虏了我的芳心(You*re gonna wish you never had met me) （你会祈祷 要是从未遇见我该有多好）But you played it 但是你玩弄它You played it 你玩弄它You played it 你玩弄它You played it to the beat 你玩弄它 伴着[]http://[/url]
1楼 21:48&|
贴吧贡献榜 登录百度帐号推荐应用
内&&容:使用签名档&&
为兴趣而生，贴吧更懂你。&或推荐到广播
255450 人聚集在这个小组
(夜猫神的多巴胺)
(zz怪兽麻麻)
手机扫描二维码，把小组装进口袋
第三方登录：