使用导出ASH信息对Oracle进行问题分析

最新推荐文章于 2025-03-18 11:45:39 发布
最新推荐文章于 2025-03-18 11:45:39 发布
阅读量685 收藏 7
点赞数 18
文章标签： 程序人生
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/minebmw7/article/details/144600229
版权

前几天在大额交易时由于节点间私网流量过大，导致重复交易。因此需要通过分析某银行网银问题时间段备份的视图bnet.ash0930，来找出引发严重问题的原因。

1. Top sql优化

1.1 查询视图中发生gc等待事件的object_id

set line 200 pages 1000
col sample_time for a30
col sql_id for a30
col event for a30
col sql_opname for a30
select CURRENT_OBJ#,count(*)
from bnet.ash0929
where INSTANCE_NUMBER = 1
and wait_class<>'Idle'
and event like 'gc%'
group by CURRENT_OBJ#
order by count(*);

只截取了数量最多的
CURRENT_OBJ#   COUNT(*)
------------ ----------
       18888      10052
       18924      10071
       18922      12047
    28143904      13415
       18879      14438
       18878      15446
    28143898      17853
       18887      18042
    50192501      22330
    23764946      22409
       18831      23546
       27893      25877
       19240      35406
           0      37075
       18912      37184
    16784208      43557
    47820497      73711
       18894      79603  --问题表T_COR 
    50192500     101998 
    25514317     315976

1.2 通过OBJECT_ID查询对象名称

SQL> col OBJECT_NAME for a30
SQL> col OBJECT_TYPE for a30
SQL> col OWNER for a30
SQL> select OBJECT_NAME, OBJECT_TYPE, OWNER from dba_objects where OBJECT_ID = 18894;


OBJECT_NAME                    OBJECT_TYPE                    OWNER
------------------------------ ------------------------------ ------------------------------------------------------------
T_COR                      TABLE                          BNET

1.3 通过object_id查询出现gc等待事件的sql_id

set line 200 pages 1000
col sample_time for a30
col sql_id for a30
col event for a30
col sql_opname for a30
select sql_id,event,count(*)
from bnet.ash0929
where INSTANCE_NUMBER = 1
and wait_class<>'Idle'
and CURRENT_OBJ# = 18894
and event like 'gc%'
group by sql_id,event
order by count(*);

fkbnpvfcj0jqt                  gc cr grant 2-way                    1084
ffnrbs41f3f6u                  gc cr grant 2-way                    1199
8vw6303fbmmrq                  gc cr grant 2-way                    1298
6qu0mr11q37xr                  gc current block 2-way               1334
20mu0b2t8upr7                  gc current block 2-way               1345
6a0da6m2tr0zv                  gc current block 2-way               1467
0g0u537huwndc                  gc cr grant 2-way                    1513
377902xcd03p9                  gc cr grant 2-way                    1696
0mnq9qxk9u5am                  gc current block 2-way               1944
1xc5agz3fxxa8                  gc buffer busy acquire               3472
6a0da6m2tr0zv                  gc cr grant 2-way                    3477
3gyrujtbxz66q                  gc buffer busy acquire               4793
20mu0b2t8upr7                  gc cr grant 2-way                    4928
6qu0mr11q37xr                  gc cr grant 2-way                    5783
发现集群等待事件发生在sql_id 1xc5agz3fxxa8和3gyrujtbxz66q两条sql语句。

1.4 sql语句文本

sql_id = ' 1xc5agz3fxxa8'

SQL Text
------------------------------
SELECT COUNT(0) FROM 
(SELECT 1 FROM T_COR TC WHERE ( TC.BANK_ID_F=:1 ) AND ( TC.SERVICE_ID=:2 or TC.SERVICE_ID=:3 or TC.SERVICE_ID=:4 or TC.SERVICE_ID=:5 or TC.SERVICE_ID=:6 or TC.SERVICE_ID=:7 or TC.SERVICE_ID=:8 or TC.SERVICE_ID=:9 or TC.SERVICE_ID=:10 or TC.SERVICE_ID=:11 or TC.SERVICE_ID=:12 or TC.SERVICE_ID=:13 or TC.SERVICE_ID=:14 or TC.SERVICE_ID=:15 or TC.SERVICE_ID=:16 or TC.SERVICE_ID=:17 or TC.SERVICE_ID=:18 or TC.SERVICE_ID=:19 or TC.SERVICE_ID=:20 or TC.SERVICE_ID=:21 or
TC.SERVICE_ID=:22 or TC.SERVICE_ID=:23 or TC.SERVICE_ID=:24 ) 
AND TC.TRF_CUR='001' 
AND TC.T_SUBMIT_DATE>=trunc(:25 ) 
and TC.T_SUBMIT_DATE<trunc(:26 )+1 
UNION ALL 
SELECT 1 FROM T_GROUP TG WHERE ( TG.BANK_ID_I=:27 ) AND ( TG.SERVICE_ID=:28 or TG.SERVICE_ID=:29 or TG.SERVICE_ID=:30 or TG.SERVICE_ID=:31 or TG.SERVICE_ID=:32 or TG.SERVICE_ID=:33 or TG.SERVICE_ID=:34 or TG.SERVICE_ID=:35 or TG.SERVICE_ID=:36 or TG.SERVICE_ID=:37 or TG.SERVICE_ID=:38 or TG.SERVICE_ID=:39 or TG.SERVICE_ID=:40 or
TG.SERVICE_ID=:41 or TG.SERVICE_ID=:42 or TG.SERVICE_ID=:43 or TG.SERVICE_ID=:44 or TG.SERVICE_ID=:45 or TG.SERVICE_ID=:46 or TG.SERVICE_ID=:47 or TG.SERVICE_ID=:48 or TG.SERVICE_ID=:49 or TG.SERVICE_ID=:50 ) 
AND TG.TRF_CUR='001' 
AND TG.T_SUBMIT_DATE>=trunc(:51 ) 
and TG.T_SUBMIT_DATE<trunc(:52 )+1 )


sql_id = '3gyrujtbxz66q’

SQL Text
------------------------------

SELECT COUNT(0) FROM (
   SELECT 1 
      FROM T_COR TC 
      WHERE ( TC.BANK_ID_F = 57879 ) 
      AND ( TC.SERVICE_ID=:2 or TC.SERVICE_ID=:3 or TC.SERVICE_ID=:4 or TC.SERVICE_ID=:5 or TC.SERVICE_ID=:6 or TC.SERVICE_ID=:7 or TC.SERVICE_ID=:8 or TC.SERVICE_ID=:9 or TC.SERVICE_ID=:10 or TC.SERVICE_ID=:11 or TC.SERVICE_ID=:12 or TC.SERVICE_ID=:13 or TC.SERVICE_ID=:14 or TC.SERVICE_ID=:15 or TC.SERVICE_ID=:16 or TC.SERVICE_ID=:17 or TC.SERVICE_ID=:18 or TC.SERVICE_ID=:19 or TC.SERVICE_ID=:20 or TC.SERVICE_ID=:21 or
TC.SERVICE_ID=:22 or TC.SERVICE_ID=:23 or TC.SERVICE_ID=:24 ) 
AND TC.TRF_CUR='001' 
AND TC.T_SUBMIT_DATE>=trunc(:25 ) 
and TC.T_SUBMIT_DATE<trunc(:26 )+1 
and TC.ACTNUM_F = :27 
UNION ALL 
   SELECT 1 
     FROM T_GROUP TG 
     WHERE ( TG.BANK_ID_I=:28 ) 
AND ( TG.SERVICE_ID=:29 or TG.SERVICE_ID=:30 or TG.SERVICE_ID=:31 or TG.SERVICE_ID=:32 or TG.SERVICE_ID=:33 or TG.SERVICE_ID=:34 or TG.SERVICE_ID=:35 or TG.SERVICE_ID=:36 or TG.SERVICE_ID=:37 or TG.SERVICE_ID=:38 or TG.SERVICE_ID=:39 or TG.SERVICE_ID=:40 or TG.SERVICE_ID=:41 or TG.SERVICE_ID=:42 or TG.SERVICE_ID=:43 or TG.SERVICE_ID=:44 or TG.SERVICE_ID=:45 or TG.SERVICE_ID=:46 or TG.SERVICE_ID=:47 or TG.SERVICE_ID=:48 or TG.SERVICE_ID=:49 or TG.SERVICE_ID=:50 or TG.SERVICE_ID=:51 ) 
   AND TG.TRF_CUR='001' 
   AND TG.T_SUBMIT_DATE>=trunc(:52 ) 
   and TG.T_SUBMIT_DATE<trunc(:53 )+1 
   and TG.ACTNUM_I = :54 )

发现涉及表T_COR相关的sql语句均为统计语句，此类语句执行对表的统计信息更新的及时性和索引选择依赖性较高。

1.5 sql语句执行情况

sql_id = '3gyrujtbxz66q’

SHIJIAN      INST_ID PLAN_HASH_VALUE       EXECU_D        BG_D        DR_D      ET_D      CT_D IO_TIME CLUS_TIME AP_TIME CC_TIME ET_ONETIME RW_ONETIME
------------ ------- --------------- ------------- ----------- ----------- --------- --------- ------- --------- ------- ------- ---------- ----------
20200929 16        1      3076256026            11    13599787      310037     37525       168    1446     35906       0      96       3411          0
20200929 17        1      3076256026             0    48217116     1488553     64162       433    3555     59972       0     215      64162          0
20200929 18        1      3076256026             9   227318556     6618534     43654      1406    8477     32941       0     190       4850 2.88888889
20200929 18        2      3076256026            10    59333320     2127512     10707       410    1931      8181       0      14       1071        1.6
20200929 19        1      3076256026            14    29118583      769059       954       148     575       189       0       0         68 1.07142857
20200930 18        2      3076256026             0   106951120     3485405     23488       632    4653     17796       0      58      23488          4
20200930 19        1      3076256026            49    87326268      443124      1633       235     579       629       0       7         33 1.04081633
20200930 19        2      3076256026            15    41307997     1286099      2192       256    1094       754       0       0        146        1.2   

sql_id = 1xc5agz3fxxa8

SHIJIAN      INST_ID PLAN_HASH_VALUE       EXECU_D        BG_D        DR_D      ET_D      CT_D IO_TIME CLUS_TIME AP_TIME CC_TIME ET_ONETIME RW_ONETIME
------------ ------- --------------- ------------- ----------- ----------- --------- --------- ------- --------- ------- ------- ---------- ----------
20200929 18        1      3076256026            33   167466143     2973688     27199       883    4887     20751       0     209        824 1.36363636
20200929 18        2      3076256026            23    85248108     2355600     14829       524    2536     11472       0      27        645 1.26086957
20200929 20        1      3076256026            79   108099611          12       364       199       0        17       0       0          5          1
20200929 20        2      3076256026           137   185840188      156679       731       354      86        38       0       0          5  .99270073
20200930 10        1      3076256026            99   133257442       40260      1110       278      30       513       0       0         11          1
20200930 10        2      3076256026            26    29427755       27715      8429       161     284      7898       0      27        324 .769230769
20200930 11        1      3076256026            45    37639927      547472     27846       332    1386     25927       0      77        619         .8
20200930 17        1      3076256026             0    66035388     1531193     73436       492    3536     68828       0     513      73436          0
20200930 18        1      3076256026             2   296973394     7701847     70186      1598   12713     54596       0     359      35093          6
20200930 18        2      3076256026             5   219477858     6215827     49409      1265    8999     38276       0     115       9882          3
20200930 19        1      3076256026           387   652729612     2373562      7254      1570    2451      1993       0      16         19 1.02583979
20200930 19        2      3076256026           199   347259145     1968164      4814       909    1656      1591       0      10         24 1.03517588
20200930 20        1      3076256026           356   561135506      223511      2212      1063     176       198       0       1          6          1
20200930 20        2      3076256026           325   513154984      292101      2013       977     196       130       0       0          6          1
20200930 21        1      3076256026           135   217225051      327056      1087       416     313        71       0       0          8          1
20200930 21        2      3076256026           132   212984890           4       668       375       0         9       0       0          5          1
20200930 22        1      3076256026           130   212669239       17365       672       373      10        12       0       0          5          1

发现2条语句执行在9月29日和30日发生问题时段中，产生了大量集群cr块传输，伴随大量集群等待时间，并且有执行失败情况。

1.6 查询等待情况

1.6.1 gc等待

set line 200 pages 1000
col sample_time for a30
col sql_id for a30
col event for a30
col sql_opname for a30
select sql_id,event,BLOCKING_SESSION,count(*)
from bnet.ash0929
where INSTANCE_NUMBER = 1
and wait_class<>'Idle'
and CURRENT_OBJ# = 18894
and event like 'gc%'
group by sql_id,event,BLOCKING_SESSION
order by count(*); 

1xc5agz3fxxa8                  gc buffer busy acquire                     9733        413
cyksmpq9ypkgh                  gc cr grant 2-way                                      429
1xc5agz3fxxa8                  gc buffer busy acquire                     5552        448
ffnrbs41f3f6u                  gc current block 2-way                                 452
848v05j3hu9k6                  gc current block 2-way                                 461
377902xcd03p9                  gc current block 2-way                                 470
6ymfasu8cnn10                  gc cr grant 2-way                                      483
3farurg1cdh7u                  gc cr grant 2-way                                      516
0mnq9qxk9u5am                  gc cr grant 2-way                                      558
7bvwjpr9yg4jj                  gc cr grant 2-way                                      593
8qfn3fsphgn7u                  gc cr grant 2-way                                      671
3r0mpta4q57t0                  gc cr grant 2-way                                      712
8x4h2sw9tmw4g                  gc cr grant 2-way                                      775
3gyrujtbxz66q                  gc cr grant 2-way                                      834
4s2zqh04kpu7d                  gc cr multi block request                              912
fkbnpvfcj0jqt                  gc cr grant 2-way                                     1084
ffnrbs41f3f6u                  gc cr grant 2-way                                     1199
8vw6303fbmmrq                  gc cr grant 2-way                                     1298
6qu0mr11q37xr                  gc current block 2-way                                1334
20mu0b2t8upr7                  gc current block 2-way                                1345
6a0da6m2tr0zv                  gc current block 2-way                                1467
0g0u537huwndc                  gc cr grant 2-way                                     1513
377902xcd03p9                  gc cr grant 2-way                                     1696
0mnq9qxk9u5am                  gc current block 2-way                                1944
6a0da6m2tr0zv                  gc cr grant 2-way                                     3477
20mu0b2t8upr7                  gc cr grant 2-way                                     4928
6qu0mr11q37xr                  gc cr grant 2-way                                     5783
SQL> set line 200 pages 1000
SQL> col sample_time for a30
SQL> col sql_id for a30
SQL> col event for a30
SQL> col sql_opname for a30
SQL> select sql_id,event,BLOCKING_SESSION,count(*)
  2  from bnet.ash0929
  3  where INSTANCE_NUMBER = 1
  4  and wait_class<>'Idle'
  5  and CURRENT_OBJ# = 18894
  6  and event like 'gc%'
  7  and SESSION_ID = 5552
  8  group by sql_id,event,BLOCKING_SESSION
  9  order by count(*); 

SQL_ID                         EVENT                          BLOCKING_SESSION   COUNT(*)
------------------------------ ------------------------------ ---------------- ----------
3gyrujtbxz66q                  gc cr block 2-way                                        1
3gyrujtbxz66q                  gc buffer busy acquire                                   2
3gyrujtbxz66q                  gc cr grant congested                                    3
3gyrujtbxz66q                  gc current block 2-way                                  27
3gyrujtbxz66q                  gc buffer busy acquire                    14333         36
3gyrujtbxz66q                  gc buffer busy acquire                     4548         40
3gyrujtbxz66q                  gc buffer busy acquire                    13018         47
3gyrujtbxz66q                  gc cr grant 2-way                                       59
3gyrujtbxz66q                  gc buffer busy acquire                     4806         65
3gyrujtbxz66q                  gc buffer busy acquire                     9733         79
3gyrujtbxz66q                  gc buffer busy acquire                     5111        102

11 rows selected.
SQL> set line 200 pages 1000
SQL> col sample_time for a30
SQL> col sql_id for a30
SQL> col event for a30
SQL> col sql_opname for a30
SQL> select sql_id,event,BLOCKING_SESSION,count(*)
  2  from bnet.ash0929
  3  where INSTANCE_NUMBER = 1
  4  and wait_class<>'Idle'
  5  and CURRENT_OBJ# = 18894
  6  and event like 'gc%'
  7  and SESSION_ID = 5111
  8  group by sql_id,event,BLOCKING_SESSION
  9  order by count(*); 

SQL_ID                         EVENT                          BLOCKING_SESSION   COUNT(*)
------------------------------ ------------------------------ ---------------- ----------
1xc5agz3fxxa8                  gc current block congested                               1
1xc5agz3fxxa8                  gc cr block 2-way                                        3
1xc5agz3fxxa8                  gc buffer busy acquire                                   6
1xc5agz3fxxa8                  gc cr grant congested                                    7
1xc5agz3fxxa8                  gc buffer busy acquire                     4548         34
1xc5agz3fxxa8                  gc buffer busy acquire                    14333         35
1xc5agz3fxxa8                  gc current block 2-way                                  44
1xc5agz3fxxa8                  gc buffer busy acquire                    13018         46
1xc5agz3fxxa8                  gc cr grant 2-way                                       57
1xc5agz3fxxa8                  gc buffer busy acquire                     4806         66
1xc5agz3fxxa8                  gc buffer busy acquire                     9733         79
1xc5agz3fxxa8                  gc buffer busy acquire                     5552         85

12 rows selected.

发现集群等待的原因sql_id 1xc5agz3fxxa8和3gyrujtbxz66q相互等待sql语句持有的buffer，从而产生了大量的集群等待。

1.7 执行计划

SQL_ID=>'1XC5AGZ3FXXA8'
DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'1XC5AGZ3FXXA8')
------------------------------------------------------------------------------------------------------------------------SQL Monitoring Report

SQL Text
------------------------------
SELECT COUNT(0) FROM 
(SELECT 1 FROM T_COR TC WHERE ( TC.BANK_ID_F=:1 ) AND ( TC.SERVICE_ID=:2 or TC.SERVICE_ID=:3 or TC.SERVICE_ID=:4 or TC.SERVICE_ID=:5 or TC.SERVICE_ID=:6 or TC.SERVICE_ID=:7 or TC.SERVICE_ID=:8 or TC.SERVICE_ID=:9 or TC.SERVICE_ID=:10 or TC.SERVICE_ID=:11 or TC.SERVICE_ID=:12 or TC.SERVICE_ID=:13 or TC.SERVICE_ID=:14 or TC.SERVICE_ID=:15 or TC.SERVICE_ID=:16 or TC.SERVICE_ID=:17 or TC.SERVICE_ID=:18 or TC.SERVICE_ID=:19 or TC.SERVICE_ID=:20 or TC.SERVICE_ID=:21 or
TC.SERVICE_ID=:22 or TC.SERVICE_ID=:23 or TC.SERVICE_ID=:24 ) 
AND TC.TRF_CUR='001' 
AND TC.T_SUBMIT_DATE>=trunc(:25 ) 
and TC.T_SUBMIT_DATE<trunc(:26 )+1 
UNION ALL 
SELECT 1 FROM TRANS_GROUP TG WHERE ( TG.BANK_ID_I=:27 ) AND ( TG.SERVICE_ID=:28 or TG.SERVICE_ID=:29 or TG.SERVICE_ID=:30 or TG.SERVICE_ID=:31 or TG.SERVICE_ID=:32 or TG.SERVICE_ID=:33 or TG.SERVICE_ID=:34 or TG.SERVICE_ID=:35 or TG.SERVICE_ID=:36 or TG.SERVICE_ID=:37 or TG.SERVICE_ID=:38 or TG.SERVICE_ID=:39 or TG.SERVICE_ID=:40 or
TG.SERVICE_ID=:41 or TG.SERVICE_ID=:42 or TG.SERVICE_ID=:43 or TG.SERVICE_ID=:44 or TG.SERVICE_ID=:45 or TG.SERVICE_ID=:46 or TG.SERVICE_ID=:47 or TG.SERVICE_ID=:48 or TG.SERVICE_ID=:49 or TG.SERVICE_ID=:50 ) 
AND TG.TRF_CUR='001' 
AND TG.T_SUBMIT_DATE>=trunc(:51 ) 
and TG.T_SUBMIT_DATE<trunc(:52 )+1 )

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  2
 Session             :  BNET (8805:50271)
 SQL ID              :  1xc5agz3fxxa8
 SQL Execution ID    :  33554911
 Execution Started   :  11/09/2020 13:15:32
 First Refresh Time  :  11/09/2020 13:15:36
 Last Refresh Time   :  11/09/2020 13:16:47
 Duration            :  75s
 Module/Action       :  JDBC Thin Client/-
 Service             :  oraDB
 Program             :  JDBC Thin Client
 Fetch Calls         :  1

Binds
========================================================================================================================
| Name | Position |     Type      |                                       Value                                        |
========================================================================================================================
| :1   |        1 | NUMBER        | 90454                                                                              |
| :2   |        2 | VARCHAR2(128) | EB020                                                                              |
| :3   |        3 | VARCHAR2(128) | EB021                                                                              |
| :4   |        4 | VARCHAR2(128) | EB030                                                                              |
| :5   |        5 | VARCHAR2(128) | EB031                                                                              |
| :6   |        6 | VARCHAR2(128) | EB071                                                                              |
| :7   |        7 | VARCHAR2(128) | EB070                                                                              |
| :8   |        8 | VARCHAR2(128) | EB810                                                                              |
| :9   |        9 | VARCHAR2(128) | EB811                                                                              |
| :10  |       10 | VARCHAR2(128) | EB830                                                                              |
| :11  |       11 | VARCHAR2(128) | EB831                                                                              |
| :12  |       12 | VARCHAR2(128) | EB820                                                                              |
| :13  |       13 | VARCHAR2(128) | EB091                                                                              |
| :14  |       14 | VARCHAR2(128) | EB172                                                                              |
| :15  |       15 | VARCHAR2(128) | EB270                                                                              |
| :16  |       16 | VARCHAR2(128) | EB271                                                                              |
| :17  |       17 | VARCHAR2(128) | EB431                                                                              |
| :18  |       18 | VARCHAR2(128) | EB441                                                                              |
| :19  |       19 | VARCHAR2(128) | EB510                                                                              |
| :20  |       20 | VARCHAR2(128) | EB511                                                                              |
| :21  |       21 | VARCHAR2(128) | EB541                                                                              |
| :22  |       22 | VARCHAR2(128) | EB900                                                                              |
| :23  |       23 | VARCHAR2(128) | EB484                                                                              |
| :24  |       24 | VARCHAR2(128) | EB931                                                                              |
| :25  |       25 | TIMESTAMP     | 78780B06010101                                                                     |
| :26  |       26 | TIMESTAMP     | 78780B06010101                                                                     |
| :27  |       27 | NUMBER        | 90454                                                                              |
| :28  |       28 | VARCHAR2(128) | EB020                                                                              |
| :29  |       29 | VARCHAR2(128) | EB021                                                                              |
| :30  |       30 | VARCHAR2(128) | EB030                                                                              |
| :31  |       31 | VARCHAR2(128) | EB031                                                                              |
| :32  |       32 | VARCHAR2(128) | EB071                                                                              |
| :33  |       33 | VARCHAR2(128) | EB070                                                                              |
| :34  |       34 | VARCHAR2(128) | EB810                                                                              |
| :35  |       35 | VARCHAR2(128) | EB811                                                                              |
| :36  |       36 | VARCHAR2(128) | EB830                                                                              |
| :37  |       37 | VARCHAR2(128) | EB831                                                                              |
| :38  |       38 | VARCHAR2(128) | EB820                                                                              |
| :39  |       39 | VARCHAR2(128) | EB091                                                                              |
| :40  |       40 | VARCHAR2(128) | EB172                                                                              |
| :41  |       41 | VARCHAR2(128) | EB270                                                                              |
| :42  |       42 | VARCHAR2(128) | EB271                                                                              |
| :43  |       43 | VARCHAR2(128) | EB431                                                                              |
| :44  |       44 | VARCHAR2(128) | EB441                                                                              |
| :45  |       45 | VARCHAR2(128) | EB510                                                                              |
| :46  |       46 | VARCHAR2(128) | EB511                                                                              |
| :47  |       47 | VARCHAR2(128) | EB541                                                                              |
| :48  |       48 | VARCHAR2(128) | EB900                                                                              |
| :49  |       49 | VARCHAR2(128) | EB484                                                                              |
| :50  |       50 | VARCHAR2(128) | EB931                                                                              |
| :51  |       51 | TIMESTAMP     | 78780B06010101                                                                     |
| :52  |       52 | TIMESTAMP     | 78780B06010101                                                                     |
========================================================================================================================

Global Stats
======================================================================================
| Elapsed |   Cpu   |    IO    | Cluster  |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
======================================================================================
|      76 |      12 |       45 |       18 |     0.42 |     1 |   969K | 109K | 848MB |
======================================================================================

SQL Plan Monitoring Details (Plan Hash Value=3076256026)
===========================================================================================================================================================================================
| Id |             Operation             |             Name             |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |   Activity Detail         |
|    |                                   |                              | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |     (# samples)   |
===========================================================================================================================================================================================
|  0 | SELECT STATEMENT                  |                              |         |      |        72 |     +4 |     1 |        1 |      |       |          |  |
|  1 |   SORT AGGREGATE                  |                              |       1 |      |        72 |     +4 |     1 |        1 |      |       |          |  |
|  2 |    VIEW                           |                              |       2 |   23 |        61 |     +4 |     1 |       12 |      |       |          |  |
|  3 |     UNION-ALL                     |                              |         |      |        61 |     +4 |     1 |       12 |      |       |          |  |
|  4 |      FILTER                       |                              |         |      |        61 |     +4 |     1 |       12 |      |       |          |  |
|  5 |       TABLE ACCESS BY INDEX ROWID | T_COR                    |       1 |   18 |        75 |     +1 |     1 |       12 | 104K | 813MB |    95.89 | gc cr block 2-way (1)     |
|    |                                   |                              |         |      |           |        |       |          |      |       |          | gc cr grant 2-way (4)     |
|    |                                   |                              |         |      |           |        |       |          |      |       |          | gc current block 2-way (13)  |
|    |                                   |                              |         |      |           |        |       |          |      |       |          | Cpu (8)                   |
|    |                                   |                              |         |      |           |        |       |          |      |       |          | db file sequential read (44) |
|  6 |        INDEX RANGE SCAN           | IDX_TRANSCOR_T_SUBMIT_CUST   |      14 |    4 |        72 |     +4 |     1 |       1M |   19 | 152KB |     2.74 | gc current block 2-way (1)   |
|    |                                   |                              |         |      |           |        |       |          |      |       |          | Cpu (1)                   |
|  7 |      FILTER                       |                              |         |      |           |        |     1 |          |      |       |          |  |
|  8 |       TABLE ACCESS BY INDEX ROWID | T_GROUP                  |       1 |    5 |         1 |    +75 |     1 |        0 | 1641 |  13MB |     1.37 | db file sequential read (1)  |
|  9 |        INDEX RANGE SCAN           | IDX_TRANSGROUP_T_SUBMIT_DATE |       3 |    3 |         1 |    +75 |     1 |    10228 |   64 | 512KB |          |  |



Plan hash value: 3076256026

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                              |       |       |    22 (100)|          |
|   1 |  SORT AGGREGATE                 |                              |     1 |       |            |          |
|   2 |   VIEW                          |                              |     2 |       |    22   (0)| 00:00:01 |
|   3 |    UNION-ALL                    |                              |       |       |            |          |
|*  4 |     FILTER                      |                              |       |       |            |          |
|*  5 |      TABLE ACCESS BY INDEX ROWID| T_COR                    |     1 |    23 |    17   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | IDX_TRANSCOR_T_SUBMIT_CUST   |    14 |       |     4   (0)| 00:00:01 |
|*  7 |     FILTER                      |                              |       |       |            |          |
|*  8 |      TABLE ACCESS BY INDEX ROWID| T_GROUP                  |     1 |    23 |     5   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN          | IDX_TRANSGROUP_T_SUBMIT_DATE |     3 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SET$1 / from$_subquery$_001@SEL$1
   3 - SET$1
   4 - SEL$2
   5 - SEL$2 / TC@SEL$2
   6 - SEL$2 / TC@SEL$2
   7 - SEL$3
   8 - SEL$3 / TG@SEL$3
   9 - SEL$3 / TG@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      INDEX_RS_ASC(@"SEL$3" "TG"@"SEL$3" ("TRANS_GROUP"."T_SUBMIT_DATE"))
      INDEX_RS_ASC(@"SEL$2" "TC"@"SEL$2" ("TRANS_COR"."T_SUBMIT_DATE" "TRANS_COR"."CUST_ID"))
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(TRUNC(:26)+1>TRUNC(:25))
   5 - filter(("TC"."BANK_ID_F"=:1 AND INTERNAL_FUNCTION("TC"."SERVICE_ID") AND "TC"."TRF_CUR"='001'))
   6 - access("TC"."T_SUBMIT_DATE">=TRUNC(:25) AND "TC"."T_SUBMIT_DATE"<TRUNC(:26)+1)
   7 - filter(TRUNC(:52)+1>TRUNC(:51))
   8 - filter(("TG"."BANK_ID_I"=:27 AND INTERNAL_FUNCTION("TG"."SERVICE_ID") AND "TG"."TRF_CUR"='001'))
   9 - access("TG"."T_SUBMIT_DATE">=TRUNC(:51) AND "TG"."T_SUBMIT_DATE"<TRUNC(:52)+1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   6 - "TC".ROWID[ROWID,10]
   9 - "TG".ROWID[ROWID,10]

SQL_ID=>'3GYRUJTBXZ66Q'

DBMS_SQLTUNE.REPORT_SQL_MONITOR(SQL_ID=>'3GYRUJTBXZ66Q')
------------------------------------------------------------------------------------------------------------------------SQL Monitoring Report

SQL Text
------------------------------

SELECT COUNT(0) FROM (
 SELECT 1 FROM T_COR TC 
  WHERE ( TC.BANK_ID_F = 57879 ) 
  AND ( TC.SERVICE_ID=:2 or TC.SERVICE_ID=:3 or TC.SERVICE_ID=:4 or TC.SERVICE_ID=:5 or TC.SERVICE_ID=:6 or TC.SERVICE_ID=:7 or TC.SERVICE_ID=:8 or TC.SERVICE_ID=:9 or TC.SERVICE_ID=:10 or TC.SERVICE_ID=:11 or TC.SERVICE_ID=:12 or TC.SERVICE_ID=:13 or TC.SERVICE_ID=:14 or TC.SERVICE_ID=:15 or TC.SERVICE_ID=:16 or TC.SERVICE_ID=:17 or TC.SERVICE_ID=:18 or TC.SERVICE_ID=:19 or TC.SERVICE_ID=:20 or TC.SERVICE_ID=:21 or
TC.SERVICE_ID=:22 or TC.SERVICE_ID=:23 or TC.SERVICE_ID=:24 ) 
  AND TC.TRF_CUR='001' 
  AND TC.T_SUBMIT_DATE>=trunc(:25 ) 
  and TC.T_SUBMIT_DATE<trunc(:26 )+1 
  and TC.ACTNUM_F = :27 
UNION ALL 
SELECT 1 FROM T_GROUP TG 
WHERE ( TG.BANK_ID_I=:28 ) 
AND ( TG.SERVICE_ID=:29 or TG.SERVICE_ID=:30 or TG.SERVICE_ID=:31 or TG.SERVICE_ID=:32 or TG.SERVICE_ID=:33 or TG.SERVICE_ID=:34 or TG.SERVICE_ID=:35 or TG.SERVICE_ID=:36 or TG.SERVICE_ID=:37 or TG.SERVICE_ID=:38 or TG.SERVICE_ID=:39 or TG.SERVICE_ID=:40 or TG.SERVICE_ID=:41 or TG.SERVICE_ID=:42 or TG.SERVICE_ID=:43 or TG.SERVICE_ID=:44 or TG.SERVICE_ID=:45 or TG.SERVICE_ID=:46 or TG.SERVICE_ID=:47 or TG.SERVICE_ID=:48 or TG.SERVICE_ID=:49 or TG.SERVICE_ID=:50 or TG.SERVICE_ID=:51 ) 
AND TG.TRF_CUR='001' 
AND TG.T_SUBMIT_DATE>=trunc(:52 ) 
and TG.T_SUBMIT_DATE<trunc(:53 )+1 
and TG.ACTNUM_I = :54 )

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  2
 Session             :  BNET (19882:3823)
 SQL ID              :  3gyrujtbxz66q
 SQL Execution ID    :  33554519
 Execution Started   :  11/05/2020 09:38:07
 First Refresh Time  :  11/05/2020 09:38:27
 Last Refresh Time   :  11/05/2020 09:38:42
 Duration            :  35s
 Module/Action       :  JDBC Thin Client/-
 Service             :  oraDB
 Program             :  JDBC Thin Client
 Fetch Calls         :  1

Binds
========================================================================================================================
| Name | Position |     Type      |                                       Value                                        |
========================================================================================================================
| :1   |        1 | NUMBER        | 57879                                                                              |
| :2   |        2 | VARCHAR2(128) | EB020                                                                              |
| :3   |        3 | VARCHAR2(128) | EB021                                                                              |
| :4   |        4 | VARCHAR2(128) | EB030                                                                              |
| :5   |        5 | VARCHAR2(128) | EB031                                                                              |
| :6   |        6 | VARCHAR2(128) | EB071                                                                              |
| :7   |        7 | VARCHAR2(128) | EB070                                                                              |
| :8   |        8 | VARCHAR2(128) | EB810                                                                              |
| :9   |        9 | VARCHAR2(128) | EB811                                                                              |
| :10  |       10 | VARCHAR2(128) | EB830                                                                              |
| :11  |       11 | VARCHAR2(128) | EB831                                                                              |
| :12  |       12 | VARCHAR2(128) | EB820                                                                              |
| :13  |       13 | VARCHAR2(128) | EB091                                                                              |
| :14  |       14 | VARCHAR2(128) | EB172                                                                              |
| :15  |       15 | VARCHAR2(128) | EB270                                                                              |
| :16  |       16 | VARCHAR2(128) | EB271                                                                              |
| :17  |       17 | VARCHAR2(128) | EB431                                                                              |
| :18  |       18 | VARCHAR2(128) | EB441                                                                              |
| :19  |       19 | VARCHAR2(128) | EB510                                                                              |
| :20  |       20 | VARCHAR2(128) | EB511                                                                              |
| :21  |       21 | VARCHAR2(128) | EB541                                                                              |
| :22  |       22 | VARCHAR2(128) | EB900                                                                              |
| :23  |       23 | VARCHAR2(128) | EB484                                                                              |
| :24  |       24 | VARCHAR2(128) | EB931                                                                              |
| :25  |       25 | TIMESTAMP     | 78780B04010101                                                                     |
| :26  |       26 | TIMESTAMP     | 78780B04010101                                                                     |
| :27  |       27 | VARCHAR2(128) | 358458327279                                                                       |
| :28  |       28 | NUMBER        | 57879                                                                              |
| :29  |       29 | VARCHAR2(128) | EB020                                                                              |
| :30  |       30 | VARCHAR2(128) | EB021                                                                              |
| :31  |       31 | VARCHAR2(128) | EB030                                                                              |
| :32  |       32 | VARCHAR2(128) | EB031                                                                              |
| :33  |       33 | VARCHAR2(128) | EB071                                                                              |
| :34  |       34 | VARCHAR2(128) | EB070                                                                              |
| :35  |       35 | VARCHAR2(128) | EB810                                                                              |
| :36  |       36 | VARCHAR2(128) | EB811                                                                              |
| :37  |       37 | VARCHAR2(128) | EB830                                                                              |
| :38  |       38 | VARCHAR2(128) | EB831                                                                              |
| :39  |       39 | VARCHAR2(128) | EB820                                                                              |
| :40  |       40 | VARCHAR2(128) | EB091                                                                              |
| :41  |       41 | VARCHAR2(128) | EB172                                                                              |
| :42  |       42 | VARCHAR2(128) | EB270                                                                              |
| :43  |       43 | VARCHAR2(128) | EB271                                                                              |
| :44  |       44 | VARCHAR2(128) | EB431                                                                              |
| :45  |       45 | VARCHAR2(128) | EB441                                                                              |
| :46  |       46 | VARCHAR2(128) | EB510                                                                              |
| :47  |       47 | VARCHAR2(128) | EB511                                                                              |
| :48  |       48 | VARCHAR2(128) | EB541                                                                              |
| :49  |       49 | VARCHAR2(128) | EB900                                                                              |
| :50  |       50 | VARCHAR2(128) | EB484                                                                              |
| :51  |       51 | VARCHAR2(128) | EB931                                                                              |
| :52  |       52 | TIMESTAMP     | 78780B04010101                                                                     |
| :53  |       53 | TIMESTAMP     | 78780B04010101                                                                     |
| :54  |       54 | VARCHAR2(128) | 358458327279                                                                       |
========================================================================================================================

Global Stats
======================================================================================
| Elapsed |   Cpu   |    IO    | Cluster  |  Other   | Fetch | Buffer | Read | Read  |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes |
======================================================================================
|      35 |    9.50 |     0.01 |       24 |     1.37 |     1 |   836K |    5 | 40960 |
======================================================================================

SQL Plan Monitoring Details (Plan Hash Value=3076256026)
==========================================================================================================================================================================================
| Id |             Operation             |             Name             |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Activity |   Activity Detail        |
|    |                                   |                              | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |   (%)    |     (# samples)  |
==========================================================================================================================================================================================
|  0 | SELECT STATEMENT                  |                              |         |      |         1 |    +35 |     1 |        1 |      |       |          | |
|  1 |   SORT AGGREGATE                  |                              |       1 |      |         1 |    +35 |     1 |        1 |      |       |          | |
|  2 |    VIEW                           |                              |       2 |   23 |           |        |     1 |          |      |       |          | |
|  3 |     UNION-ALL                     |                              |         |      |           |        |     1 |          |      |       |          | |
|  4 |      FILTER                       |                              |         |      |           |        |     1 |          |      |       |          | |
|  5 |       TABLE ACCESS BY INDEX ROWID | T_COR                    |       1 |   18 |        35 |     +1 |     1 |        0 |    4 | 32768 |    81.82 | gc current block 2-way (19) |
|    |                                   |                              |         |      |           |        |       |          |      |       |          | Cpu (8)                  |
|  6 |        INDEX RANGE SCAN           | IDX_TRANSCOR_T_SUBMIT_CUST   |      14 |    4 |        26 |    +10 |     1 |     906K |      |       |    15.15 | gc current block 2-way (3)  |
|    |                                   |                              |         |      |           |        |       |          |      |       |          | Cpu (2)                  |
|  7 |      FILTER                       |                              |         |      |           |        |     1 |          |      |       |          | |
|  8 |       TABLE ACCESS BY INDEX ROWID | T_GROUP                  |       1 |    5 |         1 |    +35 |     1 |        0 |      |       |     3.03 | Cpu (1)                  |
|  9 |        INDEX RANGE SCAN           | IDX_TRANSGROUP_T_SUBMIT_DATE |       3 |    3 |         1 |    +35 |     1 |     9396 |      |       |          | |

Plan hash value: 3076256026

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                              |       |       |    22 (100)|          |
|   1 |  SORT AGGREGATE                 |                              |     1 |       |            |          |
|   2 |   VIEW                          |                              |     2 |       |    22   (0)| 00:00:01 |
|   3 |    UNION-ALL                    |                              |       |       |            |          |
|*  4 |     FILTER                      |                              |       |       |            |          |
|*  5 |      TABLE ACCESS BY INDEX ROWID| T_COR                    |     1 |    37 |    17   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | IDX_TRANSCOR_T_SUBMIT_CUST   |    14 |       |     4   (0)| 00:00:01 |
|*  7 |     FILTER                      |                              |       |       |            |          |
|*  8 |      TABLE ACCESS BY INDEX ROWID| T_GROUP                  |     1 |    37 |     5   (0)| 00:00:01 |
|*  9 |       INDEX RANGE SCAN          | IDX_TRANSGROUP_T_SUBMIT_DATE |     3 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SET$1 / from$_subquery$_001@SEL$1
   3 - SET$1
   4 - SEL$2
   5 - SEL$2 / TC@SEL$2
   6 - SEL$2 / TC@SEL$2
   7 - SEL$3
   8 - SEL$3 / TG@SEL$3
   9 - SEL$3 / TG@SEL$3

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$1")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      INDEX_RS_ASC(@"SEL$3" "TG"@"SEL$3" ("TRANS_GROUP"."T_SUBMIT_DATE"))
      INDEX_RS_ASC(@"SEL$2" "TC"@"SEL$2" ("TRANS_COR"."T_SUBMIT_DATE" "TRANS_COR"."CUST_ID"))
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(TRUNC(:26)+1>TRUNC(:25))
   5 - filter(("TC"."ACTNUM_F"=:27 AND "TC"."BANK_ID_F"=:1 AND INTERNAL_FUNCTION("TC"."SERVICE_ID") AND
              "TC"."TRF_CUR"='001'))
   6 - access("TC"."T_SUBMIT_DATE">=TRUNC(:25) AND "TC"."T_SUBMIT_DATE"<TRUNC(:26)+1)
   7 - filter(TRUNC(:53)+1>TRUNC(:52))
   8 - filter(("TG"."ACTNUM_I"=:54 AND "TG"."BANK_ID_I"=:28 AND INTERNAL_FUNCTION("TG"."SERVICE_ID")
              AND "TG"."TRF_CUR"='001'))
   9 - access("TG"."T_SUBMIT_DATE">=TRUNC(:52) AND "TG"."T_SUBMIT_DATE"<TRUNC(:53)+1)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT(*)[22]
   6 - "TC".ROWID[ROWID,10]
   9 - "TG".ROWID[ROWID,10]

1.8 表TRANS_COR相关信息

OWNER        TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN PARTIT ANALYZED                EST_M
------------ ------------------------------ ---------- ---------- ----------- ------ ------------------ ----------
BNET       T_COR                       266936275   20187856         521 NO     20201109 22:22:47  147367.901


OWNER        INDEX_NAME                     INDEX_TYPE   UNIQUENESS     NUM_ROWS ANALYZED           STATUS  PARTIT DISTINCT_KEYS
------------ ------------------------------ ------------ ------------ ---------- ------------------ ------- ------ -------------
BNET       IDX_TRANSCOR_BATCUS            NORMAL       NONUNIQUE     264052160 20201109 22:22     VALID   NO          29833338
BNET       IDX_TRANSCOR_CUSTRANSID_CUSID  NORMAL       NONUNIQUE     266630626 20201109 22:22     VALID   NO          47734659
BNET       IDX_TRANSCOR_CUST_F_SUBMIT     NORMAL       NONUNIQUE     254256683 20201109 22:22     VALID   NO         216955801
BNET       IDX_TRANSCOR_CUST_RETDATE      NORMAL       NONUNIQUE     261332183 20201109 22:22     VALID   NO         154871003
BNET       IDX_TRANSCOR_CUS_ACTSEQ        NORMAL       NONUNIQUE     264667380 20201109 22:22     VALID   NO           3166464
BNET       IDX_TRANSCOR_IBKACT_F          NORMAL       NONUNIQUE     269793093 20201109 22:22     VALID   NO           3220736
BNET       IDX_TRANSCOR_IBKACT_T          NORMAL       NONUNIQUE     273847480 20201109 22:22     VALID   NO          43292757
BNET       IDX_TRANSCOR_RTNDAT_CUST       NORMAL       NONUNIQUE     294552845 20201109 22:22     VALID   NO         169712541
BNET       IDX_TRANSCOR_TRFDAT_CUST       NORMAL       NONUNIQUE     264108357 20201109 22:22     VALID   NO         196979220
BNET       IDX_TRANSCOR_T_SUBMIT_CUST     NORMAL       NONUNIQUE     278272773 20201109 22:22     VALID   NO         142404779
BNET       IDX_TRANS_COR_TIME_STAMP       NORMAL       NONUNIQUE     268277071 20201109 22:22     VALID   NO         266936275
BNET       PK_TRANS_COR                   NORMAL       UNIQUE        261788276 20201109 22:22     VALID   NO         261788276

INDEX_OWNER  INDEX_NAME                     COLUMN_NAME              COLUMN_POSITION
------------ ------------------------------ ------------------------ ---------------
BNET       IDX_TRANSCOR_BATCUS            BAT_SEQ                                1
BNET       IDX_TRANSCOR_BATCUS            CUST_ID                                2
BNET       IDX_TRANSCOR_CUSTRANSID_CUSID  CUST_TRANS_ID                          1
BNET       IDX_TRANSCOR_CUSTRANSID_CUSID  CUST_ID                                2
BNET       IDX_TRANSCOR_CUST_F_SUBMIT     CUST_ID                                1
BNET       IDX_TRANSCOR_CUST_F_SUBMIT     F_SUBMIT_DATE                          2
BNET       IDX_TRANSCOR_CUST_RETDATE      CUST_ID                                1
BNET       IDX_TRANSCOR_CUST_RETDATE      RET_REMIT_STATUS                       2
BNET       IDX_TRANSCOR_CUST_RETDATE      RETURN_DATE                            3
BNET       IDX_TRANSCOR_CUS_ACTSEQ        CUST_ID                                1
BNET       IDX_TRANSCOR_CUS_ACTSEQ        SERVICE_ID                             2
BNET       IDX_TRANSCOR_CUS_ACTSEQ        ACTSEQ_F                               3
BNET       IDX_TRANSCOR_IBKACT_F          IBKNUM                                 1
BNET       IDX_TRANSCOR_IBKACT_F          ACTNUM_F                               2
BNET       IDX_TRANSCOR_IBKACT_T          ACTIBK_T                               1
BNET       IDX_TRANSCOR_IBKACT_T          ACTNUM_T                               2
BNET       IDX_TRANSCOR_RTNDAT_CUST       RETURN_DATE                            1
BNET       IDX_TRANSCOR_RTNDAT_CUST       CUST_ID                                2
BNET       IDX_TRANSCOR_TRFDAT_CUST       CUST_ID                                1
BNET       IDX_TRANSCOR_TRFDAT_CUST       SERVICE_ID                             2
BNET       IDX_TRANSCOR_TRFDAT_CUST       TRF_DATE                               3
BNET       IDX_TRANSCOR_T_SUBMIT_CUST     T_SUBMIT_DATE                          1
BNET       IDX_TRANSCOR_T_SUBMIT_CUST     CUST_ID                                2
BNET       IDX_TRANS_COR_TIME_STAMP       TIME_STAMP                             1
BNET       PK_TRANS_COR                   TRANS_ID                               1

1.9 总结

表总行数 266936275 BANK_ID_F 11352 SERVICE_ID 14 TRF_CUR 2 T_SUBMIT_DATE 19195904

目前两条sql语句均使用索引IDX_TRANSCOR_T_SUBMIT_CUST，先进行索引范围扫描，然后回表查询相应的列值，组后做统计。由于两条sql语句是进行统计符合条件的行数，结果不需要有序，因此走索引快速扫 iffs速度会快很多，相应读取的块数会减少，从而减少私网传输块的数量和集群等待时间。

1.10 建议

在列T_SUBMIT_DATE, BANK_ID_F，SERVICE_ID，TRF_CUR上创建复合索引。 语句如下： Create index IDX_TRANSCOR_SUBMIT_BANK_SERV_CUR ON T_COR(T_SUBMIT_DATE, BANK_ID_F，SERVICE_ID，TRF_CUR);

本文由 mdnice 多平台发布

minebmw7
关注
【oracle性能】11g生成ASH报告
debimeng
11-12 1058
oracle 11g生成ASH报告 ASH以V$SESSION为基础，每秒采样一次，记录活动会话等待的事件。不活动的会话不会采样，采样工作由新引入的后台进程MMNL来完成。ASH buffers 的最小值为1MB,最大值不超过30MB.内存中记录数据。期望值是记录一小时的内容。 数据库版本：11.2.0.4；系统版本：Oracle Linux6.4 SQL&gt; @$ORACLE_HOME...
oracle报表生成器，生成AWR,ASH等报告
05-05
Oracle报表生成器集成了AWR和ASH报告的生成和分析功能，使得DBA无需手动执行复杂的SQL查询或使用Oracle Enterprise Manager等管理工具。其主要特性可能包括： 1. 自动快照收集：定期或按需自动收集AWR和ASH数据。 2...
Oracle 导出某时间段AWR与ASH性能报告操作日志
04-29
Oracle数据库，导出某时间段AWR与ASH性能报告操作日志,用于对oracle数据库进行性能分析。
oracle ash dump 导出,Oracle 导出 ASH的dump信息
weixin_32827751的博客
04-10 322
Oracle 导出 ASH的dump信息发布时间：2020-04-03 16:54:02来源：51CTO阅读：2130作者：hbxztc最近在看一些Oracle分享的时候，经常提到导出ASH的dump给另外的人来做分析，但我没有什么相关的操作，不知道是怎样的一个操作流程，于是上网看了各种博文。于是自己动手做实验做一次导出导入。实验环境：Oracle 11.2.0.4+rhel 7.2执行下面的语句...
oracle dump导出数据库,Oracle 导出 ASH的dump信息
weixin_36161279的博客
04-03 263
最近在看一些Oracle分享的时候，经常提到导出ASH的dump给另外的人来做分析，但我没有什么相关的操作，不知道是怎样的一个操作流程，于是上网看了各种博文。于是自己动手做实验做一次导出导入。实验环境：Oracle 11.2.0.4+rhel 7.2执行下面的语句对ASH信息做dump操作sys@ORA11G>alter system set events 'immediate trace ...
ASH内存强制Flush日志解决一例
bukebushuo1的专栏
06-27 808
Oracle ASH（Active Session History）是作为细粒度的AWR报告，经常在我们进行性能调优过程中被应用到。和所有的监控手段一样，ASH是建立在定时性能数据采样收集，最后集中汇总分析的基础上。ASH和AWR相比，采样频率更加密集，数据以活跃会话active session为中心。 在实际中，我们也可能会遇到与ASH有关的问题故障，本文简单介绍一个案例，供将来有需要的朋友待
Oracle 10g用ASH生成性能报告
08-01
Oracle 10g用ASH生成性能报告 Oracle 10g用ASH生成性能报告
通过AWR和ASH报告进行Oracle性能分析和优化
而ASH报告则记录了数据库活动的详细信息，可以用于分析数据库性能问题。 ## 1.2 AWR和ASH报告的重要性 AWR和ASH报告对于数据库性能分析和优化非常重要。通过这两种报告，可以全面了解数据库的活动情况，发现潜在的...
Oracle 导出 ASH的dump信息
weixin_33814685的博客
09-23 447
最近在看一些Oracle分享的时候，经常提到导出ASH的dump给另外的人来做分析，但我没有什么相关的操作，不知道是怎样的一个操作流程，于是上网看了各种博文。于是自己动手做实验做一次导出导入。实验环境：Oracle 11.2.0.4+rhel 7.2执行下面的语句对ASH信息做dump操作sys@ORA11G>altersystemsetevents'immed...
如何手动生成ASH报告
热门推荐
Watson的专栏
12-28 1万+
一, ASH 说明       ASH 的数据也会定期的写入磁盘,但是将整个ASH 内容频繁的写入磁盘不容易，所以只有1/10的active session 数据写入磁盘,可以通过V$ACTIVE_SESSION_HISTORY 视图查看内存中ASH 的信息,也可以将内存中的ASM 数据dump 到外部文件，并转移到其他系统进行分析。ASH Memory Size[最小1M，最大30M]:  S
Oracle 11g ASH报告的生成方法
cuantangnie3915的博客
03-24 438
数据库报ORA-600错误 Wed Mar 23 15:28:50 2016 Errors in file /u01/app/oracle/diag/rdbms/gtfdb/gtfdb2/trace/gtfdb2_ora...
oracle数据库 生成awr报告、ash报告详细步骤
m0_37857602的博客
08-31 1万+
oracle数据库 生成awr报告、ash报告详细步骤 一、生成awr详细步骤 1. 手动生成AWR快照号:sqlplus下执行（可选） exec dbms_workload_repository.create_snapshot 2.sqlplus下执行： @?/rdbms/admin/awrrpt.sql 或者 @$ORACLE_HOME/rdbms/admin/awrr
Oracle 数据库中如何收集ASH Report
xiaoheliushuiya
01-15 592
Oracle 数据库中如何收集ASH Report （版权声明，本人原创或者翻译的文章如需转载，如转载用于个人学习，请注明出处；否则请与本人联系，违者必究） ASH(Active Session History 活动会话历史记录)临时地保存系统当前的性能诊断信息。和AWR不同，ASH是保存内存之中, 当ASH分配的空间用光的时候，新的记录会覆盖掉旧的记录；AWR会有延迟，看的历史的性能诊断信息，最...
【程序人生】成功人生架构图（分层模型）
做一个厉害的工程师
03-18 3138
🌳 根基层（价值观）│🏋️♂️ 支柱层（健康）│⚙️ 驱动层（学习）│🕸️ 网络层（关系）│🎯 目标层（财富）│🌟 顶层（意义）🔄 外层（调节环）
音效音频素材-拟声-古风音效-人声-哈欠.zip
06-04
音效音频素材-拟声-古风音效-人声-哈欠.zip
音效音频素材-拟声-生活音效-开关灯.zip
06-04
音效音频素材-拟声-生活音效-开关灯.zip
音效音频素材-拟声-物品音效-炮.zip
最新发布
06-04
音效音频素材-拟声-物品音效-炮.zip
Oracle报表生成器：自动化AWR和ASH报告
这些报告对于数据库管理员(DBA)和性能分析师来说至关重要，因为它们提供了深入的数据库性能分析和诊断信息，帮助用户理解数据库的运行状况，并采取相应措施来优化性能或进行故障排除。 ### 知识点详解 #### Oracle...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值