How to Analyze an AWR Report in Oracle -4 TOP SQL

Hi,

I will continue to explain How to Analyze or Interpret an AWR report in Oracle in this article.

 

 

Read previous Articles before this.

 

 

SQL Statistics

One of the most important sections of the AWR reports is the section that provides most resource consuming SQLs ( called TOP SQLs ). This section lists the TOP SQLs according to the following criteria. .

 

SQL ordered by Elapsed Time

First TOP SQL section is SQL ordered by Elapsed Time which includes Top Elapsed times SQLs. Top elapsed time SQLs are ordered according to elapsed time that took maximum execution time during processing

 

 

 

SQL ordered by CPU Time

This section lists the most CPU-consuming SQLs in the database. SQL that spend the most CPU time are candidates for SQL Tuning, they are immediately examined and if it is problem , then SQL Tuning is done.

 

 

 

SQL ordered by User I/O Wait Time

This section lists SQLs ordered by User I/O wait time in the database.

 

 

 

 

SQL ordered by Gets

This section lists SQLs ordered by Gets that TOP logical reading SQLs from Buffer Gets in the database

 

 

 

SQL ordered by Physical Reads (UnOptimized)

This section list the SQLs that perform the highest Physical I/O in the database. The tables that are used by queries that make physical I / O in this section are generally non-indexed. If so, index is created on the related tables and related column.

SQL ordered by Executions

This section list the SQLs that perform the highest executions in the database. Most executed SQLs are candidates for SQL Tuning, they are immediately examined and if it is problem , then SQL Tuning is done.

 

 

You can access details of SQLs ( Full text SQL )  by clicking SQL ID in the TOP SQL Sections like following.

 

 

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

2 thoughts on “How to Analyze an AWR Report in Oracle -4 TOP SQL

  • August 28, 2019 at 2:39 pm
    Permalink

    Hi Mehmet,
    If the CPU usage is high, which are the area to focus on AWR, SQL Bases on CPU time? Any pointers?

    Regards,
    Chhetri

    Reply
    • August 28, 2019 at 3:46 pm
      Permalink

      Hi Shiva,

      You should check DB CPU on Load Profile sections and CPU Wait on TOP Foreground events sections.

      Reply