so why some sql statements were parsed but were not executed?  February 01, 2002
Reviewer:  David  from Irving, Texas

Thanks Tom for your interpretation on the negative values of execute to parse. 
According to your percentage equition, how could parse be bigger than exec. I 
thought the purpose of parsing is for execution. So the smallest percentage 
should be 0, that is one parsing on execution. 

 

There are tools (bad ones) that parse a statement but never execute it.  Or they 
parse it to discover what inputs/outputs it takes and then later on parse it 
again to execute it (so the parse/execute is 2:1). 

It is commonly referred to as "inefficient coding techniques".  I see it with 
some 3rd "generic" libraries.  They parse like there is no tomorrow -- works 
great in a single user environment -- starts to stink with two users (or more).

Just cause you parsed, dosen't mean you are obligated to execute.   

negative value on Physical reads  August 20, 2004
Reviewer:  June  from MD, US

Tom, 

what may cause the negative  Physical reads as following in STATSPACK? Thanks!

----------------------
Cache Sizes
> ~~~~~~~~~~~
>            db_block_buffers:     285000          log_buffer:   52428800
>               db_block_size:      16384    shared_pool_size:       230M
> 
> Load Profile
> ~~~~~~~~~~~~                            Per Second       Per Transaction
>                                    ---------------       ---------------
>                   Redo size:             64,994.29              3,981.60
>               Logical reads:             11,386.58                697.55
>               Block changes:                232.94                 14.27
>              Physical reads:         -6,914,215.22           -423,569.86
>             Physical writes:                106.05                  6.50
>                  User calls:                 44.82                  2.75
>                      Parses:                  5.95                  0.36
>                 Hard parses:                  1.07                  0.07
>                       Sorts:                  8.90                  0.55
>                      Logons:                  0.16                  0.01
>                    Executes:                 54.94                  3.37
>                Transactions:                 16.32
> 
>   % Blocks changed per Read:    2.05    Recursive Call %:   98.72
>  Rollback per transaction %:    0.91       Rows per Sort: #######
> 
> 
 

this means the numbers "have rolled" -- and either gone negative or if they were 
not using signed integers, they hit 4billion and rolled to zero.

if the next report is OK, they hit 4billion and rolled to zero.
if the next report is still negative, they are using signed integers. 

Is there a hard limit of 4 Billion  August 20, 2004
Reviewer:  Neeraj Nagpal  from Los Angeles, CA

Tom,
   When you say numbers "have rolled", could you please clarify who may or may 
not be using the signed integers and is there a hard limit of 4billion on these 
numbers.

Thanks so much,
Neeraj 

most of the counters are 32bit or 64bit integers depending on OS/version and so 
on. 

Negative values of execute to parse in Statspack report.  August 20, 2004
Reviewer:  Ramana Mallela  from Walnut Creek , CA

Hi Tom,

Extending the Original question. Please advise how I can identify those SQL's, 
Which PARSE but never EXECUTE.

Thanks
Ramana 

v$sql -- look at the parse and execute counts. 

cpu above 100%  August 21, 2004
Reviewer:  RD  from NZ

Hi Tom,

Please can you explain "parse cpu to parse elapsed" percentage and it's meaning 
in simple words to me.
Then maybe I can understand how it is possible for me 
to get the following percentage in my statspack report.


Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.88    In-memory Sort %:  100.00
            Library Hit   %:   99.55        Soft Parse %:   99.64
         Execute to Parse %:   29.78         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:  127.27     % Non-Parse CPU:   97.12



Parse CPU to Parse Elapsd is more than 100 percent. It's really got me. 

Further I also would like to understand why would oracle parse a sql and not 
execute it. Under what circumstances is this possible?

Thanks tons as always,
Regards,
RD.
 

parse cpu = amount of cpu time used to parse
elapsed time parsing = amount of time on the wall clock spent parsing.

decode(:prsela, 0, to_number(null)
                      , round(100*:prscpu/:prsela,2))  pctval



in a perfect world, with no contention -- parse cpu = parse elapsed.

ratio = 100%

in a bad world, it takes longer to parse (elapsed) then cpu time used 
(contention).  ratio < 100%


in your case, what this is saying is the CPU exceeded the elapsed, which 
technically is not possible -- but happens due to the way "small fast things" 
are measured on computers.  It is hard to measure things that happen very 
rapidly accurately.  So, this ratio, when > 100%, is the same as "100%" for all 
intents and purposes

If you want to read more about the measurement issue -- I have a brief write up 
on it in Effective Oracle By Design -- Cary Millsap has written much more 
extensively on it in his Oracle performance book (excellent book by the way)



As for the last question -- Oracle does what it is told to do.  Oracle is told 
by you to PARSE.  If you do not tell Oracle to execute the statment, it won't.  
that is how you have parse but no execute. 

why would oracle parse a sql and not execute it?  August 21, 2004
Reviewer:  Kamal Kishore  from New Jersey, USA

I guess one way could be you do a DBMS_SQL.PARSE but do not do a 
DBMS_SQL.EXECUTE after that.
 

negative values of execute to parse in Statspack report  August 30, 2004
Reviewer:  Ramana Mallela  from Walnut Creek CA

Hi Tom,

Earlier in your followup you mentioned as It is commonly referred to as 

"inefficient coding techniques".  I see it with some 3rd "generic" libraries.  
They parse like there is no tomorrow -- works great in a single user environment 
-- starts to stink with two users (or more). "

Please advise how they can be eliminated or efficiently coded ?

Thanks in advance.

Ramana

 

by only parsing a statment ONCE per session, 

not once per execution
not once to "describe a table" 
not once to "describe the ith column in a table"


by only parsing ONCE -- which is the least and the most times a statement to be 
executed (and if you aren't going to execute it, don't parse it) needs be. 

negative values of execute to parse in Statspack report  August 31, 2004
Reviewer:  Ramana Mallela  from Walnut Creek, CA

Hi Tom, 

Sorry for taking more of you precious time on this issue. Let me re-phrase my 
question.

As you said following are the sql's which are parsing,parsing.......& parsing.My 
question is how differently the dev team can code the following SELECT 
statements,so that they parse once and execute many times.

Details 
========================

  1   select PARSE_CALLS,EXECUTIONS,SQL_TEXT from v$sql
  2  where executions = 0 and parse_calls > 0
  3* order by parse_calls desc
SQL> /

PARSE_CALLS EXECUTIONS SQL_TEXT
----------- ---------- -------------------------------------------------------
       5102          0 SELECT SKILL_ID FROM WN_JOB_ORDER_SKILLS WHERE JOB_ORDE
                       R_ID = :"SYS_B_0"

       3247          0 SELECT SKILL_ID FROM WN_COMPANY_SKILLS WHERE COMPANY_BI
                       N_ID = :"SYS_B_0"

       2476          0 SELECT TASK_ID, OFFICE, TASK_TYPE, DUE_DATE, TASK_STATU
                       S, MEMO_TEXT, ALARM_WHEN, TO_CHAR(ALARM_WHEN, :"SYS_B_0
                       ") ALARM_TIME FROM WN_TASKS WHERE ASSIGNMENT_ID = :"SYS
                       _B_1" ORDER BY DUE_DATE, TO_CHAR(ALARM_WHEN, :"SYS_B_2"
                       )

        747          0 SELECT * FROM WN_OFFICE_SORT_DEF_BINS WHERE OFFICE = :"
                       SYS_B_0" OR OFFICE IN (SELECT OFFICE FROM WN_OFFICE_LOG
                       INS WHERE LOGIN = :"SYS_B_1" AND ACTIVE = :"SYS_B_2")

        717          0 select srl_id, srl_name, login, table_name, created_dat
                       e from wn_srl where login = :"SYS_B_0"

        635          0 select country_code, country_name from wn_countries
        343          0 SELECT * FROM WN_JOB_ORDERS WHERE JOB_ORDER_ID = :"SYS_
                       B_0"

        120          0 select * from wn_employee_resume where person_id = :"SY
                       S_B_0"


Regards 
Ramana

 

ask the development team why

a) cursor-sharing=force must be on?  why havent they fix the bugs in their code?
b) why they parse a statement 5,102 times... but never actually RUN IT???  sort 
of like compiling a subroutine 5,102 times -- and never actually calling it


 

these are my stats  September 22, 2004
Reviewer:  A reader

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:   99.99       Redo NoWait %:   99.99
            Buffer  Hit   %:   93.16    In-memory Sort %:  100.00
            Library Hit   %:   99.61        Soft Parse %:   99.59
         Execute to Parse %:   11.79         Latch Hit %:   99.98
Parse CPU to Parse Elapsd %:   11.36     % Non-Parse CPU:   97.69

how can i get rid off low values of execute to parse and Parse CPU to Parse 
Elapsd %.

thanks 

hey, 1 cpu second spent parsing, versus 2 elapsed seconds spent parsing would 
result in a 50% ratio.

Seems really bad right -- 50%

but hey, we are talking about 1 cpu second and 2 wall clock seconds in a 15 
minute period.  

big deal, nothing to be concerned about


(this is why ratios in general can only be used as a RED FLAG causing you to 
dive into the details to see if a problem ACTUALLY IN PRACTICE EXISTS)


So, dive into the details here.  Is the elapsed time minus the cpu time (wait 
time for parsing) divided by the number of users (arg, very ratio like, averages 
 -- another awesome way to hide information but statspack is very high level 
like that) a major portion of their wait event time over the period of 
observation.


For example, lets say you have a 30 minute stats pack (1,800 seconds)

You have 500 seconds of parse elapsed time.
You have  50 seconds of parse cpu time

450 missing seconds.
But you had 1,000 users.

0.450 average seconds of wait per session in the period of observation.  Your 
end users would not notice that you speeded them up by an entire 1/2 of a second 
here, it isn't the low hanging fruit.




This is similar to my analogy:

On my last car trip, I stopped at 500 red lights.  Was that

a) good
b) bad
c) neither good nor bad


No matter what you choose, I will prove the other TWO are in fact correct.  
Why/How?  I control the situation and the other numbers you don't have access 
to, haven't looked at. 

BUT WHAT DOES THIS INDICATE  September 22, 2004
Reviewer:  A reader

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

THIS TARGET (100%) 

do you understand what a ratio is?

do you understand what ratios do?  how they work? 

did you read the example above showing that 10% could be *just fine*?


ratios are ratios

ratio based tuning "get these to 100%" is fruitless.

the devil is in the details, please -- go to the details and actually *analyze 
the issue*



If you read in the paper


"And in population news, the fastest growing country on the planet is BallyBoo.  
It grew 500% in the last year"


what can you tell me?  List the facts that you can tell me about the country of 
BallyBoo, it's population, it's impact on the world economy.



Now, if I tell you that the existing married residents of BallyBoo just had 
their parents move into the country with them as well as their 2 brothers and 2 
sisters -- so the population went from 2 to 10 -- well, hmmm.  Who really cares 
at this point?


Approximately 50% of married people are male.  Now what? (other than you could 
probably infer that the other 50% are not male)