ITPub博客

首页 > Linux操作系统 > Linux操作系统 > dbms_xplan.display_cursor管道函数

dbms_xplan.display_cursor管道函数

原创 Linux操作系统 作者:wei-xh 时间:2011-09-19 20:42:11 0 删除 编辑

One of the questions that pops up on the internet from time to time is the one about finding SQL that’s doing full tablescans.

Since the appearance of the dynamic performance view v$sql_plan in Oracle 9i this has been quite easy – provided you can check memory soon enough. A query like the following will give you the sql_id (hash_value, if you’re still on 9i) and child_number of any query that has a full tablescan (or index fast full scan) in its execution path.

 
select
        distinct sql_id /* hash_value */ , child_number
from
        v$sql_plan
where   (operation = 'TABLE ACCESS' and ptions = 'FULL')
or      (operation = 'INDEX' and ptions = 'FAST FULL SCAN')
;

Note – just because there is a full tablescan in the plan this doesn’t mean that it will definitely happen, and you don’t necessarily know how often it might happen each time the query executes: so finding the SQL isn’t the whole story.

Once you have the sql_id (hash_value) and child_number you can always check v$sql (or v$sqlstats in 10g) for the SQL text and any other details you might want. If you’re on 10g, though, there’s an interesting variation on this theme. It’s easy (though resource-intensive on big busy systems) to use dbms_xplan to print the SQL text and execution plans for the suspect queries.

The function dbms_xplan.display_cursor() is a “pipelined function”, which means that it can behave like a table if you apply the table() operator to it; and table() operators can appear in lateral joins in Oracle. This means we can take the previous query, put it into an inline view, and (laterally) join it to dbms_xplan.display_cursor() as follows:

 
select
        plan_table_output  -- (the column of the pipelined function)
from    (
                select
                        distinct sql_id, child_number
                from
                        v$sql_plan
                where   (operation = 'TABLE ACCESS' and ptions = 'FULL')
                or      (operation = 'INDEX' and ptions = 'FAST FULL SCAN')
        ) v,
        table(dbms_xplan.display_cursor(v.sql_id, v.child_number))
;

Notice that the table() operator has to appear after the view that the pipelilned function is referencing, which alluws us to pass columns from the view into the pipelined function. This query gives us the dbms_xplan.display_cursor() output – with all the variations that allows – for every query that’s still in memory that has done a tablescan or index fast full scan.

You may find this a useful thing to run occasionally. But do be cautious – it WILL hammer the library cache for some time, especially if you have a large shared pool and a busy system.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22034023/viewspace-707896/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 伪造baseline
请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2318493