Mega Code Archive
Display the text of a cached query in the shared pool, then the execution plan
<![CDATA[
SQL>
SQL> SELECT sql_text
2 FROM v$sql
3 WHERE sql_text LIKE '%&1%'
4 AND sql_text NOT LIKE '%v$sql%'
5 and rownum < 20
6 /
Enter value for 1:
old 3: WHERE sql_text LIKE '%&1%'
new 3: WHERE sql_text LIKE '%%'
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select id, name, block_size, advice_status, size_for_estimate, size_factor, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads,
estd_physical_read_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')
delete from user_history$ where user# = :1
DECLARE STATUS VARCHAR2(20); DSTART DATE; DSTOP DATE; PVALUE NUMBER; PNAME VARCHAR2(30); BEGIN PNAME := 'mbrc'; DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pva
lue, stattab => ' mystats', statid => 'DAY', statown => 'rntsoft'); DBMS_OUTPUT.PUT_LINE('average multiblock readcount: '||pvalue); END;
SELECT NULL FROM DR$INDEX WHERE IDX_STATUS = :B2 AND IDX_OWNER# = -USERENV('SESSIONID') AND IDX_NAME = :B1
update sys.job$ set this_date=:1 where job=:2
update sys.job$ set this_date=:1 where job=:2
select ts# from ts$ where name=:1
SELECT topology FROM SDO_TOPO_METADATA_TABLE a, TABLE(a.Topo_Geometry_Layers) b WHERE b.owner = 'RNTSOFT' AND b.table_name = 'ORD'
SELECT DBMS_METADATA.GET_DDL('TABLE', 'emp', 'STUDENT1') FROM DUAL
delete from idl_sb4$ where obj#=:1 and part=:2 and version<>:3
update histgrm$ set col#=col#-:1 where (obj#=:2 or obj#=:3) and col#>:4
delete from viewtrcol$ where obj#=:1
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
begin "SYS"."DBMS_REPCAT_UTL"."DROP_USER_REPSCHEMA"(:myuser); end;
begin :1 := ctxsys.drvxmd.NextIndexObject(:2,:3,:4);end;
begin :1 := ctxsys.drvxmd.NextIndexObject(:2,:3,:4);end;
DELETE FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_INFO'
SELECT IXO_CLA_ID, IXO_OBJ_ID, IXO_ACNT FROM DR$INDEX_OBJECT WHERE IXO_IDX_ID = :B1 ORDER BY IXO_CLA_ID
delete from defrole$ where user#=:1 and role#=:2 and not exists (select null from sysauth$ where grantee#=1 and privilege#=:2)
begin "SYS"."DBMS_REGISTRY_SYS"."DROP_USER"(:myuser); end;
19 rows selected.
SQL> col id format 99
SQL> col operation format a20
SQL> col options format a20
SQL> col object_name format a30
SQL> col cost format 9999
SQL>
SQL> SELECT id, operation, options, object_name, cost
2 FROM v$sql_plan VP, v$sql VS
3 WHERE VP.address = VS.address
4 AND VP.hash_value = VS.hash_value
5 AND sql_text LIKE '%&1%'
6 AND sql_text NOT LIKE '%v$sql%'
7 and rownum < 20
8 ORDER BY id
9 /
Enter value for 1:
old 5: AND sql_text LIKE '%&1%'
new 5: AND sql_text LIKE '%%'
ID OPERATION OPTIONS OBJECT_NAME COST
--- -------------------- -------------------- ------------------------------ -----
0 DELETE STATEMENT 2
0 SELECT STATEMENT 3
0 DELETE STATEMENT 1
1 DELETE FGACOL$
1 DELETE OBJAUTH$
1 TABLE ACCESS BY INDEX ROWID OBJAUTH$ 3
2 INDEX RANGE SCAN I_FGACOL
2 INDEX RANGE SCAN I_OBJAUTH1 2
2 INDEX RANGE SCAN I_OBJAUTH1 2
3 NESTED LOOPS 53
6 TABLE ACCESS BY INDEX ROWID USER$ 1
8 TABLE ACCESS BY INDEX ROWID OBJ$ 3
9 INDEX RANGE SCAN I_OBJ2 2
10 TABLE ACCESS BY INDEX ROWID NTAB$ 12
11 INDEX RANGE SCAN I_NTAB3
12 TABLE ACCESS CLUSTER COL$ 1
13 NESTED LOOPS 18
ID OPERATION OPTIONS OBJECT_NAME COST
--- -------------------- -------------------- ------------------------------ -----
14 INDEX RANGE SCAN I_OBJAUTH1 2
15 FIXED TABLE FULL X$KZSRO 16
19 rows selected.
SQL>
]]>