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



R12.1/R12.2 are pretty big  and time consuming upgrade. We need to find all the long running sql in order to resolve the  R12.2 upgrade performance issues. Since each iteration takes lot of time,it is important ,we tried to find out the performances issues in less iterations.

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 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 workers of a job.

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 / _rowsource_execution_statistics = TRUE. It should be run with out any  delay to get all the information as otherwise these information will be flushed out of SGA

SET pages 0

SET lines 300

SET LONG 10000

SET LONGCHUNKSIZE 10000

SPOOL<report_name>.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<report_name>.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 longchunksize 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

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

How to generate the AWR reports
1) Go to $ORACLE_HOME/rdbms/admin

2) Run awrrpt.sql generate the AWR reports.

3) Always choose 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

All about AWR ( Automatic Workload Repository)

AWR repository

Oracle ASH(Active Session History) Useful queries

Oracle Performance Tuning

How to create ADDM task and check its report

How to find session details in Oracle database


Leave a Reply