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';