Query v$db_object_cache table : db_object_cache « System Tables Views « Oracle PL / SQL






Query v$db_object_cache table

 
SQL>
SQL> SELECT owner, name, type, loads,
  2         executions, locks, pins, kept
  3  FROM   v$db_object_cache
  4  where  rownum < 50
  5  ORDER BY executions DESC;

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

delete from dependency$ where d_obj#=:1
CURSOR                                2         47          2          0 NO



                                                                                                                                      Page           1

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

select rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,nvl(degree,1), nvl(instances,1) from tab$ where obj# = :1
NOT LOADED                            2         19          0          0 NO



                                                                                                                                      Page           2

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

update sys.job$ set this_date=:1 where job=:2
CURSOR                                2         17          0          0 NO



                                                                                                                                      Page           3

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3
CURSOR                                2          6          0          0 NO



                                                                                                                                      Page           4

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

insert into wrh$_filemetric_history     (snap_id, dbid, instance_number,      fileid, creationtime,   begin_time, end_time, intsize, group_id,      avgreadtime, avgwritetime, physicalread,      physic
alwrite, phyblkread, phyblkwrite)  select         :snap_id, :dbid, :instance_number,      fileid, cr
eationtime,      begtime, endtime, intsize_csec, groupid,      avrdtime, avwrtime, phyread,      phywrite, phybkrd, phybkwr  from x$kewmflmv
CURSOR                                1          2          0          0 NO


                                                                                                                                      Page           5

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---


SELECT snap_id , SERVICE_NAME_HASH FROM   (SELECT /*+ ordered use_nl(t2) index(t2) */ t2.snap_id , t1.NAME_HASH  SERVICE_NAME_HASH FROM V$SERVICES t1, WRH$_SERVICE_NAME t2      WHERE t2.dbid(+)  = :db
id  AND t2.SERVICE_NAME_HASH(+) = t1.NAME_HASH) WHERE nvl(snap_id, 0) < :snap_id
CURSOR                                1          2          0          0 NO


                                                                                                                                      Page           6

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---


insert into wrh$_buffer_pool_statistics   (snap_id, dbid, instance_number, id, name, block_size, set_msize,    cnum_repl, cnum_write, cnum_set, buf_got, sum_write, sum_scan,    free_buffer_wait, write
_complete_wait, buffer_busy_wait,    free_buffer_inspected, dirty_buffers_inspected, db_block_change
,    db_block_gets, consistent_gets, physical_reads, physical_writes)  select    :snap_id, :dbid, :instance_number, id, name, block_size, set_msize,    cnum_repl, cnum_write, cnum_set, buf_got, sum_wr
ite, sum_scan,    free_buffer_wait, write_complete_wait, buffer_busy_wait,    free_buffer_inspected,


                                                                                                                                      Page           7

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
 dirty_buffers_inspected, db_block_change,    db_block_gets, consistent_gets, physical_reads, physical_writes  from    v$buffer_pool_statistics
CURSOR                                1          2          0          0 NO




                                                                                                                                      Page           8

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
select ts# from ts$ where name=:1
NOT LOADED                            1          1          0          0 NO




                                                                                                                                      Page           9

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
BEGIN DBMS_FEATURE_RAC(:feature_boolean, :aux_cnt, :feature_info);  END;
CURSOR                                2          1          0          0 NO




                                                                                                                                      Page          10

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
select snap_interval, retention,most_recent_snap_time, most_recent_snap_id, status_flag, most_recent_purge_time, most_recent_split_id, most_recent_split_time, mrct_snap_time_num, mrct_purge_time_num,
snapint_num, retention_num, swrf_version, registration_status, mrct_baseline_id, topnsql from wrm$_w
r_control where dbid = :dbid
CURSOR                                1          1          0          0 NO



                                                                                                                                      Page          11

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

 select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from "JAVA2S"."PERSON" A where( "FIRST_NAME" is null)
NOT LOADED                            6          1          0          0 NO



                                                                                                                                      Page          12

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

select kernelcols, initrans, maxtrans, nvl(tab#, 0), ts#, file#, block#  from tab$ where obj# = :1
CURSOR                                1          1          0          0 NO



                                                                                                                                      Page          13

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

select FILE_MAP_IDX,FILE_CFGID,FILE_STATUS,FILE_NAME, FILE_TYPE,FILE_STRUCTURE,FILE_SIZE,FILE_NEXTS,LIB_IDX from gv$map_file where inst_id = USERENV('Instance')
CURSOR                                1          0          0          0 NO



                                                                                                                                      Page          14

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

select FILE_MAP_IDX,FILE_CFGID,FILE_STATUS,FILE_NAME, FILE_TYPE,FILE_STRUCTURE,FILE_SIZE,FILE_NEXTS,LIB_IDX from gv$map_file where inst_id = USERENV('Instance')
CURSOR                                1          0          0          0 YES



                                                                                                                                      Page          15

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

update sys.job$ set this_date=:1 where job=:2
CURSOR                                1          0          0          0 YES



                                                                                                                                      Page          16

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

select rowcnt,blkcnt,empcnt,avgspc,chncnt,avgrln,nvl(degree,1), nvl(instances,1) from tab$ where obj# = :1
CURSOR                                1          0          0          0 NO



                                                                                                                                      Page          17

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

select ts# from ts$ where name=:1
CURSOR                                1          0          0          0 NO



                                                                                                                                      Page          18

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
SYS
WRH$_SYSTEM_EVENT
TABLE                                 1          0          0          0 NO



                                                                                                                                      Page          19

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

SELECT snap_id , SERVICE_NAME_HASH FROM   (SELECT /*+ ordered use_nl(t2) index(t2) */ t2.snap_id , t1.NAME_HASH  SERVICE_NAME_HASH FROM V$SERVICES t1, WRH$_SERVICE_NAME t2      WHERE t2.dbid(+)  = :db
id  AND t2.SERVICE_NAME_HASH(+) = t1.NAME_HASH) WHERE nvl(snap_id, 0) < :snap_id
CURSOR                                1          0          0          0 YES



                                                                                                                                      Page          20

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

SELECT O.SUBNAME FRO
NOT LOADED                            0          0          0          0 NO



                                                                                                                                      Page          21

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3
CURSOR                                1          0          0          0 YES



                                                                                                                                      Page          22

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

UPDATE SYS.SCHEDULER
NOT LOADED                            0          0          0          0 NO



                                                                                                                                      Page          23

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

insert into wrh$_filemetric_history     (snap_id, dbid, instance_number,      fileid, creationtime,   begin_time, end_time, intsize, group_id,      avgreadtime, avgwritetime, physicalread,      physic
alwrite, phyblkread, phyblkwrite)  select         :snap_id, :dbid, :instance_number,      fileid, cr
eationtime,      begtime, endtime, intsize_csec, groupid,      avrdtime, avwrtime, phyread,      phywrite, phybkrd, phybkwr  from x$kewmflmv
CURSOR                                1          0          0          0 YES


                                                                                                                                      Page          24

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---


select kernelcols, initrans, maxtrans, nvl(tab#, 0), ts#, file#, block#  from tab$ where obj# = :1
CURSOR                                1          0          0          0 YES


                                                                                                                                      Page          25

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---


insert into wrh$_buffer_pool_statistics   (snap_id, dbid, instance_number, id, name, block_size, set_msize,    cnum_repl, cnum_write, cnum_set, buf_got, sum_write, sum_scan,    free_buffer_wait, write
_complete_wait, buffer_busy_wait,    free_buffer_inspected, dirty_buffers_inspected, db_block_change
,    db_block_gets, consistent_gets, physical_reads, physical_writes)  select    :snap_id, :dbid, :instance_number, id, name, block_size, set_msize,    cnum_repl, cnum_write, cnum_set, buf_got, sum_wr
ite, sum_scan,    free_buffer_wait, write_complete_wait, buffer_busy_wait,    free_buffer_inspected,


                                                                                                                                      Page          26

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
 dirty_buffers_inspected, db_block_change,    db_block_gets, consistent_gets, physical_reads, physical_writes  from    v$buffer_pool_statistics
CURSOR                                1          0          0          0 YES




                                                                                                                                      Page          27

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
 select /*+ all_rows ordered */ A.rowid, :1, :2, :3 from "JAVA2S"."PERSON" A where( "FIRST_NAME" is null)
CURSOR                                1          0          0          0 NO

PUBLIC


                                                                                                                                      Page          28

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
UTL_FILE
SYNONYM                               1          0          0          0 NO




                                                                                                                                      Page          29

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
SELECT CV.FLAGS FROM
NOT LOADED                            0          0          0          0 NO




                                                                                                                                      Page          30

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
SELECT :B1 TYPE, BP.
NOT LOADED                            0          0          0          0 NO

JAVA2S


                                                                                                                                      Page          31

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
DYNAMICQUERY
PROCEDURE                             1          0          0          0 NO

JAVA2S


                                                                                                                                      Page          32

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
USER_CONS_COLUMNS
NOT LOADED                            1          0          0          0 NO




                                                                                                                                      Page          33

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
select streams_pool_size_for_estimate s,           streams_pool_size_factor * 100 f,           estd_spill_time + estd_unspill_time, 0  from v$streams_pool_advice
CURSOR                                1          0          0          1 NO




                                                                                                                                      Page          34

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
select streams_pool_size_for_estimate s,           streams_pool_size_factor * 100 f,           estd_spill_time + estd_unspill_time, 0  from v$streams_pool_advice
CURSOR                                1          0          1          0 YES




                                                                                                                                      Page          35

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
delete from dependency$ where d_obj#=:1
CURSOR                                1          0          0          0 NO




                                                                                                                                      Page          36

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
delete from dependency$ where d_obj#=:1
CURSOR                                1          0          2          0 YES




                                                                                                                                      Page          37

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
SELECT OWNER, OBJECT
NOT LOADED                            0          0          0          0 NO




                                                                                                                                      Page          38

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
INSERT INTO purchase VALUES ('S', 'GA', '28-FEB-00', 10)
CURSOR                                1          0          0          0 NO




                                                                                                                                      Page          39

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
INSERT INTO purchase VALUES ('S', 'GA', '28-FEB-00', 10)
NOT LOADED                            2          0          0          0 NO

SYS


                                                                                                                                      Page          40

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
WRH$_DATAFILE
TABLE                                 1          0          0          0 NO




                                                                                                                                      Page          41

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
select snap_interval, retention,most_recent_snap_time, most_recent_snap_id, status_flag, most_recent_purge_time, most_recent_split_id, most_recent_split_time, mrct_snap_time_num, mrct_purge_time_num,
snapint_num, retention_num, swrf_version, registration_status, mrct_baseline_id, topnsql from wrm$_w
r_control where dbid = :dbid
CURSOR                                1          0          0          0 YES



                                                                                                                                      Page          42

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
SYS
HISTGRM$
TABLE                                 1          0          0          0 YES



                                                                                                                                      Page          43

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---

select id, name, block_size, advice_status,                  size_for_estimate, size_factor, buffers_for_estimate,        estd_physical_read_factor, estd_physical_reads,              estd_physical_rea
d_time,                                     estd_pct_of_db_time_for_reads,
     estd_cluster_reads,                                          estd_cluster_read_time                               from   gv$db_cache_advice where inst_id = userenv('instance')
CURSOR                                1          0          0          0 NO


                                                                                                                                      Page          44

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---


select id, name, block_size, advice_status,                  size_for_estimate, size_factor, buffers_for_estimate,        estd_physical_read_factor, estd_physical_reads,              estd_physical_rea
d_time,                                     estd_pct_of_db_time_for_reads,
     estd_cluster_reads,                                          estd_cluster_read_time                               from   gv$db_cache_advice where inst_id = userenv('instance')


                                                                                                                                      Page          45

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
NOT LOADED                            1          0          0          0 NO


SELECT SUM(BLOCKS *


                                                                                                                                      Page          46

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
NOT LOADED                            0          0          0          0 NO


UPDATE WRI$_DBU_USAG


                                                                                                                                      Page          47

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
NOT LOADED                            0          0          0          0 NO


select   LOW_OPTIMAL_SIZE,           HIGH_OPTIMAL_SIZE,           OPTIMAL_EXECUTIONS,           ONEPASS_EXECUTIONS,           MULTIPASSES_EXECUTIONS,           TOTAL_EXECUTIONS    from   GV$SQL_WORKAR
EA_HISTOGRAM    where  INST_ID = USERENV('Instance')


                                                                                                                                      Page          48

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
CURSOR                                1          0          0          0 YES


select   LOW_OPTIMAL_SIZE,           HIGH_OPTIMAL_SIZE,           OPTIMAL_EXECUTIONS,           ONEPASS_EXECUTIONS,           MULTIPASSES_EXECUTIONS,           TOTAL_EXECUTIONS    from   GV$SQL_WORKAR
EA_HISTOGRAM    where  INST_ID = USERENV('Instance')


                                                                                                                                      Page          49

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
CURSOR                                1          0          0          0 NO


SELECT S.OID SOID, S


                                                                                                                                      Page          50

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
NOT LOADED                            0          0          0          0 NO


BEGIN DBMS_FEATURE_RAC(:feature_boolean, :aux_cnt, :feature_info);  END;


                                                                                                                                      Page          51

OWNER
----------------------------------------------------------------
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
TYPE                              LOADS EXECUTIONS      LOCKS       PINS KEP
---------------------------- ---------- ---------- ---------- ---------- ---
CURSOR                                1          0          0          0 YES





                                                                                                                                      Page          52

49 rows selected.

SQL>
SQL>

 








Related examples in the same category