@thatjeffsmith hi Jeff, is it possible to see execution plan just by passing sql_id somehow,i dont think so as it just show running sql plan
— Mayank (@mayankeurope) February 15, 2016
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:
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.
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 did this by running, first:
SYS@orcl?? >alias plan= SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR)
Or, if you KNOW the SQL_ID and CHILD_NUMBER…
SYS@orcl?? >alias plan2=SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(:ID, :CHILD)) 2* / SYS@orcl?? >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.
1 Comment
Don’t forget to set PAGESIZE appropriately when viewing plans 🙂