Getting Your Plans via SQL_ID

thatjeffsmith SQL Developer 0 Comments

Tell Others About This Story:

So of course you can just run a query in the worksheet to get this. But, you need to know a few things. So why not make it easy on yourself and code it into SQL Developer as a report?

So that’s what I’ve done as an example:

Top Grid Data coming from V$SQL.

Top Grid Data coming from V$SQL.

So, if you KNOW your SQL_ID, you can click on the SQL_ID field in the column header, and paste the SQL_ID to filter on that text. The bottom grid auto-populates on the top row, so you won’t need to click on anything after that to read the plan.

Here’s the query for the top bits – note I’m hard-filtering out certain schemas:

SELECT EXECUTIONS,
       PARSING_SCHEMA_NAME WHO,
       SQL_ID,
       CHILD_NUMBER,
       SUBSTR(SQL_TEXT, 0, 55) || ' ...',
       FIRST_LOAD_TIME
FROM V$SQL
WHERE PARSING_SCHEMA_NAME NOT IN ('SYS', 'CTXSYS', 'XDB', 'APEX_050000', 'ORDS_METADATA') AND
UPPER(SUBSTR(SQL_TEXT, 0, 8)) NOT LIKE '%DECLARE%' AND
UPPER(SUBSTR(SQL_TEXT, 0, 8)) NOT LIKE '%BEGIN%'
ORDER BY executions DESC

Hot Tip: Use a fixed width font for the Code Editors so the plan text lines up nicely!

I created another child report to just show the query itself.

This is a DBMS_OUTPUT style report.

This is a DBMS_OUTPUT style report.

Here’s the code behind this report:

DECLARE
code CLOB;
 
BEGIN
SELECT regexp_replace(sql_text, '( ){2,}', '<br>') INTO code
FROM v$sqlarea
WHERE sql_id = :SQL_ID;
 
dbms_output.put_line('<FONT SIZE=12>');
dbms_output.put_line(code);
END;

Now Let’s Talk About SQLcl

I’m lazy. Just show me the plan for the last query I ran.

This is easy with DBMS_XPLAN.

But remember, I’m lazy.

So I aliased this as ‘plan.’

I just have to run 'plan' now.

I just have to run ‘plan’ now.

I did this by running, first:

[email protected]?? >alias plan= SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR)

Or, if you KNOW the SQL_ID and CHILD_NUMBER…

You pass in the values in order that they appear in the aliased command.

You pass in the values in order that they appear in the aliased command.

[email protected]?? >alias plan2=SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:ID, :CHILD))
  2* /
[email protected]?? >plan2 'b6ty4f8a3has5' 1
 
Command=plan2
 
PLAN_TABLE_OUTPUT                                                                                                                             
SQL_ID  b6ty4f8a3has5, child NUMBER 1                                                                                                         
-------------------------------------                                                                                                         
SELECT  signature,TYPE  FROM    all_identifiers  WHERE   line = :1                                                                            
AND :2 BETWEEN col AND col + LENGTH(name)      AND object_name = :3 AND                                                                       
owner = :4      AND name        = :5      AND object_type = :6                                                                                
 
Plan hash VALUE: 363803733                                                                                                                    
 
--------------------------------------------------------------------------------------------------------------------------------------------  
| Id  | Operation                        | Name       | ROWS  | Bytes | Cost (%CPU)| TIME     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |  
--------------------------------------------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                 |            |       |       |     1 (100)|          |       |       |        |      |            |  
|*  1 |  FILTER                          |            |       |       |            |          |       |       |        |      |            |  
|   2 |   PX COORDINATOR                 |            |       |       |            |          |       |       |        |      |            |  
|   3 |    PX SEND QC (RANDOM)           | :TQ10000   |     1 |   287 |     1 (100)| 00:00:01 |       |       |  Q1,00 | P->S | QC (RAND)  |  
 
PLAN_TABLE_OUTPUT                                                                                                                             
|   4 |     PX PARTITION LIST ALL        |            |     1 |   287 |     1 (100)| 00:00:01 |     1 |     2 |  Q1,00 | PCWC |            |  
|*  5 |      FIXED TABLE FULL            | X$COMVW$   |     1 |   287 |     1 (100)| 00:00:01 |       |       |  Q1,00 | PCWP |            |  
|   6 |   NESTED LOOPS SEMI              |            |     1 |    15 |     2   (0)| 00:00:01 |       |       |        |      |            |  
|*  7 |    FIXED TABLE FULL              | X$KZSRO    |     2 |    12 |     0   (0)|          |       |       |        |      |            |  
|*  8 |    INDEX RANGE SCAN              | I_SYSAUTH1 |     1 |     9 |     1   (0)| 00:00:01 |       |       |        |      |            |  
|   9 |   NESTED LOOPS SEMI              |            |     1 |    18 |     2   (0)| 00:00:01 |       |       |        |      |            |  
|* 10 |    FIXED TABLE FULL              | X$KZSRO    |     2 |    12 |     0   (0)|          |       |       |        |      |            |  
|* 11 |    INDEX RANGE SCAN              | I_OBJAUTH1 |     1 |    12 |     1   (0)| 00:00:01 |       |       |        |      |            |  
|  12 |   NESTED LOOPS SEMI              |            |     1 |    72 |     4   (0)| 00:00:01 |       |       |        |      |            |  
|  13 |    MERGE JOIN CARTESIAN          |            |     1 |    60 |     3   (0)| 00:00:01 |       |       |        |      |            |  
|  14 |     NESTED LOOPS                 |            |     1 |    54 |     3   (0)| 00:00:01 |       |       |        |      |            |  
|  15 |      TABLE ACCESS BY INDEX ROWID | USER$      |     1 |    17 |     1   (0)| 00:00:01 |       |       |        |      |            |  
|* 16 |       INDEX UNIQUE SCAN          | I_USER1    |     1 |       |     0   (0)|          |       |       |        |      |            |  
|* 17 |      INDEX RANGE SCAN            | I_OBJ2     |     1 |    37 |     2   (0)| 00:00:01 |       |       |        |      |            |  
|  18 |     BUFFER SORT                  |            |     2 |    12 |     1   (0)| 00:00:01 |       |       |        |      |            |  
 
PLAN_TABLE_OUTPUT                                                                                                                             
|* 19 |      FIXED TABLE FULL            | X$KZSRO    |     2 |    12 |     0   (0)|          |       |       |        |      |            |  
|* 20 |    INDEX RANGE SCAN              | I_OBJAUTH1 |   276 |  3312 |     1   (0)| 00:00:01 |       |       |        |      |            |  
|  21 |   NESTED LOOPS SEMI              |            |     1 |    53 |     4   (0)| 00:00:01 |       |       |        |      |            |  
|  22 |    MERGE JOIN CARTESIAN          |            |     1 |    41 |     3   (0)| 00:00:01 |       |       |        |      |            |  
|  23 |     NESTED LOOPS                 |            |     1 |    35 |     3   (0)| 00:00:01 |       |       |        |      |            |  
|  24 |      NESTED LOOPS                |            |     1 |    27 |     2   (0)| 00:00:01 |       |       |        |      |            |  
|  25 |       TABLE ACCESS BY INDEX ROWID| USER$      |     1 |    17 |     1   (0)| 00:00:01 |       |       |        |      |            |  
|* 26 |        INDEX UNIQUE SCAN         | I_USER1    |     1 |       |     0   (0)|          |       |       |        |      |            |  
|  27 |       TABLE ACCESS BY INDEX ROWID| TRIGGER$   |     1 |    10 |     1   (0)| 00:00:01 |       |       |        |      |            |  
|* 28 |        INDEX UNIQUE SCAN         | I_TRIGGER2 |     1 |       |     0   (0)|          |       |       |        |      |            |  
|* 29 |      INDEX RANGE SCAN            | I_OBJ1     |     1 |     8 |     1   (0)| 00:00:01 |       |       |        |      |            |  
|  30 |     BUFFER SORT                  |            |     2 |    12 |     2   (0)| 00:00:01 |       |       |        |      |            |  
|* 31 |      FIXED TABLE FULL            | X$KZSRO    |     2 |    12 |     0   (0)|          |       |       |        |      |            |  
|* 32 |    INDEX RANGE SCAN              | I_OBJAUTH1 |     1 |    12 |     1   (0)| 00:00:01 |       |       |        |      |            |  
--------------------------------------------------------------------------------------------------------------------------------------------  
 
PLAN_TABLE_OUTPUT                                                                                                                   
 
Predicate Information (IDENTIFIED BY operation id):                                                                                 
---------------------------------------------------                                                                                 
 
   1 - FILTER((INTERNAL_FUNCTION("OWNER") OR  IS NOT NULL OR (INTERNAL_FUNCTION("OBJECT_TYPE#") AND  IS NOT NULL) OR                
              (INTERNAL_FUNCTION("OBJECT_TYPE#") AND  IS NOT NULL) OR ("OBJECT_TYPE#"=12 AND  IS NOT NULL)))                        
   5 - FILTER(("LINE"=:1 AND "OBJECT_NAME"=:3 AND "OWNER"=:4 AND "NAME"=:5 AND "OBJECT_TYPE"=:6 AND "COL"<=:2 AND                   
              "COL"+LENGTH("NAME")>=:2))                                                                                            
   7 - FILTER(("CON_ID"=0 OR "CON_ID"=3))                                                                                           
   8 - access("GRANTEE#"="KZSROROL")                                                                                                
       FILTER(((INTERNAL_FUNCTION("PRIVILEGE#") AND (:B1=7 OR :B2=8 OR :B3=9)) OR (:B4=13 AND INTERNAL_FUNCTION("PRIVILEGE#")) OR   
              (:B5=12 AND INTERNAL_FUNCTION("PRIVILEGE#")) OR (:B6=11 AND INTERNAL_FUNCTION("PRIVILEGE#")) OR (:B7=14 AND           
              INTERNAL_FUNCTION("PRIVILEGE#")) OR (:B8=22 AND INTERNAL_FUNCTION("PRIVILEGE#"))))                                    
  10 - FILTER(("CON_ID"=0 OR "CON_ID"=3))                                                                                           
  11 - access("OBJ#"=OBJ_ID(:B1,:B2,:B3,:B4) AND "GRANTEE#"="KZSROROL")                                                             
 
PLAN_TABLE_OUTPUT                                                                                                         
       FILTER(("GRANTEE#"="KZSROROL" AND INTERNAL_FUNCTION("PRIVILEGE#")))                                                
  16 - access("U"."NAME"=:B1)                                                                                             
  17 - access("SPECOBJ"."OWNER#"="U"."USER#" AND "SPECOBJ"."NAME"=:B1 AND "SPECOBJ"."TYPE#"=DECODE(:B2,11,9,14,13,NULL))  
       FILTER("SPECOBJ"."TYPE#"=DECODE(:B1,11,9,14,13,NULL))                                                              
  19 - FILTER(("CON_ID"=0 OR "CON_ID"=3))                                                                                 
  20 - access("OA"."OBJ#"="SPECOBJ"."OBJ#" AND "OA"."GRANTEE#"="KZSROROL" AND "OA"."PRIVILEGE#"=26)                       
       FILTER(("OA"."PRIVILEGE#"=26 AND "OA"."GRANTEE#"="KZSROROL"))                                                      
  26 - access("U"."NAME"=:B1)                                                                                             
  28 - access("T"."OBJ#"=OBJ_ID(:B1,:B2,12,:B3))                                                                          
  29 - access("TABOBJ"."OBJ#"="T"."BASEOBJECT" AND "TABOBJ"."OWNER#"="U"."USER#")                                         
  31 - FILTER(("CON_ID"=0 OR "CON_ID"=3))                                                                                 
  32 - access("OA"."OBJ#"="TABOBJ"."OBJ#" AND "OA"."GRANTEE#"="KZSROROL" AND "OA"."PRIVILEGE#"=26)                        
       FILTER(("OA"."PRIVILEGE#"=26 AND "OA"."GRANTEE#"="KZSROROL"))                                                      
 
 
 
 74 ROWS selected

Summary: When It Hurts, BE LAZY.

Create a report. Alias a command.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Leave a Reply

Your email address will not be published. Required fields are marked *