Understanding Query Paths

Operations that Retrieve Rows (Access Paths)

As I mentioned earlier, some operations retrieve rows from data sources, and in those cases, the object_name column shows the name of the data source, which can be a table, a view, etc. However, the optimizer might choose to use different techniques to retrieve the data depending on the information it has available from the database statistics. These different techniques that can be used to retrieve data are usually called access paths, and they are displayed in the operations column of the plan, usually enclosed in parenthesis.

Below is a list of the most common access paths with a small explanation of them (source). I will not cover them all because I don’t want to bore you  . I’m sure that after reading the ones I include here you will have a very good understanding of what access paths are and how they can affect the performance of you queries.

Full Table Scan

A full table scan reads all rows from a table, and then filters out those rows that do not meet the selection criteria (if there is one). Contrary to what one could think, full table scans are not necessarily a bad thing. There are situations where a full table scan would be more efficient than retrieving the data using an index.

Table Access by Rowid

A rowid is an internal representation of the storage location of data. The rowid of a row specifies the data file and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row because it specifies the exact location of the row in the database.

In most cases, the database accesses a table by rowid after a scan of one or more indexes.

Index Unique Scan

An index unique scan returns at most 1 rowid, and thus, after an index unique scan you will typically see a table access by rowid (if the desired data is not available in the index). Index unique scans can be used when a query predicate references all of the columns of a unique index, by using the equality operator.

Index Range Scan

An index range scan is an ordered scan of values, and it is typically used when a query predicate references some of the leading columns of an index, or when for any reason more than one value can be retrieved by using an index key. These predicates can include equality and non-equality operators (=, <. >, etc).

Index Full Scan

An index full scan reads the entire index in order, and can be used in several situations, including cases in which there is no predicate, but certain conditions would allow the index to be used to avoid a separate sorting operation.

Index Fast Full Scan

An index fast full scan reads the index blocks in unsorted order, as they exist on disk. This method is used when all of the columns the query needs to retrieve are in the index, so the optimizer uses the index instead of the table.

Index Join Scan

An index join scan is a hash join of multiple indexes that together return all columns requested by a query. The database does not need to access the table because all data is retrieved from the indexes.

Operations that Manipulate Data

As I mentioned before, besides the operations that retrieve data from the database, there are some other types of operations you may see in an execution plan, which do not retrieve data, but operate on data that was retrieved by some other operation. The most common operations in this group are sorts and joins.

Sorts

A sort operation is performed when the rows coming out of the step need to be returned in some specific order. This can be necessary to comply with the order requested by the query, or to return the rows in the order in which the next operation needs them to work as expected, for example, when the next operation is a sort merge join.

Joins

When you run a query that includes more than one table in the FROM clause the database needs to perform a join operation, and the job of the optimizer is to determine the order in which the data sources should be joined, and the best join method to use in order to produce the desired results in the most efficient way possible.

Both of these decisions are made based on the available statistics.

Here is a small explanation for the different join methods the optimizer can decide to use:

Nested Loops Joins

When this method is used, for each row in the first data set that matches the single-table predicates, the database retrieves all rows in the second data set that satisfy the join predicate. As the name implies, this method works as if you had 2 nested for loops in a procedural programming language, in which for each iteration of the outer loop the inner loop is traversed to find the rows that satisfy the join condition.

As you can imagine, this join method is not very efficient on large data sets, unless the rows in the inner data set can be accessed efficiently (through an index).

In general, nested loops joins work best on small tables with indexes on the join conditions.

Hash Joins

The database uses a hash join to join larger data sets. In summary, the optimizer creates a hash table (what is a hash table?) from one of the data sets (usually the smallest one) using the columns used in the join condition as the key, and then scans the other data set applying the same hash function to the columns in the join condition to see if it can find a matching row in the hash table built from the first data set.

You don’t really need to understand how a hash table works. In general, what you need to know is that this join method can be used when you have an equi-join, and that it can be very efficient when the smaller of the data sets can be put completely in memory.

On larger data sets, this join method can be much more efficient than a nested loop.

Sort Merge Joins

A sort merge join is a variation of a nested loops join. The main difference is that this method requires the 2 data sources to be ordered first, but the algorithm to find the matching rows is more efficient.

This method is usually selected when joining large amounts of data when the join uses an inequality condition, or when a hash join would not be able to put the hash table for one of the data sets completely in memory.Undes

http://sql.standout-dev.com/2016/01/understanding-querys-execution-plan/

Response-Time Analysis Made Easy

System-Level Response-Time Analysis

 DBAs typically want answers to these questions:

• In general, how well is my database running? What defines efficiency?
• What average response time are my users experiencing?
• Which activities affect overall response time the most?

1) how well, in general, a database is running can be obtained by issuing this query in Oracle Database 10g:

                               
select  METRIC_NAME,
        VALUE
from    SYS.V_$SYSMETRIC
where   METRIC_NAME IN ('Database CPU Time Ratio',
                        'Database Wait Time Ratio') AND
        INTSIZE_CSEC = 
        (select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC); 

METRIC_NAME                         VALUE
------------------------------ ----------
Database Wait Time Ratio                6
Database CPU Time Ratio                94

You can also take a quick look over the last hour to see if the database has experienced any dips in overall performance by using this query:
                               
select  end_time,
        value
from    sys.v_$sysmetric_history
where   metric_name = 'Database CPU Time Ratio'
order by 1;

END_TIME                  VALUE
-------------------- ----------
22-NOV-2004 10:00:38         98
22-NOV-2004 10:01:39         96
22-NOV-2004 10:02:37         99
22-NOV-2004 10:03:38        100
22-NOV-2004 10:04:37         99
22-NOV-2004 10:05:38         77
22-NOV-2004 10:06:36        100
22-NOV-2004 10:07:37         96
22-NOV-2004 10:08:39        100
.

And, you can get a good idea of the minimum, maximum, and average values of overall database efficiency by querying the V$SYSMETRIC_SUMMARY view with a query such as this:
                               
select  CASE METRIC_NAME
            WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'
            WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'
            ELSE METRIC_NAME
            END METRIC_NAME,
                CASE METRIC_NAME
            WHEN 'SQL Service Response Time' then ROUND((MINVAL / 100),2)
            WHEN 'Response Time Per Txn' then ROUND((MINVAL / 100),2)
            ELSE MINVAL
            END MININUM,
                CASE METRIC_NAME
            WHEN 'SQL Service Response Time' then ROUND((MAXVAL / 100),2)
            WHEN 'Response Time Per Txn' then ROUND((MAXVAL / 100),2)
            ELSE MAXVAL
            END MAXIMUM,
                CASE METRIC_NAME
            WHEN 'SQL Service Response Time' then ROUND((AVERAGE / 100),2)
            WHEN 'Response Time Per Txn' then ROUND((AVERAGE / 100),2)
            ELSE AVERAGE
            END AVERAGE
from    SYS.V_$SYSMETRIC_SUMMARY 
where   METRIC_NAME in ('CPU Usage Per Sec',
                      'CPU Usage Per Txn',
                      'Database CPU Time Ratio',
                      'Database Wait Time Ratio',
                      'Executions Per Sec',
                      'Executions Per Txn',
                      'Response Time Per Txn',
                      'SQL Service Response Time',
                      'User Transaction Per Sec')
ORDER BY 1

METRIC_NAME                       MINIMUM    MAXIMUM    AVERAGE
------------------------------ ---------- ---------- ----------
CPU Usage Per Sec                       0          7          1
CPU Usage Per Txn                       1         29          8
Database CPU Time Ratio                61        100         94
Database Wait Time Ratio                0         39          5
Executions Per Sec                      2         60          8
Executions Per Txn                     16        164         41
Response Time Per Txn (secs)            0        .28        .08
SQL Service Response Time (sec          0          0          0
User Transaction Per Sec                0          1          0


DBA will then want to know what types of user activities are responsible for making the database work so hard.


select  case db_stat_name
            when 'parse time elapsed' then 
                'soft parse time'
            else db_stat_name
            end db_stat_name,
        case db_stat_name
            when 'sql execute elapsed time' then 
                time_secs - plsql_time 
            when 'parse time elapsed' then 
                time_secs - hard_parse_time
            else time_secs
            end time_secs,
        case db_stat_name
            when 'sql execute elapsed time' then 
                round(100 * (time_secs - plsql_time) / db_time,2)
            when 'parse time elapsed' then 
                round(100 * (time_secs - hard_parse_time) / db_time,2)  
            else round(100 * time_secs / db_time,2)  
            end pct_time
from
(select stat_name db_stat_name,
        round((value / 1000000),3) time_secs
    from sys.v_$sys_time_model
    where stat_name not in('DB time','background elapsed time',
                            'background cpu time','DB CPU')),
(select round((value / 1000000),3) db_time 
    from sys.v_$sys_time_model 
    where stat_name = 'DB time'),
(select round((value / 1000000),3) plsql_time 
    from sys.v_$sys_time_model 
    where stat_name = 'PL/SQL execution elapsed time'),
(select round((value / 1000000),3) hard_parse_time 
    from sys.v_$sys_time_model 
    where stat_name = 'hard parse elapsed time')
order by 2 desc;


DB_STAT                          TIME_SECS       PCT_TIME
-----------------------------    ---------       --------
sql execute elapsed time         13263.707       45.84                                 
PL/SQL execution elapsed time    13234.738       45.74                                 
hard parse elapsed time           1943.687        6.72                                  
soft parse time                    520.584         1.8
.

It's much easier to tell now that the bulk of overall wait time is due, for example, to user I/O waits than to try to tally individual wait events to get a global picture. As with response-time metrics, you can also look back in time over the last hour with a query like this one:
                               
select  to_char(a.end_time,'DD-MON-YYYY HH:MI:SS') end_time,
        b.wait_class,
        round((a.time_waited / 100),2) time_waited 
from    sys.v_$waitclassmetric_history a,
        sys.v_$system_wait_class b
where   a.wait_class# = b.wait_class# and
        b.wait_class != 'Idle'
order by 1,2;

END_TIME             WAIT_CLASS      TIME_WAITED
-------------------- --------------- -----------
22-NOV-2004 11:28:37 Application               0
22-NOV-2004 11:28:37 Commit                  .02
22-NOV-2004 11:28:37 Concurrency               0
22-NOV-2004 11:28:37 Configuration             0
22-NOV-2004 11:28:37 Network                 .01
22-NOV-2004 11:28:37 Other                     0
22-NOV-2004 11:28:37 System I/O              .05
22-NOV-2004 11:28:37 User I/O                  0
.

You can, of course, just focus on a single SID with the V$SESS_TIME_MODEL view and obtain data for all statistical areas of a session. You can also view current session wait activity using the new wait classes using the following query:
                               
select  a.sid,
        b.username,
        a.wait_class,
        a.total_waits,
        round((a.time_waited / 100),2) time_waited_secs
from    sys.v_$session_wait_class a,
        sys.v_$session b
where   b.sid = a.sid and
        b.username is not null and
        a.wait_class != 'Idle'
order by 5 desc;

SID USERNAME   WAIT_CLASS      TOTAL_WAITS TIME_WAITED_SECS
--- ---------- --------------- ----------- ----------------
257 SYSMAN     Application          356104            75.22
255 SYSMAN     Commit                14508            25.76
257 SYSMAN     Commit                25026            22.02
257 SYSMAN     User I/O              11924            19.98
.
.
.


you can use the following query. In the example below, we're looking at activity from midnight to 5 a.m. on November 21, 2004, that involved user I/O waits:
                               
select  sess_id,
        username,
        program,
        wait_event,
        sess_time,
        round(100 * (sess_time / total_time),2) pct_time_waited
from
(select a.session_id sess_id,
        decode(session_type,'background',session_type,c.username) username,
        a.program program,
        b.name wait_event,
        sum(a.time_waited) sess_time
from    sys.v_$active_session_history a,
        sys.v_$event_name b,
        sys.dba_users c
where   a.event# = b.event# and
        a.user_id = c.user_id and
        sample_time > '21-NOV-04 12:00:00 AM' and 
        sample_time < '21-NOV-04 05:00:00 AM' and
        b.wait_class = 'User I/O'
group by a.session_id,
        decode(session_type,'background',session_type,c.username),
        a.program,
        b.name),
(select sum(a.time_waited) total_time
from    sys.v_$active_session_history a,
        sys.v_$event_name b
where   a.event# = b.event# and
        sample_time > '21-NOV-04 12:00:00 AM' and 
        sample_time < '21-NOV-04 05:00:00 AM' and
        b.wait_class = 'User I/O')
order by 6 desc;

SESS_ID USERNAME PROGRAM    WAIT_EVENT                SESS_TIME PCT_TIME_WAITED
------- -------- ---------- ------------------------- ---------- -------------
    242 SYS      exp@RHAT9K db file scattered read       3502978         33.49
    242 SYS      oracle@RHA db file sequential read      2368153         22.64
    242 SYS      oracle@RHA db file scattered read       1113896         10.65
    243 SYS      oracle@RHA db file sequential read       992168          9.49

                            
The Oracle Database 10g V$ACTIVE_SESSION_HISTORY view comes into play here to provide an insightful look back in time at session experiences for a given time period. This view gives you a lot of excellent information without the need for laborious tracing functions. We'll see more use of it in the next section, which deals with analyzing the response times of SQL statements.

SQL Response-Time Analysis

Examining the response time of SQL statements became easier in Oracle9i, and with Oracle Database 10g, DBAs have many tools at their disposal to help them track inefficient database code.

Historically the applicable V$ view here has been V$SQLAREA. Starting with Oracle9i, Oracle added the ELAPSED_TIME and CPU_TIME columns, which have been a huge help in determining the actual end user experience of a SQL statement execution (at least, when dividing them by the EXECUTIONS column, which produces the average amount of time per execution).

In Oracle Database 10g, six new wait-related and timing columns have been added to V$SQLAREA:

APPLICATION_WAIT_TIME
CONCURRENCY_WAIT_TIME
CLUSTER_WAIT_TIME
USER_IO_WAIT_TIME
PLSQL_EXEC_TIME
JAVA_EXEC_TIME

The new columns are helpful in determining, for example, the amount of time that a procedure spends in PL/SQL code vs. standard SQL execution, and if a SQL statement has experienced any particular user I/O waits. For example, a query you could use to find the top five SQL statements with the highest user I/O waits would be:
                               
select *
from
(select sql_text,
        sql_id,
        elapsed_time,
        cpu_time,
        user_io_wait_time
from    sys.v_$sqlarea
order by 5 desc)
where rownum < 6;

SQL_TEXT                  SQL_ID       ELAPSED_TIME CPU_TIME  USER_IO_WAIT_TIME
------------------------- ------------ ------------ ---------- ---------------
select /*+ rule */ bucket db78fxqxwxt7     47815369   19000939            3423
SELECT :"SYS_B_0" FROM SY agdpzr94rf6v     36182205   10170226            2649
select obj#,type#,ctime,m 04xtrk7uyhkn     28815527   16768040            1345
select grantee#,privilege 2q93zsrvbdw4     28565755   19619114             803
select /*+ rule */ bucket 96g93hntrzjt      9411028    3754542             606
                            
Of course, getting the SQL statements with the highest elapsed time or wait time is good, but you need more detail to get to the heart of the matter—which is where the V$ACTIVE_SESSION_HISTORY view again comes into play. With this view, you can find out what actual wait events delayed the SQL statement along with the actual files, objects, and object blocks that caused the waits (where applicable).

For example, let's say you've found a particular SQL statement that appears to be extremely deficient in terms of user I/O wait time. You can issue the following query to get the individual wait events associated with the query along with the corresponding wait times, files, and objects that were the source of those waits:
                               
select event,
        time_waited,
        owner,
        object_name,
        current_file#,
        current_block# 
from    sys.v_$active_session_history a,
        sys.dba_objects b 
where   sql_id = '6gvch1xu9ca3g' and
        a.current_obj# = b.object_id and
        time_waited <> 0;

EVENT                     TIME_WAITED OWNER  OBJECT_NAME           file  block
------------------------- ----------- ------ --------------------- ---- ------
db file sequential read         27665 SYSMAN MGMT_METRICS_1HOUR_PK    3  29438
db file sequential read          3985 SYSMAN SEVERITY_PRIMARY_KEY     3  52877
                            
Of course, you can use V$ACTIVE_SESSION_HISTORY in a historical fashion to narrow down unoptimized SQL statements for a particular time period. The point is that Oracle Database 10g makes it a lot easier to conduct response-time analysis on SQL statements with simplified data dictionary views, as opposed to the time-consuming trace-and-digest method.

Conclusion

Avoiding I/O Disk Contention

Disk contention occurs when multiple processes try to access the same physical disk simultaneously. Disk contention can be reduced, thereby increasing performance, by distributing the disk I/O more evenly over the available disks.




A large difference in the number of physical writes and reads between disks may indicate that a disk is being overburdened.

Monitor the System Using Unix Utilities

UNIX/LINUX UTILITIES

   Using the sar command to monitor CPU usage

   Using sar and vmstat to monitor paging/swapping

   Using the sar command to monitor disk I/O problems

   Finding the worst user on the system using the top command

   Using the uptime command to monitor the CPU load

   Using the mpstat command to identify CPU bottlenecks

   Combining ps with selected V$ views

   Using iostat to identify disk I/O bottlenecks

   Determining shared memory usage using ipcs

   Monitoring system load using vmstat

   Monitoring disk free space

   Monitoring network performance

Top 25 initialization parameters

TOP 25 INITIALIZATION PARAMETERS

The following list is my list of the top 25 most important initialization parameters, in order of importance.

1.   MEMORY_TARGET This is the initialization parameter setting for all of the memory allocated to both the PGA and SGA combined (new in 11g). Setting MEMORY_TARGET enables Automatic Memory Management, so Oracle allocates memory for you based on system needs, but you can also set minimum values for key parameters. MEMORY_TARGET is used for everything that SGA_TARGET was used for but now additionally includes the PGA (especially important as MEMORY_TARGET now includes the important area PGA_AGGREGATE_TARGET). Important parameters such as DB_CACHE_SIZE, SHARED_POOL_SIZE, PGA_AGGREGATE_TARGET, LARGE_POOL_SIZE, and JAVA_POOL_SIZE are all set automatically when you set MEMORY_TARGET. Setting minimum values for important initialization parameters in your system is also a very good idea.

2.   MEMORY_MAX_TARGET This is the maximum memory allocated for Oracle and the maximum value to which MEMORY_TARGET can be set.

3.   DB_CACHE_SIZE Initial memory allocated to data cache or memory used for data itself. This parameter doesn’t need to be set if you set MEMORY_TARGET or SGA_TARGET, but setting a value for this as a minimum setting is a good idea. Your goal should always be toward a memory-resident database or at least toward getting all data that will be queried in memory.

4.   SHARED_POOL_SIZE Memory allocated for the data dictionary and for SQL and PL/SQL statements. The query itself is put in memory here. This parameter doesn’t need to be set if you set MEMORY_TARGET, but setting a value for this as a minimum is a good idea. Note that SAP recommends setting this to 400M. Also note that the Result Cache gets its memory from the shared pool and is set with the RESULT_CACHE_SIZE and RESULT_CACHE_MODE (FORCE/AUTO/MANUAL) initialization parameters. Lastly, an important note since 11g is that this parameter now includes some SGA overhead (12M worth) that it previously did not in version 10g. Set this 12M higher than you did in 10g!

5.   INMEMORY_SIZE The In-Memory column store resides in this area, which is separate from the buffer cache used to store data in memory. Tables, tablespaces, partitions, and other objects can have single columns stored in this memory area in a compressed fashion. This allows for much faster analytics (like summing an individual column). Oracle builds indexes to make this even faster based on ranges of values. This is new in 12c.

6.   SGA_TARGET If you use Oracle’s Automatic Shared Memory Management, this parameter is used to determine the size of your data cache, shared pool, large pool, and Java pool automatically (see Chapter 1 for more information). Setting this to 0 disables it. This parameter doesn’t need to be set if you set MEMORY_TARGET, but you may want to set a value for this as a minimum setting for the SGA if you’ve calibrated it in previous versions. The SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and DB_CACHE_SIZE are all set automatically based on this parameter (or MEMORY_TARGET if used). INMEMORY_SIZE is also included in this number.

7.   PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT The _TARGET is a soft memory cap for the total of all users’ PGAs. This parameter doesn’t need to be set if you set MEMORY_TARGET, but setting a value as a minimum setting is a good idea. Note that SAP specifies to set this to 20 percent of available memory for OLTP and 40 percent for OLAP. The _LIMIT sets the upper limit that is allowed (the hard memory cap).

8.   SGA_MAX_SIZE Maximum memory that SGA_TARGET can be set to. This parameter doesn’t need to be set if you set MEMORY_TARGET, but you may want to set a value if you use SGA_TARGET.

9.   OPTIMIZER_MODE FIRST_ROWS, FIRST_ROWS_n, or ALL_ROWS. Although RULE/CHOOSE are definitely desupported and obsolete and people are often scolded for even talking about using rule-based optimization, I was able to set the mode to RULE. Consider the following error I received when I set OPTIMIZER_MODE to a mode that doesn’t exist (SUPER_FAST):

10.   SEC_MAX_FAILED_LOGIN_ATTEMPTS If the user fails to enter the correct password after this many tries (new as of 11g) the server process drops the connection and the server process is terminated. The default is 3 (consider increasing this value for less secure systems). A similar parameter in my top 25 list in the prior edition included SEC_CASE_SENSITIVE_LOGON, which was new as of 11g, but is deprecated as of 12.1. Be careful if you’re still using this parameter in 11g (fix case issues with passwords by ensuring passwords can be lower, upper, or mixed case before you upgrade to 12c!).

11.   CURSOR_SHARING Converts literal SQL to SQL with bind variables, reducing parse overhead. The default is EXACT. Consider setting it to FORCE after research.

12.   OPTIMIZER_USE_INVISIBLE_INDEXES The default is FALSE to ensure invisible indexes are not used by default (new in 11g). Set this parameter to TRUE to use all of the indexes and to check which ones might have been set incorrectly to be invisible; this could be a helpful tuning exercise, or it could also bring the system to halt, so only use it in development.

13.   OPTIMIZER_USE_PENDING_STATISTICS The default is FALSE to ensure pending statistics are not used, whereas setting this to TRUE enables all pending statistics to be used (new in 11g).

14.   OPTIMIZER_INDEX_COST_ADJ Coarse adjustment between the cost of an index scan and the cost of a full table scan. Set between 1 and 10 to force index use more frequently. Setting this parameter to a value between 1 and 10 pretty much guarantees index use, however, even when not appropriate, so be careful because it is highly dependent on the index design and implementation being correct. Please note that if you are using Applications 11i, setting OPTIMIZER_INDEX_COST_ADJ to any value other than the default (100) is not supported (see My Oracle Support Note 169935.1). I’ve seen a benchmark where this was set to 200. Also, see bug 4483286. SAP suggests that you not set it for OLAP, but set it to 20 for OLTP.

15.   DB_FILE_MULTIBLOCK_READ_COUNT For full table scans to perform I/O more efficiently, this parameter reads the given number of blocks in a single I/O. The default value is 12812cR2, but it is usually noted not to change this from the default.

16.   LOG_BUFFER Server processes making changes to data blocks in the buffer cache generate redo data into the log buffer. SAP says to use the default, whereas Oracle Applications sets it to 10M. I’ve seen benchmarks with it set over 100M.

17.   DB_KEEP_CACHE_SIZE Memory allocated to the keep pool or an additional data cache that you can set up outside the buffer cache for very important data that you don’t want pushed out of the cache.

18.   DB_RECYCLE_CACHE_SIZE Memory allocated to a recycle pool or an additional data cache that you can set up outside the buffer cache and in addition to the keep pool described in item 17. Usually, DBAs set this up for ad hoc user query data with poorly written queries.

19.   OPTIMIZER_USE_SQL_PLAN_BASELINES The default is TRUE, which means Oracle uses these baselines if they exist (new in 11g). Note that Stored Outlines are deprecated (discouraged but they still work) in 11g, as they are replaced with SQL Plan Baselines.

20.   OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES The default is FALSE, which means that Oracle does not capture them by default, but if you create some, it will use them as stated in the previous parameter (new in 11g).

21.   LARGE_POOL_SIZE Total bytes in the large pool allocation for large PL/SQL and a few other Oracle options less frequently used.

22.   STATISTICS_LEVEL Used to enable advisory information and optionally keep additional OS statistics to refine optimizer decisions. TYPICAL is the default.

23.   JAVA_POOL_SIZE Memory allocated to the JVM for Java stored procedures.

24.   JAVA_MAX_SESSIONSPACE_SIZE Upper limit on memory that is used to keep track of the user session state of Java classes.

25.   OPEN_CURSORS Specifies the size of the private area used to hold (open) user statements. If you get an “ORA-01000: maximum open cursors exceeded,” you may need to increase this parameter, but make sure you are closing cursors that you no longer need. Prior to 9.2.0.5, these open cursors were also cached and, at times, caused issues (ORA-4031) if OPEN_CURSORS was set too high. As of 9.2.0.5, SESSION_CACHED_CURSORS now controls the setting of the PL/SQL cursor cache. Do notset the parameter SESSION_CACHED_CURSORS as high as you set OPEN_CURSORS, or you may experience ORA-4031 or ORA-7445 errors. SAP recommends setting this to 2000; Oracle Applications has OPEN_CURSORS at 600 and SESSION_CACHED_CURSORS at 500.

Undestanding AWR

Instance Efficiency

The Instance Efficiency section continues to grow and shows information for many of the common hit ratios

Things to look for include the following:

   A Buffer Nowait % of less than 99 percent. This value is the ratio of hits on a request for a specific buffer where the buffer was immediately available in memory. If the ratio is low, then there are (hot) blocks being contended for that should be found in the Buffer Wait section.

   A Buffer Hit % of less than 95 percent. This value is the ratio of hits on a request for a specific buffer when the buffer was in memory and no physical I/O was needed. While originally one of the few methods of measuring memory efficiency, it still is an excellent method for showing how often you need to do a physical I/O, which merits further investigation as to the cause. Unfortunately, if you have unselective indexes that are frequently accessed, that will drive your hit ratio higher, which can be a misleading indication of good performance to some DBAs. When you effectively tune your SQL and have effective indexes on your entire system, this issue is not encountered as frequently and the hit ratio is a better performance indicator. A high hit ratio is not a measure of good performance, but a low hit ratio is often a sign of performance that can be improved or should at least be looked into.

   A hit ratio that is steadily at 95 percent and then one day goes to 99 percent should be checked for bad SQL or a bad index that is causing a surge of logical reads (check the load profile and top buffer gets SQL).

   A hit ratio that is steadily at 95 percent and then drops to 45 percent should be checked for bad SQL or a dropped index (check the top physical reads SQL) causing a surge in physical reads that are not using an index or an index that has been dropped (I’ve seen this more often than you can imagine).

   A Library Hit % of less than 95 percent. A lower library hit ratio usually indicates that SQL is being pushed out of the shared pool early (could be due to a shared pool that is too small). A lower ratio could also indicate that bind variables are not used or some other issue is causing SQL not to be reused (in which case, a smaller shared pool may only be a bandage that potentially fixes a resulting library latch problem). Despite the rants about lowering your shared pool all the time to fix library cache and shared pool latching issues, most multiterabyte systems I’ve seen with heavy usage have shared pools in the gigabytes without any issues because they’ve fixed the SQL issues. You must fix the problem (use bind variables or CURSOR_SHARING) and then appropriately size the shared pool. I’ll discuss this further when I get to latch issues.

   An In-memory Sort % of less than 95 percent in OLTP. In an OLTP system, you really don’t want to do disk sorts. Setting the MEMORY_TARGET or PGA_AGGREGATE_TARGET (or SORT_AREA_SIZE in previous versions) initialization parameter effectively eliminates this problem. Note that In-memory Sort % appears only in the AWR Report (as in 11g), not in the Statspack Report.

   A Soft Parse % of less than 95 percent. As covered in the Load Profile section (last section), a soft parse ratio that is less than 80 percent indicates that SQL is not being reused and needs to be investigated.

   A Latch Hit % of less than 99 percent is usually a big problem. Finding the specific latch will lead you to solving this issue. I cover this in detail in the “Latch Free” section later in the chapter.

   A Flash Cache Hit % (only appears in the AWR Report on 12c) of less than 90 percent could be a problem if you have a large amount of Flash Cache (system dependent). If you are using Exadata, Chapter 11 provides some queries to help tune this problem if you’re not appropriately using your Flash Cache.

Shared Pool Statistics

The Shared Pool Statistics section that follows the Instance Efficiency section (both in the AWR Report and Statspack) shows the percentage of the shared pool in use and the percentage of SQL statements that have been executed multiple times (as desired). 

Top Wait Events

The Top Wait Events section of Statspack is probably the most revealing section in the entire report when you are trying to eliminate bottlenecks quickly on your system.