Oracle PL/SQL/System Tables Views/db object cache
Query v$db_object_cache table
<source lang="sql">
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 "sqle"."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 "sqle"."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 sqle
Page 31
OWNER
NAME
TYPE LOADS EXECUTIONS LOCKS PINS KEP
---------- ---------- ---------- ---------- ---
DYNAMICQUERY PROCEDURE 1 0 0 0 NO sqle
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>
</source>
