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
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.
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.
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.
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