High Parsing Count: Soft Parsing or Hard Parsing?


select TSARPPRZOFF,             TSARPPRZOFFFID,          TSARPMXN,
 TSARPMXNFID,             TSARPSCONTO,             TSARPTSCONTO,
 TSARPSCONTOFID,          TSARPTSCONTOFID
from
 tst_pma_articolo  where  1 = 1  and tsarpid = 7223  and tsarptpnopr = 61
 and tsarpnthem = 'SOTTOCOSTO'  and tsarpnprs = 8

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse   106480      1.03       1.07          0          0          0           0
Execute 106480      2.10       2.32          0          0          0           0
Fetch   212960      1.66       1.60          0     319440          0      106480
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   425920      4.79       5.00          0     319440          0      106480

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
 1  TABLE ACCESS BY INDEX ROWID TST_PMA_ARTICOLO (cr=3 pr=0 pw=0 time=22 us)
 1   INDEX UNIQUE SCAN TST_PMA_ARTICOLO_PK (cr=2 pr=0 pw=0 time=11 us)(object id 86621)

As shown above this statement has been executed for 106480 times, with parsing at each time! It’s not using bind variables which is of course  to be corrected. Beside that, all input values are same values on 106480 executions. We need to find out why this statement has to be re-parsed?

Based on the statement “Misses in library cache during parse: 1”, we know that totally 106479 times parsing are soft parsing, in addition to one hard parsing at the 1st run.

If “Misses in library cache during parse: 106480” had appeared, we need to seriously consider why so many hard parsings happened. The v$sql_shared_cursor view records root causes for the mismatches.


select *
 from v$sql_shared_cursor
 where kglhdpar in
 (select address
 from v$sql
 where upper(sql_text) like '%&SQL_TEXT_LIKE%');

The following script print out only columns where mismatch occurred.


set serveroutput on

declare
 c         number;
 col_cnt   number;
 col_rec   dbms_sql.desc_tab;
 col_value varchar2(4000);
 ret_val    number;
begin
 c := dbms_sql.open_cursor;
 dbms_sql.parse(c,
 'select q.sql_text, s.*
 from v$sql_shared_cursor s, v$sql q
 where s.sql_id = q.sql_id
 and s.child_number = q.child_number
 and s.sql_id = ''&1''',
 dbms_sql.native);
 dbms_sql.describe_columns(c, col_cnt, col_rec);

 for idx in 1 .. col_cnt loop
 dbms_sql.define_column(c, idx, col_value, 4000);
 end loop;

 ret_val := dbms_sql.execute(c);

 while(dbms_sql.fetch_rows(c) > 0) loop
 for idx in 1 .. col_cnt loop
 dbms_sql.column_value(c, idx, col_value);
 if col_rec(idx).col_name in ('SQL_ID', 'ADDRESS', 'CHILD_ADDRESS',
 'CHILD_NUMBER', 'SQL_TEXT') then
 dbms_output.put_line(rpad(col_rec(idx).col_name, 30) ||
 ' = ' || col_value);
 elsif col_value = 'Y' then
 dbms_output.put_line(rpad(col_rec(idx).col_name, 30) ||
 ' = ' || col_value);
 end if;

 end loop;

 dbms_output.put_line('--------------------------------------------------');

 end loop;

 dbms_sql.close_cursor(c);

end;
/

set serveroutput off
set echo on

Advertisements
Tags:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: