Performance Tuning Tools

 

We have discussed the tools that are being used for Performance Tuning in our previous article.

Please find the article here

Performance Tuning in Oracle – Part1

In this article we will be discussing the additional tools which are used in Performance Tuning.

1. V$Dynamic Perfromance Views
2. Oracle Enterprise Manager (OEM)
3. AWR, ADDM, ASH reports
4. SQL Tuning advisor and baselines
5. OS Monitoring

V$ Dynamic Performance Views:

V$ VIEWS are the part of X$ fixed tables. V$ dynamic views are from the memory directly giving the real time data. If the DB is restarted, the historical information from these V$ dynamic views are not available. This is the reason when the DB IS restarted between the 2 awr snapshot IDs, we cannot generate the awr report.

Some of the important and frequently used V$ views which we are using to tune the Oracle Database are as follows.

1. V$SYSSTAT – This has the performance statistics of the database upto 500 records.
2. V$SESSION – Information of all the users that are connected to the Database.
3. V$SESSTAT – To get the detailed statistics of the session from v$session
4. V$PROCESS – Contains the information of the processes at server process level.
5. V$SQL, V$SQL_PLAN, V$SQL_PLAN_STATISTICS

There are many V$ views that are going to help us in the tuning, but the above are the most commonly used dynamic views.

In tuning practices a set of V$  tables has the most important information which is needed to tune is are the “wait interface tables”. When you query a statement, it spends some time to execute the Oracle Code which will actually consume the CPU. Any statement that is being executed must and will consume some CPU. But oracle needs to wait for some activity to be finished before executing the oracle code. The cycle of executing a statement would be like the program has to go inside the CPU, process it, execute the code and then fetch the records. So in order to perform this activity, the process might be on wait to get the available resources.

For example, if there are 2 sqls that are already running then the processes and CPUs are busy in executing these SQLs. So, if a new request comes, it must wait to finish the already executing sqls so that the CPU and process will be free and available in executing the new request. The most notably, IO requests have to be finished for the internal resources like locks and latches to be available. This information of number and the duration of the wait times will be recorded by the wait interface.

Time model tables are introduced from the Oracle 10g which gives the details of the time information like DB time, Elapsed time, Parsing times etc. So from this we can know for how long the wait existed, for how much time the process has been waiting for executing a statement or a program.

To get a summary of the waits experienced in the database at a high-level, below query is useful.

The time model data can be shown as a whole for the database in V$SYS_TIME_MODEL and for individual session in V$SESS_TIME_MODEL.  Below are the statistics from the views.

DB Time – Shows the elapsed time in microseconds spent executing the requests. This includes all

the user SQLs but not from the background processes.

DB CPU – CPU time spent executing the requests in microseconds. This includes all the user SQLs but     not from the background processes.

Background elapsed Time – Shows the elapsed time spent by the background processes in microseconds.

Background CPU time – Shows the CPU time consumed by the background processes.

Sequence load elapsed time – Shows the time took for loading the sequence numbers to cache. This is when an application uses the numbers defined in the CACHE of Create Sequence clause.

Parse time elapsed – Shows the total time taken for parsing the SQLs.

Failed parse elapsed time – Shows the time spent on the failed parse requests.

1. Background elapsed time is the combination of the background CPU time and background wait time.
2. DB time includes the following
   1. DB CPU
   2. User wait time (from the wait interface)
   3. Connection management call elapsed time
   4. Sequence load elapsed time
   5. SQL executing time
   6. Parse time
      1. Hard parse elapsed time – If it is hard parsing then it is by sharing criteria or by the bind variable mismatch.
      2. Failed parse elapsed time – If it is failed parse then it is by out of memory.
   7. PL/SQL execution elapsed time
   8. Inbound PL/SQL rpc elapsed time
   9. PL/SQL compilation elapsed time
   10. Java execution elapsed time

OEM

Oracle Enterprise Manager (OEM) is user friendly tool with which we can easily monitor the performance of a database by navigating to the different tabs (windows) and can easily understand where is the actual problem through the graphical outputs. But this needs an additional licence.

Through OEM we can see the graphical outputs of the statistics, CPU time, DB time etc and these will be from the V$ Dynamic performance Views only

AWR, ADDM, ASH reports

AWR

AWR (Automatic Workload Repository) gathers, processes and maintains performance stats for problem detection and for self-tuning. The data is stored both in memory and database. So the data gathered is shown in report as well as in views. We can see the report in HTML or text for the current database performance status and analysis.

The AWR is used to collect performance statistics including:

• Wait events used to identify performance problems.
• Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
• Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
• Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
• Object usage statistics.
• Resource intensive SQL statements.

Different Components that uses AWR are:

• Automatic Database Diagnostic Monitor
• Undo Advisor
• SQL Tuning Advisor
• Segment Advisor

By default the snapshots are generated hourly basis and keeps the statistics in repository for 8 days. We can also modify the default setting as per the requirement using the below statement.

Here , Retention = 14400 minutes (10 days)

Snapshot retention period Interval = 15 minutes Snapshot interval period

topnsql = Number of Top SQL to flush

dbid = database identifier

Different types of AWR Reports for different purposes:

By default, gathering statistics using AWR is enabled and controlled by STATISTICS_LEVEL initializing parameter. This should be set to TYPICAL or ALL to enable. Default setting is Typical and if we set it to Basic, this will disable many features of Oracle Database which includes AWR. So, Oracle does not recommend to set it to basic.

We can also manually capture the AWR repots using the DBMA_WORKLOAD_REPOSITORY package but segment statistics, memory advisory information etc will be disabled.

The space consumed in the sysaux tablespace by the AWR depends on the snapshot time, active sessions and the retention period. If the snapshot time is 15mins then the data gathered will be more and specific and thus, the space consumed will be more. And if the snapshot is 1 hour the space consumption will be less and the data will be based on the one-hour interval.

ADDM (Automatic Database Diagnostic Monitor)

This is a server-based expert that reviews the database performance for every 60 min by default. ADDM will reads all the AWR snapshots and try to detect the problems and advises us the best solutions that we can apply to get rid of the problems in the database.

ADDM recommends multiple solutions like

1. CPU Bottlenecks
2. Undersized memory structures
3. I/O capacity issues
4. RAC specific issues
5. High load sql statements
6. Database Configuration
7. Hardware changes
8. Schema changes
9. Application changes &
10. Other advisories

 

ASH (Active Session History):

Active Session History Reports shows you the below mentioned stats regarding your Oracle Database,

• Transient performance problems (usually not captured in ADDM reports)
• Time / session / action /module / SQL_ID based target performance analysis

Below mentioned is the script which you can run to generate your ASH report,

ASH reports Give the following information:

• Top User Events (frequent wait events)
• Details to the wait events
• Top Queries
• Top Sessions
• Top Blocking Sessions
• Top DB Object.
• Activity Over Time

 

Here AWR, ADDM & ASH is used for Instance level tuning whereas the V$ Dynamic views are used for SQL and Instance level tuning.

 

TO BE CONTINUED…

 

.