Thanks for the question, sam.
Asked: May 25, 2001 - 9:39 am UTC
Last updated: May 31, 2004 - 8:42 pm UTC
Version: 8.1.7
Viewed 1000+ times
You Asked
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 1 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.02 1 3 0 1
Above is the tkprof output ..
1.From the above output it shows that, the sql statement has been parsed once. Iam I right.
Is a sql statement parsed more than one time, is it good or bad, what does it signigy i.e. what does it tell. Why will a sql statement get parsed twice.
Should we make any change in the sql statement so that it is parsed only once.
If for a session there are totally 100 sql statements and the parse count is 10, what does this signify?
2.In the above example there are 1 disk reads, and 3 logical reads.
Logical reads = current+query;
By logical reads do we mean that the data is being read from the data buffers.
What do current and query mean within the context of logical reads.
Which one is better more disk reads or more buffer reads and why?
If disk reads are much much more than the logical reads , what should be done to increase the buffer reads? Are there any changes we need to make in the query?
3.For example if the execute and fetch count for a sql statement is 100 and the rows returned is 70 , what does this tell. What are the changes suggested in the sql statements.
What is going wrong?
Tom, Iam immersed in the world of tkprof as of now, so you can expect a few more questions like this from me. But be assured that with each passing hour , the clarity in my questions will increase.
Thank you
and Tom said...
1) yes, parsed once.
if you have it parsed more then once in the same program execution, you have just wasted resources. You should strive for a parse count of 1. In your case, you only EXECUTED it once so this is a trivial example. You would like to see a parse followed by many executes.
You will get a parse count > 1 by explicitly coding something like:
loop
open cursor
parse statment
bind values
execute cursor
close cursor
end loop
in this case, you should code:
open cursor
parse statment
loop
bind values
execute cursor
end loop
close cursor
how you achieve this exactly varies by the language you program in and the API you us.
if you have 100 sql statments and only 10 parses -- you have done something magical. You have executed 90 statements without even parsing them.
I think what you mean is "if a SINGLE statement was parsed ten times and executed 100 times -- what does this signify". It signifies you parsed it 9 times more then you should have.
2) logical ios are buffer reads, yes.
o QUERY How many blocks we processed in 'consistent read' mode. This will include counts of blocks read from the rollback segment in order to 'rollback' a block.
o CURRENT How many blocks were read in 'current' mode. Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieve for a query are gotten as they existed when the query began. Current mode blocks are gotten as they exist right now, not from a previous point in time. During a SELECT, we might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (we need the 'right now' information on that, not the consistent read). During a modification, we will access the blocks in current mode in order to write to them.
doing a physical IO takes longer then a buffered read, you should strive to do buffered reads in most cases.
3) if you execute a query 100 times and fetch from it 100 time and get 30 rows -- that indicates 30 times it returned no data.
Consider:
create table t as select rownum x from all_objects where rownum <= 70;
begin
for i in 1..100
loop
begin
select x into l_x from t where x = i;
exception
when no_data_found then l_x := null;
end;
.....
end loop;
that'll execute the query 100 times -- find only 70 rows. thats what that means. It is not an error, it is what it is ;)
My book, coming out in June, has a big chapter on this stuff with lots more detail.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment