[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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1078633/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1078633/