Continued from Chapter 1
Topic Covered : TOP Timed Events Explained
Top Timed Events:
Top timed events are a good place to start when tuning as it always gives an indication of the areas that use most of the DB time. Top timed event sections are available on both AWR & Statspack report.
DB Time = DB CPU (Service Time by CPU) + Non idle Wait Time (Time waiting for various DB instance resources). Tuning aims to reduce the DB time by reducing the CPU time and wait time.
When we do compare CPU and wait time, it gives an impression of current behaviour. If CPU time is dominant, then it a good sign, however high CPU time sometimes caused by poorly written SQLs. If we do encounter increase in wait time in proportion to load , then its a sign of problem are and need tuning.
Tuning advice as per Thumb rule, it might differ for actual scenarios :
Topic Covered : TOP Timed Events Explained
Top Timed Events:
Top timed events are a good place to start when tuning as it always gives an indication of the areas that use most of the DB time. Top timed event sections are available on both AWR & Statspack report.
DB Time = DB CPU (Service Time by CPU) + Non idle Wait Time (Time waiting for various DB instance resources). Tuning aims to reduce the DB time by reducing the CPU time and wait time.
When we do compare CPU and wait time, it gives an impression of current behaviour. If CPU time is dominant, then it a good sign, however high CPU time sometimes caused by poorly written SQLs. If we do encounter increase in wait time in proportion to load , then its a sign of problem are and need tuning.
Tuning advice as per Thumb rule, it might differ for actual scenarios :
- If high/very high CPU time is observed, Mostly the SQL needs tuning.
- If high Wait time is observed, mostly the DB Instance needs tuning. No gain will achieve by simply adding CPUs/nodes.
- If the CPU time doesn't decrease as load increases can scale better. Adding more CPUs and RAC would be beneficial.
DB time is the total time spent in database calls for user sessions. This comes from Time Model statistics names V$SYS_TIME_MODEL & V$SESS_TIME_MODEL.
Time Model explained: The names are very much self explanatory on what they represent. However I have explained few of them.
1. DB Time: Amount of elapsed time (in microseconds) spent on database user level calls. Its a cumulative time spent for each users calls from the time the instance is started. The time taken by pt 2 till pt 9 are subset of DB Time, excluding pt 10; the time spent on background process like PMON.
2. DB CPU: Amount of CPU time spent on database user level calls.
3. Parse Time: Amount of time spent on parsing SQL statements.
4. SQL Execute elapsed time: Amount of elapsed time SQL statements are executing.
5. Connection management call elapsed time: Time spent on connecting and disconnecting session calls.
6. Sequence load elapsed time: Time spent on getting the next sequence number from data dictionary.
7. PL/SQL Execution/compilation elapsed time: Time spent running the PL/SQL interpreter and compiler respectively.
8. JAVA execution elapsed time: Time spent on running the JVM.
9. Inbound PL/SQL RPC elapsed time: Time spent on executing inbound remote procedure calls. It includes time spent on SQL execution and running JVM.
10. RMAN CPU time: Time spent by RMAN backup/restore operations.
1. DB Time: Amount of elapsed time (in microseconds) spent on database user level calls. Its a cumulative time spent for each users calls from the time the instance is started. The time taken by pt 2 till pt 9 are subset of DB Time, excluding pt 10; the time spent on background process like PMON.
2. DB CPU: Amount of CPU time spent on database user level calls.
3. Parse Time: Amount of time spent on parsing SQL statements.
4. SQL Execute elapsed time: Amount of elapsed time SQL statements are executing.
5. Connection management call elapsed time: Time spent on connecting and disconnecting session calls.
6. Sequence load elapsed time: Time spent on getting the next sequence number from data dictionary.
7. PL/SQL Execution/compilation elapsed time: Time spent running the PL/SQL interpreter and compiler respectively.
8. JAVA execution elapsed time: Time spent on running the JVM.
9. Inbound PL/SQL RPC elapsed time: Time spent on executing inbound remote procedure calls. It includes time spent on SQL execution and running JVM.
10. RMAN CPU time: Time spent by RMAN backup/restore operations.
