Tuning Scripts

------------------------------ Scripts Required for Tuning -------------------------------


select * from (select x.cpu_time,x.executions,x.cpu_exec_ratio,x.parsing_schema_name,sql_text, x.sql_id,x.module from sys.dba_hist_sqltext dhst,
(select dhss.sql_id sql_id,sum(dhss.cpu_time_delta) cpu_time,sum(executions_delta) EXECUTIONS,
avg(cpu_time_delta /decode(executions_delta, 0, 1,executions_delta)) cpu_exec_ratio,
max(module) module,max(parsing_schema_name) parsing_schema_name
from dba_hist_sqlstat dhss
where dhss.snap_id in
(select snap_id from dba_hist_snapshot where
begin_interval_time>=to_date('17/05/2012 00:00:01','DD/MM/YYYY HH24:MI:SS')
and end_interval_time<=to_date('17/05/2012 11:59:59','DD/MM/YYYY HH24:MI:SS')) group by dhss.sql_id) x
--where x.sql_id=dhst.sql_id order by x.cpu_exec_ratio desc) where rownum<30;
where x.sql_id=dhst.sql_id order by x.cpu_time desc) where rownum<10;



select * from table(DBMS_XPLAN.DISPLAY_AWR('1bf4h2j1qv5mt'));




select * from
(
   select
      DECODE
      (GROUPING(a.object_name), 1, 'All Objects', a.object_name)
   AS "Object",
sum(case when
   a.statistic_name = 'ITL waits'
then
   a.value else null end) "ITL Waits",
sum(case when
   a.statistic_name = 'buffer busy waits'
then
   a.value else null end) "Buffer Busy Waits",
sum(case when
   a.statistic_name = 'row lock waits'
then
   a.value else null end) "Row Lock Waits",
sum(case when
   a.statistic_name = 'physical reads'
then
   a.value else null end) "Physical Reads",
sum(case when
   a.statistic_name = 'logical reads'
then
   a.value else null end) "Logical Reads"
from
   v$segment_statistics a
where
   a.owner like upper('&owner')
group by
   rollup(a.object_name)) b
where (b."ITL Waits">0 or b."Buffer Busy Waits">0)






select task_id, type, message
from dba_advisor_findings
where impact= ( select MAX(impact) from dba_advisor_findings)


select attr4
from dba_advisor_objects
where task_id=120082


select trim(attr1), ATTR1, TRIM(attr2) ATTR2, TRIM(attr3) ATTR3
FROM dba_advisor_actions
WHERE task_id=120082

select message
from dba_advisor_rationale
where task_id=120082



select
   a.average_wait                                  c1,
   b.average_wait                                  c2,
   a.total_waits /(a.total_waits + b.total_waits)  c3,
   b.total_waits /(a.total_waits + b.total_waits)  c4,
   (b.average_wait / a.average_wait)*100           c5
from
  v$system_event  a,
  v$system_event  b
where
   a.event = 'db file scattered read'
and
   b.event = 'db file sequential read';
 
 
 
 
---  Which OPTIMIZER_GOAL / OPTIMIZER_MODE is being used?
 
SELECT name,value
FROM v$parameter
WHERE name = 'optimizer_mode';




--- Have the tables been analyzed? What is the level of analysis? Do any tables have a degree of parallelism?

SELECT table_name, num_rows, degree
FROM dba_tables
WHERE owner = 'JISPBILCORBILLINGPRD501'
AND table_name = 'TBLTCUSTOMERRECHARGEHISTORY';





--- Index details (column names and the order they appear in indexes)


SELECT c.index_name, i.uniqueness, c.column_name, c.column_position
FROM user_ind_columns c, user_indexes i
WHERE i.table_name = 'TBLTCUSTOMERRECHARGEHISTORY'
AND i.index_name = c.index_name
ORDER BY c.index_name,
c.column_position;


SELECT name, value FROM v$sysstat
WHERE name='sorts (memory)' ORDER BY name;




-- sessions with highest CPU consumption
--======================================================================================
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as
"CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;



-- sessions with the highest time for a certain wait
--======================================================================================
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;



-- sessions with highest DB Time usage
--======================================================================================
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as
"DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "%
CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND  sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;




AWR Collection Interval change
--===============================================================================

execute dbms_workload_repository.modify_snapshot_settings (retention=>43200);

-- 43200 in minutes

execute dbms_workload_repository.modify_snapshot_settings (interval=>60, retention=>43200);