请教一个SQL语句的执行计划优化

显示全部楼层 · 2007-9-26 18:42:10

--追踪共享池没有绑定变量的SQL
SELECT X.*
FROM V$SQL X
WHERE FORCE_MATCHING_SIGNATURE IN
(
SELECT FORCE_MATCHING_SIGNATURE
FROM V$SQL
WHERE FORCE_MATCHING_SIGNATURE > 0
AND EXECUTIONS > 0
AND FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(1) > 1
)
该语句大家都比较喜欢的嘻嘻! 当我执行起来好慢哦!
执行计划大概如下重要的
SELECT
CPU COST=1203,6709
FILTER
FILTER PREDICATES= EXISTS(SELECT 0 FROM SYS.X$KGLCURSOR_CHILD WHERE KGLOBT49>0 AND ...)
FIXEDTABLE FULL CPU COST=38,0020
FILTER PREDICATES= INST_ID=USERENV('INSTANCE')
FILTER
HASH GROUP BY CPU COST=1165,6689
FIXED TABLE FULL CPU COST=35,5325
FILTER PREDICATES=> KGLOBT49>0 AND KGLOBT05 >0 ANDINST_ID=USERENV('INSTANCE')
如果加了提升 /*+ UNNEST*/ 就执行的很快意味着把子查询给展开了可通过上面的执行计划如何得知子查询被展开了?

千问 · 2007-9-26 18:42:10

你把新执行计划贴出来，不就知道了么

千问 · 2007-9-26 18:42:10

exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; --先收集一下统计信息.看会不会快一点.

千问 · 2007-9-26 18:42:10

在10g以上的版本可以用下面的语句:
SET pages 10000
SET linesize 250
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
(SELECT FORCE_MATCHING_SIGNATURE,
COUNT(*) cnt
FROM v$sqlarea
WHERE FORCE_MATCHING_SIGNATURE!=0
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(*) > 20
)
,
sq AS
(SELECT sql_text ,
FORCE_MATCHING_SIGNATURE,
row_number() over (partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
FROM v$sqlarea s
WHERE FORCE_MATCHING_SIGNATURE IN
(SELECT FORCE_MATCHING_SIGNATURE
FROM c
)
)
SELECT sq.sql_text ,
sq.FORCE_MATCHING_SIGNATURE,
c.cnt "unshared count"
FROM c,
sq
WHERE sq.FORCE_MATCHING_SIGNATURE=c.FORCE_MATCHING_SIGNATURE
AND sq.p =1
ORDER BY c.cnt DESC--试一下这个语句,会不会快一点.

千问 · 2007-9-26 18:42:10

abao2000521 发表于 2015-7-3 16:28
在10g以上的版本可以用下面的语句:
SET pages 10000
SET linesize 250
谢谢其实我关注的是 SQL的执行计划