When tuning the SQL statement

3 values you should know when tuning the SQL statement

Tuning SQL statement is one of the major activities in performance tuning for every Oracle DBA. We would refer execution plan, SQL tuning advisor, SQL access advisor, Object statistics etc… to start identifying the issue with the SQL statement. In this process we often come across SQL_ID, HASH_VALUE and PLAN_HASH_VALUE details and use them for issue identification/resolution

1. SQL_ID: It represents SQL statement with unique set of 13 alpha numeric characters.

2. HASH_VALUE: Is unique numeric value to identify/search SQL statement in shared pool.

HASH_VALUE is the unique SQL statement numeric value generated for every SQL statement runs in the database. This value is compared with the one in LIBRARY CACHE to identify if the query should undergo hard parsing or soft parsing.
Oracle database first generates HASH_VALUE of SQL text and then generates SQL_ID of that statement

3. PLAN_HASH_VALUE: Is the numeric value for each new execution plan optimizer generates.

PLAN_HASH_VALUE is the distinct numeric value generated by Optimizer when generating a new execution plan of SQL statement

select sql_id, sql_text, hash_value, plan_hash_value

from v$sql

where sql_text like 'select * from emp'

In case you happen to know the SQL ID already and would like to know the corresponding hash value, you can use the function DBMS_UTILITY.SQLID_TO_SQLHASH, which takes the sql_id (VARCHAR2) and returns a NUMBER.

1. Soft parse (library cache hit): if the statement hashes to a value that is identical to one already present in the shared pool and the texts of the matching hash values are the same and its parsed representation can be shared, Oracle looks up the execution plan and executes the statement accordingly. Literals must also be the same for Oracle to be able to use the same shared SQL area. The exception is when CURSOR_SHARING is set to FORCE.
2. Hard parse (library cache miss): if the statement has a hash value different from the ones that are available in the SGA or its parsed representation cannot be shared, Oracle hands the code over to the query optimizer. The query optimizer then has to build an executable version from scratch.

As a performance tuner, you might want to see what’s been running for a long time on your system. And that’s easy with a query to V$SESSION

SQL> select username, sql_id
  2  from   v$session
  3  where  status = 'ACTIVE'
  4  and    last_call_et > 10
  5  and    username is not null;

USERNAME                                      SQL_ID
--------------------------------------------- -------------
MY_USER                                       ff35fbgz27513

And since I’ve got the SQL_ID, its just as easy to look up the SQL text in V$SQL

SQL> select sql_text
  2  from   v$sql
  3  where  sql_id = 'ff35fbgz27513';

SQL_TEXT
----------------------------------------------------------------
SELECT COUNT(*) FROM DBA_OBJECTS, DBA_OBJECTS

But now what ? Somewhere in my application code, is a SQL statement that starts with “SELECT COUNT(*)” and its running badly. How do I find it ? (This is the polite way of saying “How do I locate the desk of the person that wrote it” )
Luckily for me, it is coming from PL/SQL. Because finding the source of the statement, is then trivial. On V$SQL there is also two columns of interest:

SQL> select PROGRAM_ID, PROGRAM_LINE#
  2  from   v$sql
  3  where  sql_id = 'ff35fbgz27513';

PROGRAM_ID PROGRAM_LINE#
---------- -------------
    102001            19

Also ..

SELECT s.SID, s.STATUS, s.process, s.osuser, a.sql_text, p.program
  FROM v$session s, v$sqlarea a, v$process p
WHERE s.PREV_HASH_VALUE = a.hash_value
   AND s.PREV_SQL_ADDR = a.address
   AND s.paddr = p.addr
   AND s.STATUS = 'ACTIVE';

SQL Query Result Cache

PL/SQL Function Cache here

The SQL query result cache enables explicit caching of query result sets and query fragments in database memory. A dedicated memory buffer stored in the shared pool can be used for storing and retrieving the cached results (SQL query result cache). The query results stored in this cache become invalid when data in the database objects being accessed by the query is modified.

Although the SQL query cache can be used for any query, good candidate statements are the ones that need to access a very high number of rows to return only a fraction of them. This is mostly the case for data warehousing applications.

In the graphic shown in the slide, if the first session executes a query, it retrieves the data from the database and then caches the result in the SQL query result cache. If a second session executes the exact same query, it retrieves the result directly from the cache instead of using the disks.

The query optimizer manages the result cache mechanism depending on the settings of the RESULT_CACHE_MODE parameter in the initialization parameter file.

You can use this parameter to determine whether or not the optimizer automatically sends the results of queries to the result cache. You can set the RESULT_CACHE_MODE parameter at the system, session, and table level. The possible parameter values are MANUAL, and FORCE:

When set to MANUAL (the default), you must specify, by using the RESULT_CACHE hint, that a particular result is to be stored in the cache. 

When set to FORCE, all results are stored in the cache

Managing the SQL Query Result Cache

You can alter various parameter settings in the initialization parameter file to manage the SQL query result cache of your database.

By default, the database allocates memory for the result cache in the shared pool inside the SGA. The memory size allocated to the result cache depends on the memory size of the SGA as well as the memory management system.

You can change the memory allocated to the result cache by setting the RESULT_CACHE_MAX_SIZE parameter. The result cache is disabled if you set its value to 0. The value of this parameter is rounded to the largest multiple of 32 KB that is not greater than the specified value. If the rounded value is 0, then the feature is disabled.

Use the RESULT_CACHE_MAX_RESULT parameter to specify the maximum amount of cache memory that can be used by any single result.

The default value is 5%, but you can specify any percentage value between 1 and 100. This parameter can be implemented at the system and session level.

Use the RESULT_CACHE_REMOTE_EXPIRATION parameter to specify the time (in number of minutes) for which a result that depends on remote database objects remains valid. The default value is 0, which implies that results using remote objects should not be cached. Setting this parameter to a nonzero value can produce stale answers. For example, if the remote table used by a result is modified at the remote database.

Using the RESULT_CACHE Hint

If you want to use the query result cache and the RESULT_CACHE_MODE initialization parameter is set to MANUAL, you must explicitly specify the RESULT_CACHE hint in your query.

This introduces the ResultCache operator into the execution plan for the query. When you execute the query, the ResultCache operator looks up the result cache memory to check whether the result for the query already exists in the cache. If it exists, the result is retrieved directly out of the cache. If it does not yet exist in the cache, the query is executed, the result is returned as output, and is also stored in the result cache memory.

If the RESULT_CACHE_MODE initialization parameter is set to FORCE, and you do not want to store the result of a query in the result cache, you must then use the NO_RESULT_CACHE hint in your query.

For example, when the RESULT_CACHE_MODE value equals FORCE in the initialization parameter file, and you do not want to use the result cache for the EMPLOYEES table, then use the NO_RESULT_CACHE hint.

Note: Use of the [NO_] RESULT_CACHE hint takes precedence over the parameter settings.

Using the DBMS_RESULT_CACHE Package

You can use the DBMS_RESULT_CACHE package to perform various operations such as viewing the status of the cache (OPEN or CLOSED), retrieving statistics on the cache memory usage, and flushing the cache. For example, to view the memory allocation statistics, use the following SQL procedure:

SQL> set serveroutput on
SQL> execute dbms_result_cache.memory_report

R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1024 bytes
Maximum Cache Size  = 720896 bytes (704 blocks)
Maximum Result Size = 35840 bytes (35 blocks)
[Memory]
Total Memory = 46284 bytes [0.036% of the Shared Pool]
... Fixed Memory = 10640 bytes [0.008% of the Shared Pool]
... State Object Pool = 2852 bytes [0.002% of the Shared Pool]
... Cache Memory = 32792 bytes (32 blocks) [0.025% of the Shared Pool]
....... Unused Memory = 30 blocks
....... Used Memory = 2 blocks
........... Dependencies = 1 blocks
........... Results = 1 blocks
............... SQL = 1 blocks

Viewing SQL Query Result Cache Information

SQL Query Result Cache: Considerations

Any user-written function used in a function-based index must have been declared with the DETERMINISTIC keyword to indicate that the function always returns the same output value for any given set of input argument values.

·       The purge works only if the cache is not in use; disable (close) the cache for flush to succeed.

·       With bind variables, cached result is parameterized with variable values. Cached results can be found only for the same variable values. That is, different values or bind variable names cause cache miss.

 

OCI Client Query Cache

You can enable caching of query result sets in client memory with Oracle Call Interface (OCI) Client Query Cache in Oracle Database 11g.

The cached result set data is transparently kept consistent with any changes done on the server side. Applications leveraging this feature see improved performance for queries that have a cache hit.

Additionally, a query serviced by the cache avoids round trips to the server for sending the query and fetching the results. Server CPU, which would have been consumed for processing the query, is reduced thus improving server scalability.

Client-side caching is useful when you have applications that produce repeatable result sets, small result sets, static result sets, or frequently executed queries on database objects that do not change often.

Client and server result caches are autonomous; each can be enabled/disabled independently.
Note: You can monitor the client query cache using the client_result_cache_stats$ view.

The following two parameters can be set in your initialization parameter file:

·       CLIENT_RESULT_CACHE_SIZE: A nonzero value enables the client result cache. This is the maximum size of the client per-process result set cache in bytes. All OCI client processes get this maximum size and can be overridden by the OCI_RESULT_CACHE_MAX_SIZEparameter.

·       CLIENT_RESULT_CACHE_LAG: Maximum time (in milliseconds) since the last round-trip to the server, before which the OCI client query executes a round-trip to get any database changes related to the queries cached on client.

A client configuration file is optional and overrides the cache parameters set in the server initialization parameter file. Parameter values can be part of a sqlnet.ora file. When parameter values shown in the slide are specified, OCI client caching is enabled for OCI client processes using the configuration file.

OCI_RESULT_CACHE_MAX_RSET_SIZE/ROWS denotes the maximum size of any result set in bytes/rows in the per-process query cache.

OCI applications can use application hints to force result cache storage. The application hints can be SQL hints:

·       /*+ result_cache */

·       /*+ no_result_cache */