SQL_TUNING
Turning on 10046 tracing for the sql:
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
Or if the sql is already running you can turn the trace on for this sql_id:
How to trace sql id:
1. ALTER SYSTEM SET EVENTS sql_trace [sql: sql_id=3s1yukp05bzg7] bind=true, wait=true';
2. execute the query
3. alter system set events 'sql_trace off';
4. Find the trace
You do not need to leave the trace on, just collect for 10 minutes.
Explain plan of a sql query:
SET VERIFY OFF
set echo off
set pagesize 400
set linesize 300
set long 4999
set longc 4999
explain plan for
SELECT * from soe.employee;
select * from table(dbms_xplan.display);
----------invisible index demo------------------------------
SET VERIFY OFF
set echo off
set pagesize 400
set linesize 300
set long 4999
set longc 4999
explain plan for
SELECT * /*+ use_invisible_indexes */ from soe.employee;
select * from table(dbms_xplan.display);
--------------Indexes presence on a table------------------
SELECT aic.index_owner, aic.table_name, aic.INDEX_NAME, listagg(aic.column_name,',') within group (order by aic.column_position) cols FROM all_ind_columns aic where aic.table_name='TABLE_NAME' group by aic.index_owner, aic.table_name, aic.INDEX_NAME order by aic.index_owner, aic.table_name ;
----------SQL Tracing-------------
1. Set the below trace at session level to get trace :
alter session set tracefile_identifier='06502';
alter session set events '06502 trace name errorstack level 3';
2. Run the plsql block and generate the error.
3. To close the trace set the following
alter session set events '06502 trace name context off';
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
---------Query execution time check-----------
------this query will give the avg execution time per each execution ---taking calculations from gv$sql executions count and elapsed time
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = TRIM('&&sql_id.')
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs
FROM gv$sql
WHERE sql_id = TRIM('&&sql_id.')
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
FROM dba_hist_sqlstat
WHERE sql_id = TRIM('&&sql_id.')
AND executions_total > 0
GROUP BY
plan_hash_value )
SELECT p.plan_hash_value,
ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
avg_et_secs NULLS LAST;
--------------------------------------------------------------
---------sql plan changes app --------
set lines 200
set pagesize 200
col execs for 999,999,999
col avg_etime for 999,999.999 heading avg_exec|time(s)
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
col SQL_PROFILE for a45
set verify off
col PLAN_HASH_VALUE for 9999999999 heading 'Plan Hash'
col PARSING_SCHEMA_NAME for a10 heading 'Parsing Schema'
col END_INTERVAL_TIME for a30
SELECT STAT.SNAP_ID,STAT.SQL_ID, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME, round(ELAPSED_TIME_DELTA/1000000,2) exec_sec, SS.END_INTERVAL_TIME,SQL_PROFILE FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID = TXT.DBID AND SS.DBID = STAT.DBID AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND STAT.SNAP_ID = SS.SNAP_ID AND STAT.INSTANCE_NUMBER = 1 AND SS.BEGIN_INTERVAL_TIME >= sysdate-7 AND UPPER(STAT.SQL_ID) = upper('&sqlid') ORDER BY stat.snap_id desc
/
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))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,SQL_PROFILE
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where upper(sql_id) like upper('&&sqlid')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1,2,begin_interval_time
/
PROMPT +-------------------------------------------------+
PROMPT | Execution times of the various plans in history |
PROMPT +-------------------------------------------------+
set lines 200 pages 200
col execs for 999,999,999
col etime for 999,999,999.9
col avg_etime for 999,999.999
col avg_cpu_time for 999,999.999
col avg_lio for 999,999,999.9
col avg_pio for 9,999,999.9
col begin_interval_time for a30
col node for 99999
--break on plan_hash_value on startup_time skip 1
select sql_id, plan_hash_value,
sum(execs) execs,
sum(etime) etime,
sum(etime)/sum(execs) avg_etime,
sum(cpu_time)/sum(execs) avg_cpu_time,
sum(lio)/sum(execs) avg_lio,
sum(pio)/sum(execs) avg_pio
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
cpu_time_delta/1000000 cpu_time,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&&sqlid'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
)
group by sql_id, plan_hash_value
order by 5
/
=====================check plan diff sql id
set linesize 200
set pagesize 40
col sql_plan_hash_value for 9999999999 heading 'Sql|Plan Hash'
col rows_processed for 999999999 heading 'Rows|Processed'
col SORTS for 9999999
set verify off
col last_load for a19
col plan_hash_value for 9999999999 heading "Plan|Hash Value"
select plan_hash_value,to_char(LAST_LOAD_TIME,'DD-MON-YY HH24:MI:SS') last_load,SORTS,FETCHES,EXECUTIONS,PARSE_CALLS,DISK_READS,DIRECT_WRITES,BUFFER_GETS,ROWS_PROCESSED,HASH_VALUE,OBJECT_STATUS from gv$sqlarea where SQL_ID = '&&sqlid';
PROMPT +---------------------------------------+
PROMPT | &&sqlid Query Last 5 plan history
PROMPT +---------------------------------------+
set lines 200
set pagesize 200
col snap_id for 999999
col instance_number for 9999
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999
col SQL_PROFILE for a32
col begin_interval_time for a26
col node for 99999
--define sqlid=&1
set verify off
select * from (select ss.snap_id, ss.instance_number node, to_char(begin_interval_time,'DD-MON-YY HH24:MI:SS') Begin_Interval, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,SQL_PROFILE
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id ='&&sqlid'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
--and begin_interval_time > sysdate-1/24
order by begin_interval_time desc,1, 2)
where rownum <= 5
/
PROMPT +---------------------------------------+
PROMPT | &&sqlid Avg Exec Plan History
PROMPT +---------------------------------------+
set lines 200 pages 200
col execs for 999,999,999
col etime for 999,999,999 heading 'Exec_Time(sec)'
col avg_etime for 999,990.999 heading 'Avg |Exec_Time(sec)'
col avg_cpu_time for 999,999.999
col avg_lio for 999,999,999 heading 'Avg | Logical IO'
col avg_pio for 9,999,999 heading 'Avg | Physical IO'
col begin_interval_time for a30
col node for 99999
select sql_id, plan_hash_value,
sum(execs) execs,
sum(etime) etime,
sum(etime)/sum(execs) avg_etime,
sum(cpu_time)/sum(execs) avg_cpu_time,
sum(lio)/sum(execs) avg_lio,
sum(pio)/sum(execs) avg_pio
from (
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
elapsed_time_delta/1000000 etime,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
buffer_gets_delta lio,
disk_reads_delta pio,
cpu_time_delta/1000000 cpu_time,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio,
(cpu_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta)) avg_cpu_time
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = '&&sqlid'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
)
group by sql_id, plan_hash_value
order by 5
/
==================================
------------processing sql id ------------------------
set linesize 200
set pagesize 40
col sql_plan_hash_value for 9999999999 heading 'Sql|Plan Hash'
col rows_processed for 999999999 heading 'Rows|Processed'
set verify off
col last_load for a19 heading "Last Load Time"
col plan_hash_value for 9999999999 heading "Plan|Hash Value"
select plan_hash_value,to_char(LAST_LOAD_TIME,'DD-MON-YY HH24:MI:SS') last_load,SORTS,FETCHES,EXECUTIONS,PARSE_CALLS,DISK_READS,DIRECT_WRITES,BUFFER_GETS,ROWS_PROCESSED,HASH_VALUE,OBJECT_STATUS from gv$sqlarea where SQL_ID = '&&sqlid';
=========================================
=====stale stat sql id ============
set lines 500
col table_owner for a15
col table_name for a30
col partition_name for a30
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 = '&&sqlid' UNION select distinct sql_id, object#, object_name, object_owner from dba_hist_sql_plan where sql_id = '&&sqlid') a
, sys.dba_tab_modifications b, dba_tab_statistics c
where a.sql_id = '&&sqlid'
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;
========================================================
======Plan change REport ==============
with samples as
(select *
from dba_hist_sqlstat st
join dba_hist_snapshot sn
using (snap_id, instance_number)
where
-- sql_id='sqlid'
-- parsing_schema_name = 'schema'
--and module 'DBMS_SCHEDULER' -- no sql tuning task
begin_interval_time between sysdate - '&num_days' and sysdate
and executions_delta > 0),
/* just statements that had at least 2 different plans during that time */
sql_ids as
(select sql_id,
count(distinct plan_hash_value) plancount
from samples
group by sql_id
having count(distinct plan_hash_value) > 2),
/* per combination of sql_id and plan_hash_value, elapsed times per execution */
plan_stats as
(select sql_id,
plan_hash_value,
min(parsing_schema_name),
count(snap_id) snap_count,
max(end_interval_time) last_seen,
min(begin_interval_time) first_seen,
sum(executions_delta) total_execs,
sum(elapsed_time_delta) / sum(executions_delta) elapsed_per_exec_thisplan
from sql_ids
join samples
using (sql_id)
group by sql_id, plan_hash_value),
/* how much different is the elapsed time most recently encountered from other elapsed times in the measurement interval? */
elapsed_time_diffs as
(select p.*,
elapsed_per_exec_thisplan - first_value(elapsed_per_exec_thisplan)
over(partition by sql_id order by last_seen desc) elapsed_per_exec_diff,
(elapsed_per_exec_thisplan - first_value(elapsed_per_exec_thisplan)
over(partition by sql_id order by last_seen desc)) / elapsed_per_exec_thisplan elapsed_per_exec_diff_ratio
from plan_stats p),
/* consider just statements for which the difference is bigger than our configured threshold */
impacted_sql_ids as
(select *
from elapsed_time_diffs ),
/* for those statements, get all required information */
all_info as
(select sql_id,
plan_hash_value,
-- parsing_schema_name,
snap_count,
last_seen,
first_seen,
total_execs,
round(elapsed_per_exec_thisplan / 1e6, 2) elapsed_per_exec_thisplan,
round(elapsed_per_exec_diff / 1e6, 2) elapsed_per_exec_diff,
round(100 * elapsed_per_exec_diff_ratio, 2) elapsed_per_exec_diff_pct,
round(max(abs(elapsed_per_exec_diff_ratio))
over(partition by sql_id), 2) * 100 max_abs_diff,
round(max(elapsed_per_exec_diff_ratio) over(partition by sql_id), 2) * 100 max_diff,
'select * from table(dbms_xplan.display_awr(sql_id=>''' || sql_id ||
''', plan_hash_value=>' || plan_hash_value || '));' xplan
from elapsed_time_diffs
where sql_id in (select sql_id from impacted_sql_ids))
/* format the output */
select
a.sql_id,
plan_hash_value,
-- parsing_schema_name,
a.snap_count,
total_execs,
to_char(a.elapsed_per_exec_thisplan, '999999.99') elapsed_per_exec_thisplan,
to_char(a.elapsed_per_exec_diff, '999999.99') elapsed_per_exec_diff,
to_char(a.elapsed_per_exec_diff_pct, '999999.99') elapsed_per_exec_diff_pct,
to_char(first_seen, 'dd-mon-yy hh24:mi') first_seen,
to_char(last_seen, 'dd-mon-yy hh24:mi') last_seen
--xplan
from all_info a where sql_id in (select distinct sql_id from all_info where elapsed_per_exec_diff_pct < -50)
order by sql_id, elapsed_per_exec_diff_pct;
=============================
=====explain plan from sql_id==============
SELECT t.*
FROM gv$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t
WHERE s.sql_id='&&sql_id';
=============================
Stale Stat check query
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 = '&&sqlid' UNION select distinct sql_id, object#, object_name, object_owner from dba_hist_sql_plan where sql_id = '&&sqlid') a
, sys.dba_tab_modifications b, dba_tab_statistics c
where a.sql_id = '&&sqlid'
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;
+++++++++++++++++++++++++++++++++++++++++++++++
To check unindexed foreign key columnn in database;
column columns format a30 word_wrapped
column table_name format a15 word_wrapped
column constraint_name format a15 word_wrapped
select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i,
user_indexes ui
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
and ui.table_name = i.table_name
and ui.index_name = i.index_name
and ui.index_type IN ('NORMAL','NORMAL/REV')
group by i.index_name
);
Blocking session:
============
select
(select username from v$session where sid=a.sid) blocker,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
ALTER SESSION SET EVENTS '10730 trace name context forever level [1, 2, 3]';
perfstats_query:
SELECT
SYSDATE,
sql_id,
sql_fulltext,
hash_value,
parsing_schema_name,
module,
first_load_time,
last_active_time,
parse_calls,
executions,
round(cpu_time / (executions * 1000000) ) AS cputime,
round(user_io_wait_time / (executions * 1000000) ) AS iowait,
round(elapsed_time / (executions * 1000000),2 ) AS elaptimesecs
FROM
gv$sqlarea
WHERE
executions != 0
AND parsing_schema_name NOT IN (
'SYS',
'SYSTEM',
'DBSNMP',
'PERFSTATS'
)
AND module NOT IN (
'SQL Developer',
'Toad'
)
and round(elapsed_time/(executions*1000000))>3
========
col BEGIN_INTERVAL_TIME for a30
select snap_id,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > systimestamp -1 order by BEGIN_INTERVAL_TIME
select snap_id,instance_number inst_id,sql_id,plan_hash_value,parsing_schema_name,EXECUTIONS_TOTAL,EXECUTIONS_DELTA,ELAPSED_TIME_TOTAL,ELAPSED_TIME_DELTA from DBA_HIST_SQLSTAT where sql_id=TRIM('&&sql_id.');
select sql_id,plan_hash_value,elapsed_time,executions from gv$sql where sql_id =TRIM('&&sql_id.');
select Inst_id,SQL_FULLTEXT,SQL_ID,EXECUTIONS,ELAPSED_TIME from gv$sqlarea where sql_id =TRIM('&&sql_id.') ;
SELECT STAT.SNAP_ID,STAT.SQL_ID, PLAN_HASH_VALUE, PARSING_SCHEMA_NAME,elapsed_time_total,executions_total,elapsed_time_delta, nvl(executions_delta,0) executions_delta ,round(ELAPSED_TIME_DELTA/1000000,2) exec_sec, SS.END_INTERVAL_TIME,SQL_PROFILE FROM DBA_HIST_SQLSTAT STAT, DBA_HIST_SQLTEXT TXT, DBA_HIST_SNAPSHOT SS
WHERE STAT.SQL_ID = TXT.SQL_ID AND STAT.DBID = TXT.DBID AND SS.DBID = STAT.DBID AND SS.INSTANCE_NUMBER = STAT.INSTANCE_NUMBER AND STAT.SNAP_ID = SS.SNAP_ID AND STAT.INSTANCE_NUMBER = 1 AND SS.BEGIN_INTERVAL_TIME >= sysdate-10 AND UPPER(STAT.SQL_ID) = upper('&sqlid') ORDER BY stat.snap_id desc
/
No comments:
Post a Comment