-
Hello,
Q1.
1.1 When one talk about RECURSIVE CALLS (v$sysstat), that means calls due to internal SQL statements & SQL statements in a PL/SQL or only internal SQL statements. (NB : in tkprof output file : RECURSIVE could mean internal stmt or SQL stmt in PL/SQL statement).
1.2 In one of our Database, I took a look in the v$sysstat view and I noticed that RECURSIVE CALLS is a little bit high (12510330). Is it possible to reduce that ? (NB : I took this statistics 16 hours after the startup of the DB).
Q2.
2.1 In the same DB, OPENED CURSORS CUMULATIVE = 708243. The PARSE COUNT = 709891. Is there a correlation between OPEN CURSORS CUMULATIVE
and PARSE COUNT.
2.2 When you issue the following SQL statement :
SELECT COUNT(*) FROM Z_TEST;
In the v$sysstat : OPENED CURSORS CUMULATIVE = 500
When we issue the same statement a second time :
In the v$sysstat : OPENED CURSORS CUMULATIVE = 500
I though that Oracle close implicit cursor and so we must have
500 then 501 for the OPENED CURSORS CUMULATIVE. Am I wrong ?
Thanks
-
Hi,
1.1 Recurcive calls are the calls done by ORACLE on its dictionary. For example to parse a SQL statement it needs to query the dictionary for checking tablename, columns, access rights etc. Before acessing the table it requires datafile, extent info. To minimise recurcive calls you can increase shared pool so that some of it gets buffered.
1.2 You look at the total of recursive calls in relation to user calls. As long as you have a healthy ratio its OK.
2.1 Parse count should be related to number of executions to find out the effectiveness of SQL area in the shared pool.
2.2 ORACLE reuses information if it is already present. That may be reason you find that there is no change in the cumulative cursor information.
Baliga
