 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.
|
Followup:
|
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: #######
>
>
|
Followup:
|
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
|
Followup:
|
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
|
Followup:
|
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.
|
Followup:
|
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
|
Followup:
|
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
|
Followup:
|
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
|
Followup:
|
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%)
|
Followup:
|
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)
|
|