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
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.