To take trace or dump of library cache, enter following command on sqlplus.
alter session set events 'immediate trace name library_cache level 10';
I have create a table name pk of one column (id) and inserted with some values as given below..
create table pk(id);
insert into pk values (2121);
insert into pk values(2121);
/
/
/
12288 rows created.
First flush the shared pool for any unwanted or unrelated used space on library cache
SQL> alter system flush shared_pool;
System altered.
Then, clear the buffer cache for any preloaded dirty buffer in buffer cache
SQL> alter system flush buffer_cache;
System altered.
now set autotrace on to view optimizer execution and sql execution statistics;
set autotrace on;
here view on library cache dump after performing query on table pk ( select * from pk)
select * from pk;
2121
2121
2121
2121
2121
2121
ID
----------
2121
12288 rows selected.
SP2-0612: Error generating AUTOTRACE report
Execution Plan
----------------------------------------------------------
Plan hash value: 888312997
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12288 | 156K| 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| PK | 12288 | 156K| 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
to get trace location, view location (value) from v$diag_info
select * from v$diag_info;
SQL> select * from v$diag_info;
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Enabled
TRUE
1 ADR Base
c:\app\vishwakarma
1 ADR Home
c:\app\vishwakarma\diag\rdbms\orcl\orcl
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Trace
c:\app\vishwakarma\diag\rdbms\orcl\orcl\trace
1 Diag Alert
c:\app\vishwakarma\diag\rdbms\orcl\orcl\alert
1 Diag Incident
c:\app\vishwakarma\diag\rdbms\orcl\orcl\incident
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Diag Cdump
c:\app\vishwakarma\diag\rdbms\orcl\orcl\cdump
1 Health Monitor
c:\app\vishwakarma\diag\rdbms\orcl\orcl\hm
1 Default Trace File
c:\app\vishwakarma\diag\rdbms\orcl\orcl\trace\orcl_ora_7556.trc
INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
1 Active Problem Count
0
1 Active Incident Count
0
11 rows selected.
check trace of library cache in trace file which has extention of .trc
you can view this using text editor or notepad and search your query.
========================================================================
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1
CPU : 2 - type 8664, 2 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:1521M/4085M, Ph+PgF:4148M/8169M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Windows thread id: 4928, image: ORACLE.EXE (SHAD)
*** 2017-03-18 23:44:59.808
*** SESSION ID:(9.3) 2017-03-18 23:44:59.808
*** CLIENT ID:() 2017-03-18 23:44:59.808
*** SERVICE NAME:(SYS$USERS) 2017-03-18 23:44:59.808
*** MODULE NAME:(sqlplus.exe) 2017-03-18 23:44:59.808
*** ACTION NAME:() 2017-03-18 23:44:59.808
kwqmnich: current time:: 18: 14: 59: 0
kwqmnich: instance no 0 repartition flag 1
kwqmnich: initialized job cache structure
*** 2017-03-18 23:45:00.544
kwqinfy: Call kwqrNondurSubInstTsk
*** 2017-03-18 23:46:50.366
Library Cache Dump
SGA:63def5f0 Flags=2c7
DebugContext: DebugNameSpace=0 DebugType=0 DebugLevel=0x0
LIBRARY CACHE HASH TABLE: size=131072 count=6604
Buckets with more than 20 objects:
NONE
Hash Chain Size Number of Buckets
--------------- -----------------
0 124631
1 6282
2 155
3 4
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
========================================================================
There many query listed with the bucket, so search for your query ..................
=======================================================================
Bucket: #=27456 Mutex=63efc898(0, 6, 0, 6)
LibraryHandle: Address=5becc958 Hash=5dc66b40 LockMode=0 PinMode=0 LoadLockMode=0 Status=VALD
ObjectName: Name=EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select * from pk
FullHashValue=7b8f5775eab4d2d47c16bf605dc66b40 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=1573284672 OwnerIdn=0
Statistics: InvalidationCount=0 ExecutionCount=1 LoadCount=2 ActiveLocks=0 TotalLockCount=1 TotalPinCount=1
Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 KeepHandle=1 BucketInUse=0 HandleInUse=0
Concurrency: DependencyMutex=5becca08(0, 0, 0, 0) Mutex=5becca80(9, 20, 0, 6)
Flags=RON/PIN/TIM/PN0/DBN/[10012841]
WaitersLists:
Lock=5becc9e8[5becc9e8,5becc9e8]
Pin=5becc9f8[5becc9c8,5becc9c8]
Timestamp: Current=03-18-2017 23:48:12
LibraryObject: Address=5e354220 HeapMask=0000-0001-0001 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]
DataBlocks:
Block: #='0' name=PCUR^5dc66b40 pins=0 Change=NONE
Heap=5becc8a0 Pointer=5e354308 Extent=5e3541a0 Flags=I/-/P/A/-/-
FreedLocation=0 Alloc=1.515625 Size=4.000000 LoadTime=7815789
ChildTable: size='16'
Child: id='0' Table=5e3550d0 Reference=5e354b70 Handle=5becc758
NamespaceDump:
Parent Cursor: sql_id=7s5pzc1fwcuu0 parent=000007FF5E354308 maxchild=1 plk=n ppn=n
you can get more detail in v$sql related to hash values and the sql statement that is present in the library cache.