Home » RDBMS Server » Performance Tuning » Regarding parse_calls in v$sqlarea
Regarding parse_calls in v$sqlarea [message #203904] Thu, 16 November 2006 22:17 Go to next message
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

Re: Regarding parse_calls in v$sqlarea [message #204117 is a reply to message #203904] Fri, 17 November 2006 23:28 Go to previous messageGo to next message
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.
Re: Regarding parse_calls in v$sqlarea [message #204400 is a reply to message #203904] Mon, 20 November 2006 08:05 Go to previous message
michael_bialik
Messages: 611
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.
Previous Topic: Oracle 9i materialized view refresh problems
Next Topic: Difference between two query
Goto Forum:
  


Current Time: Sat Dec 10 20:33:07 CST 2016

Total time taken to generate the page: 0.08109 seconds