SQL, Explain-Plan, Show me the Data, automated

 TL;DR: Find relevant info about the data behind an SQL: easy, and automated.

OK, so you found the SQL-statement that seems to cause problems. From Statspack or AWR, or just from v$sqlarea. You got the stmnt, the sql_id, and probably the explain-plan(s) using dbms_xplan. In earlier blogs I also showed you how to check on the history of an sql_id (from dba_hist_sqlstat) and how to find the bind-variables (from v$sql_bind_capture).

How about we also do some inspection of the data in the tables and indexes? And how about we automate that job ? 

Take this (simple) SQL stmnt as an example: 

select e.ename, e.hiredate, d.dname, d.loc 
from emp e, dept d 
where 1=1 
and e.deptno = d.deptno 
and e.ename = :vc_ename 
order by e.ename, d.dname; 

In practice, problem-queries may fetch data form 10+ tables but we'll keep this example small. 

We can use dbms_xplan to show the explain:

-----------------------------------------------------------------
| Id  | Operation                      | Name      | Cost (%CPU)|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |            |
|   1 |  SORT ORDER BY                 |           |     0   (0)|
|   2 |   NESTED LOOPS                 |           |            |
|   3 |    NESTED LOOPS                |           |            |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP       |            |
|*  5 |      INDEX RANGE SCAN          | EMP_ENAME |            |
|*  6 |     INDEX UNIQUE SCAN          | PK_DEPT   |            |
|   7 |    TABLE ACCESS BY INDEX ROWID | DEPT      |            |
-----------------------------------------------------------------

But we can take it a few steps further: we know the plan is stored in v$sql_plan. So let's combine v$sql_plan with the information in dba_tables and dba_indexes:

column obj_own_name     format A30
column last_analyzed    format A20
column est_rows         format 999,999
column est_keys         format 999,999

select p.object_owner || '.' || p.object_name || ' (T)' as obj_own_name
, to_char ( t.last_analyzed, 'YYYY-MON-DD HH24:MI:SS' ) as last_analyzed
, t.num_rows est_rows
from v$sql_plan p
   , dba_tables t
where 1=1
and p.object_owner = t.owner
and p.object_name = t.table_name
and sql_id = '&1'
and p.object_type like 'TAB%'
order by p.position ;

select p.object_owner || '.' || p.object_name
|| case i.uniqueness
   when 'UNIQUE' then
        ' (UNQ)'
   else ' (IDX)'
end  as obj_own_name
, to_char ( i.last_analyzed, 'YYYY-MON-DD HH24:MI:SS' ) as last_analyzed
, i.distinct_keys est_keys
from v$sql_plan p
   , dba_indexes i
where 1=1
and p.object_owner = i.owner
and p.object_name = i.index_name
and sql_id = '&1'
and p.object_type like 'IND%'
order by p.position ;

These queries will display the relevant statistics (number of rows and number of keys). Note that the statistics can be out of date, hence we also generated the count-queries:

OBJ_OWN_NAME		       LAST_ANALYZED	    EST_ROWS
------------------------------ -------------------- --------
SCOTT.EMP (T)		       2023-JUL-23 06:38:30	  14
SCOTT.DEPT (T)		       2023-JUL-23 06:38:30	   4

OBJ_OWN_NAME		       LAST_ANALYZED	    EST_KEYS
------------------------------ -------------------- --------
SCOTT.EMP_ENAME (IDX)	       2023-JUL-23 06:38:30	  14
SCOTT.PK_DEPT (UNQ)	       2023-JUL-23 06:38:30	   4

-- Generated count-queries ready to copy/paste
select ' SCOTT.DEPT  :' || to_char ( count ('x' ), '9,999,999,999' ) from SCOTT.DEPT;
select ' SCOTT.EMP   :' || to_char ( count ('x' ), '9,999,999,999' ) from SCOTT.EMP;

This will give you a quick insight 

Note that the dbms_xplan-advanced will provide you most of the same information, and more. But being paranoid, I can never resist to verify for myself. Sometimes the "statistics" are way off the mark, the last-analyzed may be outdated, or you may have overlooked a particularly large table in the from-clause, or some tables were possibly hidden by a view. 

Warning: Careful with count-queries.

The Count-queries are deliberately Not Executed Automatically: on some systems a thoughtless count can take minutes or even hours. But if you need them: copy/paste and run .... 

In summary: 

We combine the information in v$sql_plan with the available statistics, and we generate count-queries in case you need them. By automating this in a script, we save the copy/paste/typing effort, and we obtain a more or less standardized format of data. 

And all of this with fairly simple SQL statements...

Remember, in a good RDBMS:

Everything is SQL, and SQL is everything.

Happy Selecting !