What is the real top Oracle Database SQL?

Posted on 3-Sep-2013 by Craig Shallahamer, craig@orapub.com

What is the real top SQL?

It is common for Oracle DBAs to talk about the top SQL. But for something to be top, there must be an associated ranking criteria. Look at the below pie chart. This is one way Stori's presents the top sql, that is, the SQL that offers us the most opportunity to improve performance.


But how did Stori come to this conclusion and how did Stori rank the SQL?

Conceptually, it's pretty straightforward. For example, suppose there is a raging CPU bottleneck. Stori will probably rank statements very high that consumes lots of CPU. Obviously Stori does much more, but I think you get the idea. (more: FAQs | Using Stori | How do I find the top SQL?)

Let's suppose I want to take matters into my own hands. The below output shows there is a CPU bottleneck. Therefore, I asked Stori for the top SQL based on CPU consumption.

How can I help you? get cpu utilization
.92

How can I help you? get sql id by cpu_time_s top 3
bgx1d4x6bk1vk dygn0jztzdg9g 8pq06s4k5vsm1

Just for fun, ask Stori, get sql id by cpu_time_s top 2 value. Notice anything different?

How can I help you? get sql id by cpu_time_s top 2 value
bgx1d4x6bk1vk (1312.502491) dygn0jztzdg9g (1178.448831)

The value just after the SQL ID the statement's CPU consumption in seconds over the analysis scope. This is one way to get a numeric perspective of the differences between the top statements.

Sometimes a chart is nice when telling the performance story. I'll create a top SQL pie chart based on CPU consumption like this:

How can I help you? get chart pie appl sql id by cpu_time_s
work/1377799860_appl_sql_cpu_pie.png

And here is the chart!

But what if the OS bottleneck is IO? Or what if we need to know the longest running (i.e., elapsed time) statements? Stori provides many SQL ranking options. These options can be seen by asking Stori for help and looking closely at the STAT options.

How can I help you? help get sql

  Here are the 'get sql' commands I understand.

  sql  id by STAT [top n, rank n] [module MODULE] [type TYPE] [value]
       (text, STAT) id SQLID
       (STAT, total STAT) [top n, rank n] [type TYPE] 

     STAT   : elapsed_time_s, cpu_time_s, buffer_gets,
              sorts, executions, parse_calls, disk_reads,
              direct_writes
     TYPE   : query, dml, other
     MODULE : Module name (% OK, replace space with '_')
     SQLID  : the actual SQL ID

     Examples: get sql id by elapsed_time_s top 3
               get sql id by cpu_time_s module ModuleABC
               get sql elapsed_time_s id 7aw349n5chnzz
               get sql total buffer_gets
    
  top (sql|sql_id, module, program|program_id, command|command_class) [n] [value]

Looking at the STAT options, you can see the SQL ranking options are elapsed time, cpu consumption, buffer gets (logical IO), sorts, the number of executions, the number of parse calls, the number of physical blocks read, and direct writes. You can even specify the type of SQL as either query, dml, or other.

So when you ask Stori, get top sql realize that Stori looks at what occurred at the operating system, the Oracle activity, and also SQL activity. Still, sometimes when exploring the performance situation, I want to get SQL ranked a specific way. And Stori frees me to do that as well!

All the best in your Oracle database performance tuning work!

Craig.

If you have any questions or comments, feel free to email me directly at craig at orapub.com.

How To Tell If The IO Subsystem Reads Are Struggling 5 Steps For DBAs To Learn More And Work Less How OS CPU Run Queue Time Relates To Oracle Database Time