Oracle Statistics
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Oracle Statistics

  1. #1
    Join Date
    Sep 2000
    Posts
    64
    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
    Sofiane

  2. #2
    Join Date
    Jan 2001
    Posts
    126
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width