ITPub博客

首页 > 数据库 > Oracle > [20140209]add_colored_sql.txt

[20140209]add_colored_sql.txt

原创 Oracle 作者:lfree 时间:2014-02-10 08:58:32 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6291305