| 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 -> Re: bad ratio in parse_calls to executions of SQL
Thank you Mr. Harald Wakonig for your email. Please note that If you have a
question you should normally post a message to this newsgroup, Not reply to
replies while asking question.
Anyywah please find attached the answers for your questions. Please refer to your Oracle provided manuals for further information!
Happy Holidays
"The Views expressed here are my own and not necessarily those of Oracle Corporation"
SQL Parsing Flow Diagram
Statement
Submitted
|
Is it in an open cursor?--------------YES----V
| |
NO |
| |
Is SESSION_CACHED_CURSOR = Value |
and cursor in --------------YES----V In these 3 cases we
Session Cursor cache? | know that the cursor has
| | already been parsed, so
NO | re-parsing is
| | unnecessary.
Is HOLD_CURSOR=Y |
and cursor in --------------YES----V
Held cursor cache? |
| |
NO |
| | ^
OPEN A CURSOR | CLIENT SIDE |
| | -------------|
Statement is Hashed and compared | SERVER SIDE |
with the Hashed value in the sql area | V
| V
Is it in sql area? --YES-(Soft Parse)--> ---------
| | |
NO | EXECUTE |
| | |
PARSE STATEMENT ('Hard' Parse)---------> ---------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
in a soft-parse ?
----------------------------------------------------------------------------
----------------------------------------------------------------------------
And the raw trace shows.........
>>> Step 2. >>> Note the 'mis=1' which indicates the hard parse. >>> The fact that the PARSE line is there indicates a parse call.PARSING IN CURSOR #1 len=33 dep=0 uid=8 select * from dual where 600=600
FETCH #1:c=0,e=0,p=0,cr=1,cu=3,mis=0,r=1,dep=0,og=3 STAT #1 id=1 cnt=1 STAT #1 id=2 cnt=1
>>> Step 3
>>> Another 'hard' parse as we'd expect.
PARSING IN CURSOR #1 len=45 dep=0 uid=8
select /* Dummy */ * from dual where 600=600
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=3
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3
FETCH #1:c=0,e=0,p=0,cr=1,cu=3,mis=0,r=1,dep=0,og=3 STAT #1 id=1 cnt=1 STAT #1 id=2 cnt=1
>>> Step 4
>>> Now we find it in the shared pool despite the parse call (mis=0)
PARSING IN CURSOR #1 len=33 dep=0 uid=8
select * from dual where 600=600
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3
FETCH #1:c=0,e=0,p=0,cr=1,cu=3,mis=0,r=1,dep=0,og=3 STAT #1 id=1 cnt=1 STAT #1 id=2 cnt=1
>>> Step 5
>>> Let's remove the SQL in the shared pool
PARSING IN CURSOR #1 len=31 dep=0 uid=8
alter system flush shared_pool
END OF STMT
PARSE #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3
>>> Step 6 (following 26 pages of recursive SQL)
>>> Same query as the soft parse above but this time we've got to do the
work
>>> and so 'mis=1'.
PARSING IN CURSOR #1 len=33 dep=0 uid=8
select * from dual where 600=600
END OF STMT
PARSE #1:c=0,e=0,p=1,cr=62,cu=4,mis=1,r=0,dep=0,og=3
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=3
FETCH #1:c=0,e=0,p=0,cr=1,cu=3,mis=0,r=1,dep=0,og=3 STAT #1 id=1 cnt=1 STAT #1 id=2 cnt=1
And the tkprof output :
select *
from
dual where 600=600
call count cpu elapsed disk query current rows
Note: Above implies 3 parse calls but below indicates that 2 of these were
real misses.
If the shared pool was flushed when we have parsed the SQL and have to
reparse it during an execute then you'd have a line which reads
'Misses in library cache during execute'. This is an indication of a
RELOAD.
Misses in library cache during parse: 2
Optimizer hint: RULE
Parsing user id: 8 (KQUINN)
Rows Execution Plan
select /* Dummy */ *
from
dual where 600=600
call count cpu elapsed disk query current rows
Misses in library cache during parse: 1
Optimizer hint: RULE
Parsing user id: 8 (KQUINN)
Rows Execution Plan
alter system flush shared_pool
call count cpu elapsed disk query current rows
Misses in library cache during parse: 0
Optimizer hint: RULE
Parsing user id: 8 (KQUINN)
SELECT * FROM emp WHERE ename='CLARK';
is used by the application instead of
SELECT * FROM emp WHERE ename=:bind1;
Eg 2:
SELECT sysdate FROM dual;
does not use bind variables but would not be considered as a literal SQL statement for this article as it can be shared.
Eg 3:
SELECT version FROM app_version WHERE version>2.0;
If this same statement was used for checking the 'version' throughout the application then the literal value '2.0' is always the same
so this statement can be considered sharable.
Hard Parse
Soft Parse
Identical Statements ?
SELECT ENAME from EMP;
SELECT ename from emp;
Although both of these statements are really the same they are not identical as an upper case 'E' is not the same as a lower case 'e'.
Sharable SQL
User SCOTT has a table called EMP and issues:
SELECT ENAME from EMP;
User FRED has his own table called EMP and also issues:
SELECT ENAME from EMP;
Although the text of the statements are identical the EMP tables are
different objects. Hence these are different versions of the same basic
statement. There are many
things that determine if two identical SQL strings are truely the same
statement (and hence can be shared) including:
All object names must resolve to the same actual objects
The optimizer goal of the sessions issuing the statement should be the
same
The types and lengths of any bind variables should be "similar". (We
dont discuss the details of this here but different types or lengths of bind
variables can
cause statements to be classed as different versions)
Versions of a statement
Library Cache and Shared Pool latches
The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool.
The library cache latches (and the library cache pin latch in Oracle 7) protect operations within the library cache itself.
All of these latches are potential points of contention. The number of latch
gets occurring is influenced directly by the amount activity in the shared
pool, especially
parse operations. Anything that can minimise the number of latch gets and
indeed the amount of activity in the shared pool is helpful to both
performance and
scalability.
Literal SQL versus Shared SQL
Literal SQL
SELECT distinct cust_ref FROM orders WHERE total_cost < 10000.0;
versus
SELECT distinct cust_ref FROM orders WHERE total_cost < :bindA;
For the first statement the CBO could use histogram statistics that have
been gathered to decide if it would be fastest to do a full table scan of
ORDERS or to use an
index scan on TOTAL_COST (assuming there is one). In the second statement
CBO has no idea what percentage of rows fall below ":bindA" as it has no
value for
this bind variable to determine an execution plan . Eg: ":bindA" could be
0.0 or 99999999999999999.9
There could be orders of magnitude difference in the response time between
the two execution paths so using the literal statement is preferable if you
want CBO to
work out the best execution plan for you. This is typical of Decision
Support Systems where there may not be any 'standard' statements which are
issued repeatedly
so the chance of sharing a statement is small. Also the amount of CPU spent
on parsing is typically only a small percentage of that used to execute each
statement so it is probably more important to give the optimizer as much
information as possible than to minimize parse times.
Sharable SQL
Eg: Even parsing a simple SQL statement may need to acquire a library cache latch 20 or 30 times.
The best approach to take is that all SQL should be sharable unless it is
adhoc or infrequently used SQL where it is important to give CBO as much
information as
possible in order for it to produce a good execution plan.
Reducing the load on the Shared Pool
Parse Once / Execute Many
By far the best approach to use in OLTP type applications is to parse a
statement only once and hold the cursor open, executing it as required. This
results in only the
initial parse for each statement (either soft or hard). Obviously there will
be some statements which are rarely executed and so maintaining an open
cursor for them is
a wasteful overhead.
Note that a session only has <> cursors available and holding cursors open is likely to increase the total number of concurrently open cursors.
In precompilers the HOLD_CURSOR parameter controls whether cursors are held open or not while in OCI developers have direct control over cursors .
Eliminating Literal SQL
SELECT substr(sql_text,1,40) "SQL",
count(*) ,
sum(executions) "TotExecs"
Note: If there is latch contention for the library cache latches the above
statement may cause yet further contention problems.
The values 40,5 and 30 are example values so this query is looking for
different statements whose first 40 characters are the same which have only
been executed a
few times each and there are at least 30 different occurrances in the shared
pool. This query uses the idea it is common for literal statements to begin
"SELECT
col1,col2,col3 FROM table WHERE ..." with the leading portion of each
statement being the same.
Note:There is often some degree of resistance to converting literal SQL to
use bind variables. Be assured that it has been proven time and time again
that
performing this conversion for the most frequently occurring statements can
eliminate problems with the shared pool and improve scalability greatly.
See the documentation on the tool/s you are using in your application to determine how to use bind variables in statements.
Stages of Optimization
o Parse - Includes some simple transformations o Transformation - View Merging
Sub-Query Merging
Predicate Pushdown
Transitive Predicate Generation
o Search Space Definition - Determination of which join orders to
evaluate.
o Enumeration - Costing of the above join orders and picking the cheapest.
o Row Source Compilation - Building Query Execution Runtime structures.
Parse
Performs basic syntactic checking.
Simple transformations are performed:
o between -> range
o exists : exists (select * from ... =>> exists (select 1 from ...
o ALL/IN -> changed to and/or statements
e.g. ... x1 in (45,576,42) =>> ... x1=45 or x1=576 or x1=42
o 1 or 0 >= (select count(*) ...) -> exists
example query:
select ...
from X
where 1 >= (select count(*) from Y)
VVVV
select ...
from X
where exists (select 1 from Y)
Once the SQL has been modified (if necessary) then we check to see if the
statement exists in the library cache area of the shared pool - if so then
we
implement a soft parse.
The SQL is checked by producing a hash value and comparing this with
hashed values already in the library cache. Hashing is still resource
intensive
though not as costly as a full parse. See [NOTE:32895.1].
Transformation
View Merging
View merging involves rewriting the view as a select on the view's base tables. These selects can then be incorporated back into the original query potentially opening new joins. e.g.
The following constructs in views will mean that the are NOT merged:
o group by
o aggregate
o distinct
o duplicate sensitivity in outer query block
o rownum
o union
o intersect
o minus
o certain types of outer join
If the view is non mergeable then the view definition as a whole is placed in to the from clause as an inline view and this is exectued as a separate query block.
Sub-Query Merging
There are 3 classes of Sub-Query:
o Single Row Sub-Queries
o Those that can be converted into NOT EXISTS
o Those that can be converted to exists
o NOT IN, ALL are converted into NOT EXISTS
o IN, SOME, ANY are converted into EXISTS
Single Row Sub-Query
A single row Sub-Query returns only 1 row from the Sub-Query block. Otherwise errors are returned.
If the Sub-Query is not correlated then the Sub-Query is evaluated once and the resultant value is substituted into the where clause.
e.g.
select ... from ... where x = (select y from ...)
VVVVV select ... from ... where x = Evaluated_Value
Sub-Query That can be converted to NOT EXISTS
NOT IN Sub-Query
A normal join says if there is a row in the inner table that joins then return the row. Anti joiun is the opposite of this in that it returns the when there is no join to the inner table.
NOT IN can be converted into an anti-join if the following conditions are met:
o All columns referenced in Sub-Queries must be known to be Not NULL
o Sub-Query Cannot be correlated
o Where clause of the outer query block must not have any or's in it
o loads of other stuff
Otherwise it gets transformed into a NOT EXISTS
ALL Sub-Query
All always is transformed into a NOT EXISTS:
ALL => NOT EXISTS: x = all(select y from t where p)
VVVVV not exists (select 1 from t where NOT nvl(p,false) and x != y)
In this case the optimizer adds nvl(p,false) here to get rid of the null problem. The null problem is that the sub-Query can return 3 values (True, False and unknown(NULL)) but the exists operator only understands true and false. So if the Sub-Query returns null after the transformation you get incorrect results because null rows are eliminated. Hence we need to handle nulls with the nvl function - this gives them a value that we can then handle in the EXISTS operator..
Sub-Queries That can be converted to EXISTS
IN/=ANY Sub-Query
Firstly we try to flatten an IN into a join under the same rules that
apply
to view merging. e.g.
select ... from X where c IN (select d from Y where P)
VVVVV select ... from X,Y where X.c = Y.d and P
If we fail to satisfy any of these conditions and the Sub-Query is not correlated then we transform it into an inline view adding a distinct to the select list. e.g.
select AVG... from X where c IN (select d from Y where P) group by ...
VVVVV
select AVG... from X,(select distinct d from Y where P) where X.c = Y.d
group
by ...
Otherwise it will be transformed into an EXISTS.
IN => EXISTS: x in (select y from t where p)
VVVVV exists (select 1 from t where p and x=y)
In this case a null value for x works out ok - because it does not get NOTted.
Duplicate Sensitive Queries
Queries where the outer block of a Sub-Query are duplicate sensitive do
not get
flattened into joins. Instead they get transformed into inline views or
exists
Sub-Queries as described above.
If a subquery has one of the following it is not Duplicate sensitive
o Fully satisfied unique not null index (i.e. a Primary Key)
o Distinct in the Sub-Query
o Outer block has min/max type aggregates
Predicate Pushdown
Transitive Predicate Generation
Search Space Definition
Joining
We evaluate every possible join order for a query.
If we have 5 tables or less then we join everything to everything else to
allow
the possibility of cartesian products. The maximum number of join orders
for 5
tables is 5! (5 factorial) or 120 join orders.
If we have 6 or more tables then we reduce the search space required by
elimination of join orders that do not appear in the query itself
We prune out cartesian products and put them at the 'end' of
the join order so that the cartesian products are evaluated last when,
hopefully, the row sources have been narrowed down to a reasonable number
of
rows.
The maximum number of join orders we will evaluate is 80,000.
Star Joins
Star join just forces the big table to be last in the join order (at the
top of the tree).
The means that it gets the cheapest cross product and can then go into
the big base table using a concatenated index.
It adds back certain join strategies if certain conditions are met.
Costing
The Cardinality of tables is calculated. Costs of the following are calculated:
o Cardinality of tables
o Single table predicate e.deptmo = 10
o Double table predicate e.deptno = d.deptno
o Costs of indexes on the tables
Density = frequency of no popular values. This is used for...
Costs are based on disk i/o - 1 i/o9 is give a nominal cost of 1 and every
other
operation is costed on this basis.
FTS cost is not blocks/MBR Cost - there is a factor involved
Clustering factor or balls & Cells
"Harald Wakonig" <wakonig_at_compuserve.com> wrote in message news:38661B9E.6287EDE1_at_compuserve.com...
> Hallo, > Hi, > > > Oracle does two types of parses - Hard Parse and Soft Parse. > > as you are very knowledgable about parsing, I want to ask some add-onquestions
> about that topic. > > Q1) you distinct between "soft parses" and "hard parses" - which of themare
> > SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREA > ORDER BY PARSE_CALLS; > > Q2) Steps during parsing (from Oracle-Concepts Manual) > ====================================================== > Please can you determine, which steps are in a hard-parse and which are ina
> soft-parse ? > --------------------------------------------------- > > (1) translating a SQL statement, verifying it to be a valid statement > > (2) performing data dictionary lookups to check table and columndefinitions
> change during the statement's parsing > > (4) checking privileges to access referenced schema objects > > (5) determining the optimal execution plan for the statement > > (6) loading it into a shared SQL area > > (7) for distributed statements, routing all or part of the statement toremote
> nodes that contain referenced data > > Some details which I try to understand: > > I understand, that Step (4) needs to be done by each execution - it'spossible,
> access one of the tables... > > Step (1) definitely needs only be done once; an statement which is alreadyin
> > > Q3) Soft Parse and library cache latch and shared pool latch > ============================================================ > you point out, that for each execution a soft-parse is necessary. Doesthis
> soft-parse require one of those two latches (assuming that there is enough > Shared Pool size)? > > I assume the shared pool latch is used in Step (6) , which is after step(4) -
> if the statement is already there ? > > Q4) Is Step (5) - determine the optimal execution plan - part of the "soft > parse" or part of the "hard parse" ? > ============================================================================
> the statement uses bind-varibles ! (..WHERE sal > :v_sal ); > > Thank you, > > Harald > > > >Received on Sun Dec 26 1999 - 10:18:53 CST
 |
 |