| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Negative "Execute to Parse" ratio with STATSPACK
I know from Thomas Kyte's site that the negative value for "Execute to Parse ratio" means that some SQL statements are parsed but never executed ... or could also mean are parsed MORE than they are executed.
Now to find out which of those statements are causing the negative ratio, I did this:
SQL> set linesize 400 SQL> column sql_text format a100 SQL> select parse_calls, executions, sql_textfrom v$sqlarea
4
SQL> /
PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- -------------------------------------------------
27957 0 BEGIN sys.dbms_ijob.remove(:job); END;
2 1 INSERT INTO sched_task_time ( sched_task_id,
sched_time, status ) VALUES ( :1, sysdate, :2 )
1658 1642 delete from dependency$ where d_obj#=:1
96 0 insert into javasnm$(short, longname, longdbcs)
values(:1,:2,:3)
2 1 select parse_calls, executions, sql_text from
v$sqlarea where parse_calls > executions
27957 595 select u1.user#, u2.user#, u3.user#, failures,
flag, interval#, what, nlsenv, env from
sys.job$ j, sys.user$ u1, sys.user$ u2,
sys.user$ u3 where job=:1 and (next_date <
sysdate or :2 != 0) and lowner = u1.name and
powner = u2.name and cowner = u3.name for
update nowait
27957 125 update sys.job$ set failures=0, this_date=null,
flag=:1, last_date=:2, next_date=:3,
total=total+(sysdate-nvl(this_date,sysdate))
where job=:4
27957 141 update sys.job$ set this_date=:1 where job=:2
8 rows selected.
... but 7 out of 8 of the above are from Oracle itself! Why would that be?
And the SQL "BEGIN sys.dbms_ijob.remove(:job); END;" I never do call. We do use DBMS_JOB ... but not dbms_ijob ... and we hardly call DBMS_JOB.REMOVE()! Here are my stats ( on our test database ):
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 944.28 3,759.38
Logical reads: 446.44 1,777.39
Block changes: 4.71 18.74
Physical reads: 63.01 250.84
Physical writes: 1.00 3.97
User calls: 35.60 141.75
Parses: 7.04 28.01
Hard parses: 0.02 0.08
Sorts: 3.81 15.16
Logons: 0.34 1.35
Executes: 6.78 26.98
Transactions: 0.25
% Blocks changed per Read: 1.05 Recursive Call %: 30.77
Rollback per transaction %: 2.65 Rows per Sort: 18.91
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 99.99 Redo NoWait %: 100.00
Buffer Hit %: 85.89 In-memory Sort %: 98.54
Library Hit %: 99.81 Soft Parse %: 99.72
Execute to Parse %: -3.83 Latch Hit %: 99.96
Parse CPU to Parse Elapsd %: % Non-Parse CPU:
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 58.06 58.84
% SQL with executions>1: 57.36 56.64
 |
 |