Perf Tuning/ Trace/Gather Stats

How to Use AWR Reports to Diagnose Database Performance Issues (Doc ID 1359094.1)
https://carlos-sierra.net/tag/execution-plan/
http://kerryosborne.oracle-guy.com/category/oracle/plan-stability/
https://mauro-pagano.com/2015/05/04/historical-sql-monitor-reports-in-12c/
https://community.oracle.com/thread/3269017 SQL review from an DBA perspective

Enable Trace and Generate TKPROF
SELECT p.PID, p.SPID, s.SID,s.serial# FROM v$process p, v$session s WHERE s.paddr = p.addr AND s.SID = 2414 ;
PID SPID SID

---

552 4999 2414

ORADEBUG SETOSPID 5382
ORADEBUG UNLIMIT
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER,LEVEL 12
ORADEBUG TRACEFILE_NAME

SQL> ORADEBUG SETOSPID 5382
Oracle pid: 366, Unix process pid: 2244, image: oracle@123.nus.sri.com
SQL> ORADEBUG UNLIMIT
Statement processed.
SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER,LEVEL 12
Statement processed.
SQL> ORADEBUG TRACEFILE_NAME
/u01/app/db_1/diag/rdbms/SR12/SR/trace/SR_ora_2244.trc
SQL>

1. Turn off the SQL trace for the session.

   SQL> ORADEBUG SETOSPID 5382
   SQL>oradebug event 10046 trace name context off 

Others:
—————-
alter system set events ‘sql_trace[sql: cjrha4bzuupzf] level=12’;
alter system set events ‘sql_trace[sql: cjrha4bzuupzf] off’;

tkprof aa.trc aa.tkprof explain=apps/ZW48Acbrtkmts sort=prsela,exeela,fchela sys=no

SQLID TEXT AND Plan Details

SELECT inst_id,sql_id,LAST_LOAD_TIME, LAST_ACTIVE_TIME ,PLAN_HASH_VALUE,SQL_FULLTEXT,ROWS_PROCESSED,EXECUTIONS,sql_plan_baseline,sql_profile
FROM   gV$SQl a
WHERE  a.sql_id ='7ttgy18puazt6'
ORDER BY LAST_ACTIVE_TIME desc;

–> awr_high_cost_sql.sql

select p.sql_id,p.PLAN_HASH_VALUE,p.TIMESTAMP,p.cost
from dba_hist_sql_plan p,dba_hist_sqltext s
where p.id = 0 and p.sql_id = s.sql_id
and p.sql_id='8ap1zyhp2q7xd'
order by p.TIMESTAMP desc;

Select * from table(dbms_xplan.display_cursor(‘9w6zqap3f5xbw’));
Select * from table(dbms_xplan.display_awr(‘9w6zqap3f5xbw’));
Select * from table(dbms_xplan.display_awr(‘9w6zqap3f5xbw’,’2095498791′));
Select * from table(dbms_xplan.display_awr(‘9w6zqap3f5xbw’,’2716887413′));

@?/rdbms/admin/sqltrpt.sql–> SQL Tuning Advisor
coe_xfr_sql_profile.sql – -> To Create SQL Profile
@?/rdbms/admin/awrrpti.sql – -> To Create AWR by Node
@?/rdbms/admin/awrrpt.sql – -> To Create AWR by DB
@?/rdbms/admin/awrsqrpt.sql – -> To Create AWR by SQL
@?/rdbms/admin/ashrpt.sql – -> To Create ASH
@?/rdbms/admin/utlrp.sql – -> Recompile Invalids

select object_name,owner,created,status,object_type,’/*’ || created || ‘*/’ || dbms_metadata.get_ddl(‘INDEX’,object_name,owner)
from dba_objects
where object_type = ‘INDEX’ and object_name in
(select INDEX_NAME from dba_indexes where table_name in (‘GL_PERIOD_STATUSES’))
order by created desc, object_name;

select * from DBA_IND_COLUMNS where table_name=’GL_PERIOD_STATUSES’;

–> ADDMM Suggestin

Set pages 1000
Set lines 75
Select a.execution_end,a.OWNER,a.TASK_NAME,a.DESCRIPTION,a.ADVISOR_NAME, b.type, b.impact, d.rank, d.type,
'Message           : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message    : '||c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,
Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
--And a.task_name like 'ADDM%'
and a.status='COMPLETED'
and trunc(a.execution_end)>sysdate -1
Order by a.execution_end,b.impact, d.rank;

Gather Stats /Stale :

select OWNER,table_name,stale_stats,last_analyzed,NUM_ROWS,blocks,OBJECT_TYPE from
dba_tab_statistics where table_name=’WF_ITEMS’;

SELECT ob.owner, ob.object_name, ob.object_type, savtime,analyzetime,
DECODE(rowcnt,0,0,round((samplesize/rowcnt)*100,0)) "Sample(%)",
flags, rowcnt, blkcnt, avgrln ,samplesize
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE 1=1
and object_name='WF_ITEMS'
and object_type in ('TABLE')
and object_id=obj#
and  savtime > SYSDATE - 30
order by savtime desc;

select ‘exec fnd_stats.gather_table_stats(”’||owner||”’,”’||table_name||”’,percent=>25,degree=>8,cascade=>true);’ ” GSS”
from dba_tables where table_name in (‘WF_ITEMS’);
set lines 300 pages 300
select ‘exec DBMS_STATS.GATHER_TABLE_STATS(ownname =>”’||owner||”’,tabname =>”’||table_name||”’,ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,degree=>8,cascade=>true);’ ” GSS”
from dba_tables where table_name in (‘WF_ITEMS’);

SELECT owner,
segment_name,
segment_type,
tablespace_name,
round(bytes/1048576/1024,2) GB,
initial_extent,
next_extent,
extents,
pct_increase
FROM
DBA_SEGMENTS
WHERE
SEGMENT_TYPE = ‘TABLE’
AND SEGMENT_NAME in (‘HR_LOCATIONS_ALL_TL’,’HR_LOCATIONS_ALL’);

Select * from DBA_TAB_MODIFICATIONS  where table_name='WF_ITEMS';

SELECT m.table_owner
, m.table_name
, t.last_analyzed,m.timestamp
, m.inserts
, m.updates
, m.deletes
, t.num_rows
, round(( m.inserts + m.updates + m.deletes ) / CASE WHEN t.num_rows IS NULL OR t.num_rows = 0 THEN 1 ELSE t.num_rows END,0) "Change Factor"
FROM dba_tab_modifications m
, dba_tables t
WHERE t.owner = m.table_owner
AND t.table_name = m.table_name
AND m.inserts + m.updates + m.deletes > 1
AND m.table_name='WF_ITEMS'
and round(( m.inserts + m.updates + m.deletes ) / CASE WHEN t.num_rows IS NULL OR t.num_rows = 0 THEN 1 ELSE t.num_rows END,0) >1
ORDER BY "Change Factor" DESC;

select trunc(last_analyzed),count(*)
from dba_tables
where owner  not in ('SYS','SYSTEM')
group by trunc(last_analyzed)
order by 1 desc;