2017-08-22

Tables that a query is touching

Here is a query that tells tables that a query is using. Give a sql_id as a parameter.
  select distinct t.owner, t.table_name, t.degree, t.num_rows, t.last_analyzed, t.partitioned
    from v$sql_plan p, dba_tables t 
   where p.sql_id = :sql_id  
     and (p.object_owner,p.object_name) in (
            select t.owner,t.table_name 
              from dual
            union all
            select owner,index_name
              from dba_indexes i
             where i.table_owner = t.owner 
               and i.table_name = t.table_name )
   order by t.owner, t.table_name
;

Earlier I have posted SQL queries to find
-Tables that a view or a procedure is using: http://rafudb.blogspot.fi/2013/04/tables-used.html
-Information about queries that have been touching a table. Diagnostics pack needed http://rafudb.blogspot.fi/2014/11/ash-mining-slow-queries.html

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.