Display the text of a cached query in the shared pool, then the execution plan. : V_sql « System Tables Views « Oracle PL / SQL






Display the text of a cached query in the shared pool, then the execution plan.

    
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 => 'java2s');  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 = 'JAVA2S'  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>

   
    
    
    
  








Related examples in the same category

1.Query v$sql for sql text