How To Solve A Similar SQL Statement Parsing Issue

Posted on 25-Jul-2017 by Craig Shallahamer / OraPub / craig@orapub.com

In this third parse article, I'm turning my focus to solving an Oracle performance parsing issue caused by many similar SQL statements.

My second parse article focused on some diagnostic clues or tells related to parsing issues caused by similar yet unique SQL statements. And, in Part 1 I focused on what a DBA may see when their system is subjected to a gazillion similar statements.

You have probably heard of the cursor_sharing instance parameter. But have you seen an experiment showing the expected performance improvement?

In this post, I explain parsing, what cursor sharing does and then demonstrate its performance impact. And at the end, I will leave you with a kind of performance dilemma that I will address in the next post.

What Parsing Does

When a client process (e.g., SQL*Plus) sends SQL or PL/SQL to its foreground process, it also instructs its foreground process to either parse, execute or fetch. Our focus is primarily parsing.

Among other things, when a foreground process finishes parsing the following has occurred.

The SQL Hash Value

In this article, the hash value creation is super important. A hash value is simply the SQL text represented as a number. Oracle uses a hash function, taking the SQL text as input and outputting a number. Hashing is interesting because different inputs can result in the same hash value.

Try the below SQL on one of your production systems and you may see notice a hash value is used by more than one SQL_ID.

select hash_value, count(distinct(sql_id))
from   v$sql
group by hash_value
having count(distinct(sql_id)) > 1
order by 2
/

You are likely to see this:

SQL> select hash_value, count(distinct(sql_id))
  2  from   v$sql
  3  group by hash_value
  4  having count(distinct(sql_id)) > 1
  5  order by 2
  6  /

no rows selected

However, you may also see something like this example from a production Peoplesoft Student Information system.

HASH_VALUE COUNT(DISTINCT(SQL_ID))
---------- -----------------------
 379531379                       2
1387856076                       2
1662059511                       2
2278240341                       2

Or this example from a CRM system.

# CRM
HASH_VALUE COUNT(DISTINCT(SQL_ID))
---------- -----------------------
2097958813                       2
2850500270                       2
1694592591                       2
1555496248                       2

Observing The Identifiers Together

Let's put this together showing the sql_id, hash_value, plan_hash_value and the sql_text. Notice below there are no hash_value duplicates and cursor_sharing is set the default EXACT. Here is an example:

SQL> alter session set cursor_sharing=exact;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select dummy from dual where dummy = '-';

no rows selected

SQL> select dummy from dual where dummy = 'S';

no rows selected

SQL> select dummy from dual where dummy = 'M';

no rows selected

SQL> select dummy from dual where dummy = 'L';

no rows selected

SQL> select dummy from dual where dummy = 'X';

D
-
X

SQL> select dummy from dual where dummy = '+';

no rows selected

SQL> select dummy from dual where Dummy = '+';

no rows selected

SQL> select dummy from dual where  dummy = '+';

no rows selected

SQL> set tab off 
SQL> set linesize 300
SQL> col sql_text format a50
SQL> def yourSQL='dummy from dual'
SQL> select sql_id, hash_value, plan_hash_value, sql_text 
  2  from   v$sql 
  3  where  lower(sql_text) like '%&yourSQL%' 
  4    and  lower(sql_text) not like '%v$sql%';
old   3: where  lower(sql_text) like '%&yourSQL%'
new   3: where  lower(sql_text) like '%dummy from dual%'

SQL_ID        HASH_VALUE PLAN_HASH_VALUE SQL_TEXT
------------- ---------- --------------- --------------------------------------------------
fs970tvybwqks 4240333400       272002086 select dummy from dual where dummy = '+'
f1z17mqqdx37t 2900266233       272002086 select dummy from dual where dummy = 'L'
b5k69vyp3jdnx 2855843485       272002086 select dummy from dual where dummy = 'S'
ah15kvw3v5ncr  129159575       272002086 select dummy from dual where Dummy = '+'
fd5a3zsgdu0gx  517800445       272002086 select dummy from dual where dummy = 'X'
bv4x5rft7um64 2994556100       272002086 select dummy from dual where dummy = '-'
6aa9ymvunuzrg 4115496687       272002086 select dummy from dual where  dummy = '+'
0vnnj9yhdzqqh 2699025104       272002086 select dummy from dual where dummy = 'M'

8 rows selected.

What's the big deal or potential problem? Well... these similar SQL statement have different hash values, which means they will reside in the library cache on a different library cache hash chain. This also means, they will each have their own parent cursor and child cursor. And, each cursor creation takes CPU, memory and serialization control resources.

Wouldn't it be nice if the cursors of these similar SQL statements could be somehow shared?

Yes, it would be nice and it is possible... but not in every case. Read on!

Cursor Sharing: The Transformer

The beauty of cursor sharing is this: Before the foreground process creates the hash value, it transforms the SQL statement by literally changing the SQL text.

In fact, if you do a library cache dump, you will only see the transformed text, not the original.

The benefit? The transformed text is more likely to be shared, which will reduce hard parsing.

Currently, the transformation at least includes changing filter literals (e.g., where id = 'abc'), into bind variables (e.g., where id = :"SYS_B_0"). Enabling Oracle to transform SQL text is simple! All we need to do is change the cursor_sharing instance parameter from the default value of EXACT to FORCE. The change immediately takes effect!

Here is an example of what I mean along with the results.

SQL> alter session set cursor_sharing=force;

Session altered.

SQL> alter system flush shared_pool;

System altered.

SQL> select dummy from dual where dummy = '-';

no rows selected

SQL> select dummy from dual where dummy = 'S';

no rows selected

SQL> select dummy from dual where dummy = 'M';

no rows selected

SQL> select dummy from dual where dummy = 'L';

no rows selected

SQL> select dummy from dual where dummy = 'X';

D
-
X

SQL> select dummy from dual where dummy = '+';

no rows selected

SQL> select dummy from dual where Dummy = '+';

no rows selected

SQL> select dummy from dual where  dummy = '+';

no rows selected

SQL> set tab off 
SQL> set linesize 300
SQL> col sql_text format a50
SQL> def yourSQL='dummy from dual'
SQL> select sql_id, hash_value, plan_hash_value, sql_text 
  2  from   v$sql 
  3  where  lower(sql_text) like '%&yourSQL%' 
  4    and  lower(sql_text) not like '%v$sql%';
old   3: where  lower(sql_text) like '%&yourSQL%'
new   3: where  lower(sql_text) like '%dummy from dual%'

SQL_ID        HASH_VALUE PLAN_HASH_VALUE SQL_TEXT
------------- ---------- --------------- --------------------------------------------------
b29t2wzb08fsm 3590601491       272002086 select dummy from dual where  dummy = :"SYS_B_0"
5x6d939fncj51 1564886177       272002086 select dummy from dual where Dummy = :"SYS_B_0"
fcuxnmpcqyj46 1500464262       272002086 select dummy from dual where dummy = :"SYS_B_0"

Nice! We have reduced the number of cursors from eight to three! And, if I had not messed with spacing and capitalization, which a Developer would never ever do (lol), there would only be one hash value and one SQL_ID.

The result should be a dramatic decrease in hard parsing, CPU utilization and more resources to process an increased workload!

Do you really believe that? Let's check it out!

Seeing Is Believing: Setup

To demonstrate the impact of cursor_sharing, I'm going to place a unique SQL statement load on the system. I will monitor the CPU utilization and run queue using vmstat. Throughput will be monitored using a simple PL/SQL script reporting the SQL executions per second and hard parses per second.

Below is the SQL I run before I launch an "angry" script in the background.

drop table toomuch;
create table toomuch ( c1 number );
insert into toomuch select level just_a_column from dual connect by level <= 50000;
commit;
select count(*) from toomuch;

alter system set resource_manager_plan=''; 
alter system flush shared_pool;
ALTER SYSTEM SET CURSOR_SHARING=EXACT;
-- ALTER SYSTEM SET CURSOR_SHARING=FORCE;

Below is the core "angry" code, which I place into a shell script, so I can repeatedly run it in the background.

sqlplus system/manager <<EOF

show parameter resource_manager_plan
show parameter PARALLEL_MAX_SERVERS
show parameter cursor_sharing

declare
  sql_var varchar2(500);
  the_value_var number;
  loop_var number;
begin
  while 1 = 1
  loop
   sql_var := 'select c1 from toomuch where c1 = '||dbms_random.value(0,9999999) ;
   execute immediate sql_var ;
   dbms_lock.sleep(0.003);
  end loop;
end;
/
EOF

Below is the SQL script used to measure the throughput and hard parsing activity. There are many ways to get the data, this is just one.

-- Measure rates of execution and hard parsing
-- To use this script, you must copy/paste the entire
-- script into sqlplus one time and then
-- repeatedly copy/paste only the second part
--
set serveroutput on
variable stat1_value_var number;
variable stat1_persec_var number;
variable stat2_value_var number;
variable stat2_persec_var number;
variable sleep_time_var number;
set serveroutput on
begin
  :stat1_value_var := 0;
  :stat2_value_var := 0;
  :sleep_time_var  := 5;
end;
/
--
-- Repeatedly copy/paste the below into SQLPLUS
--
begin
  select (value-:stat1_value_var)/:sleep_time_var 
  into   :stat1_value_var 
  from   v$sysstat 
  where  name = 'execute count';

  select (value-:stat2_value_var)/:sleep_time_var 
  into   :stat2_value_var 
  from   v$sysstat 
  where  name = 'parse count (hard)';

  dbms_output.put_line('.');
  dbms_output.put_line( round(:stat1_value_var,2)||' exec/sec, '||round(:stat2_value_var,2)||' hp/sec, ');
end;
/
begin
  select value
  into   :stat1_value_var 
  from   v$sysstat 
  where  name = 'execute count';

  select value
  into   :stat2_value_var 
  from   v$sysstat 
  where  name = 'parse count (hard)';

  dbms_lock.sleep( :sleep_time_var );
end;
/

Seeing Is Believing: Results

The results are startling! Check out the chart below. Each data point represents the average of five samples.

The experimental results clearly show that regardless of the cursor_sharing setting, as the number of "angry" scripts running in the background increases, so do the executions per second. That is probably not a surprise.

What may be a surprise is this.

Just one data point example; with 10 angry script running in the background and with cursor sharing set to the default EXACT my test system was able to process an average of 2101 executions per second with 1025 hard parses/sec. However, with cursor sharing set to FORCE my system was able to process average of 54942 executions/sec with no hard parses.

In other words, using cursor sharing resulted in 53 times more throughput! Amazing.

Below is a table with the five sample average values.

Want More? Performance Dilemma

Clearly, cursor sharing SQL statement transformation made a massive difference. But it eventually leveled off. This occurred when I saturated the CPU subsystem. So, I'm wondering... can we push even more executions per second through the system without increasing CPU capacity? Yes we can! But that's the topic of my next article.

Craig.

If you have any questions or comments, feel free to email me directly at craig at orapub.com.

How To Tell If Similar SQL Statements Are Causing Parsing Issues How To Approach Different Oracle Database Performance Problems The Thrill Of Solving The Puzzle