Hello,
First of all, I want to thank you for being part of the DBAParadise community!
You can download it here!
Did I mention that I have more cool stuff to help you and teach you!
Would you like to know how to determine the SQL Text of the SQL Statement that is causing the performance issue?
Sometimes it could be a challenge to determine what the SQL Text is, because the user doesn't know it either, or because the statement is generated by the application, and is run through the application.
Wouldn't it be awesome to know exactly what to do, how to approach this challenge?
As promised, I am sending your way, my methodology of determining the SQL Text of the SQL Statement and ALL the details you need with it!
As a recap, the first question to ask is: "What is the text of the SQL Statement that is slow?"
Initially the answer to this question is unknown. You have to connect the dots between what the user is running: report, script, application, and what SQL is running in the database.
The user might know the report name, or some of the table names the report is touching. This is a great starting point!
If the user is sharing the report name, or script name, you can open the report or script, or ask the user to send you the content of it, and you have the SQL Text of the report.
If the user knows some of the tables in the report, then you can run a search for the SQL Text that contains those tables. You will identify a few statements.
Today I am teaching you below, how to identify those few statements that contain the tables the user mentioned. Caution! Depending on the length of the statement, you might not get the full text displayed.
****************************************************************************************
--> Purpose: Script to find the SQL Statement that contains a specified string.
--> It returns the SQL_ID, SQL_TEXT and others.
--> Parameters: sql_string
--> To Run: @findsql.sql sql_string
--> Example: @findsql.sql 27b3qfm5x89xn
-->
--> Copyright 2020@The Ultimate SQL Tuning Formula
set echo off
set define '&'
set verify off
define sql_string=&1
set verify off
set feedback off
set linesize 200
set heading on
set termout on
col sql_text format a50 word_wrapped
select /* findsql */ sql_id, child_number, hash_value, address, executions, sql_text
from gv$sql
where command_type in (2,3,6,7,189)
and UPPER(sql_text) like UPPER('%&sql_string%')
and UPPER(sql_text) not like '%FINDSQL%';
****************************************************************************************
--> Purpose: Script to find the SQL Statement that contains a specified string.
--> It returns the SQL_ID, SQL_TEXT and others.
--> Caution: This script requires that you have License
--> for the Diagnostic and Tuning Pack
-->
--> Parameters: sql_string
--> To Run: @findsql_awr.sql sql_string
--> Example: @findsql_awr.sql 27b3qfm5x89xn
-->
--> Copyright 2020@The Ultimate SQL Tuning Formula
set echo off
set define '&'
set verify off
define sql_string=&1
set verify off
set feedback off
set linesize 200
set heading on
set termout on
col sql_text format a50 word_wrapped
select /* findsql */ sql_id, child_number, hash_value, address, executions, to_char(substr(sql_text,1,4000)) text
from dba_hist_sqltext
where command_type in (2,3,6,7,189)
and UPPER(sql_text) like UPPER('%&sql_string%')
and UPPER(sql_text) not like '%FINDSQL%';
****************************************************************************************
Stay tuned for the next email, that will show you how you get the exact SQL TEXT, once you got a few options today!
Until next time!
Diana
