whatsqlid and whatsqlplan
whatsqlid searches SQL-History and searches for a TableName/Index etc.
whatsqlid searches SQL-History for any recently executed SQL-Statement.
Normally SQL-History can warehouse SQL-Statements for a full day or more.
Once the sqlid is identified, then the whatsqlplan script is executed.
whatsqlplan creates and displays the SQL-Explain-Plan on the SQL that was executed.
Example: whatsqlid
19-JUN-13 OATC (oratest) SQLText SQLID
14:54:04
LastActive
Date Time SQL Hash-ID
---------- --------------
20-JUN-13 55dc767ajydh3
18:58:48
Example: whatsqplan
OATC (oratest) SQLText SQLID
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID 55dc767ajydh3, child number 0
-------------------------------------
SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTION_VALUES WHERE
PROFILE_OPTION_ID = :B4 AND APPLICATION_ID = :B3 AND LEVEL_ID = 10003
AND LEVEL_VALUE = :B2 AND LEVEL_VALUE_APPLICATION_ID = :B1 AND
PROFILE_OPTION_VALUE IS NOT NULL
Plan hash value: 2802907561
| Id | Operation | Name |Rows|Bytes|Cost (%CPU)|Time|
-----------------------------------------------------------------------------------------
| 0 |SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS BY INDEX|FND_PROFILE_OPTION_VALUES | 1 | 25 | 3 (0)|00:01 |
|* 2 | INDEX RANGE SCAN |FND_PROFILE_OPTION_VALUES_U1 | 1 | | 2 (0)|00:01 |
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PROFILE_OPTION_VALUE" IS NOT NULL)
2 - access("APPLICATION_ID"=:B3 AND "PROFILE_OPTION_ID"=:B4 AND "LEVEL_ID"=10003 AND
"LEVEL_VALUE"=:B2 AND "LEVEL_VALUE_APPLICATION_ID"=:B1)
#!/usr/bin/ksh
# whatsqlid ()
#----------------------------------------------------------------------#
# Module Name: whatsqlid (c) Copyright #
# #
# Purpose: Report on the v$SQL Sessions from SQL-History #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ ------------------------------------- #
# 10-May-2012 M.Barone Module design/creation #
#----------------------------------------------------------------------#
{
DIRNAM=$(dirname $0)
ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
HNAME=$(uname -n)
clear
#--------------------------------------------------------------#
# TEST: Number ($#) of arguments passed to this function? #
#--------------------------------------------------------------#
if [ $# = 0 ]
then
clear
echo
echo " #--------------------------------------------------#"
echo " # whatsqlid: Oracle Apps SQLText Area SQL-ID #"
echo " #--------------------------------------------------#"
echo
read SEARCH?" Please Enter Search-SQL-Text-Statement: "
echo " Connecting as SYSDBA"
sleep 2
clear
else
SEARCH=$1
clear
fi
ORANAM=${ORASID}
#----------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#----------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE
CONNECT / as SYSDBA
clear BREAKS
clear BUFFER
clear COLUMNS
clear COMPUTES
clear SCREEN
clear SQL
clear TIMING
set feedback off
set flush on
set heading on
set pause off
set space 1
set termout on
set verify on
set linesize 80
set pagesize 999
SPOOL /tmp/whatsqlid_$$.txt
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
COLUMN sdate FORMAT A10 HEADING 'LastActive|Date Time'
COLUMN sidno FORMAT A14 HEADING 'SQL Hash-ID'
TTITLE CENTER '${ORANAM} (${HNAME}) SQLText SQLID' -
LEFT DATEVAR skip 1 -
LEFT TIMEVAR skip 2
SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
TO_CHAR(last_active_time, 'DD-MON-YY HH24:MI:SS') sdate,
sql_id sidno
FROM v\$sql
WHERE sql_text LIKE '%${SEARCH}%'
ORDER BY sdate;
EXIT;
END_FILE
print "Please see /tmp/whatsqlid_$$.txt"
}
#!/usr/bin/ksh
# whatsqlplan ()
#-----------------------------------------------------------------------#
# Module Name: whatsqlplan (c) Copyright #
# #
# Purpose: Report on the v$SQL Sessions from SQL-History #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ ------------------------------------- #
# 12-May-2012 M.Barone Module design/creation #
#-----------------------------------------------------------------------#
{
DIRNAM=$(dirname $0)
ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
HNAME=$(uname -n)
clear
#---------------------------------------------------------------#
# TEST: Number ($#) of arguments passed to this function? #
#---------------------------------------------------------------#
if [ $# = 0 ]
then
clear
echo
echo " #--------------------------------------------------#"
echo " # whatsqlplan: Oracle Apps SQLText Plan SQL-ID #"
echo " #--------------------------------------------------#"
echo
print " Please execute whatsqlid to Collect SQL-ID and SQL-No"
print
read SQLID?" Please Enter Search-SQL-ID: "
read SQLNO?" Enter Search-SQL-No: "
echo " Connecting as SYSDBA"
sleep 2
clear
else
SEARCH=$1
clear
fi
ORANAM=${ORASID}
#-----------------------------------------------------------------------#
#-----------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#-----------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE
CONNECT / as SYSDBA
clear BREAKS
clear BUFFER
clear COLUMNS
clear COMPUTES
clear SCREEN
clear SQL
clear TIMING
set feedback off
set flush on
set heading on
set pause off
set space 1
set termout on
set verify on
set linesize 80
set pagesize 999
SPOOL /tmp/whatsqlplan$$.txt
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
TTITLE CENTER '${ORANAM} (${HNAME}) SQLText SQLID' skip 2
SELECT *
FROM table(DBMS_XPLAN.DISPLAY_CURSOR('${SQLID}',${SQLNO}));
EXIT;
END_FILE
print
print "Please see /tmp/whatsqlplan$$.txt"
print
}
No comments:
Post a Comment