当前位置：首页 > news >正文

Oracle如何定位硬解析高的语句？

news 2025/7/10 7:15:19

==查询subpool 情况

select KSMDSIDX supool,round(sum(KSMSSLEN)/1024/1024,2) SQLA_size_mb  
from x$ksmss  
where KSMDSIDX<>0  
and KSMSSNAM='SQLA'  
group by KSMDSIDX;

==查询subpool top5

 SELECT *FROM (SELECT KSMDSIDX subpool,KSMSSNAM name,ROUND(KSMSSLEN / 1024 / 1024 / 1024, 2) compent_size_gb,ROW_NUMBER() OVER(PARTITION BY KSMDSIDX ORDER BY KSMSSLEN DESC) RANKFROM x$ksmss) tWHERE t.RANK < 6AND subpool > 0ORDER BY t.subpool, -t.compent_size_gb;

==监控 size>10m的sql cursor

Select sysdate,sql_text,sql_id,sharable_mem from v$sqlarea where sharable_mem > 10000000 order by sharable_mem;

==找硬解析次数大于200的语句

With c As(Select inst_id,force_matching_signature,round(Sum(sharable_mem) / 1024 / 1024, 2) shared_mb,Max(sql_id) As max_sql_id,Count(*) cntFrom gv$sqlareaWhere force_matching_signature != 0Group By inst_id, force_matching_signatureHaving Count(*) > = 200),
sq As(Select inst_id,sql_text,plan_hash_value,force_matching_signature,row_number() over(Partition By inst_id, force_matching_signature, plan_hash_value Order By inst_id, sql_id Desc) pFrom gv$sqlarea sWhere force_matching_signature In(Select force_matching_signature From c))
Select Sysdate,inst_id,max_sql_id               As "sql_id",plan_hash_value,shared_mb                As "shared mem(MB)",force_matching_signature,cnt                      As "sql_count",rank,sql_textFrom (Select c.inst_id,sq.sql_text,c.max_sql_id,sq.plan_hash_value,sq.force_matching_signature,c.shared_mb,c.cnt,row_number() over(Partition By c.inst_id Order By c.inst_id, c.shared_mb Desc, c.cnt Desc) rankFrom c, sqWhere sq.force_matching_signature = c.force_matching_signatureAnd sq.inst_id = c.inst_idAnd sq.p = 1Order By inst_id, c.shared_mb Desc, c.cnt Desc) tWhere t.rank <= 20;