Hello!
Once again, it is amazing to see so many people really using the information I am sending you, and taking action, practicing the scripts and finding SQL statements and their SQL_ID!
Today I've got both, an awesome free training for you and a little tease-preview as to what is coming tomorrow!
From the previous 2 emails, you got the script that finds the SQL Statements, and then you got the script that gives you the full SQL text, which will help you identify the exact SQL_ID.
With this information now in hand, you can easily identify the execution plan of the SQL Statement you are troubleshooting! Which brings you so much closer
to identifying the problem with the SQL Statement!
No more waiting, here is the script to identify the execution plan of the SQL Statement. You can also download the pdf here.
****************************************************************************************
--> Script to display the execution plan of an SQL Statement
-->
--> Parameters: sql_id
--> To Run: @xplan.sql sql_id
--> Example: @xplan.sql 27b3qfm5x89xn
-->
--> Copyright 2020@The Ultimate SQL Tuning Formula
define sqlid=&1
set linesize 200
set pagesize 200
col sql_id for A15
col child_number for 9999999999999
col plan_hash_value for 9999999999999
col elapsed for
col plan_table_output for A100
select SQL_ID
, CHILD_NUMBER
, plan_hash_value
, round(ELAPSED_TIME/1000000/greatest(EXECUTIONS,1),3) ELAPSED
, round(CPU_TIME/1000000/greatest(EXECUTIONS,1),3) CPU
, EXECUTIONS "EXEC"
, BUFFER_GETS/greatest(EXECUTIONS,1) lio
, DISK_READS/greatest(EXECUTIONS,1) pio
, ROWS_PROCESSED/greatest(EXECUTIONS,1) NUM_ROWS
--, SQL_TEXT
from v$sql
where sql_id ='&sqlid'
order by sql_id, child_number
/
-- Display the execution plan from the cursor cache.
select plan_table_output from table(dbms_xplan.display_cursor('&sqlid',NULL,'ADVANCED -PROJECTION -BYTES RUNSTATS_LAST'));
****************************************************************************************
--> Script to display the execution plan of an SQL Statement from AWR
-->
--> Caution: This script requires that you have License
--> for the Diagnostic and Tuning Pack
-->
--> Parameters: sql_id
--> To Run: @xplan_awr.sql sql_id
--> Example: @xplan.sql 27b3qfm5x89xn
-->
--> Copyright 2020@The Ultimate SQL Tuning Formula
define sqlid=&1
set linesize 200
set pagesize 0
select plan_table_output from table(DBMS_XPLAN.DISPLAY_AWR('&sqlid',null,null,'ALL'));
****************************************************************************************
Now, here is the big news & a tease:
I hope you have been enjoying the 3 free training emails, as the response has been absolutely overwhelming.
Tomorrow morning enrollment and an invitation to join my #1 program The Ultimate SQL Tuning Formula will be going live for a very limited time for only CAD 12.99
I only have this introductory price special for TUSTF (as the cool kinds call it) a few times a year.
The Ultimate SQL Tuning Formula will teach you all you need to know to be successful in troubleshooting performance problems in your Oracle Database!
Will you be the next one to take action?
