Oracle provides several utilities for diagnosing database performance issues:
• Automatic workload repository (AWR)
• Automatic database diagnostic monitor (ADDM)
• Active session history (ASH)
• Statspack
AWR, ADDM, and ASH tools provide advance reporting capabilities that allow you to troubleshoot and resolve performance issues and are available through Diagnostics and Tuning Pack as an extra license from Oracle.
Statspack is a free utility and requires no license, but with multitenant environment, you need to deploy Statspack in the pluggable database for tuning statements in the pluggable database, and the jobs will need to be configured using dbms_scheduler.
All of these tools rely heavily on the underlying v$ dynamic performance views. Oracle maintains a vast collection of these views, which track and accumulate metrics of database performance.
For example, if you run the following query, you will notice that Oracle Database 23c has about 875 v$ views and gv$ has almost the same:
SQL> select substr(name,1,2) view_name, count(*) from v$fixed_table group by view_name; VIEW_NAM COUNT(*)
V$FIXED_TABLE provides information about the dynamic performance views including the underlying X$ tables and GV$ views for RAC environments.
The Oracle performance utilities rely on periodic snapshots gathered from these internal performance views. Two of the most useful views, with regard to performance statistics, are the v$sysstat and v$sesstat views.
The v$sysstat view offers more than 800 types of database statistics.
This v$sysstat view contains information about the entire database, whereas the v$sesstat view contains statistics on individual sessions. A few of the values in the v$sysstat and v$sesstat views represent the current usage of the resource.
These values are as follows:
• Opened cursors current
• Logins current
• Session cursor cache current
• Work area memory allocated
The rest of the values are cumulative. The values in v$sysstat are cumulative for the entire container, from the time the instance was started or pluggable opened. The values in the v$sesstat are cumulative per session, from the time the session was started. Some of the more important performance-related cumulative values are these:
• CPU used
• Consistent gets
• Physical reads
• Physical writes
For the cumulative statistics, the way to measure periodic usage is to note the value of a statistic at a starting point and then note the value again at a later point in time and capture the delta. This is the approach used by the Oracle performance utilities, such as AWR and Statspack. Periodically, Oracle will take snapshots of the dynamic wait interface views and store them in a repository. You can also manually capture a snapshot.
Tip You can access aWr,addm, and ash from enterprise manager. If you have access to enterprise manager, you will find the interface fairly intuitive and visually helpful. statspack is only available as text.
Leave a Reply