ITPub博客

首页 > 数据库 > Oracle > [20210926]使用dbms_workload_repository.add_colored_sql.txt

[20210926]使用dbms_workload_repository.add_colored_sql.txt

原创 Oracle 作者:lfree 时间:2021-09-28 09:40:06 0 删除 编辑

[20210926]使用dbms_workload_repository.add_colored_sql.txt

--//生产系统一条ql语句感觉有一些怪异,awr收到的信息以及查询DBA_HIST_SQLSTAT视图都很奇怪,使用
--//dbms_workload_repository.add_colored_sql标注观察看看,做一个记录:

1.环境:
SYS@127.0.0.1:xxxx/zzzz> @ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//sql_id = 7ksrtc8rzpawc
SELECT a.object_name, c.sid,
       CASE TO_CHAR(b.locked_mode)
       WHEN '0' THEN 'NONE'
       WHEN '1' THEN 'NULL'
       WHEN '2' THEN 'ROW-S (RS)'
       WHEN '3' THEN 'ROW-X (RX)'
       WHEN '4' THEN 'SHARE (S)'
       WHEN '5' THEN 'S/ROW-X (SRX)'
       WHEN '6' THEN 'Exclusive (X)'
       ELSE TO_CHAR(b.locked_mode)
       END locked_mode, c.SERIAL#, b.process, c.program, c.SQL_ADDRESS
  FROM all_objects a, sys.gv_$locked_object b, sys.GV_$SESSION C
 WHERE a.object_id = b.object_id
   AND b.process   = c.process
 ORDER BY a.object_name

--//后记:仔细查看才发现华为的研发写错了,应该写成如下:
SELECT a.object_name, c.sid,
       CASE TO_CHAR(b.locked_mode)
       WHEN '0' THEN 'NONE'
       WHEN '1' THEN 'NULL'
       WHEN '2' THEN 'ROW-S (RS)'
       WHEN '3' THEN 'ROW-X (RX)'
       WHEN '4' THEN 'SHARE (S)'
       WHEN '5' THEN 'S/ROW-X (SRX)'
       WHEN '6' THEN 'Exclusive (X)'
       ELSE TO_CHAR(b.locked_mode)
       END locked_mode, c.SERIAL#, b.process, c.program, c.SQL_ADDRESS
  FROM all_objects a, sys.gv_$locked_object b, sys.GV_$SESSION C
 WHERE a.object_id = b.object_id
   AND b.process   = c.process
   AND b.INST_ID = c.INST_ID
   ~~~~~~~~~~~~~~~~~~~~~~~~~
   and b.SESSION_ID =c.SID
   ~~~~~~~~~~~~~~~~~~~~~~~~~~
 ORDER BY a.object_name

--//b.process   = c.process 连接条件加上与不加上应该不影响查询结果.
--//华为研发真应该给自己打脸,太丢人了,估计拿着单机版本的数据库做的测试.无语!!想当然以为process字段唯一的.
--//在我看来给客户发现研发的错误是非常丢脸的事情.
SELECT COUNT (*)
  FROM (SELECT process
          FROM gv$session
         WHERE INST_ID = 1
        INTERSECT
        SELECT process
          FROM gv$session
         WHERE INST_ID = 2)

  COUNT(*)
----------
      1741

--//虽然windwos的process看起来类似1084:1080这样,但是如果反查:
select * from gv$session where process='1084:1080';
--//结果我不贴出了.
--//可以发现我们的程序非常变态,1个程序要打开4个连接,其中有1个连接到另外的实例.

2.测试:
SYS@127.0.0.1:xxxx/zzzz> select sysdate from dual ;
SYSDATE
-------------------
2021-09-26 08:49:06

SYS@127.0.0.1:xxxx/zzzz> exec dbms_workload_repository.add_colored_sql('7ksrtc8rzpawc');
PL/SQL procedure successfully completed.

SYS@127.0.0.1:xxxx/zzzz> select * from DBA_HIST_COLORED_SQL ;
      DBID SQL_ID        CREATE_TIME
---------- ------------- -------------------
2417323702 7ksrtc8rzpawc 2021-09-26 08:49:14

SYS@127.0.0.1:xxxx/zzzz> select * from sys.wrm$_colored_sql;
      DBID SQL_ID            OWNER CREATE_TIME
---------- ------------- --------- -------------------
2417323702 7ksrtc8rzpawc         1 2021-09-26 08:49:14

2.等一段时间看看awr报表:
--//我看了9-10点的awr报表,仅仅出现在SQL ordered by Sharable Memory,SQL ordered by Version Count
SQL ordered by Sharable Memory
Only Statements with Sharable Memory greater than 1048576 are displayed
Sharable Mem (b) Executions  % Total SQL Id        SQL Module SQL Text
194,528,928                    1.39  7ksrtc8rzpawc            SELECT a.object_name, c.sid, C...
104,135,664                    0.74  5r14h528vkacs            select to_char(min(start_time

--//明天继续观察,观察时间2021/09/28 09/12/30
$ rlsql -s -l  sys/Password_106@127.0.0.1:xxxx/zzzz as sysdba  <<< "@ sqlh 7ksrtc8rzpawc 60550  " | awk '$10>=0 || $10='0'{ print $0}'
   SNAP_ID INSTANCE_NUMBER PLAN_HASH_VALUE BEGIN_INTERVAL_TIME      END_INTERVAL_TIME        ELAPSED_TIME_DELTA CPU_TIME_DELTA EXECUTIONS_DELTA EXECUTIONS_TOTAL ROWS_PROCESSED_DELTA BUFFER_GETS_DELTA LOADED_VERSIONS
     60550               1      3030673966 2021-09-24 13:00:31.390  2021-09-24 14:00:39.168             2171154        2163667                0                0                    0               202              35
     60550               1      4164392588 2021-09-24 13:00:31.390  2021-09-24 14:00:39.168             8964592        8696676               11            12145                    0           2596341              37
     60551               1      3030673966 2021-09-24 14:00:39.168  2021-09-24 15:00:47.185             2537991        2519622                0                0                    0               226              47
     60551               1      4164392588 2021-09-24 14:00:39.168  2021-09-24 15:00:47.185             4595819        4449324               12            12157                    0            480638              47
     60552               1      3030673966 2021-09-24 15:00:47.185  2021-09-24 16:00:54.814             2663995        2575604                0                0                    0               281              57
     60552               1      4164392588 2021-09-24 15:00:47.185  2021-09-24 16:00:54.814            18936715       17793290               12            12169                    0           3859239              59
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     60553               1      3030673966 2021-09-24 16:00:54.814  2021-09-24 17:00:02.131             2930805        2794577                0                0                    0               289              67
     60553               1      4164392588 2021-09-24 16:00:54.814  2021-09-24 17:00:02.131            17185326       15880588               11            12180                    0           3006125              69
     60554               1      3030673966 2021-09-24 17:00:02.131  2021-09-24 18:00:08.949             2845827        2772578                0                0                    0               310              72
     60554               1      4164392588 2021-09-24 17:00:02.131  2021-09-24 18:00:08.949            17543958       16821439               12            12192                    0           3788169              74
     60555               1      3030673966 2021-09-24 18:00:08.949  2021-09-24 19:00:16.374             2472227        2459627                0                0                    0               306              80
     60555               1      4164392588 2021-09-24 18:00:08.949  2021-09-24 19:00:16.374            11726661       11607235               12            12204                    0           3911169              80
     60556               1      3030673966 2021-09-24 19:00:16.374  2021-09-24 20:00:23.301             2107047        2098680                0                0                    0               263              91
     60556               1      4164392588 2021-09-24 19:00:16.374  2021-09-24 20:00:23.301             7760131        7699825               13            12217                    0           1743354              92
     60602               1      3030673966 2021-09-26 17:00:40.432  2021-09-26 18:00:47.512             2933909        2744578                0                0                    0               291              74
     60602               1      4164392588 2021-09-26 17:00:40.432  2021-09-26 18:00:47.512            15417212       14824749               12            12761                    0           2436171              76
     60603               1      3030673966 2021-09-26 18:00:47.512  2021-09-26 19:00:54.580             2476196        2464624                0                0                    0               241              51
     60603               1      4164392588 2021-09-26 18:00:47.512  2021-09-26 19:00:54.580             6304023        6193061               12            12773                    0            836437              52
     60604               1      3030673966 2021-09-26 19:00:54.580  2021-09-26 20:00:01.259             2329792        2296649                0                0                    0               223              38
     60604               1      4164392588 2021-09-26 19:00:54.580  2021-09-26 20:00:01.259             4467477        4316343               12            12785                    0            480755              39
     60605               1      3030673966 2021-09-26 20:00:01.259  2021-09-26 21:00:08.801             2292893        2283651                0                0                    0               250              46
     60605               1      4164392588 2021-09-26 20:00:01.259  2021-09-26 21:00:08.801             8601945        8548701               12            12797                    0           2383295              47
     60606               1      3030673966 2021-09-26 21:00:08.801  2021-09-26 22:00:16.185             2315537        2311647                0                0                    0               238              58
     60606               1      4164392588 2021-09-26 21:00:08.801  2021-09-26 22:00:16.185             6663848        6607996               12            12809                    0           1547417              59
     60607               1      3030673966 2021-09-26 22:00:16.185  2021-09-26 23:00:23.674             2277466        2272657                0                0                    0               237              69
     60607               1      4164392588 2021-09-26 22:00:16.185  2021-09-26 23:00:23.674             6983427        6936945               12            12821                    0           1760850              70
     60608               1      3030673966 2021-09-26 23:00:23.674  2021-09-27 00:00:30.047             2346573        2332640                0                0                    0               222              81
     60608               1      4164392588 2021-09-26 23:00:23.674  2021-09-27 00:00:30.047             4585585        4536311               12            12833                    0            693810              82
     60609               1      3030673966 2021-09-27 00:00:30.047  2021-09-27 01:00:36.974             2326362        2311648                0                0                    0               228              91
     60609               1      4164392588 2021-09-27 00:00:30.047  2021-09-27 01:00:36.974             5337363        5278198               12            12845                    0            836276              93

--//18936715/10^6/12  = 1.578秒,有点慢.不是太慢.总共16秒不到.
--//查询awr snap_id=60551,6055报表,也仅仅出现在SQL ordered by Sharable Memory部分.

SQL ordered by Sharable Memory

    Only Statements with Sharable Memory greater than 1048576 are displayed

Sharable Mem (b) Executions % Total  SQL Id         SQL Module       SQL Text
33,993,403       12         0.24     7ksrtc8rzpawc  JDBC Thin Client SELECT a.object_name, c.sid, C...
31,845,595       12         0.22     7ksrtc8rzpawc  JDBC Thin Client SELECT a.object_name, c.sid, C...
18,671,197       24         0.13     5r14h528vkacs  JDBC Thin Client select to_char(min(start_time)...
17,779,445       24         0.12     5r14h528vkacs  JDBC Thin Client select to_char(min(start_time)...

--//sql_id=5r14h528vkacs也在SQL ordered by Elapsed Time部分.它的执行时间有点长.

SQL ordered by Elapsed Time

    Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
    % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
    %Total - Elapsed Time as a percentage of Total DB time
    %CPU - CPU Time as a percentage of Elapsed Time
    %IO - User I/O Time as a percentage of Elapsed Time
    Captured SQL account for 66.7% of Total DB Time (s): 46,970
    Captured PL/SQL account for 6.1% of Total DB Time (s): 46,970

Elapsed Time (s)  Executions  Elapsed Time per Exec (s)  %Total  %CPU   %IO    SQL Id        SQL Module        SQL Text
...
1,218.97          24          50.79                      2.60    36.48  63.65  5r14h528vkacs JDBC Thin Client  select to_char(min(start_time)...
...

--//我以为标注后会出现在SQL ordered by Elapsed Time部分,实际上使用dbms_workload_repository.add_colored_sql仅仅保证出现在awr报表(我估计).

3.收尾:

SYS@127.0.0.1:xxxx/zzzz> exec dbms_workload_repository.remove_colored_sql('7ksrtc8rzpawc');
PL/SQL procedure successfully completed.

SYS@127.0.0.1:xxxx/zzzz> select * from sys.wrm$_colored_sql;
no rows selected


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

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

注册时间:2008-01-03

  • 博文量
    3031
  • 访问量
    6775268