Hello again!
You have been waiting patiently for email #2 in the free training series, right?
Or, were you like Kate, who emailed me last night and said:
"Diana! I want Email #2! I want Email #2! I want Email #2! PLEASSEEEE"
:)
First of all, wow! What a response to the first email training! It is SO great to see so many people excited about improving their skills, and learning something new, and being able to apply it right away!
Today's free training is SUPER important, it is built on yesterday's foundation.
Yesterday, you got the script findsql.sql, or the findsql_awr.sql. After you ran it with some options for table names or specific parts of a SQL statement, you got multiple SQL_IDs and partial texts displayed.
Why is it so important to identify the exact SQL Statement?
Because you need the SQL_ID of that statement and to tune the problem SQL_ID and not something else!
It is that simple!
Once you know the SQL_ID, then you can find out the Plan Hash Value (PHV), and get the execution plan!
So how do you get the exact SQL_ID?
If you got multiple SQL Statements returned by the findsql.sql script, and SQL_IDs, you must get the full text of each SQL_ID. Then you consult with the user, to determine which one is the statement that the user was running, and caused the performance problem? Once the user identifies the statement, you have your exact SQL_ID!!!
Today's free training gives you the script that returns the full text of the SQL Statement!
****************************************************************************************
--> Purpose: Script to display the SQL text for a specific SQL_ID.
--> It returns the SQL_TEXT.
--> Parameters: sql_id
--> To Run: @sqltext.sql sql_id
--> Example: @sqltext.sql 27b3qfm5x89xn
-->
--> Copyright 2020@The Ultimate SQL Tuning Formula
set echo off
set define '&'
set verify off
define _sql_id=&1
set verify off
set feedback off
set linesize 200
set heading on
set termout on
col sqltext format a100 word_wrapped
select sql_text as sqltext
from gv$sqltext
where sql_id = '&_sql_id'
order by piece;
****************************************************************************************
--> Purpose: Script to display the SQL text for a specific SQL_ID.
--> It returns the SQL_TEXT.
--> Caution: This script requires that you have License
--> for the Diagnostic and Tuning Pack
-->
--> Parameters: sql_id
--> To Run: @sqltext_awr.sql sql_id
--> Example: @sqltext_awr.sql 27b3qfm5x89xn
-->
--> Copyright 2020@The Ultimate SQL Tuning Formula
set echo off
set define '&'
set verify off
define _sql_id=&1
set verify off
set feedback off
set linesize 200
set heading on
set termout on
col sqltext format a100 word_wrapped
select to_char(substr(sql_text,1,4000)) sqltext from dba_hist_sqltext where sql_id='&_sql_id';
****************************************************************************************
Stay tuned for the next email in this series, that will show you how you get the execution plan of the SQL Statement!
You can also download a pdf version of these scripts!
Until next time!
Diana
