神剑山庄资源网 Design By www.hcban.com
select case when status='UNUSABLE' then 'alter index '||owner||'.'||index_name||' rebuild online compute statistics;' when to_number(degree)>1 then 'alter index /* '||degree ||' */'||owner||'.'||index_name||' noparallel;' end case from (select * from dba_indexes where degree<>‘DEFAULT') a where status='UNUSABLE' or to_number(degree)>1 and owner not in ('SYS','SYSTEM','MANAGER','WMSYS');
语句运行的速度很快,但是从statspack中发现这条语句的逻辑读单次高达26846。使用set autotrace比较了下9i和10g的执行计划和统计信息,发现9i查询这个视图的代价非常的高,而10g则有了一定的改善。在Oracle9i中,optimizer_mode默认是CHOOSE,所以查询数据字典使用了RBO,而Oracle10g则默认为ALL_ROWS,所以采用了CBO。
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production PL/SQL Release 9.2.0.6.0 - Production CORE 9.2.0.6.0 Production TNS for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production NLSRTL Version 9.2.0.6.0 - Production SQL> set autot trace SQL> select * from dba_indexes; 1242 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS (OUTER) 2 1 NESTED LOOPS (OUTER) 3 2 NESTED LOOPS 4 3 NESTED LOOPS 5 4 NESTED LOOPS (OUTER) 6 5 NESTED LOOPS 7 6 NESTED LOOPS (OUTER) 8 7 NESTED LOOPS 9 8 TABLE ACCESS (FULL) OF 'OBJ$' 10 8 TABLE ACCESS (BY INDEX ROWID) OF 'IND$' 11 10 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE) 12 7 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 13 12 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) 14 6 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$' 15 14 INDEX (UNIQUE SCAN) OF 'I_OBJ1' (UNIQUE) 16 5 TABLE ACCESS (CLUSTER) OF 'USER$' 17 16 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE) 18 4 TABLE ACCESS (CLUSTER) OF 'USER$' 19 18 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE) 20 3 TABLE ACCESS (CLUSTER) OF 'USER$' 21 20 INDEX (UNIQUE SCAN) OF 'I_USER#' (NON-UNIQUE) 22 2 TABLE ACCESS (CLUSTER) OF 'SEG$' 23 22 INDEX (UNIQUE SCAN) OF 'I_FILE#_BLOCK#' (NON-UNIQUE) 24 1 TABLE ACCESS (CLUSTER) OF 'TS$' 25 24 INDEX (UNIQUE SCAN) OF 'I_TS#' (NON-UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 42924 consistent gets 0 physical reads 0 redo size 98000 bytes sent via SQL*Net to client 1558 bytes received via SQL*Net from client 84 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1242 rows processed
SQL>select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio NLSRTL Version 10.2.0.4.0 - Production 21:32:11 SYS@coll>set autot trace 21:32:15 SYS@coll>select * from dba_indexes; 1162 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3901056803 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1164 | 330K| 237 (3)| 00:00:03 | |* 1 | HASH JOIN RIGHT OUTER | | 1164 | 330K| 237 (3)| 00:00:03 | | 2 | TABLE ACCESS FULL | TS$ | 9 | 189 | 4 (0)| 00:00:01 | |* 3 | HASH JOIN RIGHT OUTER | | 1164 | 306K| 232 (3)| 00:00:03 | | 4 | TABLE ACCESS FULL | SEG$ | 2635 | 102K| 18 (0)| 00:00:01 | |* 5 | HASH JOIN | | 1164 | 261K| 214 (3)| 00:00:03 | | 6 | TABLE ACCESS FULL | USER$ | 35 | 560 | 2 (0)| 00:00:01 | |* 7 | HASH JOIN | | 1164 | 243K| 211 (3)| 00:00:03 | | 8 | TABLE ACCESS FULL | USER$ | 35 | 560 | 2 (0)| 00:00:01 | |* 9 | HASH JOIN RIGHT OUTER | | 1164 | 225K| 208 (2)| 00:00:03 | | 10 | TABLE ACCESS FULL | USER$ | 35 | 560 | 2 (0)| 00:00:01 | |* 11 | HASH JOIN | | 1164 | 206K| 206 (2)| 00:00:03 | |* 12 | HASH JOIN OUTER | | 1164 | 172K| 174 (2)| 00:00:03 | | 13 | MERGE JOIN | | 1164 | 142K| 142 (2)| 00:00:02 | |* 14 | TABLE ACCESS BY INDEX ROWID| IND$ | 1164 | 104K| 109 (0)| 00:00:02 | | 15 | INDEX FULL SCAN | I_IND1 | 1164 | | 2 (0)| 00:00:01 | |* 16 | SORT JOIN | | 10589 | 341K| 33 (7)| 00:00:01 | |* 17 | TABLE ACCESS FULL | OBJ$ | 10589 | 341K| 32 (4)| 00:00:01 | | 18 | TABLE ACCESS FULL | OBJ$ | 10592 | 279K| 31 (0)| 00:00:01 | | 19 | TABLE ACCESS FULL | OBJ$ | 10592 | 310K| 31 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("I"."TS#"="TS"."TS#"(+)) 3 - access("I"."FILE#"="S"."FILE#"(+) AND "I"."BLOCK#"="S"."BLOCK#"(+) AND "I"."TS#"="S"."TS#"(+)) 5 - access("IO"."OWNER#"="IU"."USER#") 7 - access("U"."USER#"="O"."OWNER#") 9 - access("ITO"."OWNER#"="ITU"."USER#"(+)) 11 - access("I"."BO#"="IO"."OBJ#") 12 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+)) 14 - filter(BITAND("I"."FLAGS",4096)=0) 16 - access("O"."OBJ#"="I"."OBJ#") filter("O"."OBJ#"="I"."OBJ#") 17 - filter(BITAND("O"."FLAGS",128)=0) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 876 consistent gets 0 physical reads 0 redo size 92582 bytes sent via SQL*Net to client 1339 bytes received via SQL*Net from client 79 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1162 rows processed select /*+ rule */* from dba_indexes; 1162 rows selected. Elapsed: 00:00:00.55 Execution Plan ---------------------------------------------------------- Plan hash value: 2107813288 -------------------------------------------------------------- | Id | Operation | Name | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | | 1 | NESTED LOOPS OUTER | | | 2 | NESTED LOOPS OUTER | | | 3 | NESTED LOOPS | | | 4 | NESTED LOOPS | | | 5 | NESTED LOOPS OUTER | | | 6 | NESTED LOOPS | | | 7 | NESTED LOOPS OUTER | | | 8 | NESTED LOOPS | | |* 9 | TABLE ACCESS FULL | OBJ$ | |* 10 | TABLE ACCESS BY INDEX ROWID| IND$ | |* 11 | INDEX UNIQUE SCAN | I_IND1 | | 12 | TABLE ACCESS BY INDEX ROWID | OBJ$ | |* 13 | INDEX UNIQUE SCAN | I_OBJ1 | | 14 | TABLE ACCESS BY INDEX ROWID | OBJ$ | |* 15 | INDEX UNIQUE SCAN | I_OBJ1 | | 16 | TABLE ACCESS CLUSTER | USER$ | |* 17 | INDEX UNIQUE SCAN | I_USER# | | 18 | TABLE ACCESS CLUSTER | USER$ | |* 19 | INDEX UNIQUE SCAN | I_USER# | | 20 | TABLE ACCESS CLUSTER | USER$ | |* 21 | INDEX UNIQUE SCAN | I_USER# | | 22 | TABLE ACCESS CLUSTER | SEG$ | |* 23 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | | 24 | TABLE ACCESS CLUSTER | TS$ | |* 25 | INDEX UNIQUE SCAN | I_TS# | -------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 9 - filter(BITAND("O"."FLAGS",128)=0) 10 - filter(BITAND("I"."FLAGS",4096)=0) 11 - access("O"."OBJ#"="I"."OBJ#") 13 - access("I"."INDMETHOD#"="ITO"."OBJ#"(+)) 15 - access("I"."BO#"="IO"."OBJ#") 17 - access("ITO"."OWNER#"="ITU"."USER#"(+)) 19 - access("U"."USER#"="O"."OWNER#") 21 - access("IO"."OWNER#"="IU"."USER#") 23 - access("I"."TS#"="S"."TS#"(+) AND "I"."FILE#"="S"."FILE#"(+) AND "I"."BLOCK#"="S"."BLOCK#"(+)) 25 - access("I"."TS#"="TS"."TS#"(+)) Note ----- - rule based optimizer used (consider using cbo) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 25254 consistent gets 26 physical reads 0 redo size 93977 bytes sent via SQL*Net to client 1339 bytes received via SQL*Net from client 79 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1162 rows processed
神剑山庄资源网 Design By www.hcban.com
神剑山庄资源网
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件!
如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
神剑山庄资源网 Design By www.hcban.com
暂无dba_indexes视图的性能分析的评论...
《魔兽世界》大逃杀!60人新游玩模式《强袭风暴》3月21日上线
暴雪近日发布了《魔兽世界》10.2.6 更新内容,新游玩模式《强袭风暴》即将于3月21 日在亚服上线,届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。
艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕,并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时,他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中,玩家将会进入一个全新的海盗主题大逃杀式限时活动,其中包含极高的风险和史诗级的奖励。
《强袭风暴》不是普通的战场,作为一个独立于主游戏之外的活动,玩家可以用大逃杀的风格来体验《魔兽世界》,不分职业、不分装备(除了你在赛局中捡到的),光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式,玩家在加入海盗主题的预赛大厅区域前,可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹,《巨龙崛起》和《魔兽世界:巫妖王之怒 经典版》的玩家都可以获得奖励。
更新日志
2024年09月08日
2024年09月08日
- 人听人爱,入耳即溶《天碟落地3《HIFI瑰宝》示范颠峰之作》[WAV+CUE/分轨][839.9MB]
- 莫扎特安魂曲舍尔琴,维也纳国家歌剧院,1958【FLAC】
- 罗西尼五部歌剧序曲班贝格佩雷亚1958年【FLAC】
- 【HIRES】李云迪-莫扎特:钢琴奏鸣曲计划-萨尔兹堡-2024-24-96FLAC分轨
- 珍娜JheenaLodwick-GettingToKnowYou(XRCD)[WAV+CUE]
- 珍纳JheenaLodwick-AllMyLoving全我所爱(XRCD)[WAV+CUE]
- JheenaLodwick-Feeling挚爱(XRCD)[WAV+CUE]
- 人听人爱,入耳即溶《天碟落地2《测机王HQ》日本母带发烧音质》[WAV+CUE/分轨][709.9MB]
- 人听人爱,入耳即溶《天碟落地1《示范碟皇》殿堂级演绎名作》[WAV+CUE/分轨][786.9MB]
- MICO刘为《观·自在行》CD[FLAC/分轨][1.3GB]
- 珍纳JheenaLodwick-SingingInTheRain(XRCD)[WAV+CUE]
- 珍纳JheenaLodwick-Feelings非常感觉2-HDCD[WAV+CUE]
- 珍纳精选JheenaLodwick-JheenaLodwickGreatest[香港版]2015[WAV+CUE]
- 谁杀死了HI-FI音乐-《涂鸦精选VOL.2》[WAV+CUE]
- Mav-Mav《美芙的祈祷》(2000)[WAV+CUE]