現在ライブラリキャッシュに残っている実行プラン情報を確認できます。ある特定（SQL_ID＋CHILD_NO）の実行プランを確認するならDBMS_XPLAN.DISPLAY_CURSOR()を利用するので通常この動的パフォーマンスビューを参照する事はほとんどないのですが、個人的には次のような事を目的に参照する事があります。
例えば「夜間バッチでアクセスのあったテーブルを知りたい」といった要望があった場合、V$SQL_PLAN（RAC構成の場合はGV$SQL_PLANですね）を定期的にselectして調べた事があります。「更新のあったテーブルを調べる」のであればDBA_TAB_MODIFICATIONSを参照すればなんとかなるのですが、SELECTされたテーブルは抽出する事ができません。その点V$SQL_PLANは実行プランに利用された（アクセスのあった）テーブルは全て取得する事ができます。但しライブラリキャッシュからエージアウトしてしまうと取りこぼしとなるのでこの点は注意が必要です。参考までにですが当方は15分インターバルでSELECTしましたが「おおよそ漏れなく取得できた」ように思います。
SYNONYMやVIEWが含まれるSQLは実テーブル名にて表示されます
- SYNONYMで記載したSQLの場合V$SQL_PLANには実テーブル名で表示されます。
- VIEWを記載したSQLはVIEQを構成するSQLに使われている実テーブルが表示されます。
-- SCOTT ユーザにて以下実行
select * from V_ADDRESS;
-- SQL_IDは'b99y1qtkcwbcz'でした。
select sql_text,sql_id,child_number from v$sql where sql_id ='b99y1qtkcwbcz';
SQL_TEXT SQL_ID CHILD_NUMBER
___________________________ ________________ _______________
select * from V_ADDRESS b99y1qtkcwbcz 0
SQL>
-- V_ADDRESS は以下のようなVIEWです。
--
CREATE OR REPLACE VIEW SCOTT.V_ADDRESS
(RNAME, PREFID, NAME, NAME_R, CITY_CODE, CITY_NAME, OAZA_TOWN_CHOME_CODE
,OAZA_TOWN_CHOME_NAME, LATITUDE, LONGITUDE)
AS
select
r.NAME RNAME,p.PREFID,p.NAME,p.NAME_R,a.CITY_CODE,a.CITY_NAME
,a.OAZA_TOWN_CHOME_CODE,a.OAZA_TOWN_CHOME_NAME
,a.LATITUDE,a.LONGITUDE
from MST_ADDRESS a
inner join OY_PREFECTURE p on to_number(a.PREFECTURES_CODE) = p.PREFID
inner join OY_REGION r on p.REGIN_ID = r.REGION_ID
;
-- かつVIEWで使われている以下テーブルはSYNONYM名です
CREATE OR REPLACE SYNONYM SCOTT.MST_ADDRESS FOR SOONI.MST_ADDRESS;
CREATE OR REPLACE SYNONYM SCOTT.OY_PREFECTURE FOR SOONI.OY_PREFECTURE;
CREATE OR REPLACE SYNONYM SCOTT.OY_REGION FOR SOONI.OY_REGION;
上記のような時V$SQL_PLANを参照すると以下の通りです。VIEWもSYNONYMも展開された状況で確認する事ができます。
SQL> select
2 TIMESTAMP
3 ,ID
4 ,OPERATION
5 ,OBJECT_OWNER
6 ,OBJECT_NAME
7 ,OBJECT_TYPE
8 from v$sql_plan p where p.SQL_ID='b99y1qtkcwbcz' and p.CHILD_NUMBER = 0
9 order by id;
TIMESTAMP ID OPERATION OBJECT_OWNER OBJECT_NAME OBJECT_TYPE
____________ _____ ___________________ _______________ ________________ _________________
22-05-02 0 SELECT STATEMENT
22-05-02 1 HASH JOIN
22-05-02 2 MERGE JOIN
22-05-02 3 TABLE ACCESS SOONI OY_REGION TABLE
22-05-02 4 INDEX SOONI PK_REGION INDEX (UNIQUE)
22-05-02 5 SORT
22-05-02 6 TABLE ACCESS SOONI OY_PREFECTURE TABLE
22-05-02 7 TABLE ACCESS SOONI MST_ADDRESS TABLE
8行が選択されました。
SQL>
V$SQLと結合してある特定ユーザがアクセスしたテーブルを抽出する
例えば以下のようなSQLを定期的（15分程度）に実行した結果を保持しておくとアクセスしたテーブル、インデックスを調べる事ができます。カバリングインデックスの場合対象テーブルが出てきませんので、必要に応じてDBA_INDEXES等と結合してアクセステーブルを導出してください。（DBA_INDEXやDBA_TABLESと都度結合すると負荷も上がってくるので、いったん以下SQLで導出されたデータに対し結合するとよいとおもいます）
select to_number(to_char(systimestamp,'YYYYMMDDHH24MISSff6')) SCANNO ,p.SQL_ID ,p.CHILD_NUMBER ,TIMESTAMP ,ID ,OPERATION ,OBJECT_OWNER ,OBJECT_NAME ,OBJECT_TYPE from v$sql_plan p inner join v$sql s on p.SQL_ID = s.SQL_ID and p.CHILD_NUMBER = s.CHILD_NUMBER where 1=1 and s.PARSING_SCHEMA_NAME ='SCOTT' and p.OBJECT_TYPE is not null order by p.SQL_ID,p.CHILD_NUMBER,id ;
