AWR
Automatic workload repository resides in SYSAUX tablespace.
Each snapshot has a unique ID know as "snap_id" and detail can be found in "dba_hist_snapshot" view
Instance Efficiency Percentages:
Buffer Nowait% - Shows the % of times when data buffers were accessed directly without any wait time.
Buffer Hit Ratio - Measures how many times a required block was found in memory rather than having to execute an expensive read operation on disk to get the block.
Library Hit% - Shows the % of times when SQL statements and PL/SQL packages were found in the shared pool.
Execute to Parse % - Shows how often parsed SQL statements are reused without re-parsing.
Parse CPU to Parse Elapsed % - Gives the ratio of CPU time spent to parse SQL statements.
Redo NoWait % - Shows whether the redo log buffer has sufficient size.
In-memory Sort % - Shows the percentage of times when sorts are performed in memory instead of using temporary tablespaces.
Soft Parse % - Shows how often sessions issued a SQL statements that is already in the shared pool and how it can use an existing version of the statement.
Latch Hit % - Shows how often latches were acquired without having to wait.
Non-Parse CPU % - Shows the percentage of how much CPU resources were spent on the actual SQL execution.
Soft Parse % - 100% indicates that the SQL statements are actively re-used
"% Non-Parse CPU". - 100% means most of the CPU resources are used into operations other than parsing, which is good for database health.
Parse CPU to parse Elapsed % - It is very low, it reveals that oracle waits for some resources during parsing of SQL statements. To be investigated further.
Minimize the number of Hard parses. This reduction yields the benefits of minimizing CPU overhead spent performing costly parse work
Top 10 Foreground Events by Total Wait Time:
Shows those database events that might constitute the bottleneck for the system
Total Wait Time (sec) which show how many times DB was waiting in this class
The % SQL with executions >1 statistics indicate how many SQL statements are executed more than one time. This measures how well the applications are tuned and how well they make use of Bind variables.
Time Model Statistics:
detailed explanations of system resource consumptions
Operating System Statistics - Detail:
This is the information related to OS
SQL Ordered by Elapsed Time
This lists SQL queries ordered by Elapsed time
Query which has low executions and high Elapsed time per Exec (s) and this query could be a candidate for troubleshooting or optimizations
SQL Ordered by CUP Time:
SQL queries are listed on the basis of CPU taken by the query i.e. queries causing high load on the system
Automatic workload repository resides in SYSAUX tablespace.
Each snapshot has a unique ID know as "snap_id" and detail can be found in "dba_hist_snapshot" view
Instance Efficiency Percentages:
Buffer Nowait% - Shows the % of times when data buffers were accessed directly without any wait time.
Buffer Hit Ratio - Measures how many times a required block was found in memory rather than having to execute an expensive read operation on disk to get the block.
Library Hit% - Shows the % of times when SQL statements and PL/SQL packages were found in the shared pool.
Execute to Parse % - Shows how often parsed SQL statements are reused without re-parsing.
Parse CPU to Parse Elapsed % - Gives the ratio of CPU time spent to parse SQL statements.
Redo NoWait % - Shows whether the redo log buffer has sufficient size.
In-memory Sort % - Shows the percentage of times when sorts are performed in memory instead of using temporary tablespaces.
Soft Parse % - Shows how often sessions issued a SQL statements that is already in the shared pool and how it can use an existing version of the statement.
Latch Hit % - Shows how often latches were acquired without having to wait.
Non-Parse CPU % - Shows the percentage of how much CPU resources were spent on the actual SQL execution.
Soft Parse % - 100% indicates that the SQL statements are actively re-used
"% Non-Parse CPU". - 100% means most of the CPU resources are used into operations other than parsing, which is good for database health.
Parse CPU to parse Elapsed % - It is very low, it reveals that oracle waits for some resources during parsing of SQL statements. To be investigated further.
Minimize the number of Hard parses. This reduction yields the benefits of minimizing CPU overhead spent performing costly parse work
Top 10 Foreground Events by Total Wait Time:
Shows those database events that might constitute the bottleneck for the system
Total Wait Time (sec) which show how many times DB was waiting in this class
The % SQL with executions >1 statistics indicate how many SQL statements are executed more than one time. This measures how well the applications are tuned and how well they make use of Bind variables.
Time Model Statistics:
detailed explanations of system resource consumptions
Operating System Statistics - Detail:
This is the information related to OS
SQL Ordered by Elapsed Time
This lists SQL queries ordered by Elapsed time
Query which has low executions and high Elapsed time per Exec (s) and this query could be a candidate for troubleshooting or optimizations
SQL Ordered by CUP Time:
SQL queries are listed on the basis of CPU taken by the query i.e. queries causing high load on the system
No comments:
Post a Comment