OraFAQ Forum: Performance Tuning » Regarding parse

Home » RDBMS Server » Performance Tuning » Regarding parse_calls in v$sqlarea

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Regarding parse_calls in v$sqlarea [message #203904]

Thu, 16 November 2006 22:17

reena_ch30
Messages: 100
Registered: December 2005

Senior Member

Here is the sample query:-

select count(*) from emp;
COUNT(*)
----------
15

Now i check v$sqlarea
select address, hash_value, parse_calls,sql_text from v$sql where sql_text like '% count(*) from emp%';

Output:-
ADDRESS HASH_VALUE PARSE_CALLS SQL_TEXT
6657AC34 2099331969 1 select count(*) from emp

I execute the query again and check v$sqlarea
O/p

ADDRESS HASH_VALUE PARSE_CALLS SQL_TEXT
6657AC34 2099331969 2 select count(*) from emp

parse calls are increased by 1. why has it reparsed the query? Shouldn't it use the existing parsed sql? Is there any parameter setting to be done for this? Please help.

Thanks
Reena

Report message to a moderator

Re: Regarding parse_calls in v$sqlarea [message #204117 is a reply to message #203904]

Fri, 17 November 2006 23:28

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

hi reena

parse calls are increased by 1. why has it reparsed the query? Shouldn't it use the existing parsed sql? Is there any parameter setting to be done for this? Please help.

Each time a statement is executed, Oracle parses the statement for a number of reasons.
1.Does an execution plan already exist for this statement?
2.Can data in cache (or already open cursors) be used to satisfy the request?

EXECUTIONS How many times the statement has been executed
PARSE_CALLS How many times Oracle has had to re-parse the statement

http://www.billmagee.co.uk/oracle/sqltune/080_identify.html

hope this helps
Mohammad Taj.

Report message to a moderator

Re: Regarding parse_calls in v$sqlarea [message #204400 is a reply to message #203904]

Mon, 20 November 2006 08:05

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Hi.

In your case Oracle performed so-called SOFT PARSE (search of your statement in shared pool ).
You are probably using sql*plus, so each time you are executing a statement Oracle opens an implicit cursor, fetches a data and closes the cursor. The cursor is freed from session memory, so
the next time it repeats the whole cycle and searches the shared pool for a statement (soft parse).

HTH.

Report message to a moderator

Previous Topic:	Oracle 9i materialized view refresh problems
Next Topic:	Difference between two query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 02:46:18 CST 2024