https://github.com/nkarag/oracle_ready2run_scripts/

---------------------------------------------------------------------------------------------

-- Find the available execution plans for a specific SQL_ID

--

--              Note that the AVG_ET_SECS (average elpased time) will not be accurate for parallel queries. 

-- The ELAPSED_TIME column contains the sum of all parallel slaves. So the 

--              script divides the value by the number of PX slaves used which gives an 

--              approximation. 

--

--              Note also that if parallel slaves are spread across multiple nodes on

--              a RAC database the PX_SERVERS_EXECUTIONS column will not be set.

--

-- author: Nikos Karagiannidis (C) 2013 - http://oradwstories.blogspot.com

---------------------------------------------------------------------------------------------


prompt

prompt ********************************************************

prompt Find the available execution plans for a specific SQL_ID

prompt ********************************************************

prompt


set linesize 999

col avg_et_secs justify right format 9999999.99 

col cost justify right format 9999999999 

col timestamp justify center format a25 

col parsing_schema_name justify center format a30

col inst_id format 999999999 

col executions_total format 99999999999999999

col executions format 99999999999999


alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';


select 'gv$sqlarea_plan_hash' source, INST_ID, 

SQL_ID, PLAN_HASH_VALUE, 

executions, 

round(elapsed_time/decode(nvl(executions,0),0,1,executions)/1e6/

decode(px_servers_executions,0,1,px_servers_executions)/decode(nvl(executions,0),0,1,executions),2) avg_et_secs, 

px_servers_executions/decode(nvl(executions,0),0,1,executions) avg_px,

optimizer_cost cost, 

LAST_LOAD_TIME timestamp, 

parsing_schema_name --FIRST_LOAD_TIME, LAST_LOAD_TIME, LAST_ACTIVE_TIME, SQL_PROFILE

from gv$sqlarea_plan_hash

where sql_id = nvl(trim('&&sql_id'),sql_id)

UNION

SELECT 'dba_hist_sql_plan' source, null INST_ID, 

t1.sql_id sql_id, t1.plan_hash_value plan_hash_value, 

t2.executions_total, 

t2.avg_et_secs avg_et_secs, 

t2.avg_px, 

t1.cost cost, 

t1.timestamp timestamp, 

NULL parsing_schema_name 

FROM dba_hist_sql_plan t1,

(

SELECT sql_id, plan_hash_value, 

max(executions_total) executions_total, --round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6,2) avg_et_secs 

round(SUM(elapsed_time_total)/decode(SUM(executions_total),0,1,SUM(executions_total))/1e6/

decode(SUM(px_servers_execs_total),0,1,SUM(px_servers_execs_total))/decode(SUM(executions_total),0,1,SUM(executions_total)),2) avg_et_secs, 

SUM(px_servers_execs_total)/decode(SUM(executions_total),0,1,SUM(executions_total)) avg_px

FROM dba_hist_sqlstat

WHERE 

executions_total > 0

GROUP BY sql_id, plan_hash_value

) t2

WHERE 

t1.sql_id = nvl(TRIM('&sql_id.'), t1.sql_id)

AND t1.depth = 0

AND t1.sql_id = t2.sql_id(+)

AND t1.plan_hash_value = t2.plan_hash_value(+)

order by avg_et_secs, cost --timestamp desc 

/

undef sql_id


Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Blocking session on RAC database

Blocking session on RAC database Blocking session of the databases Generate the report for Blocking session The SQL will provide Blocking sessions with SID, node information SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID AND L1.BLOCK > 0 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2; Blockers information select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s...
Read more

Finding object size in Oracle database

Ref: http://www.runningoracle.com/product_info.php?cPath=2_44&products_id=153 Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)  SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name  FROM DBA_SEGMENTS  WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',  'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY')  --AND TABLESPACE_NAME LIKE 'COSTE%'  --AND SEGMENT_NAME LIKE 'P2010201%'  --AND partition_name LIKE 'P20100201%' --AND segment_type = 'TABLE' --AND OWNER = 'TARG...
Read more