Pages

Tuesday, March 5, 2019

Generate Oracle Reports. AWR, ADDM, perfstat, execution_plan for sql_id

============================
ADDM
============================
Automatic Database Diagnostic Monitor (ADDM)

#! /bin/bash
. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/addmrpt.sql

============================
perfstat
============================
#! /bin/bash
. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/spreport.sql

#for specific SQL
#@sqlplus perfstat/iwantawr@igt @/software/oracle/111/rdbms/admin/sprepsql.sql

============================
AWR
============================
#! /bin/bash
. /etc/sh/orash/oracle_login.sh igt
sqlplus system/Xen86Pga@igt @${ORACLE_HOME}/rdbms/admin/awrrpt.sql

============================
Get execution plan by sql_id
============================
select sql_id, child_number, sql_text
from   v$sql 
where  sql_text like '%MY SQL TEXT%'
and    sql_text not like '%v$sql%';


sql_id = bp3rw1djnxjkr
CHILD_NUMBER = 0

SELECT * FROM TABLE(DBMS_XPLAN.display_awr('bp3rw1djnxjkr',format => 'TYPICAL +PEEKED_BINDS'));

============================
Reference
============================
Automatic Database Diagnostic Monitor (ADDM) 

PERFSTAT Report and Statspack details

1 comment: