Sunday, March 18, 2018

Optimizer queries to fetch the execution paln


Optimizer Known Records during Execution
*********************************************************************************
col savtime for a40
col object_name for a40
select savtime,owner,object_name,rowcnt,blkcnt From sys.WRI$_OPTSTAT_TAB_HISTORY w, dba_objects o
where o.owner='&owner' and o.object_name='&Obj_name' And o.object_id = w.obj#
order by o.owner, o.object_name,w.savtime;


*********************************************************************************
Blocked Session
-------------------
set lines 200 pages 1010
spool block_session.log append
COLUMN sess format A15
col BLOCK for 999
col lmode for 999
col CTIME for 9999
col REQUEST for 999
col USERNAME for a9
col STATUS for a8
col ID1 for 999999999
col ID2 for 9999999
col SERIAL# for 99999
col event for a20
select systimestamp from dual;
SELECT a.inst_id,substr(DECODE(request,0,'Holder: ','Waiter: ')||a.sid,1,15) sess, b.serial#, a.type, a.id1, a.id2,
        a.lmode, a.request,a.block, a.ctime, b.username, b.status, b.sql_id, b.event, b.ROW_WAIT_OBJ# from    gv$session b,
        (select distinct b.*
        from    gv$lock a,
                gv$lock b
        where   a.id1 = b.id1
        and     a.id2 = b.id2
        and     a.request > 0) a
where   a.sid = b.sid
and     a.inst_id = b.inst_id
order by a.id1, a.id2, a.block desc, ctime
/
select * from gv$access where object='PL_IMAGE_ITEM_GROUP';
SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE'
SELECT * FROM V$OPEN_CURSOR WHERE SID = --
SELECT s.inst_id, O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
 S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT
 FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, gV$SESSION S,
 V$PROCESS P, V$SQL SQ
 WHERE L.OBJECT_ID = O.OBJECT_ID
 AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR
 AND S.SQL_ADDRESS = SQ.ADDRESS;
SELECT
    O.OBJECT_NAME,
    S.SID,
    S.SERIAL#,
    P.SPID,
    S.PROGRAM,
    SQ.SQL_FULLTEXT,
    S.LOGON_TIME
FROM
    V$LOCKED_OBJECT L,
    DBA_OBJECTS O,
    V$SESSION S,
    V$PROCESS P,
    V$SQL SQ
WHERE
    L.OBJECT_ID = O.OBJECT_ID
    AND L.SESSION_ID = S.SID
    AND S.PADDR = P.ADDR
    AND S.SQL_ADDRESS = SQ.ADDRESS;


ActiveEvents
*********************************************************************************
select to_char(sysdate,'DD-MON-YY HH24:MI:SS') dt, inst_id, count(1) tot, count(case when username is null then 1 end) bg,
count(case when username is not null then 1 end) wbg,
count(case when status='ACTIVE' and username is not null then 1 end) act_user from gv$session group by inst_id
/
---------------------------------------------------
set lines 165 pages 1000 time on
spool wait_event07NovEvening.log append
set heading on
col inst for 99
col spid for a11
col username for a16
col osuser for a14
col event for a30 trunc
col inst for 99999
col sid for 99999
col et for 9999999
col dt for a20
col system_date for a18
col module for a18 trunc
col blocker for 999999
col bs for 99999
col Bi for 99
col rr for 99
col obj for 9999999
col wt for 99
select systimestamp from dual;
select s.inst_id as inst,s.sid as sid,p.spid as spid,s.username,s.status,s.sql_id,s.event,s.module,s.last_call_et as "et(s)",s.blocking_session bs,s.
row_wait_obj# as obj,BLOCKING_INSTANCE as Bi,ROW_WAIT_ROW# as rr, s.wait_time WT
from gv$session s,gv$process p
where wait_class !='Idle'and s.paddr = p.addr and s.inst_id=p.inst_id
order by inst,event
/
---------------------------------------------
select sql_id,event,count(*) from gv$session where event ='gc cr request' group by sql_id,event order by 3
--------------------------------------------------
set lines 1000
column module format a40
column event format a40
select inst_id, sid, serial#, username, status, sql_id, module, to_char(logon_time,'DD-MM HH24:MI') logon, (sysdate-logon_time)*24*60*60 diff, last_call_et lce, event from gv$session where wait_class !='Idle' and username is not null and username not like 'SYS' AND STATUS='ACTIVE'
---------------------------------------------------
select program,module,event,sql_id,prev_sql_id,status from gv$session where STATUS='ACTIVE' AND TYPE !='BACKGROUND';
----------------------------------------------------
select module,count(1) from gv$session group by module order by 2 desc;
oracle@HBFCPRDDB2:/home/oracle/amit$ cat lo.sql
set linesize 132
col target for a60
col "SID" for 99999
SELECT SID,  target || opname target , TOTALWORK, SOFAR, TIME_REMAINING, ELAPSED_SECONDS FROM V$SESSION_LONGOPS
where time_remaining > 0
order by time_remaining
/
-----------------------------------------------------
set lines 250 pages 9999
select inst_id,event,count(1) from gv$session where wait_class != 'Idle' group by inst_id,event order by 1,2 desc;
---------------------------------------------------
select inst_id,status,count(1) from gv$session where wait_class !='IDLE' and username is not null group by inst_id,status order by 1,2
----------------------------------------------------
select inst_id,count(*) from gv$session where status='ACTIVE' group by inst_id;
------------------------------------------------------
select sid,serial#,username,osuser,event,sql_id,p1,p2,p3,last_call_et as et from v$session where username='SYS'
---------------------

STALE STATS
************
select table_name, stale_stats, last_analyzed from dba_tab_statistics where owner='&OWNER'order by last_analyzed desc

Plan
*********************************************************************************
set lines 100 pages 2000
select * from table(dbms_xplan.display_cursor('&sid'))
select * from table(dbms_xplan.display_cursor(null,null,'iostats last +cost'));
select * from table(dbms_xplan.display_cursor('&sql_id',&child))
select * from table(dbms_xplan.display_awr('&sql_id'))
select * from table(dbms_xplan.display_awr('&sql_id',&plan_h_v));
SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child_no, 'ALLSTATS LAST +COST +BYTES')); (/*+ gather_plan_statistics */)
select * from table(dbms_xplan.display_awr('&sql_id',NULL,NULL,'ADVANCED'));
explain plan for select * from sh.sales where cust_id=:b1 and promo_id=:b2;
select * from table(dbms_xplan.display) ;

select t.*
from v$sql s,
table(dbms_xplan.display_cursor(s.sql_id, s.child_number, '+peeked_binds')) t
where sql_id = '&1'
/
*********************************************************************************
-- Show the Plan Hash Values for a Given SQLID Over a Given Period

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

SELECT DISTINCT sql_id, plan_hash_value
FROM dba_hist_sqlstat q,
    (
    SELECT /*+ NO_MERGE */ MIN(snap_id) min_snap, MAX(snap_id) max_snap
    FROM dba_hist_snapshot ss
    WHERE ss.begin_interval_time BETWEEN (SYSDATE - &No_Days) AND SYSDATE
    ) s
WHERE q.snap_id BETWEEN s.min_snap AND s.max_snap
  AND q.sql_id IN ( '&SQLID')
/
-----------------------
Audit
*********************************************************************************
select * from DBA_PRIV_AUDIT_OPTS
union all
select * from DBA_STMT_AUDIT_OPTS;
select * from dba_audit_trail;

Hard Parsing
*********************************************************************************
set line 200 pagesize 5000
col inst_id for 99
col sql_text for a100
col first_load_time for a20
col PARSING_SCHEMA_NAME for a14
SELECT inst_id,sql_id,first_load_time,PARSING_SCHEMA_NAME, sql_text
   FROM gv$sql
  WHERE parsing_schema_name not in ('XDB', 'SYS', 'DBSNMP', 'FLOWS_030000', 'EXFSYS', 'SPOTLIGHT')
and first_load_time > to_char(sysdate-1/(24*60), 'YYYY-MM-DD/HH24:MI:SS')
/
---------------
set line 200 pagesize 5000
col inst_id for 99
col sql_text for a100
col first_load_time for a20
col PARSING_SCHEMA_NAME for a14
SELECT inst_id,sql_id,first_load_time,PARSING_SCHEMA_NAME, sql_text
   FROM gv$sql
  WHERE parsing_schema_name not in ('XDB', 'SYS', 'DBSNMP', 'FLOWS_030000', 'EXFSYS', 'SPOTLIGHT')
and first_load_time > to_char(sysdate-1/(24*60), 'YYYY-MM-DD/HH24:MI:SS')
/

Hidden Parameters
*********************************************************************************
col KSPPINM for a30
col ksppstvl for a30
SELECT a.ksppinm, b.ksppstvl, b.ksppstdf FROM x$ksppi a, x$ksppcv b WHERE a.indx = b.indx and a.ksppinm like '%&name%' ORDER BY a.ksppinm
/
--------------------

Bind Values
*********************************************************************************
select DISTINCT 'VAR ' || SUBSTR(NAME,2) || CASE WHEN DATATYPE_STRING='DATE' THEN ' VARCHAR2(20)' ELSE ' ' || DATATYPE_STRING END || ';' || CHR(10) ||'EXEC ' || NAME || ':=' || CASE WHEN DATATYPE_STRING='NUMBER' then VALUE_STRING ELSE '''' || VALUE_STRING || ''';' END "DECLARE_VAR"
from v$sql_bind_capture where sql_id='&sqlid';
select sn.END_INTERVAL_TIME, sb.NAME, sb.VALUE_STRING from DBA_HIST_SQLBIND sb, DBA_HIST_SNAPSHOT sn where sb.sql_id='4ywfmsqh97pdn' and sb.WAS_CAPTURED='YES' and
sn.snap_id=sb.snap_id order by sb.snap_id, sb.NAME;
--------------------

SQL HISTORY
*********************************************************************************
All SQL hist
----------------
select sql_id, sum(t.elapsed_time_delta/1000)/sum(t.executions_delta) " Executed(in ms)"
from   dba_hist_sqlstat t, dba_hist_snapshot s
where  t.snap_id = s.snap_id
and    t.dbid = s.dbid
and    t.instance_number = s.instance_number
and    s.begin_interval_time between trunc(sysdate)-1 and trunc(sysdate) -- yesterdays stats
and    executions_delta>0
group  by sql_id
/
-------------------------
set lines 300 pages 50000 verify off
select inst_id,sql_id,plan_hash_value, child_number, executions, buffer_gets, round(buffer_gets/decode(executions,0,1,executions)) gets_exec, disk_reads, elapsed_time/executions/1000 as etpe_ms, round(rows_processed/decode(executions,0,1,executions)) rows_exec from gv$sql where sql_id='&sql_id';
select inst_id,sql_id,plan_hash_value, child_number, executions, buffer_gets, round(buffer_gets/decode(executions,0,1,executions)) gets_exec, disk_reads,
elapsed_time/1000/1000 as "et in s", round(rows_processed/decode(executions,0,1,executions)) rows_exec from gv$sql where sql_id='&sql_id';
-------------------------
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000 avg_etime_ms,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
-------------------------
--Machine
--------------------
set lines 200 pages 200
col begin_interval_time for a30
--break on plan_hash_value on startup_time skip 1
select snap_id, node, begin_interval_time, sql_id, plan_hash_value, execs, avg_etime, avg_lio,m,
round(execs*avg_etime/60,0) total_time from (
select ss.snap_id, ss.instance_number node, begin_interval_time, s.sql_id, s.plan_hash_value,
nvl(executions_delta,0) execs,
round((elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000,3) avg_etime,
round((buffer_gets_delta/decode(nvl(executions_delta,0),0,1,executions_delta))) avg_lio,
S.module as m
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where s.sql_id = nvl('&sql_id','&sql_id')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0 --and plan_hash_value=3099046451
order by 1, 2, 3)
order by 2,1,3
/
---------------------------------------------------------
select to_char(min(s.end_interval_time),'DD-MON-YYYY DY HH24:MI') sample_end
, q.sql_id
, q.plan_hash_value
, q.SORTS_TOTAL
, q.SORTS_DELTA
, q.PARSING_SCHEMA_NAME
, sum(q.EXECUTIONS_DELTA) executions
, round(sum(DISK_READS_delta)/greatest(sum(executions_delta),1),1) pio_per_exec
, round(sum(BUFFER_GETS_delta)/greatest(sum(executions_delta),1),1) lio_per_exec
, round((sum(ELAPSED_TIME_delta)/greatest(sum(executions_delta),1)/1000),1) ela_exec_in_ms
from dba_hist_sqlstat q, dba_hist_snapshot s
where q.SQL_ID=trim('&SQLID')
and s.snap_id = q.snap_id
and s.dbid = q.dbid
and s.instance_number = q.instance_number
group by s.snap_id
, q.sql_id
, q.plan_hash_value
, q.SORTS_TOTAL
, q.SORTS_DELTA
, q.PARSING_SCHEMA_NAME
order by s.snap_id, q.sql_id, q.plan_hash_value;
---------------------------------------------------------
col BEGIN_INTERVAL_TIME for a30
select snap.begin_interval_time, snap.instance_number, dhs.sql_id, dhs.plan_hash_value, dhs.elapsed_time_delta, dhs.cpu_time_delta, dhs.executions_delta,
trunc(dhs.elapsed_time_delta/dhs.executions_delta/1000,2) elapsed_per_exec_ms
from DBA_HIST_SQLSTAT dhs, dba_hist_snapshot snap
where dhs.snap_id = snap.snap_id
and dhs.instance_number = snap.instance_number
and dhs.sql_id = '&sql_ID'
and dhs.executions_delta > 0
order by snap.begin_interval_time desc;
----------------------------------------------------------
column sql_text for a40 wrap
col module for a22
select sql_id,plan_hash_value,child_number, sql_text, executions, buffer_gets, buffer_gets/executions as buffer_exec,rows_processed/executions as rows_exec,
module
from gv$sql
where sql_id='&sql_id'
/
-----------------------
Average
-------
select ss.snap_id, ss.instance_number node, to_char(begin_interval_time,'DD-MON-YY HH24MI'), s.sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000 "avg_etime_(ms)",
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(disk_reads_delta/decode(nvl(disk_reads_delta,0),0,1,executions_delta)) avg_pio,
(iowait_delta/decode(nvl(iowait_delta,0),0,1,executions_delta)) avg_iowait,
rows_processed_delta
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&sql_id'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/
------------------------
set long 90000
set lines 160 pages 2020
select sql_id, sql_fulltext from   v$sqlarea where  sql_id = '&entersqlid';
------------------------
select sql_fulltext from v$sqlarea where sql_id='&sql_id';
------------------------
select sql_id,sql_text from v$sqlarea where sql_text like '%&sqltext%';
------------------------
select inst_id, child_number, plan_hash_value, executions, buffer_gets, buffer_gets/executions as "buff_exe", rows_processed from gv$sql where sql_id='&sql_id';
------------------------
select s.inst_id,s.sid,s.sql_id,s.event,s.program,s.module,sa.executions,sa.buffer_gets/sa.executions gets,sa.ELAPSED_TIME/sa.executions elsped,sa.cluster_wait_time
from gv$session s,v$sqlarea sa where sa.sql_id=s.sql_id and s.status='ACTIVE' and s.type<>'BACKGROUND' and executions>0;
------------------------
select s.sid,s.sql_id,s.program,s.module,sa.executions,sa.buffer_gets/sa.executions gets,sa.ELAPSED_TIME/sa.executions elsped,sa.sql_text
from v$session s,v$sqlarea sa where sa.sql_id=s.sql_id
and s.sql_id='&sqlid';
-----------------------
select * from (select sql_id,sql_text,round(buffer_gets/decode(executions,0,1,executions)) gpe,executions,module,elapsed_time/1000000 ela
from gv$sqlarea order by 3 desc) where rownum <=10;

Table Details
*********************************************************************************
select owner,table_name,num_rows from dba_tables where table_name = '&name';
-----------------------
set lines 200
set pages 10000
set verify off;
col table_name for a30
select owner, table_name, partitioned, num_rows, blocks, last_analyzed,
global_stats, round(sample_size/decode (num_rows, 0, 1, null, 1, num_rows)*100,0) estimate_percent , partitioned, temporary
from dba_tables
where table_name = upper('&1');
set verify on;
-----------------------

*********************************************************************************
Index Details
*********************************************************************************
break on index_name skip 1
column column_name format a30
column column_expression for a30
set lines 120 pages 50000
select c.index_name,
        c.column_name "COLUMN_NAME", c.column_position,
        a.column_expression
from    dba_ind_columns c,
        dba_ind_expressions a
where   c.table_owner='&SCHEMA'
and     c.table_name='&table_name'
and     a.index_name(+) = c.index_name
and     a.table_owner(+) = c.table_owner
and     a.index_owner(+) = c.index_owner
and     a.table_name(+) = c.table_name
and     a.column_position(+) = c.column_position
order by 1,3
/
----------------
select owner, index_name, blevel, leaf_blocks, clustering_factor, degree from dba_indexes where owner='&owner' and table_name='&table' order by 1,2;
oracle@HBFCPRDDB2:/home/oracle/amit$ cat index _columns.sql
column column_name for a30
break on index_name skip 1 on report
select index_name, substr(column_name,1,30) column_name, COLUMN_POSITION from dba_ind_columns where TABLE_OWNER='&owner' and TABLE_NAME='&table' order by 1,3
/
----------------
select owner, Table_name, index_name, BLEVEL, LEAF_BLOCKS, last_analyzed, CLUSTERING_FACTOR, NUM_ROWS, PARTITIONED from dba_indexes where index_name='&ind_name';
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NUM_DISTINCT,NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE, HISTOGRAM from dba_tab_columns where column_name=''
------------------
*********************************************************************************
Object Details
*********************************************************************************
set lines 399 pages 1000
col owner for a20
col object_name for a30
select owner,object_name,object_type,status,object_id ,data_object_id from dba_objects where data_object_id=&obj
/
----------------
set lines 200 pages 1000
col object_name for a30
select owner,object_id,data_object_id,object_name,object_type,status from dba_objects where object_name='&object_name'
/
*********************************************************************************
REDO SIZE
*********************************************************************************
set lines 300
col module format a30
col redo for 9999999999999
select * from (
SELECT s.inst_id,s.sid, module,username,osuser, sql_hash_value, value redo, to_char(logon_time, 'dd/mm/yyyy hh24:mi:ss') lt
FROM gv$session s, gv$sesstat ss, gv$statname sn
WHERE s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND name = 'redo size'
ORDER BY redo desc
)where rownum<10
/

execute dbms_stats.gather_table_stats(ownname =>'SBIH',tabname=>'TBL_TRANSACTIONS',estimate_percent=>10,
method_opt=>'FOR ALL COLUMNS SIZE 1',granularity=>'ALL',cascade=>true,degree=>16);

alter session set "_use_nosegment_indexes"=true;
CREATE INDEX "SYSADM"."PS_IDX01" ON "SYSADM"."PS_CT_LSTIMEDTLS"("CT_LS_ID") TABLESPACE "PSINDEX" nosegment;

select dbms_metadata.get_ddl('TABLE','&TAB_NAME','&SCHEMA') from dual;
select dbms_metadata.get_ddl('INDEX','&INDEX_NAME','&SCHEMA') from dual;

select id, parent_id, operation
from (
select level lvl, id, parent_id, lpad(' ',level) || operation || ' ' || options
|| ' ' || object_name as operation
from plan_table
start with id = 0
connect by prior id = parent_id
)
order by lvl desc, id;


exec DBMS_STATS.GATHER_TABLE_STATS (ownname => 'CUSTDOMAIN',tabname => 'FTIME' , estimate_percent => 100, block_sample => TRUE,
degree => 8, method_opt => 'FOR ALL COLUMNS SIZE 1', granularity => 'ALL', cascade => TRUE);
exec dbms_stats.gather_schema_stats( ownname => 'APR_LND',estimate_percent => dbms_stats.auto_sample_size, method_opt => 'FOR ALL COLUMNS SIZE AUTO',degree => 8,no_invalidate=>false , cascade => TRUE);


set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value, nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000 avg_etime_ms,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio, MODULE
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where s.parsing_schema_name='&USER'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and ss.begin_interval_time like '%30-AUG-16%'
order by 7 desc
/

No comments:

Post a Comment