What is the real top Oracle Database SQL?
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!
Start my FREE 18 lesson Machine Learning For Oracle Professionals E-Course here.
Craig Shallahamer is a long time Oracle DBA who specializes in predictive analytics, machine learning and Oracle performance tuning. Craig is a performance researcher and blogger, consultant, author of two books, an enthusiastic conference speaker a passionate teacher and an Oracle ACE Director. More about Craig Shallahamer...
If you have any questions or comments, feel free to email me directly at craig at orapub.com.
|Tool Options: Detailing Oracle Database Process CPU Consumption||Oracle Database SQL Arrival Patterns And Impact||How To Tell If The IO Subsystem Reads Are Struggling|