Stori Logo




Analysis Results

This Stori analysis is based upon the ABC123 database Statspack data from 04-Dec-2012 03:00:04 to 04-Dec-2012 04:00:03. Details: host udbsou20, dbid 1523308526, instance 1, snap 14515 to 14525, Stori version 0.28-0.11-0.13.

Recommendations

  • For SQL ID 7aw349n5chnzz focus on reducing elapsed time and CPU time. (rank: .644)

  • For module oracle@uaadc142.arxbs.redbrick.org.pe_(TNS_V1-V3 focus on reducing elapsed time by reducing CPU consumption and by appropriately increasing IO activity. (rank: .204)

  • Look for opportunities to parallelize IO intensive processes, which will increase the IO workload on the server and decrease process duration. (rank: .155)

Application Performance Analysis

1375295334_appl_sql_opr_pie.png

Combining the operating system and Oracle system performance analysis, the application analysis focus will center squarely on identifying SQL statements which directly contribute to the performance problems.

Oracle processes spend more time consuming CPU (24269 secs) compared to waiting for non CPU resources (10962 secs). This information combined with what is occurring at the operating system level and in Oracle will guide us in our search for the key application SQL.

CPU Consumption

1375295336_appl_sql_cpu_pie.png

We want to find opportunities to reduce CPU consumption. This means focusing on the high CPU consuming SQL; identifying them and then either tuning them or reducing their execution rate. For example, the top CPU consuming SQL statement (SQLID=7aw349n5chnzz) consumes 77% of all Oracle CPU resources (SQL and anything else). More details are provided in the below SQL And PL/SQL Statement Analysis section.

IO Consumption

1375295340_oracle_io_x_BB.png

While Oracle processes are IO read intensive, the IO subsystem is responding quickly. This presents us with an opportunity! We want to creatively use available IO resources. Look for long running IO intensive SQL statements or an IO intensive batch job running as a single serial process; it will likely have a low execution count along with a high elapsed time. By modifying the application to parallelize the SQL or batch job, its duration will decrease because multiple processes will run concurrently. It is like having a single large shopping list but having three people find specific items instead of one person finding all the items. This approach will consume unused IO resources while reducing run times.

For example, the top physical read consuming SQL statement (SQLID=7aw349n5chnzz) consumes 91% of all physical blocks read.

1375295339_appl_sql_pior_pie.png

Slow running IO intensive OLTP SQL that can not be parallelized will need to be tuned: A SQL statement requiring two million physical IOs with each physical IO taking only 2 ms will still run in 4 seconds.

Programatic Module Activity

1375295335_appl_module_x_pie.png

Application code associated with one or more of the 8 specific modules is playing an important role in the system. While it is important to look at specific statements with an overall perspective, many times users experience issues related to multiple PL/SQL and SQL statements functionally grouped together.

Module analysis allows us to grasp how multiple good performing statements can together result in a poor user experience. When this activity grouping is assigned a module name, we can analyze their activity together. Then by focusing on the PL/SQL and SQL within the identified module, performance tuning efforts can directly impact the user experience. Generally speaking, focus on reducing elapsed time by reducing CPU consumption and by appropriately increasing IO activity.

Top Module Matrix

Below is a matrix for the top five modules. The ranking variable, Opportunity Rating (OPR), is an OraPub proprietary method relating performance improvement opportunity between modules. The higher the Opportunity Rating (OPR) value and the larger the different between modules directs us to focus on the higher value module.

Referencing the matrix, the module presenting us with the greatest opportuninty is, oracle@uaadc142.arxbs.redbrick.org.pe_(TNS_V1-V3 containing 41% of all the opportunity.

You can ask Stori to create this matrix with, "is top module matrix".

Top Module Matrix

Module Name EL CPU LIO PIO Srt Exe Par DW OPR
oracle@uaadc142.ar 1 1 1 1 4 2 1 2 1.83
pmdtm@uaadc149_(TN 2 2 2 2 3 3 3 1 .88
sqlplus@udbsou20_( 4 4 4 4 4 4 4 2 .15
TOAD_11.6.0.43 5 5 5 5 2 5 5 2 .12

EL: elapsed time, CPU: CPU time, LIO: logical IOs, PIO: physical IO block reads, Srt: sorts to disk, Exe: executions, Par: parses, DW: direct writes OPR: opportunity rating

Top SQL Within Top Module Matrix

Within the top module are many PL/SQL and SQL statements. Performance can likely be improved by focusing on the top statements within this module! Shown below are these top five statements.

Referencing the matrix, focus on the statements with the greatest opportunity. It is likely the top module statements are detailed in the below Statement Analysis section.

You can ask Stori to create this matrix with, "is top sql matrix module oracle@uaadc142.arxbs.redbrick.org.pe_(TNS_V1-V3".

Top SQL Within Top Module Matrix

SQL ID EL CPU LIO PIO Srt Exe Par DW OPR
7aw349n5chnzz 1 1 1 1 1 1 1 1 1
33z4yynbc063m 2 2 2 2 1 2 2 1 .5
8b9hnf7af7z36 3 3 3 3 1 3 2 1 .33

EL: elapsed time, CPU: CPU time, LIO: logical IOs, PIO: physical IO block reads, Srt: sorts to disk, Exe: executions, Par: parses, DW: direct writes OPR: opportunity rating

SQL and PL/SQL Statement Analysis

1375295334_appl_sql_opr_pie.png

Considering all the above during the analysis period, of the 206 statements executed only one need our attention. Taking into consideration the above application focused analysis and from a specific statement perspective, focus on reducing (per execution) CPU consumption. Statement performance details are shown below in both the Top SQL Matrix and the Key SQL Statement Details section.

A proprietary method developed by OraPub is used to relate and differenciate the importance between objects, such as SQL and PL/SQL statements. The higher the Opportunity rating (OPR) value and the larger the difference between objects directs us to focus on the higher value objects.

Top SQL Matrix

Below is a matrix containing the SQL IDs for top ten SQL based on a number of criteria. Reviewing the matrix will increase awareness of the overall SQL situation. For example, sometimes there is a single SQL statement that is ranked high in many areas. At this time, focus on reducing (per execution) CPU consumption.

Top SQL Matrix

SQL ID EL CPU LIO PIO Srt Exe Par DW OPR
7aw349n5chnzz 1 1 1 1 18 5 1 2 1
33z4yynbc063m 2 2 2 2 18 29 2 .5
8b9hnf7af7z36 3 3 3 3 18 2 .33
gm61prsakf2ud 4 4 5 4 15 1 .25
b02ykty03gsxg 6 5 4 13 18 34 25 2 .2
3k8yvvqc0zazk 7 6 10 15 18 8 3 2 .17
db78fxqxwxt7r 5 7 6 6 1 1 18 2 .14
3dwb0ndumvaa6 12 8 21 24 18 17 9 2 .13
7ng34ruy5awxq 8 9 7 9 2 4 5 2 .11
3v3jhaqxm6xn5 9 10 11 5 15 2 .1

EL: elapsed time, CPU: CPU time, LIO: logical IOs, PIO: physical IO block reads, Srt: sorts to disk, Exe: executions, Par: parses, DW: direct writes OPR: opportunity rating

Key SQL Statement Details

The key SQL statements are selected based on a number of criteria including both operating system and Oracle system activity. Thankfully Statspack does not collect data for every SQL statement. But then percentages of all SQL can be inflated. Obviously some SQL statements reside within a function or procedure. If so, their values are counted multiple times resulting in percentages related to all SQL being deflated. Therefore, the Percent of all SQL (i.e., collected) statistics below need to be viewed with this in mind.

Stori contains a significant amount of statement activity available through the, "is sql id ..." command set. Do a "help is sql" for details.

Identifiers
SQL ID 7aw349n5chnzz
Opportunity rating 1
Focus areas elapsed time, CPU time
SQL type query
Module oracle@uaadc142.arxbs.redbrick.org.pe_(TNS_V1-V3
Elapsed Time
Total (sec) 22672.35
Average (sec/exe) 12.356
Percent of all SQL 75.605
CPU Consumed
Total (sec) 18759.775
Average (sec/exe) 10.223
Percent of all SQL 82.743
Wait Time
Total (sec) 3912.575
Average (sec/exe) 2.132
Percent of all SQL 17.257
Executions
Total completed 1835
Per second .51
Percent of all SQL .968
Parse Cals (soft+hard)
Total (#) 29040
Average (#/exe) 1
Percent of all SQL 66.258
Physical IO Blocks Read
Total (#) 1407755356
Average (#/exe) 767169.131
Percent of all SQL 91.115
Logical IO Consumed
Total (#) 1413190231
Average (#/exe) 770130.916
Percent of all SQL 91.054
SQL hash value
New 180900863
Old 3264153475
SQL text
Partial SELECT "DONATION_ATT_ID","DONAT

There are almost always non-Application subsystem centric solution strategies as well.