I use the following sql to display all the sql in memory with "ITEM_F" in it. You can change the "ITEM_F" to whatever you want. Then I display the full sql with another sql called HASH_VALUE.sql
EXECUTIONS HASH_VALUE GETS_PER_EXEC SEC_PER_EXEC SQL_TEXT
---------- ---------- -------------- ------------ ---------------------
1 3605439262 507993.00 4.24581775 BEGIN IF '%CAPA%'
1 671043237 517057.00 26.5039965 BEGIN IF '%DEV%' =
1 336821122 527328.50 1422.86585 /* MV_REFRESH (INS) *
1 484748009 539104.00 22.037663 BEGIN IF '%MCO%' =
1 3169765572 564994.50 1274.37927 INSERT /*+ BYPASS_REC
1 2692239685 573682.50 172.683195 BEGIN DBMS_MVIEW.REFR
1 2023740499 577038.00 3.74569625 INSERT INTO MDS.MATT_
1 2724147626 655448.00 58.4461736 INSERT /*+ BYPASS_REC
1 3939050362 730432.00 23.7762682 INSERT INTO MDS.MATT_
1 3275016008 792977.50 142.068046 INSERT /*+ BYPASS_REC
1 60973157 915308.50 1074.8388 BEGIN dbms_mview.refr
1 1262896430 1044207.50 145.147301 BEGIN DBMS_MVIEW.REFR
1 481615044 1185227.50 197.121223 INSERT /*+ BYPASS_REC
1 832862631 1259484.50 1483.57486 /* MV_REFRESH (INS) *
1 413596320 1327330.00 213.819306 BEGIN dbms_mview.refr
1 1471539484 1340705.50 1091.36564 INSERT INTO MATT_AML_
1 846352950 1929911.50 1243.53836 /* MV_REFRESH (INS) *
18 3947679100 316931.68 3.47618447 SELECT distinct TOT
18 1224535723 6558946.68 29.210453 SELECT distinct CHA
20 1746064953 59020824.67 1370.51753 INSERT /*+ BYPASS_REC
EQADMRT > list
1 select sql_id,address,parse_calls,buffer_gets,executions,hash_value,buffer_gets/(executions+1) gets_per_exec,
2 elapsed_time/1000000/(executions+.000001) sec_per_exec,sql_text
3 from gv$sqlarea
4 where ( upper(sql_text) like upper('%&1%') )
5 -- and buffer_gets > 0
6* order by buffer_gets
EQADMRT > @hash_value 1471539484
HASH_VALUE=1471539484
INSERT INTO MATT_AML_STATUS_3 ( SELECT DISTINCT F.PJ_PK, F.SCI_I
TEM_SRC_ID, F.RS_SUPPLIER_ID,D.PC_MCO_ID,H.ENTRY_VALUE, G.ITEM_M
FR_PART_ID,I.PC_MCO_NO,AGILE_FLEX.TEXT FROM PCM_ANALYSIS_F F, OD
M.ODM_BUYER_LINE_CARD B , ODM_MANUFACTURER_OBJ_D C, PC_MCO_ITEM_
F D, ITEM_MFR_PART_F G,ODM_AML_PREF_STATUS_D H , PC_MCO_F I,ODM.
ODM_AGILE_FLEX_STG AGILE_FLEX WHERE F.RS_SUPPLIER_ID = B.BLC_SUP
PLIER_ID(+) AND D.PC_MCO_ID = I.PC_MCO_ID AND D.PC_MCO_WORKFLOW_
ID=I.PC_MCO_WORKFLOW_ID AND B.BLC_OBJECT_ID = C.ENTRY_ID (+) AND
F.SCI_ITEM_SRC_ID = D.ITEM_ID (+) AND C.ENTRY_ID = G.MANUFACTUR
ER_ID AND D.PC_MCO_WORKFLOW_ID IN ( SELECT PC_MCO_WORKFLOW_ID FR
OM PC_MCO_WORKFLOW_D G WHERE WORKFLOW='PCM OEM MCO V1.1' AND WOR
KFLOW_STATE NOT IN ('Canceled') ) AND D.CREATED_TIME_ID = (SELEC
T MAX(E.CREATED_TIME_ID) FROM PC_MCO_ITEM_F E WHERE E.PC_MCO_ID=
D.PC_MCO_ID) AND G.CHANGE_ID = D.PC_MCO_ID AND G.ITEM_MFR_PART_P
REFER_STATUS_ID = H.ENTRY_ID AND F.SCI_ITEM_SRC_ID=G.ITEM_ID(+)
AND G.ITEM_ID = AGILE_FLEX.ID(+) AND G.MANUBY_ID =AGILE_FLEX.OBJ
_ID(+))
EQADMRT > list
1 select
2 --inst_id,sql_id,hash_value,
3 piece, sql_text
4 from gv$sqltext b
5 where b.hash_value=&1
6 -- and inst_id=1
7* order by inst_id,hash_value,b.piece
