[20140209]add_colored_sql.txt

[20140209]dbms_workload_repository.add_colored_sql.txt

第一次看到這個過程，以為是給某一些sql語句在awr報表中顯示時加入色彩，自己看文件才知道
-- add_colored_sql()
--   Routine to add a colored SQL ID. If an SQL ID is colored, it will
--   always be captured in every snapshot, independent of its level
--   of activities (i.e. does not have to be a TOP SQL). Capturiing
--   will occur if the SQL is found in the cursor cache at
--   snapshot time.
--
--   To uncolor the SQL, call remove_colored_sql().
--
-- Input arguments:
--   dbid                     - optional dbid, default to Local DBID
--   sql_id                   - the 13-chararcter external SQL ID
--
-- Returns:
--   none.
--

--實際上把一些sql語句抓取放入awr報表中，做一個簡單的測試看看。

SCOTT@test01p> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

exec dbms_workload_repository.create_snapshot();
select * from dept where deptno=10;
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
------------------------------------------
SQL_ID  4xamnunv51w9j, child number 0
-------------------------------------
select * from dept where deptno=10
Plan hash value: 2852011669
--知道sql_id='4xamnunv51w9j'

exec dbms_workload_repository.add_colored_sql('4xamnunv51w9j');

select * from dept where deptno=10;
exec dbms_workload_repository.create_snapshot();

現在看看awr報表，發現語句記錄在報表中。
Elapsed Time (s) Executions  Elapsed Time per Exec (s)  %Total %CPU %IO SQL Id SQL Module PDB Name SQL Text
.........
0.06 2 0.03 1.33 27.69 72.23 4xamnunv51w9j SQL*Plus  TEST01P  select * from dept where deptn...
.....

看看這些資訊記錄在那些檢視與表中：

SCOTT@test01p> select * from DBA_HIST_COLORED_SQL ;
      DBID SQL_ID        CREATE_TIME             CON_ID
---------- ------------- ------------------- ----------
2119378787 4xamnunv51w9j 2014-02-09 21:07:31          1

SCOTT@test01p> column text_vc format a80
SCOTT@test01p> select owner,view_name,text_vc from dba_views where view_name='DBA_HIST_COLORED_SQL';
OWNER  VIEW_NAME            TEXT_VC
------ -------------------- --------------------------------------------------------------------------------
SYS    DBA_HIST_COLORED_SQL select dbid, sql_id, create_time, con_dbid_to_id(dbid) con_id
                              from wrm$_colored_sql where owner = 1

--可以確定資訊儲存在wrm$_colored_sql表中。

SCOTT@test01p> select * from sys.wrm$_colored_sql;
no rows selected

--沒有，我使用的是12c，查詢在pdb的sys使用者。進入root的sys使用者。

SYS@test> select * from sys.wrm$_colored_sql;
      DBID SQL_ID                  OWNER CREATE_TIME
---------- ------------- --------------- -------------------
2119378787 4xamnunv51w9j               1 2014-02-09 21:07:31

--收尾：
SYS@test> exec dbms_workload_repository.remove_colored_sql('4xamnunv51w9j');
PL/SQL procedure successfully completed.

SYS@test> select * from sys.wrm$_colored_sql;
no rows selected