[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
