REM
/*
 * SqlPlanFTS.Sql (c) Tim Onions 2007
 * Please check this script works as expected on a test system before using on a production database.
 * The author can accept no liability or responsibility for any ill-effect it may have.
 * You are free to use this script on one condition - use it to improve the performance of your database!
 */
COLUMN stmt PRINT format A60 heading "FTS Statement" WORD
COLUMN o PRINT FORMAT A32 HEADING "Table name"
COLUMN c PRINT FORMAT A16 HEADING "SQL ID [child]"
SET ECHO off VER off LINES 128

ACCEPT sql PROMPT "Enter SQL statement filter if required (NULL=all FTS SQL): "
TTitle "Tables with FTS operations - as recorded in V$SQL_PLAN"
SELECT DISTINCT object_name o
FROM v$sql_plan ses
    ,v$sql sql
WHERE SES.ADDRESS= SQL.ADDRESS
AND SES.HASH_VALUE=SQL.HASH_VALUE
AND ses.child_number=sql.child_number
AND UPPER(sql.sql_text) LIKE UPPER(NVL('&&sql','%'))
AND operation='TABLE ACCESS' 
AND options='FULL'
AND object_name NOT IN ('PLAN_TABLE','DUAL','SQLPLUS_PRODUCT_PROFILE') --ignore stuff that is probably not an issue
AND object_name NOT LIKE '%$' --attempt to ignore dictionary objects
/

ACCEPT det PROMPT "Display SQL? (Y/N):"
TTitle "SQL with one or more FTS operations - as recorded in V$SQL_PLAN"
SELECT sql.sql_fulltext stmt --NB Prior to 10g use sql_text column
     ,object_name o
     ,ses.sql_id||'['||ses.child_number||']' c
FROM v$sql_plan ses
    ,v$sql sql
WHERE SES.ADDRESS=SQL.ADDRESS
AND SES.HASH_VALUE=SQL.HASH_VALUE
AND ses.child_number=sql.child_number
AND operation='TABLE ACCESS' 
AND options='FULL'
AND object_name NOT IN ('PLAN_TABLE','DUAL','SQLPLUS_PRODUCT_PROFILE') --ignore stuff that is probably not an issue
AND object_name NOT LIKE '%$' --attempt to ignore dictionary objects
AND UPPER(sql.sql_text) LIKE UPPER(NVL('&&sql','%'))
AND UPPER('&&det')='Y'
/
TTitle off
SET DOC off
/* SUMMARY - Locate tables and SQL still in the library cache where a full table scan access path was used
 *
 * DETAIL - The V$SQL_PLAN view is very useful indeed. Not only can you see the actual access path/explain plan a statement used
 * (as opposed to the possibly flawed access path when you use the EXPLAIN PLAN command) but you can make use of the information in other ways too.
 * This script does just that, it use the OBJECT_NAME column to identify SQL statements that use a particular table (index access can be located via the script SQLPlanIndex.Sql) and combining this with the
 * OPERATION column to show any SQL that used a full table scan.
 *
 * There are many other ways that this information can be used to good effect - identifying SQL that uses a particular access method (HASH JOIN, SORT MERGE, SORTING...);
 * finding out what SQL is hitting a given table (maybe to detect whether the table is actually used or to try and trap unauthorised access/DML); seeing if a
 * given index is actually used by the application or is redundant and so coul dbe removed to save space/DB work in maintaining it.
 *
 * With the V$SQL view you must not forget, however, that it only lists SQL that is still in the library cache. So if you are trying to identify whether and object is used
 * be sure to checked V$SQL regularly over the course of a working day/week/month. If the statement gets aged out before you look for it then clearly you will miss it and
 * make a false conclusion on the results you get. Tools such as STATSPACK and AWR capture V$SQL_PLAN information and so can also be used for this type of analysis - their data is
 * retained for a number of days (configurable) and the frequency of capturing information can be set to suit your own particular needs.
 *
 * Note that this script uses some Oracle 10g specific columns in the V$SQL and V$SQL_PLAN views - it can be adapted to work in Oracle9i by replacing SQL_ID with address/hash_value and SQL_FULLTEXT with SQL_TEXT.
 *
Sample output might look like:
SYS@RACDB> start sqlplanfts
Enter SQL statement filter if required (NULL=all FTS SQL): %LBAC$POL%

Sat May 12                                                                                                             page    1
                                     Tables with FTS operations - as recorded in V$SQL_PLAN

Table name
--------------------------------
LBAC$POL

1 row selected.

Display SQL? (Y/N):y

Sat May 12                                                                                                             page    1
                                SQL with one or more FTS operations - as recorded in V$SQL_PLAN

FTS Statement                                                Table name                       SQL ID [child]
------------------------------------------------------------ -------------------------------- ----------------
select pol#, db_labels, default_format from lbac$pol         LBAC$POL                         6r75f7k88qr4a[1]
SELECT POL#, PACKAGE FROM LBAC$POL WHERE BITAND(FLAGS,1)=1   LBAC$POL                         74jp9b3h4krhd[2]
ORDER BY PACKAGE
SELECT POL#, PACKAGE FROM LBAC$POL WHERE BITAND(FLAGS,1)=1   LBAC$POL                         74jp9b3h4krhd[1]
ORDER BY PACKAGE

3 rows selected.

 * (c) Tim Onions 2007
 * Script used at the database owners own risk.
 */
REM