-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ventak,

You should be able to query v$sql and find look at the V$SQL.EXECUTIONS to determine how often something is being
called. First you should query V$SQL to determine the hash_value of the statement that you are interested in.

SQL> select hash_value,sql_text from v$sql where sql_text like '%your_wildcard_search%';

Once you identify that hash_value, you need to see which cursor you are interested in.
There could be more than one ...

Example:

SQL> select hash_value,address,child_number,child_address,rows_processed,executions,
  first_load_time from v$sql where hash_value = 4061502468;

HASH_VALUE|ADDRESS      |CHILD_NUMBER|CHILD_ADDRESS  |ROWS_PROCESSED|EXECUTIONS|FIRST_LOAD_TIME
- ----------|----------------|------------|----------------|--------------|----------|-------------------
4061502468|00000004961F5F10|       0|000000047ED12A10|         0|      0|2006-11-30/10:28:19
4061502468|00000004961F5F10|       1|0000000476B775A0|         0|      0|2006-11-30/10:28:19
4061502468|00000004961F5F10|       2|00000004669E3040|      16129|   16129|2006-11-30/10:28:19

In my example above, the cursor #2 is really the only one being executed
and it was loaded into memory on Nov 30th 2006 at 10:28 am.

If you re-run this query on your hash_value, you should see the number of executions + rows_processed increment
as the package is being executed. You could easily add "sysdate' to the query to get a feel for how often
this package gets executed.

HTH

- -peter




Venkat Rao wrote:
> Hi All,
>  
>  it possible to find how many times a particular package has been called? is it putting the cron job to find loads from v$librarychache ? or numer of executions from statspack?
>  
>  any insite help would really appreciate..
>  
>  Reg,
>  Venkat
>  
>  
>
> Send instant messages to your online friends http://uk.messenger.yahoo.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFiwMtoyy5QBCjoT0RAru1AKCOOJWiSxB+2a71cWsYHUH1TTORUACfbhOp
jcZEUIOfikE+mCIw9T3l4W4=
=k+2T
-----END PGP SIGNATURE-----

--
To unsubscribe, email: suse-oracle-unsubscribe@(protected)
For additional commands, email: suse-oracle-help@(protected)
Please see http://www.suse.com/oracle/ before posting