Oracle Database Performance Troubleshoot­ing — Top-10 SQL Queries

Although Oracle RDBMS is a relatively old technology, it continues to power many critical cloud applications and services, such as Salesforce, Amazon, and SAP as well as large enterprises, even as alternatives like NoSQL databases gain traction in the database world.

While many database optimization tools are available, we have found that the following queries are typically sufficient to identify the majority of issues during software development when using Oracle RDBMS as the back-end.

Total CPU Usage in %

Total CPU usage in % (in one minute averages, same as OS usage reported by top):

SELECT to_char(h.begin_time, ' HH24:Mi:SS ') t,
       h.VALUE
FROM v$sysmetric_history h
WHERE h.metric_id =
    (SELECT metric_id
     FROM v$sysmetric_summary
     WHERE metric_name='Host CPU Utilization (%)' )
ORDER BY t ASC;

The v$sysmetric_summary table is, in general, a source of useful performance metrics:

SELECT metric_id, metric_name FROM v$sysmetric_summary;

Temporary Space Usage in % (using current temp file allocation)

SELECT nvl(100*(u.tot/d.tot), 0) pct_temp_used
FROM
  (SELECT SUM(u.blocks) tot
   FROM v$tempseg_usage u) u,

  (SELECT SUM(d.blocks) tot
   FROM dba_temp_files d) d;

Temporary space usage (using max allowed temp file allocation)

SELECT nvl(100*(u.tot/d.tot), 0) pct_temp_used
FROM
  (SELECT SUM(u.blocks) tot
   FROM v$tempseg_usage u) u,

  (SELECT SUM(d.maxblocks) tot
   FROM dba_temp_files d) d;

Tablespaces Usage (Total, Available in MB and %)

SELECT a.TABLESPACE_NAME,
       t.status,
       a.datafiles_no datafiles_,
       t.CONTENTS type_,
       t.EXTENT_MANAGEMENT,
       t.segment_space_management segment_management,
       ROUND(b.TABLESPACE_SIZE*t.block_size/1024/1024,2) size_MB,
       ROUND((b.TABLESPACE_SIZE - b.USED_SPACE)*t.block_size/1024/1024,2) free_MB,
       ROUND(b.USED_PERCENT,2) percent_used,
       ROUND(100 - b.USED_PERCENT,2) percent_free
FROM
  (SELECT TABLESPACE_NAME,
          SUM(BYTES) BYTES,
          COUNT(*) datafiles_no
   FROM DBA_data_files
   GROUP BY TABLESPACE_NAME) a,
     DBA_TABLESPACE_USAGE_METRICS b,
     dba_tablespaces t
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME
  AND a.TABLESPACE_NAME=t.TABLESPACE_NAME
ORDER BY percent_used DESC;

Unusable Indexes

SELECT OWNER,
       index_name,
       tablespace_name,
       status
FROM dba_indexes
WHERE status='UNUSABLE';

Top 50 SQL Queries by CPU Consumption

SELECT *
FROM
  (SELECT s.sql_id,
          s.executions,
          ROUND(s.cpu_time / 1000000,2) sec,
          s.sql_text
   FROM v$sqlstats s
   ORDER BY sec DESC)
WHERE rownum <= 50;

Top 50 SQL Queries by Execution Time

SELECT *
FROM
  (SELECT BEGIN_TIME,
          ROUND(MAXQUERYLEN / 60, 2) minutes,
          undoblks,
          ph.sql_id,
          ph.sql_text
   FROM v$undostat u
   JOIN v$sqlarea_plan_hash ph ON u.maxqueryid = ph.sql_id
   ORDER BY MAXQUERYLEN DESC)
WHERE ROWNUM <= 50;

Top 20 CPU-Intensive Sessions

SELECT *
FROM (
SELECT s.sid,
       p.terminal,
       s.username,
       decode(nvl(p.background, 0), 1, bg.description, s.program) program,
       ss.VALUE / 100 cpu_sec,
       physical_reads disk_io
FROM v$process p
JOIN v$session s ON s.paddr = p.addr
JOIN v$sesstat ss ON ss.sid = s.sid
JOIN v$sess_io si ON si.sid = s.sid
LEFT JOIN v$bgprocess bg ON bg.paddr = p.addr
WHERE ss.statistic# = 12 ORDER BY ss.VALUE DESC) WHERE rownum <= 20;

Invalid Objects

SELECT o.owner,
       O.OBJECT_NAME,
       O.OBJECT_TYPE,
       o.status
FROM dba_objects o,
     dba_users u
WHERE o.owner = u.username
  AND o.status <> 'VALID'
  AND o.owner NOT IN ('SYS',
                      'SYSTEM',
                      'SYSMAN',
                      'INTERNAL',
                      'PUBLIC')
  AND u.ACCOUNT_STATUS = 'OPEN';

Average Redo Log Switches (Last 100 Days)

SELECT nvl(ROUND(AVG(x.SWITCHES), 2), 0)
FROM
  (SELECT TO_CHAR(TRUNC(FIRST_TIME), 'Month') MONTH,
          to_char(trunc(first_time), 'DD-Mon-YYYY') Archive_Date,
          COUNT(*) Switches
   FROM v$log_history
   WHERE TRUNC(first_time) > last_day(SYSDATE - 10) + 1
   GROUP BY TO_CHAR(TRUNC(FIRST_TIME), 'Month'),
            to_char(trunc(first_time), 'DD-Mon-YYYY')
   ORDER BY 1, 2) x;

Summary

Although often behind the scenes, databases are a cornerstone of modern cloud services, powering many enterprise applications.

At Nexteris, we have extensive experience in developing applications for and integrating data from Oracle RDBMS, while ensuring optimal database performance.

Contact us today to find out how we can help you!