Sqldiag.sql from Cogni

==> For RAC :

#!/bin/sh

#####################################################################

# File : sqldiag.sh

# Abstract : SQL Diagnostics

# Syntax : sqldiag.sh sid sqlid [user] [format]

# sid - TNS Entry for the database

# user - DB User (if null, then OS Login will be used

# for login into the database . use xoracle for /as sysdba

# format - Valid values for format: TEXT;

# Default output format is HTML

# History : Suresh Srinivasan 12/30/09 Initial

# : Ajit Dhamale 11/03/10

# Added cursor details, why cursors not shared

# Bind variable tables

# Amit Khadilkar 08/17/2010 Added flush_monitoring_info

# Amit Khadilkar 10/24/2010 Added sql stats

# Amit Khadilkar 23/11/2010 Modified to run on RAC databases

#####################################################################

LSID=$1

LSQLID=$2

if test "x${LSID}" = "x" || test "x${LSQLID}" = "x"

then

echo "SID and SQLID are required. Syntax sqldiag.sh sid sqlid [user] [format]"

exit

LUSER=$3

if test "x${LUSER}" = "x"

then

LUSER=${USER}

LFORMAT=$4

#LSQLID='0hngxwb88wpfg'

#LSQLID='ahunvmz3m809y'

LOUTPUTFL="${LSID}_${LSQLID}_$$"

LHEAD="/tmp/${LOUTPUTFL}_hd.sql"

LTMPSQL="/tmp/${LOUTPUTFL}_tmpsql.sql"

LRUNSQL="/tmp/${LOUTPUTFL}_runsql.sql"

LOUTPUT="/tmp/${LOUTPUTFL}_tmp.html"

LFINAL="/tmp/${LOUTPUTFL}.html"

#LEMAIL="SGuharay@consultantemail.com"

LDATE=`date +"%m-%d-%Y %T`

rpthead()

{

(

formatrpt

echo "\

set echo off verify off feed off trims on pages 5000 lines 500 long 50000;

col fk_index head \"FK Index?\" format a16;

col sql_id head \"class=awrbg SQL Id\" format a20;

col plan_hash_value head \"<p class=awrbg>Plan Hash</p>\";

col object_name head \"Object\" format a30;

col column_name head \"Column\" format a30;

col pos format 999;

col column_position head \"Posn\" format 9999;

col column_length head \"Len\" format 9999;

col cardinality format 999999;

col options format a20;

col txt format a70;

col sql_text head \"SQL\" format a70;

col id like pos;

col is_bind_sensitive head \"Bind Sense?\" format a11;

col is_bind_aware head \"Bind Aware?\" format a11;

col child_number head \"Child#\" format 999999;

col child_address head \"Child Addr\" format a20;

col cn like pos;

col optimizer_cost head \"Opt Cost\" format 99999999;

col object_status head \"Status\" format a15;

col status like object_status;

col timestamp head \"Timestamp\";

col parsing_schema_name head \"Schema\" format a20;

col subobject_name head \"Partition\" format a30;

col object_id head \"Obj Id\" format 999999999999;

col data_object_id head \"Data Obj Id\" format 999999999999;

col object_type head \"Type\";

col created head \"Created\";

col last_ddl_time head \"Last DDL\";

col temporary head \"Temp?\" format a5;

col owner head \"Owner\" format a20;

col table_owner head \"Owner\" format a20;

col index_owner head \"Index Owner\" format a20;

col table_name head \"Table\" format a30;

col index_name head \"Index\" format a30;

col segment_name head \"Segment\" format a30;

col partition_name like subobject_name;

col partition_position like pos;

col segment_type like object_type;

col index_type like object_type;

col table_type like object_type;

col tablespace_name head \"Tablespace\" format a30;

col bytesmb head \"Bytes (MB)\" format \"999,999.99\"

col num_rows head \"Rows\" format \"999,999,999,999\"

col partitioned head \"Part?\" format a5;

col avg_row_len head \"Avg Row|Length\" format \"999,999\"

col avg_space head \"Avg Space\" format \"999,999,999\"

col uniqueness head \"Unique?\" format a9;

col compression head \"Compress?\" format a9;

col last_analyzed head \"Analyzed\" format a20;

col instance_name head \"Instance\" format a9;

col host_name head \"Host\" format a20;

col version head \"Version\" format a10;

col startup_time head \"Startup\";

col curr_time head \"Run Date\";

col first_load_time head \"First Load Time\" format a20;

col last_load_time head \"Last Load Time\" format a20;

col sql_plan_baseline head \"Baseline?\" format a20;

col module head \"Module\";

col action head \"Action\";

col cpu_time head \"CPU Time\";

col elapsed_time head \"Elapsed Time\";

col Invalidations Head \"Invalidations\";

col cons_columns head \"Constraint Columns\" format a50;

col constraint_name head \"Constraint Name\";

col index_columns head \"Index Columns\" format a50;

set head off;

select 'SQL Diagnostics for SQL ID ${LSQLID}' from dual;

set head on;

select instance_name, host_name, version, to_char(startup_time,'mm/dd/yyyy hh24:mi') startup_time,

to_char(sysdate,'mm/dd/yyyy hh24:mi') curr_time from gv\$instance;

unformatrpt

) > ${LHEAD}

}

formatrpt()

{

LFRMT=$1

if test "x${LFRMT}" = "x"

then

LFRMT=${LFORMAT}

if test "x${LFRMT}" = "x"

then

LOUTPUT="/tmp/${LOUTPUTFL}_tmp.html"

LFINAL="/tmp/${LOUTPUTFL}.html"

(

echo "\

set markup html on spool on preformat off entmap off HEAD '<TITLE> SQL Diagnostics for SQL_ID ${LSQLID} </TITLE> -

-

-

-

-

-

-

-

-

</style>' -

Body 'class=awr'-

TABLE 'width=500 border=1';

spool on;

spool ${LOUTPUT};

)

else

LOUTPUT="/tmp/${LOUTPUTFL}_ptmp.html"

LFINAL="/tmp/${LOUTPUTFL}.html"

(

echo "\

set markup html on spool on preformat on entmap off HEAD '<TITLE> SQL Diagnostics for SQL_ID ${LSQLID} </TITLE>' -

Body 'TEXT=black bgcolor=white'-

TABLE 'align=center width=50% border=1 cellspacing=0 bordercolor=\"#BDBDBD\" bgcolor=white';

spool ${LOUTPUT};

)

}

unformatrpt()

{

(

echo "\

spool off;

set markup html off;

host cat ${LOUTPUT} >> ${LFINAL}

)

}

runsql()

{

(

formatrpt

echo "\

select INST_ID,PLAN_HASH_VALUE,CHILD_NUMBER,CHILD_ADDRESS,OPTIMIZER_COST,OBJECT_STATUS

,PARSING_SCHEMA_NAME, CPU_TIME,ELAPSED_TIME

,IS_BIND_SENSITIVE,IS_BIND_AWARE,

NVL(SQL_PLAN_BASELINE, 'NO-SQL-PLAN-BASELINE') SQL_PLAN_BASELINE,

first_load_time,last_load_time

from gv\$sql

where SQL_ID = '${LSQLID}' order by inst_id,PLAN_HASH_VALUE,first_load_time;

select INST_ID,child_number,fetches,ROWS_PROCESSED,LAST_ACTIVE_TIME,executions,parse_calls,loads,invalidations,

DISK_READS,BUFFER_GETS,OPTIMIZER_COST,OPTIMIZER_MODE,module,action

from gv\$sql where sql_id = '${LSQLID}' order by INST_ID,child_number;

set head off

select '<<<<< Plan Hash with SQL Stats >>>>>>' from dual;

set head on;

select INST_ID,sql_id, PLAN_HASH_VALUE,LAST_ACTIVE_TIME,PARSE_CALLS,

DISK_READS,DIRECT_WRITES,BUFFER_GETS,ROWS_PROCESSED,FETCHES,EXECUTIONS,

CPU_TIME,ELAPSED_TIME,CONCURRENCY_WAIT_TIME,USER_IO_WAIT_TIME,CLUSTER_WAIT_TIME,SORTS

IO_DISK_BYTES

from gv\$SQLSTATS_PLAN_HASH where sql_id = '${LSQLID}' order by inst_id, sql_id, plan_hash_value;

set head off;

select '<<<<<<<<<<<<<<<<< Plan Hash History >>>>>>>>>>>>>>>>>' from dual;

set head on;

select distinct plan_hash_value, to_char(timestamp, 'dd-MON-yyyy hh24:mi:ss') timestamp from dba_hist_sql_plan where sql_id = '${LSQLID}' order by timestamp desc;

set head off;

select '<<<<< SQL Shared Cursors >>>>>' from dual;

set head on;

set serveroutput on;

declare

c number;

col_cnt number;

col_rec dbms_sql.desc_tab;

col_value varchar2(4000);

ret_val number;

begin

c := dbms_sql.open_cursor;

dbms_sql.parse(c,

'select s.*

from gv\$sql_shared_cursor s

where s.sql_id = ''${LSQLID}''',

dbms_sql.native);

dbms_sql.describe_columns(c, col_cnt, col_rec);

for idx in 1 .. col_cnt loop

dbms_sql.define_column(c, idx, col_value, 4000);

end loop;

ret_val := dbms_sql.execute(c);

while(dbms_sql.fetch_rows(c) > 0) loop

for idx in 1 .. col_cnt loop

dbms_sql.column_value(c, idx, col_value);

if col_rec(idx).col_name in ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS', 'CHILD_NUMBER') then

dbms_output.put_line(rpad(col_rec(idx).col_name, 30) || ' = ' || col_value);

dbms_output.new_line;

elsif col_value = 'Y' then

dbms_output.put_line(rpad(col_rec(idx).col_name, 30) || ' = ' || col_value);

dbms_output.new_line;

if col_rec(idx).col_name = 'UNBOUND_CURSOR' then

dbms_output.put_line('UNBOUND_CURSOR - The existing child cursor was not fully built in other words, it was not optimized') ;

end if;

if col_rec(idx).col_name = 'SQL_TYPE_MISMATCH' then

dbms_output.put_line('SQL_TYPE_MISMATCH - The SQL type does not match the existing child cursor');

end if;

if col_rec(idx).col_name = 'OPTIMIZER_MISMATCH' then

dbms_output.put_line('OPTIMIZER_MISMATCH - The optimizer environment does not match the existing child cursor');

end if;

if col_rec(idx).col_name = 'OUTLINE_MISMATCH' then

dbms_output.put_line('OUTLINE_MISMATCH - The outlines do not match the existing child cursor');

end if;

if col_rec(idx).col_name = 'STATS_ROW_MISMATCH' then

dbms_output.put_line('STATS_ROW_MISMATCH - The existing statistics do not match the existing child cursor');

end if;

if col_rec(idx).col_name = 'LITERAL_MISMATCH' then

dbms_output.put_line('LITERAL_MISMATCH - Non-data literal values do not match the existing child cursor');

end if;

if col_rec(idx).col_name = 'FORCE_HARD_PARSE' then

dbms_output.put_line('FORCE_HARD_PARSE - Description not available');

end if;

if col_rec(idx).col_name = 'EXPLAIN_PLAN_CURSOR' then

dbms_output.put_line('EXPLAIN_PLAN_CURSOR - The child cursor is an explain plan cursor and should not be shared');

end if;

if col_rec(idx).col_name = 'BUFFERED_DML_MISMATCH' then

dbms_output.put_line('BUFFERED_DML_MISMATCH - Buffered DML does not match the existing child cursor');

end if;

if col_rec(idx).col_name = 'PDML_ENV_MISMATCH' then

dbms_output.put_line('PDML_ENV_MISMATCH - PDML environment does not match the existing child cursor');

end if;

if col_rec(idx).col_name = 'INST_DRTLD_MISMATCH' then

dbms_output.put_line('INST_DRTLD_MISMATCH - Insert direct load does not match the existing child cursor');

end if;

if col_rec(idx).col_name = 'SLAVE_QC_MISMATCH' then

dbms_output.put_line('SLAVE_QC_MISMATCH - The existing child cursor is a slave cursor and the new one was issued by the coordinator or, the existing child cursor was issued by the coordinator and the new one is a slave cursor)');

end if;

if col_rec(idx).col_name = 'TYPECHECK_MISMATCH' then

dbms_output.put_line('TYPECHECK_MISMATCH - The existing child cursor is not fully optimized');

end if;

if col_rec(idx).col_name = 'AUTH_CHECK_MISMATCH' then

dbms_output.put_line('AUTH_CHECK_MISMATCH - Authorization/translation check failed for the existing child cursor');

end if;

if col_rec(idx).col_name = 'BIND_MISMATCH' then

dbms_output.put_line('BIND_MISMATCH - The bind metadata does not match the existing child cursor');

end if;

if col_rec(idx).col_name = 'DESCRIBE_MISMATCH' then

dbms_output.put_line('DESCRIBE_MISMATCH - The typecheck heap is not present during the describe for the child cursor');

end if;

if col_rec(idx).col_name = 'LANGUAGE_MISMATCH' then

dbms_output.put_line('LANGUAGE_MISMATCH - The language handle does not match the existing child cursor');

end if;

if col_rec(idx).col_name = 'TRANSLATION_MISMATCH' then

dbms_output.put_line('TRANSLATION_MISMATCH - The base objects of the existing child cursor do not match');

end if;

if col_rec(idx).col_name = 'INSUFF_PRIVS' then

dbms_output.put_line('INSUFF_PRIVS - Insufficient privileges on objects referenced by the existing child cursor');

end if;

if col_rec(idx).col_name = 'INSUFF_PRIVS_REM' then

dbms_output.put_line('INSUFF_PRIVS_REM - Insufficient privileges on remote objects referenced by the existing child cursor');

end if;

if col_rec(idx).col_name = 'REMOTE_TRANS_MISMATCH' then

dbms_output.put_line('REMOTE_TRANS_MISMATCH - The remote base objects of the existing child cursor do not match');

end if;

if col_rec(idx).col_name = 'LOGMINER_SESSION_MISMATCH' then

dbms_output.put_line('LOGMINER_SESSION_MISMATCH - LogMiner Session parameters mismatch');

end if;

if col_rec(idx).col_name = 'INCOMP_LTRL_MISMATCH' then

dbms_output.put_line('INCOMP_LTRL_MISMATCH - Cursor might have some binds (literals) which may be unsafe/non-data. Value mismatch.');

end if;

if col_rec(idx).col_name = 'OVERLAP_TIME_MISMATCH' then

dbms_output.put_line('OVERLAP_TIME_MISMATCH - Mismatch caused by setting session parameter ERROR_ON_OVERLAP_TIME');

end if;

if col_rec(idx).col_name = 'EDITION_MISMATCH' then

dbms_output.put_line('EDITION_MISMATCH - Description not available');

end if;

if col_rec(idx).col_name = 'MV_QUERY_GEN_MISMATCH' then

dbms_output.put_line('MV_QUERY_GEN_MISMATCH - Internal, used to force a hard-parse decode analyzing materialized view queries');

end if;

if col_rec(idx).col_name = 'USER_BIND_PEEK_MISMATCH' then

dbms_output.put_line('USER_BIND_PEEK_MISMATCH - Cursor is not shared because value of one or more user binds is different and this has a potential to change the execution plan');

end if;

if col_rec(idx).col_name = 'TYPCHK_DEP_MISMATCH' then

dbms_output.put_line('TYPCHK_DEP_MISMATCH - Cursor has typecheck dependencies');

end if;

if col_rec(idx).col_name = 'NO_TRIGGER_MISMATCH' then

dbms_output.put_line('NO_TRIGGER_MISMATCH - Cursor and child have no trigger mismatch');

end if;

if col_rec(idx).col_name = 'FLASHBACK_CURSOR' then

dbms_output.put_line('FLASHBACK_CURSOR - Cursor non-shareability due to flashback');

end if;

if col_rec(idx).col_name = 'ANYDATA_TRANSFORMATION' then

dbms_output.put_line('ANYDATA_TRANSFORMATION - Is criteria for opaque type transformation and does not match');

end if;

if col_rec(idx).col_name = 'INCOMPLETE_CURSOR' then

dbms_output.put_line('INCOMPLETE_CURSOR - Cursor is incomplete: typecheck heap came from call memory');

end if;

if col_rec(idx).col_name = 'TOP_LEVEL_RPI_CURSOR' then

dbms_output.put_line('TOP_LEVEL_RPI_CURSOR - Is top level RPI cursor');

end if;

if col_rec(idx).col_name = 'DIFFERENT_LONG_LENGTH' then

dbms_output.put_line('DIFFERENT_LONG_LENGTH - Value of LONG does not match');

end if;

if col_rec(idx).col_name = 'LOGICAL_STANDBY_APPLY' then

dbms_output.put_line('LOGICAL_STANDBY_APPLY - Logical standby apply context does not match');

end if;

if col_rec(idx).col_name = 'DIFF_CALL_DURN' then

dbms_output.put_line('DIFF_CALL_DURN - If Slave SQL cursor/single call');

end if;

if col_rec(idx).col_name = 'BIND_UACS_DIFF' then

dbms_output.put_line('BIND_UACS_DIFF - One cursor has bind UACs and one does not');

end if;

if col_rec(idx).col_name = 'PLSQL_CMP_SWITCHS_DIFF' then

dbms_output.put_line('PLSQL_CMP_SWITCHS_DIFF - PL/SQL anonymous block compiled with different PL/SQL compiler switches');

end if;

if col_rec(idx).col_name = 'CURSOR_PARTS_MISMATCH' then

dbms_output.put_line('CURSOR_PARTS_MISMATCH - Cursor was compiled with subexecution (cursor parts were executed)');

end if;

if col_rec(idx).col_name = 'STB_OBJECT_MISMATCH' then

dbms_output.put_line('STB_OBJECT_MISMATCH - STB has come into existence since cursor was compiled');

end if;

if col_rec(idx).col_name = 'CROSSEDITION_TRIGGER_MISMATCH' then

dbms_output.put_line('CROSSEDITION_TRIGGER_MISMATCH - Description not available');

end if;

if col_rec(idx).col_name = 'PQ_SLAVE_MISMATCH' then

dbms_output.put_line('PQ_SLAVE_MISMATCH - Top-level slave decides not to share cursor');

end if;

if col_rec(idx).col_name = 'TOP_LEVEL_DDL_MISMATCH' then

dbms_output.put_line('TOP_LEVEL_DDL_MISMATCH - Is top-level DDL cursor');

end if;

if col_rec(idx).col_name = 'MULTI_PX_MISMATCH' then

dbms_output.put_line('MULTI_PX_MISMATCH - Cursor has multiple parallelizers and is slave-compiled');

end if;

if col_rec(idx).col_name = 'BIND_PEEKED_PQ_MISMATCH' then

dbms_output.put_line('BIND_PEEKED_PQ_MISMATCH - Cursor based around bind peeked values');

end if;

if col_rec(idx).col_name = 'MV_REWRITE_MISMATCH' then

dbms_output.put_line('MV_REWRITE_MISMATCH - Cursor needs recompilation because an SCN was used during compile time due to being rewritten by materialized view');

end if;

if col_rec(idx).col_name = 'ROLL_INVALID_MISMATCH' then

dbms_output.put_line('ROLL_INVALID_MISMATCH - Marked for rolling invalidation and invalidation window exceeded');

end if;

if col_rec(idx).col_name = 'OPTIMIZER_MODE_MISMATCH' then

dbms_output.put_line('OPTIMIZER_MODE_MISMATCH - Parameter OPTIMIZER_MODE mismatch (for example, all_rows versus first_rows_1)');

end if;

if col_rec(idx).col_name = 'PX_MISMATCH' then

dbms_output.put_line('PX_MISMATCH - Mismatch in one parameter affecting the parallelization of a SQL statement. For example, one cursor was compiled with parallel DML enabled while the other was not.');

end if;

if col_rec(idx).col_name = 'MV_STALEOBJ_MISMATCH' then

dbms_output.put_line('MV_STALEOBJ_MISMATCH - Cursor cannot be shared because there is a mismatch in the list of materialized views which were stale at the time the cursor was built');

end if;

if col_rec(idx).col_name = 'FLASHBACK_TABLE_MISMATCH' then

dbms_output.put_line('FLASHBACK_TABLE_MISMATCH - Cursor cannot be shared because there is a mismatch with triggers being enabled and/or referential integrity constraints being deferred');

end if;

if col_rec(idx).col_name = 'LITREP_COMP_MISMATCH' then

dbms_output.put_line('LITREP_COMP_MISMATCH - Mismatch in use of literal replacement');

end if;

if col_rec(idx).col_name = 'LOAD_OPTIMIZER_STATS' then

dbms_output.put_line('LOAD_OPTIMIZER_STATS - No description');

end if;

end loop;

dbms_output.put_line('--------------------------------------------------');

dbms_output.new_line;

end loop;

dbms_sql.close_cursor(c);

end;

-- Added by Ajit

set head off;

select '<< Why cursors are not shared ? >>' from dual ;

set head on;

select INST_ID,SQL_ID,ADDRESS,CHILD_ADDRESS,CHILD_NUMBER, trim(

decode( UNBOUND_CURSOR , 'Y' , 'UNBOUND_CURSOR - The existing child cursor was not fully built (in other words, it was not optimized)' , ' ') ||

decode ( SQL_TYPE_MISMATCH , 'Y' , 'SQL_TYPE_MISMATCH - The SQL type does not match the existing child cursor', ' ') ||

decode ( OPTIMIZER_MISMATCH , 'Y' , 'OPTIMIZER_MISMATCH - The optimizer environment does not match the existing child cursor', ' ') ||

decode ( OUTLINE_MISMATCH , 'Y' , 'OUTLINE_MISMATCH - The outlines do not match the existing child cursor', ' ') ||

decode ( STATS_ROW_MISMATCH , 'Y' , 'STATS_ROW_MISMATCH - The existing statistics do not match the existing child cursor', ' ') ||

decode ( LITERAL_MISMATCH , 'Y' , 'LITERAL_MISMATCH - Non-data literal values do not match the existing child cursor', ' ') ||

decode ( FORCE_HARD_PARSE , 'Y' , 'FORCE_HARD_PARSE - Description not available', ' ') ||

--decode ( SEC_DEPTH_MISMATCH , 'Y' , 'SEC_DEPTH_MISMATCH - Security level does not match the existing child cursor', ' ') ||

decode ( EXPLAIN_PLAN_CURSOR , 'Y' , 'EXPLAIN_PLAN_CURSOR - The child cursor is an explain plan cursor and should not be shared', ' ') ||

decode ( BUFFERED_DML_MISMATCH , 'Y' , 'BUFFERED_DML_MISMATCH - Buffered DML does not match the existing child cursor', ' ') ||

decode ( PDML_ENV_MISMATCH , 'Y' , 'PDML_ENV_MISMATCH - PDML environment does not match the existing child cursor', ' ') ||

decode ( INST_DRTLD_MISMATCH , 'Y' , 'INST_DRTLD_MISMATCH - Insert direct load does not match the existing child cursor', ' ') ||

decode ( SLAVE_QC_MISMATCH , 'Y' , 'SLAVE_QC_MISMATCH - The existing child cursor is a slave cursor and the new one was issued by the coordinator or, the existing child cursor was issued by the coordinator and the new one is a slave cursor)', ' ') ||

decode ( TYPECHECK_MISMATCH , 'Y' , 'TYPECHECK_MISMATCH - The existing child cursor is not fully optimized', ' ') ||

decode ( AUTH_CHECK_MISMATCH , 'Y' , 'AUTH_CHECK_MISMATCH - Authorization/translation check failed for the existing child cursor', ' ') ||

decode ( BIND_MISMATCH , 'Y' , 'BIND_MISMATCH - The bind metadata does not match the existing child cursor', ' ') ||

decode ( DESCRIBE_MISMATCH , 'Y' , 'DESCRIBE_MISMATCH - The typecheck heap is not present during the describe for the child cursor', ' ') ||

decode ( LANGUAGE_MISMATCH , 'Y' , 'LANGUAGE_MISMATCH - The language handle does not match the existing child cursor', ' ') ||

decode ( TRANSLATION_MISMATCH , 'Y' , 'TRANSLATION_MISMATCH - The base objects of the existing child cursor do not match', ' ') ||

decode ( INSUFF_PRIVS , 'Y' , 'INSUFF_PRIVS - Insufficient privileges on objects referenced by the existing child cursor', ' ') ||

decode ( INSUFF_PRIVS_REM , 'Y' , 'INSUFF_PRIVS_REM - Insufficient privileges on remote objects referenced by the existing child cursor', ' ') ||

decode ( REMOTE_TRANS_MISMATCH , 'Y' , 'REMOTE_TRANS_MISMATCH - The remote base objects of the existing child cursor do not match', ' ') ||

decode ( LOGMINER_SESSION_MISMATCH , 'Y' , 'LOGMINER_SESSION_MISMATCH - LogMiner Session parameters mismatch', ' ') ||

decode ( INCOMP_LTRL_MISMATCH , 'Y' , 'INCOMP_LTRL_MISMATCH - Cursor might have some binds (literals) which may be unsafe/non-data. Value mismatch.', ' ') ||

decode ( OVERLAP_TIME_MISMATCH , 'Y' , 'OVERLAP_TIME_MISMATCH - Mismatch caused by setting session parameter ERROR_ON_OVERLAP_TIME', ' ') ||

decode ( EDITION_MISMATCH , 'Y' , 'EDITION_MISMATCH - Description not available', ' ') ||

--decode ( SQL_REDIRECT_MISMATCH , 'Y' , 'SQL_REDIRECT_MISMATCH - SQL redirection mismatch', ' ') ||

decode ( MV_QUERY_GEN_MISMATCH , 'Y' , 'MV_QUERY_GEN_MISMATCH - Internal, used to force a hard-parse decode ( analyzing materialized view queries', ' ') ||

decode ( USER_BIND_PEEK_MISMATCH , 'Y' , 'USER_BIND_PEEK_MISMATCH - Cursor is not shared because value of one or more user binds is different and this has a potential to change the execution plan', ' ') ||

decode ( TYPCHK_DEP_MISMATCH , 'Y' , 'TYPCHK_DEP_MISMATCH - Cursor has typecheck dependencies', ' ') ||

decode ( NO_TRIGGER_MISMATCH , 'Y' , 'NO_TRIGGER_MISMATCH - Cursor and child have no trigger mismatch', ' ') ||

decode ( FLASHBACK_CURSOR , 'Y' , 'FLASHBACK_CURSOR - Cursor non-shareability due to flashback', ' ') ||

decode ( ANYDATA_TRANSFORMATION , 'Y' , 'ANYDATA_TRANSFORMATION - Is criteria for opaque type transformation and does not match', ' ') ||

decode ( INCOMPLETE_CURSOR , 'Y' , 'INCOMPLETE_CURSOR - Cursor is incomplete: typecheck heap came from call memory', ' ') ||

decode ( TOP_LEVEL_RPI_CURSOR , 'Y' , 'TOP_LEVEL_RPI_CURSOR - Is top level RPI cursor', ' ') ||

decode ( DIFFERENT_LONG_LENGTH , 'Y' , 'DIFFERENT_LONG_LENGTH - Value of LONG does not match', ' ') ||

decode ( LOGICAL_STANDBY_APPLY , 'Y' , 'LOGICAL_STANDBY_APPLY - Logical standby apply context does not match', ' ') ||

decode ( DIFF_CALL_DURN , 'Y' , 'DIFF_CALL_DURN - If Slave SQL cursor/single call', ' ') ||

decode ( BIND_UACS_DIFF , 'Y' , 'BIND_UACS_DIFF - One cursor has bind UACs and one does not', ' ') ||

decode ( PLSQL_CMP_SWITCHS_DIFF , 'Y' , 'PLSQL_CMP_SWITCHS_DIFF - PL/SQL anonymous block compiled with different PL/SQL compiler switches', ' ') ||

decode ( CURSOR_PARTS_MISMATCH , 'Y' , 'CURSOR_PARTS_MISMATCH - Cursor was compiled with subexecution (cursor parts were executed)', ' ') ||

decode ( STB_OBJECT_MISMATCH , 'Y' , 'STB_OBJECT_MISMATCH - STB has come into existence since cursor was compiled', ' ') ||

decode ( CROSSEDITION_TRIGGER_MISMATCH , 'Y' , 'CROSSEDITION_TRIGGER_MISMATCH - Description not available', ' ') ||

decode ( PQ_SLAVE_MISMATCH , 'Y' , 'PQ_SLAVE_MISMATCH - Top-level slave decides not to share cursor', ' ') ||

decode ( TOP_LEVEL_DDL_MISMATCH , 'Y' , 'TOP_LEVEL_DDL_MISMATCH - Is top-level DDL cursor', ' ') ||

decode ( MULTI_PX_MISMATCH , 'Y' , 'MULTI_PX_MISMATCH - Cursor has multiple parallelizers and is slave-compiled', ' ') ||

decode ( BIND_PEEKED_PQ_MISMATCH , 'Y' , 'BIND_PEEKED_PQ_MISMATCH - Cursor based around bind peeked values', ' ') ||

decode ( MV_REWRITE_MISMATCH , 'Y' , 'MV_REWRITE_MISMATCH - Cursor needs recompilation because an SCN was used during compile time due to being rewritten by materialized view', ' ') ||

decode ( ROLL_INVALID_MISMATCH , 'Y' , 'ROLL_INVALID_MISMATCH - Marked for rolling invalidation and invalidation window exceeded', ' ') ||

decode ( OPTIMIZER_MODE_MISMATCH , 'Y' , 'OPTIMIZER_MODE_MISMATCH - Parameter OPTIMIZER_MODE mismatch (for example, all_rows versus first_rows_1)', ' ') ||

decode ( PX_MISMATCH , 'Y' , 'PX_MISMATCH - Mismatch in one parameter affecting the parallelization of a SQL statement. For example, one cursor was compiled with parallel DML enabled while the other was not.', ' ') ||

decode ( MV_STALEOBJ_MISMATCH , 'Y' , 'MV_STALEOBJ_MISMATCH - Cursor cannot be shared because there is a mismatch in the list of materialized views which were stale at the time the cursor was built', ' ') ||

decode ( FLASHBACK_TABLE_MISMATCH , 'Y' , 'FLASHBACK_TABLE_MISMATCH - Cursor cannot be shared because there is a mismatch with triggers being enabled and/or referential integrity constraints being deferred', ' ') ||

decode ( LITREP_COMP_MISMATCH , 'Y' , 'LITREP_COMP_MISMATCH - Mismatch in use of literal replacement', ' ')

) why_cursor_not_shared_reason

from

gv\$sql_shared_cursor where sql_id = '${LSQLID}' ;

-- End additions by Ajit

begin

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

end;

unformatrpt

formatrpt TEXT

echo "\

set head off;

select '<<<<<<<<<<<<<<<<< Explain Plan loaded in CACHE >>>>>>>>>>>>>>>>>' from dual;

select * from table(dbms_xplan.display_cursor('${LSQLID}',null, 'peeked_binds'));

select '<<<<<<<<<<<<<<<<< Explain Plan available in AWR >>>>>>>>>>>>>>>>>' from dual;

select * from table(dbms_xplan.display_awr('${LSQLID}'));

set head on;

unformatrpt

formatrpt

echo "\

-- Added by Ajit

set head off;

select '<< Bind variables used for current execution >>' from dual;

set head on;

select INST_ID,SQL_ID,CHILD_NUMBER,NAME,POSITION,DUP_POSITION,WAS_CAPTURED,to_char(LAST_CAPTURED,'DD-MON-YYYY HH24:MI:SS') LAST_CAPTURED,VALUE_STRING

from gV\$SQL_BIND_CAPTURE

where SQL_ID in ('${LSQLID}' )

order by child_number, position, name;

set head off;

select '<< Bind variable history for previous executions >>' from dual;

set head on;

select to_char(LAST_CAPTURED,'DD-MON-YYYY HH24:MI:SS') LAST_CAPTURED,SQL_ID,NAME,POSITION,DUP_POSITION,WAS_CAPTURED,VALUE_STRING

from DBA_HIST_SQLBIND

where SQL_ID in ('${LSQLID}' )

order by position,name;

-- End aditions by Ajit

set head off;

select '<<<<<<<<<<<<<<<<< DBA Tab Modifications >>>>>>>>>>>>>>>>>' from dual;

set head on;

select distinct b.table_owner, b.table_name, b.partition_name, b.inserts, b.updates, b.deletes, b.TRUNCATED,c.STALE_STATS,

to_char(b.timestamp, 'mm/dd/yyyy hh24:mi') timestamp, to_char(c.last_analyzed, 'mm/dd/yyyy hh24:mi') last_analyzed,

c.num_rows

from (select distinct sql_id, object#, object_name, object_owner from gv\$sql_plan where sql_id = '${LSQLID}' UNION select distinct sql_id, object#, object_name, object_owner from dba_hist_sql_plan where sql_id = '${LSQLID}') a

, sys.dba_tab_modifications b, dba_tab_statistics c

where a.sql_id = '${LSQLID}'

and a.OBJECT_OWNER = b.table_owner

and a.OBJECT_NAME = b.table_name

and b.table_owner = c.owner

and b.table_name = c.table_name

and NVL(b.partition_name,'NONE') = NVL(c.partition_name,'NONE')

and b.table_name is not null

order by b.table_owner, b.table_name, b.partition_name;

set head off;

select '<<<<<<<<<<<<<<<<< NON PARTITIONED TABLES >>>>>>>>>>>>>>>>>' from dual;

set head on;

select b.owner, b.table_name, b.tablespace_name, b.num_rows, to_char(last_analyzed, 'mm/dd/yyyy hh24:mi') last_analyzed

, b.avg_row_len, b.avg_space, b.status, b.temporary, b.ini_trans, b.pct_free, b.pct_used, b.chain_cnt

, sys.dba_tables b

where a.sql_id = '${LSQLID}'

and a.OBJECT_OWNER = b.owner

and a.OBJECT_NAME = b.table_name

and b.partitioned = 'NO'

and a.object# is not null

order by b.owner, b.table_name;

set head off;

select '<<<<<<<<<<<<<<<<< NON PARTITIONED INDEXES >>>>>>>>>>>>>>>>>' from dual;

set head on;

select b.owner, b.index_name, b.index_type, b.table_owner, b.table_name, b.table_type

, b.tablespace_name

,b.num_rows, to_char(last_analyzed, 'mm/dd/yyyy hh24:mi') last_analyzed

,b.status

, b.uniqueness, b.compression

, b.blevel, b.leaf_blocks, b.distinct_keys

from (select distinct sql_id, object#, object_name, object_owner from gv\$sql_plan where operation like '%INDEX%' and sql_id = '${LSQLID}' UNION select distinct sql_id, object#, object_name, object_owner from dba_hist_sql_plan where operation like '%INDEX%' and sql_id = '${LSQLID}') a

, sys.dba_indexes b

where a.sql_id = '${LSQLID}'

and a.OBJECT_OWNER = b.owner

and a.OBJECT_NAME = b.index_name

and b.partitioned = 'NO'

and a.object# is not null

order by b.owner, b.index_name;

set head off;

select '<<<<<<<<<<<<<<<<< PARTITIONED TABLES >>>>>>>>>>>>>>>>>' from dual;

set head on;

select b.table_owner, b.table_name, b.partition_name, b.tablespace_name, b.num_rows, last_analyzed

, b.avg_row_len, b.avg_space, b.ini_trans, b.pct_free, b.pct_used, b.chain_cnt

( select * from (

select c.table_owner, c.table_name, c.partition_name, c.tablespace_name, c.num_rows,

to_char(c.last_analyzed, 'mm/dd/yyyy hh24:mi') last_analyzed, c.avg_row_len,

c.avg_space, c.ini_trans, c.pct_free, c.pct_used, c.chain_cnt,

RANK() OVER (

PARTITION BY table_name

ORDER BY partition_position desc

) partition_rank

from dba_tab_partitions c

where num_rows > 0

)

where partition_rank < 5

) b

where a.sql_id = '${LSQLID}'

and a.OBJECT_OWNER = b.table_owner

and a.OBJECT_NAME = b.table_name

and a.object# is not null

order by b.table_owner, b.table_name, b.partition_name;

set head off;

select '<<<<<<<<<<<<<<<<< PARTITIONED INDEXES >>>>>>>>>>>>>>>>>' from dual;

set head on;

select c.index_owner, c.index_name, c.partition_name, b.table_owner, b.table_name, b.table_type

, c.tablespace_name

,c.num_rows, c.last_analyzed

,c.status

, b.uniqueness, c.compression

, c.blevel, c.leaf_blocks, c.distinct_keys

, sys.dba_indexes b,

( select * from (

select d.index_owner, d.index_name, d.partition_name,

d.compression, d.tablespace_name, d.blevel, d.leaf_blocks, d.distinct_keys

,d.status,d.num_rows, to_char(d.last_analyzed, 'mm/dd/yyyy hh24:mi') last_analyzed,

RANK() OVER (

PARTITION BY d.index_owner, d.index_name

ORDER BY d.partition_position desc

) partition_rank

from dba_ind_partitions d

where d.num_rows > 0

)

where partition_rank < 5

) c

where a.sql_id = '${LSQLID}'

and a.OBJECT_OWNER = b.owner

and a.OBJECT_NAME = b.index_name

and b.owner = c.index_owner

and b.index_name = c.index_name

and a.object# is not null

order by b.table_owner, c.index_name, c.partition_name;

set head off;

select '<<<<<<<<<<<<<<<<< TABLES WITH HISTOGRAMS >>>>>>>>>>>>>>>>>' from dual;

set head on;

select distinct b.owner, b.table_name

, sys.dba_tab_col_statistics b

where a.sql_id = '${LSQLID}'

and a.OBJECT_OWNER = b.owner

and a.OBJECT_NAME = b.table_name

and b.histogram <> 'NONE'

and a.object# is not null

order by b.owner, b.table_name;

set head off;

select '<<<<<<<<<<<<<<<<< AUTHORIZED HISTOGRAMS >>>>>>>>>>>>>>>>>' from dual;

set head on;

select * from authorized_histograms order by owner, table_name;

set head off;

select '<<<<<<<<<<<<<<<<< UNINDEXED FOREIGN KEYS >>>>>>>>>>>>>>>>>' from dual;

set head on;

select distinct k.fk_index, k.owner, k.table_name, k.cons_columns, k.constraint_name, k.index_columns, k.index_name

(

select decode( b.table_name, NULL, 'DOES NOT EXIST', 'EXIST' ) FK_Index,

a.owner, a.table_name, a.columns cons_columns, a.constraint_name, b.columns index_columns, b.index_name

from

( select a.owner, substr(a.table_name,1,30) table_name,

substr(a.constraint_name,1,30) constraint_name,

max(decode(position, 1, substr(column_name,1,30),NULL)) ||

max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||

max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||

max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||

max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||

max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||

max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||

max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||

max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||

max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||

max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||

max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||

max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||

max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||

max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||

max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns

from dba_cons_columns a, dba_constraints b

where a.constraint_name = b.constraint_name

and b.constraint_type = 'R'

group by a.owner, substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,

( select table_owner, substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,

max(decode(column_position, 1, substr(column_name,1,30),NULL)) ||

max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||

max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns

from dba_ind_columns

group by table_owner, substr(table_name,1,30), substr(index_name,1,30) ) b

where a.owner = b.table_owner (+)

and a.table_name = b.table_name (+)

and b.columns (+) like a.columns || '%'

) k

where j.sql_id = '${LSQLID}'

and j.OBJECT_OWNER = k.owner

and j.OBJECT_NAME = k.table_name

and j.object# is not null

order by k.fk_index, k.owner, k.table_name;

unformatrpt

formatrpt TEXT

echo "\

set head off;

select '**** FULL SQL TEXT in CACHE ****' from dual;

select sql_fulltext from gv\$sql where sql_id = '${LSQLID}';

select '**** FULL SQL TEXT in HISTORY ****' from dual;

select sql_text from dba_hist_sqltext where sql_id = '${LSQLID}';

unformatrpt

formatrpt

echo "\

set head off;

select '**** END OF REPORT ****' from dual;

unformatrpt

echo "exit;"

) > ${LTMPSQL}

}

rpthead

runsql

cat ${LHEAD} ${LTMPSQL} > ${LRUNSQL}

if test "x${LUSER}" = "xoracle"

then

echo "Running as user oracle. Will connect to database as SYS. If failed, connect using your NT user account"

sqlplus /nolog <<!

conn / as sysdba;

@${LRUNSQL}

else

echo "Running SQL Diagnostic as ${LUSER}. ENTER PASSWORD TO AUTHENTICATE YOUR DB CONNECTION ..."

sqlplus -s ${LUSER}@${LSID} @${LRUNSQL}

echo mail id is ${LEMAIL}

#(uuencode ${LFINAL} ${LFINAL}) | mailx -s "SQL DIAGNOSTIC for RAC DB ${LSID} - ${LSQLID} on ${LDATE}" "${LEMAIL}"

rm ${LHEAD}

rm ${LTMPSQL}

rm ${LRUNSQL}

rm ${LOUTPUT}

rm ${LFINAL}

#set head off;

#select '<<<<<<<<<<<<<<<<< DBA Index Columns >>>>>>>>>>>>>>>>>' from dual;

#set head on;

#select b.* from (select distinct sql_id, object#, object_name, object_owner from gv\$sql_plan where operation like '%INDEX%' and sql_id = '${LSQLID}') a

#, sys.dba_ind_columns b

#where a.sql_id = '${LSQLID}'

#and a.OBJECT_OWNER = b.index_owner

#and a.OBJECT_NAME = b.index_name

#and a.object# is not null

#order by b.table_owner, b.table_name, b.index_name, b.column_position;

==> Non rac

#!/bin/sh

#####################################################################

# File : sqldiag.sh

# Abstract : SQL Diagnostics

# Syntax : sqldiag.sh sid sqlid [user] [format]

# sid - TNS Entry for the database

# user - DB User (if null, then OS Login will be used

# for login into the database . Use xoracle for /sysdba

# format - Valid values for format: TEXT;

# Default output format is HTML

# History : Suresh Srinivasan 12/30/09 Initial

# : Ajit Dhamale 11/03/10

# Added cursor details, why cursors not shared

# Bind variable tables

# Amit Khadilkar 08/17/2010 Added flush_monitoring_info

#####################################################################

LSID=$1

LSQLID=$2

if test "x${LSID}" = "x" || test "x${LSQLID}" = "x"

then

echo "SID and SQLID are required. Syntax sqldiag.sh sid sqlid [user] [format]"

exit

LUSER=$3

if test "x${LUSER}" = "x"

then

LUSER=${USER}

LFORMAT=$4

#LSQLID='0hngxwb88wpfg'

#LSQLID='ahunvmz3m809y'

LOUTPUTFL="${LSID}_${LSQLID}_$$"

LHEAD="/tmp/${LOUTPUTFL}_hd.sql"

LTMPSQL="/tmp/${LOUTPUTFL}_tmpsql.sql"

LRUNSQL="/tmp/${LOUTPUTFL}_runsql.sql"

LOUTPUT="/tmp/${LOUTPUTFL}_tmp.html"

LFINAL="/tmp/${LOUTPUTFL}.html"

#LEMAIL="SGuharay@consultantemail.com"

LDATE=`date +"%m-%d-%Y %T`

rpthead()

{

(

formatrpt

echo "\

set echo off verify off feed off trims on pages 5000 lines 500 long 50000;

col fk_index head \"FK Index?\" format a16;

col sql_id head \"class=awrbg SQL Id\" format a20;

col plan_hash_value head \"<p class=awrbg>Plan Hash</p>\";

col object_name head \"Object\" format a30;

col column_name head \"Column\" format a30;

col pos format 999;

col column_position head \"Posn\" format 9999;

col column_length head \"Len\" format 9999;

col cardinality format 999999;

col options format a20;

col txt format a70;

col sql_text head \"SQL\" format a70;

col id like pos;

col is_bind_sensitive head \"Bind Sense?\" format a11;

col is_bind_aware head \"Bind Aware?\" format a11;

col child_number head \"Child#\" format 999999;

col child_address head \"Child Addr\" format a20;

col cn like pos;

col optimizer_cost head \"Opt Cost\" format 99999999;

col object_status head \"Status\" format a15;

col status like object_status;

col timestamp head \"Timestamp\";

col parsing_schema_name head \"Schema\" format a20;

col subobject_name head \"Partition\" format a30;

col object_id head \"Obj Id\" format 999999999999;

col data_object_id head \"Data Obj Id\" format 999999999999;

col object_type head \"Type\";

col created head \"Created\";

col last_ddl_time head \"Last DDL\";

col temporary head \"Temp?\" format a5;

col owner head \"Owner\" format a20;

col table_owner head \"Owner\" format a20;

col index_owner head \"Index Owner\" format a20;

col table_name head \"Table\" format a30;

col index_name head \"Index\" format a30;

col segment_name head \"Segment\" format a30;

col partition_name like subobject_name;

col partition_position like pos;

col segment_type like object_type;

col index_type like object_type;

col table_type like object_type;

col tablespace_name head \"Tablespace\" format a30;

col bytesmb head \"Bytes (MB)\" format \"999,999.99\"

col num_rows head \"Rows\" format \"999,999,999,999\"

col partitioned head \"Part?\" format a5;

col avg_row_len head \"Avg Row|Length\" format \"999,999\"

col avg_space head \"Avg Space\" format \"999,999,999\"

col uniqueness head \"Unique?\" format a9;

col compression head \"Compress?\" format a9;

col last_analyzed head \"Analyzed\" format a20;

col instance_name head \"Instance\" format a9;

col host_name head \"Host\" format a20;

col version head \"Version\" format a10;

col startup_time head \"Startup\";

col curr_time head \"Run Date\";

col first_load_time head \"First Load Time\" format a20;

col last_load_time head \"Last Load Time\" format a20;

col sql_plan_baseline head \"Baseline?\" format a20;

col module head \"Module\";

col action head \"Action\";

col cpu_time head \"CPU Time\";

col elapsed_time head \"Elapsed Time\";

col Invalidations Head \"Invalidations\";

col cons_columns head \"Constraint Columns\" format a50;

col constraint_name head \"Constraint Name\";

col index_columns head \"Index Columns\" format a50;

set head off;

select 'SQL Diagnostics for SQL ID ${LSQLID}' from dual;

set head on;

select instance_name, host_name, version, to_char(startup_time,'mm/dd/yyyy hh24:mi') startup_time,

to_char(sysdate,'mm/dd/yyyy hh24:mi') curr_time from v\$instance;