Oracle 10g: SQL Statements KEPT in the Shared Pool

Hi,

i pinned a few SYS packages (DBMS_SHARED_POOL.KEEP) in my 10.2.0.1.0database (AIX
5.3). When i was looking at V$DB_OBJECT_CACHE, i found the following:

SQL> select owner,type,kept,executions,count(*) from v$db_object_cache

     where kept = 'yes' group by owner,type,kept,executions;

OWNER TYPE KEP EXECUTIONS COUNT(*)
------------ ------------ --- ---------- ----------

PERFSTAT     PACKAGE BODY YES       1184          1
SYSMAN       PACKAGE BODY YES     104252          1
SYS          PACKAGE BODY YES      66996          1
SYS          INDEX        YES          0          7
SYS          PACKAGE BODY YES         77          1
SYS          TABLE        YES          0         26
SYS          PACKAGE      YES          0          4
SYS          PACKAGE BODY YES   13325213          1
SYS          TYPE BODY    YES          0          1
SYSMAN       PACKAGE BODY YES      32838          1
SYS          TYPE         YES          0          1
             CURSOR       YES          0       1031
SYSMAN       PACKAGE BODY YES     165799          1
PERFSTAT     PACKAGE      YES          0          1
SYSMAN       PACKAGE      YES          0          4
             NOT LOADED   YES          0          1
SYS          CLUSTER      YES          0          6
SYS          PACKAGE BODY YES          0          1
SYSMAN       PACKAGE BODY YES     254736          1

What i am surprised about is that SQL statements are also KEPT even though i have not used the DBMS_SHARED_POOL.KEEP package on them. The owner name is also empty. I do use an OS authenticated user (OPS$DEV).

Its funny that there are 181 cursors with an exec count of more than 50 ( V$SQLAREA) whereas the output from V$DB_OBJECT_CACHE says that it has KEPT 1031 cursors though they have an exec count of 0.

SQL> select count(*) from v$sqlarea where executions > 50;

  COUNT(*)

       181

Seems like an automatic thing introduced in 10g. the closest bug i found was 4577168. it was closed as not a bug!

In case you are wondering which package executed 13 Million times,

SQL> select name,executions from v$db_object_cache where executions >= 13325353;

NAME                      EXECUTIONS


------------------------- ----------

STANDARD                    13325420
DBMS_ASSERT                 22892596

Funny that DBMS_ASSERT was not KEPT though it has been executed many more times.

Have anyone come across this strange behaviour before? what does NOT LOADEDmean?

thanks
anand

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 21 2006 - 01:29:42 CST