Home » Oracle » Oracle Ebuisness Suite » Top AWR useful queries for R12.2/R12.1 Upgrade

Top AWR useful queries for R12.2/R12.1 Upgrade

R12.1/R12.2 is a pretty big and time-consuming upgrade. We need to find all the long-running sql to resolve the  R12.2 upgrade performance issues. Since each iteration takes a lot of time, it is important, that we try to find out the performance issues in fewer iterations and fix them accordingly

R12.2 upgrade performance issues

Here are Top AWR useful queries to solve R12.2 upgrade performance issues

If SQL is still in memory (cursor cache) the following can be used to identify long-running SQLs that may not have been written to the AWR yet (at the last snapshot)

SELECT * FROM
 (SELECT
 ss.sql_id,
 ROUND(SUM(ss.elapsed_time/1000000),0) elapsed_time_secs,
 ROUND(SUM(ss.cpu_time/1000000),0) cpu_time_secs,
 SUM(ss.disk_reads) disk_reads,
 SUM(ss.direct_writes) direct_writes,
 SUM(ss.buffer_gets) buffer_gets,
 SUM(ss.px_servers_executions) px_server_execs,
 SUM(ss.rows_processed) rows_processed,
 SUM(ss.executions) executions,
 SUM(ss.application_wait_time) apwait_secs,
 SUM(ss.sharable_mem) sharable_mem,
 SUM(ss.total_sharable_mem) total_sharable_mem
 FROM v$sqlstats ss
 GROUP BY ss.sql_id
 ORDER BY 2 DESC)
 WHERE ROWNUM <= 100;

The following SQL script will report the longest-running SQL between two AWR snapshots

SELECT * FROM 
 (SELECT
 dhs.sql_id,
 ROUND(SUM(dhs.elapsed_time_delta/1000000),0) elapsed_time_secs,
 ROUND(SUM(dhs.cpu_time_delta/1000000),0) cpu_time_secs,
 SUM(dhs.disk_reads_delta) disk_reads,
 SUM(dhs.buffer_gets_delta) buffer_gets,
 SUM(dhs.px_servers_execs_delta) px_server_execs,
 SUM(dhs.rows_processed_delta) rows_processed,
 SUM(dhs.executions_delta) executions,
 ROUND(SUM(dhs.iowait_delta/1000000),0) iowait_secs,
 ROUND(SUM(dhs.clwait_delta/1000000),0) clwait_secs,
 ROUND(SUM(dhs.ccwait_delta/1000000),0) ccwait_secs,
 ROUND(SUM(dhs.apwait_delta/1000000),0) apwait_secs
 FROM dba_hist_sqlstat dhs
 ,v$database d
 WHERE dhs.dbid = d.dbid
 AND snap_id > &begin_snap   and snap_id <= &end_snap
 GROUP BY dhs.sql_id
 ORDER BY 2 DESC)
 WHERE ROWNUM <= 100;

Where &begin_snap and &end_snap are the start and end snapshot IDs.
The output of this statement will look similar to the following:

SQL_ID ELAPSED_TIME_SECS CPU_TIME_SECS DISK_READS BUFFER_GETS….
 ------------- ----------------- --------------- ---------- ----------- ….
 5vaxut40xbrmr 367440 42999 34838244 3795838289 ….
 943ra4b7zg28x 264369 170788 441127 562033013 ….
 fkkrk9frwqfdr 70370 6448 3599284 469639133 ….
 4847s6dt6sds9 68298 38896 7125573 1327384554 ….
 2k3uw8n473r30 63600 27402 20043712 587615960 ….

Note: The elapsed time is the maximum elapsed time for all job workers.

See also  Relink Oracle: All, Sqlplus, Oracle ,forms, lsnrctl

Enterprise Manager can also be used to identify expensive SQL as it occurs.

Obtain Display Cursor Report for Long-Running SQL

For this  STATISTICS_LEVEL=ALL and _rowsource_execution_statistics = TRUE. It should be run without any  delay to get all the information otherwise this information will be flushed out of SGA

SET pages 0 
SET lines 300 
SET LONG 10000
SET LONGCHUNKSIZE 10000 
SPOOL <report>.txt 
SELECT * FROM TABLE(dbms_xplan.display_cursor('<SQL ID>', NULL, 'ALL +ALLSTATS')); 
 SPOOL OFF; 

If the SQL is no longer in memory but is in the AWR, use the Display AWR report instead:

SET pages 0 
SET lines 300 
SET LONG 10000 
SET LONGCHUNKSIZE 10000 
SPOOL .txt 
SELECT * FROM TABLE(dbms_xplan.display_awr('<SQL ID>', NULL, NULL, 'ALL')); 
SPOOL OFF; 

Note: Be aware that the Display AWR report (DBMS_XPLAN.DISPLAY_AWR) does not report on actuals: it does not have a +ALLSTATS option, and there are no actual statistics for execution plan steps stored in AWR

Important Note: the display cursor and AWR reports only show the sql_text (first 1000 characters) and not the sql_fulltext. So, if necessary, run the following SQL script to obtain the full SQL text

SET pages 0 
SET lines 300 
SET LONG 10000 
SET LONGCHUNKSIZE 10000 
SPOOL<report_name>.txt 
SELECT sql_id, sql_text, sql_fulltext FROM v$SQL 
WHERE sql_id = '<sql_id>'; 
SPOOL OFF;  

Obtain SQL Monitor Report for SQL Using Parallel Query/DML

The main advantage of this is that it gives a good view of how parallel SQL/DML performs across stages of the plan and parallel slaves

set trimspool on 
set trim on 
set pages 0 
set long 10000000 
set long chunksize 10000000 
set linesize 200 
set termout off 
spool sql_monitor_for_<sql_id>.htm 
variable my_rept CLOB; 
BEGIN :my_rept := dbms_sqltune.report_sql_monitor(sql_id => '<sql_id>', report_level => 'ALL', type => 'HTML'); 
END; 
/ 
print :my_rept spool off; 
set termout on  

Where &begin_snap and &end_snap are the start and end snapshot IDs.

See also  How to check Tablespace in Oracle -Size,Free space,datafiles ,growth

How to find out when the particular sql ran

SELECT 
 dhs.sql_id, 
 dsn.snap_id, 
 dsn.begin_interval_time, 
 dsn.end_interval_time, 
 ROUND(SUM(dhs.elapsed_time_delta/1000000),0) elapsed_time_secs 
 FROM dba_hist_sqlstat dhs 
 ,v$database d ,dba_hist_snapshot dsn 
 WHERE dhs.dbid = d.dbid
 AND dsn.snap_id = dhs.snap_id 
 AND dsn.dbid = dhs.dbid
 AND dsn.instance_number = dhs.instance_number AND dhs.sql_id = '<SQL ID>' 
 AND dsn.snap_id > &begin_snap  and dsn.snap_id <= &end_snap
 GROUP BY dhs.sql_id, dsn.snap_id, dsn.begin_interval_time, dsn.end_interval_time 
 ORDER BY dsn.snap_id;

Where &begin_snap and &end_snap are the start and end snapshot IDs.
The output of this statement will look similar to the following:

SQL_ID SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME ELAPSED_TIME_SECS
 
 2k3uw8n473r30 8278 04-JAN-13 23.00.25.5560 05-JAN-13 00.00.21.1620 23123
 2k3uw8n473r30 8279 05-JAN-13 00.00.21.1620 05-JAN-13 01.00.38.2680 37145

How to find the CBO stats on Ebiz Environment

SELECT owner, table_name, num_rows, TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS') last_analyzed 
 FROM all_tables
 WHERE owner IN 
 (SELECT upper(oracle_username) sname
 FROM fnd_oracle_userid 
 WHERE oracle_id BETWEEN 900 AND 999
 AND read_only_flag = 'U' 
 UNION ALL
 SELECT DISTINCT upper(oracle_username) sname 
 FROM fnd_oracle_userid a,fnd_product_installations b
 WHERE a.oracle_id = b.oracle_id 
 )
 ORDER BY owner, table_name; 

The output of this statement will look similar to the following:

OWNER TABLE_NAME NUM_ROWS LAST_ANALYZED
 --- ---------    ---------- ------------------------
 ABM ABM_ACC_MAP_SUM_REP 0 06-DEC-2016 08:46:33
 ABM ABM_ACT_ACC_RU_DAT 0 06-DEC-2016 08:46:35
 ABM ABM_ACT_STA_RU_DAT 0 06-DEC-2016 08:46:36

How to get the AWR reports after the upgrade

AWR reports  can be obtained for
• The whole period that the upgrade is running.
• For the duration of long-running jobs (i.e. between the snapshots taken just before the job starts and just after it finishes).
• Each snapshot.

How to generate the AWR reports
(1) Go to $ORACLE_HOME/rdbms/admin
(2) Run awrrpt.sql to generate the AWR reports.
(3) Always choose the HTML report type.
(4) On an Oracle RAC instance, awrrpti.sql will usually suffice, as the upgrade will be run on one Oracle RAC node only.
AWR reports can be automated. This is useful if producing a large number of AWR reports, particularly for successive snapshots. See the “Automating AWR Reports” section in My Oracle Support document “Performance Diagnosis with Automatic Workload Repository (Document 1674086.1)”.
Note that some fixed objects and dictionary objects (particularly WRH$_LATCH_CHILDREN, especially if statistics_level = ALL, or there is a high retention period or a short snapshot interval) will have grown significantly during the upgrade. So, fixed object and dictionary statistics may need to be gathered before running AWRs.

See also  How to check if Port pool is free in Oracle applications R12

Related Articles

Automatic Workload Repository
Oracle ASH(Active Session History)
Oracle Performance Tuning
How to create ADDM task and check its report
How to find session details in Oracle database

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top