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.
- checks completed; permission, syntax, semantic
- hash_value created, so we can find the sql in the library cache
- sql_id created, so we can identify the sql
- ensure parent cursor and child cursors are created and ready
- ensure execution plan has been created including its plan_hash_value, so we can find it and know how to execute the sql
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.
- The throughput difference based on the cursor sharing setting
- The massive maximum throughput achieved when letting Oracle transform the SQL
- Throughput scales nicely when Oracle is able to transform the SQL
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.
