代码编织梦想

  • 服务概述

业务系统ORACLE数据库所在主机在近期遇到CPU使用率高的性能问题,工程师及时响应并协助查找了引起数据库性能问题的SQL;后续对此故障原因进行分析及相关建议,详细的故障情况及相关信息分析及总结、建议,请参阅本文档。


二、 2015/12/22 数据库运行情况

2.1 数据库整体运行情况

从如下可以看到每秒逻辑读为191,080.4,每秒事务量5.6;TOP 等待事件主要为latch: cache buffers chains与log file sync;

DB Name

DB Id

Instance

Inst num

Startup Time

Release

RAC

ORCL

123456

orcl

1

04-Oct-15 23:10

11.2.0.4.0

NO

Host Name

Platform

CPUs

Cores

Sockets

Memory (GB)

test2

AIX-Based Systems (64-bit)

16

4

30.75

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

3225

22-Dec-15 10:00:38

397

4.3

End Snap:

3226

22-Dec-15 11:00:40

397

4.3

Elapsed:

60.04 (mins)

DB Time:

411.98 (mins)

Report Summary

Load Profile

Per Second

Per Transaction

Per Exec

Per Call

DB Time(s):

6.9

1.2

0.16

0.05

DB CPU(s):

1.1

0.2

0.02

0.01

Redo size (bytes):

16,207.4

2,872.7

Logical read (blocks):

191,080.4

33,868.5

Block changes:

66.2

11.7

Physical read (blocks):

3.6

0.6

Physical write (blocks):

7.5

1.3

Read IO requests:

3.1

0.5

Write IO requests:

5.5

1.0

Read IO (MB):

0.0

0.0

Write IO (MB):

0.1

0.0

User calls:

139.3

24.7

Parses (SQL):

43.4

7.7

Hard parses (SQL):

1.2

0.2

SQL Work Area (MB):

10.7

1.9

Logons:

0.1

0.0

Executes (SQL):

44.0

7.8

Rollbacks:

0.0

0.0

Transactions:

5.6

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:

100.00

Redo NoWait %:

100.00

Buffer Hit %:

100.00

In-memory Sort %:

100.00

Library Hit %:

92.94

Soft Parse %:

97.21

Execute to Parse %:

1.32

Latch Hit %:

99.03

Parse CPU to Parse Elapsd %:

44.69

% Non-Parse CPU:

99.78

Top 10 Foreground Events by Total Wait Time

Event

Waits

Total Wait Time (sec)

Wait Avg(ms)

% DB time

Wait Class

DB CPU

3919.3

15.9

log file sync

21,691

267.1

12

1.1

Commit

latch: cache buffers chains

8,831

234.5

27

.9

Concurrency

latch free

634

55.9

88

.2

Other

latch: row cache objects

57

6.8

120

.0

Concurrency

SQL*Net more data from client

34,303

5.4

0

.0

Network

direct path write

3,160

3.4

1

.0

User I/O

db file sequential read

6,054

3.3

1

.0

User I/O

direct path read

1,419

2.1

1

.0

User I/O

direct path write temp

456

1.8

4

.0

User I/O

2.2 TOP SQL分析

对TOP SQL的SQL文本进行分析,这些SQL的文本事实上一致,因未使用绑定变量,只在一些WHERE条件中不同;

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 41.8% of Total DB Time (s): 24,719
  • Captured PL/SQL account for 0.0% of Total DB Time (s): 24,719

Elapsed Time (s)

Executions

Elapsed Time per Exec (s)

%Total

%CPU

%IO

SQL Id

SQL Module

SQL Text

395.58

174

2.27

1.60

9.32

0.00

0d6bs691axat2

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

384.70

174

2.21

1.56

9.60

0.00

2t17p90398bgm

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

372.13

170

2.19

1.51

9.69

0.00

c3hfqa71x2wnj

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

368.59

168

2.19

1.49

9.61

0.00

g355wgm5rwwct

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

328.79

147

2.24

1.33

9.13

0.00

28svs4892645j

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

323.10

139

2.32

1.31

8.73

0.00

d425ycaph4h91

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

288.47

150

1.92

1.17

10.83

0.00

8d475rkukd96y

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

283.48

147

1.93

1.15

10.66

0.00

by6yhma9213kf

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

279.30

146

1.91

1.13

10.81

0.00

68yatk0v372xa

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

256.53

102

2.51

1.04

7.99

0.00

drbr71c41vurh

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

249.43

100

2.49

1.01

8.06

0.00

3kdvnwcabvbn7

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

248.35

105

2.37

1.00

8.54

0.00

c8tbxg8w112ck

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

248.29

88

2.82

1.00

6.98

0.00

a7upv2a7xk2m3

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

247.88

97

2.56

1.00

7.94

0.00

54cr5xm5vvcky

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

2.3 TOP SQL的执行计划的分析

对TOP SQL的SQL文本进行分析,这些SQL的文本基本一致,只在一些WHERE条件中不同;未使用绑定变量; 如下选择TOP SQL中的一个,查看执行计划;

可以发现此SQL所有执行步骤均为全表扫描

SQL ID: 28qr32mp9wsrx

#

Plan Hash Value

Total Elapsed Time(ms)

Executions

1st Capture Snap ID

Last Capture Snap ID

1

3798866913

1,115,930

862

3224

3230


Back to Top

Plan 1(PHV: 3798866913)

Back to Top

Plan Statistics

  • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100

Stat Name

Statement Total

Per Execution

% Snap Total

Elapsed Time (ms)

1,115,930

1,294.58

0.09

CPU Time (ms)

245,197

284.45

0.10

Executions

862

Buffer Gets

38,294,634

44,425.33

0.09

Disk Reads

0

0.00

0.00

Parse Calls

862

1.00

0.01

Rows

55,903

64.85

User I/O Wait Time (ms)

0

Cluster Wait Time (ms)

0

Application Wait Time (ms)

0

Concurrency Wait Time (ms)

3,298

Invalidations

0

Version Count

10

Sharable Mem(KB)

2,849

Back to Plan 1(PHV: 3798866913) 
Back to Top

Execution Plan

Id

Operation

Name

Rows

Bytes

Cost (%CPU)

Time

0

SELECT STATEMENT

13639 (100)

1

   SORT UNIQUE

31

7356

13639 (2)

00:02:44

2

     UNION-ALL

3

       FILTER

4

         HASH JOIN

9

2682

2138 (2)

00:00:26

5

           TABLE ACCESS FULL

AAAAA

7941

1675K

1482 (1)

00:00:18

6

           TABLE ACCESS FULL

bbbbb

60966

4882K

655 (2)

00:00:08

7

         SORT AGGREGATE

1

39

8

           TABLE ACCESS FULL

BTOP_TRADEREVIEW_PROHIBITLEVEL

1

39

3 (0)

00:00:01

9

       HASH JOIN

9

2412

2580 (1)

00:00:31

10

         TABLE ACCESS FULL

AAAAA

216

46656

1480 (1)

00:00:18

11

         TABLE ACCESS FULL

BTOP_TASKINFO_AUTH

8568

435K

1100 (1)

00:00:14

12

       HASH JOIN

1

326

1491 (2)

00:00:18

13

         TABLE ACCESS FULL

BTOP_TASKINFO_AUDIT

1

110

2 (0)

00:00:01

14

         TABLE ACCESS FULL

AAAAA

210

45360

1489 (2)

00:00:18

15

       TABLE ACCESS FULL

AAAAA

1

216

1481 (1)

00:00:18

16

       TABLE ACCESS FULL

AAAAA

1

216

1481 (1)

00:00:18

17

       TABLE ACCESS FULL

AAAAA

5

1080

1486 (2)

00:00:18

18

       TABLE ACCESS FULL

AAAAA

12

2592

1485 (2)

00:00:18

19

       TABLE ACCESS FULL

AAAAA

1

216

1489 (2)

00:00:18

三、 2016/2/1 数据库运行情况分析

3.1 数据库整体运行情况

从如下可以看到每秒逻辑读为417,522,每秒事务量10.1;TOP 等待事件主要为latch: cache buffers chains与log file sync;

DB Name

DB Id

Instance

Inst num

Startup Time

Release

RAC

ORCL

123456

orcl

1

04-Oct-15 23:10

11.2.0.4.0

NO

Host Name

Platform

CPUs

Cores

Sockets

Memory (GB)

test2

AIX-Based Systems (64-bit)

16

4

30.75

Snap Id

Snap Time

Sessions

Cursors/Session

Begin Snap:

4233

02-Feb-16 10:00:39

389

2.9

End Snap:

4234

02-Feb-16 11:00:43

391

3.0

Elapsed:

60.06 (mins)

DB Time:

1,482.68 (mins)

Report Summary

Load Profile

Per Second

Per Transaction

Per Exec

Per Call

DB Time(s):

24.7

2.4

0.35

0.11

DB CPU(s):

2.3

0.2

0.03

0.01

Redo size (bytes):

29,471.5

2,915.1

Logical read (blocks):

417,522.4

41,297.7

Block changes:

123.2

12.2

Physical read (blocks):

3.3

0.3

Physical write (blocks):

11.7

1.2

Read IO requests:

3.3

0.3

Write IO requests:

8.7

0.9

Read IO (MB):

0.0

0.0

Write IO (MB):

0.1

0.0

User calls:

217.5

21.5

Parses (SQL):

69.3

6.9

Hard parses (SQL):

3.2

0.3

SQL Work Area (MB):

11.5

1.1

Logons:

0.2

0.0

Executes (SQL):

69.9

6.9

Rollbacks:

0.0

0.0

Transactions:

10.1

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:

100.00

Redo NoWait %:

100.00

Buffer Hit %:

100.00

In-memory Sort %:

100.00

Library Hit %:

89.65

Soft Parse %:

95.45

Execute to Parse %:

0.82

Latch Hit %:

99.31

Parse CPU to Parse Elapsd %:

29.83

% Non-Parse CPU:

99.79

Top 10 Foreground Events by Total Wait Time

Event

Waits

Total Wait Time (sec)

Wait Avg(ms)

% DB time

Wait Class

DB CPU

8145

9.2

log file sync

38,590

605.8

16

.7

Commit

latch free

3,057

344.8

113

.4

Other

latch: cache buffers chains

7,101

295.2

42

.3

Concurrency

direct path write

5,237

18.6

4

.0

User I/O

latch: row cache objects

103

13.7

133

.0

Concurrency

SQL*Net more data from client

51,994

9

0

.0

Network

direct path read

2,355

8.5

4

.0

User I/O

db file sequential read

5,656

6.6

1

.0

User I/O

direct path sync

447

5.9

13

.0

User I/O

3.2 TOP SQL分析

对TOP SQL的SQL文本进行分析,这些SQL的文本事实上一致,因未使用绑定变量,只在一些WHERE条件中不同;同时与2015/12/22时的运行情况对比,均为相同的SQL语句。

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 28.1% of Total DB Time (s): 88,961
  • Captured PL/SQL account for 0.0% of Total DB Time (s): 88,961

Elapsed Time (s)

Executions

Elapsed Time per Exec (s)

%Total

%CPU

%IO

SQL Id

SQL Module

SQL Text

934.80

143

6.54

1.05

6.11

0.00

b4mvkymt6x631

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

891.14

158

5.64

1.00

5.77

0.00

6vb78hwpujtw2

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

846.64

124

6.83

0.95

5.07

0.00

21jctffk8wpm8

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

834.32

124

6.73

0.94

5.11

0.00

7pux7h0r69sfr

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

821.54

115

7.14

0.92

4.82

0.00

004sr56y39r84

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

814.09

122

6.67

0.92

5.16

0.00

dbmdkyrvadv8h

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

784.00

158

4.96

0.88

6.63

0.00

2fugh6yg23y5a

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

780.28

122

6.40

0.88

6.36

0.00

d4xy81g1hngrx

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

774.71

145

5.34

0.87

6.15

0.00

awz5cmdkm63u9

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

773.24

87

8.89

0.87

4.44

0.00

0mz6bj9j40hxz

JDBC Thin Client

select 'HANDLETASK' as TTY, co...

3.3 TOP SQL执行计划分析

可以发现此SQL所有执行步骤均为全表扫描。与2015/12/22时为同一问题。

SQL> select * from table(dbms_xplan.display_cursor('gzw4c4g963fkn',0,'advanced'));

PLAN_TABLE_OUTPUT

----------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  gzw4c4g963fkn, child number 0

-------------------------------------

select t.type as TT1,t.TASKDETAIL as TT2, t.* from AAAAA

t ,bbbbb r  where  t.id=r.tradetaskid and t.type='R' and

t.orgid='0953' and ((isdirectional = 0 and ( r.reviewlevel<=2 and

(select count(*) from BTOP_TRADEREVIEW_PROHIBITLEVEL where

reviewparamruleid = r.reviewparamruleid and prohibitlevel = '2')= 0))

or (isdirectional = 1 and DIRECTIONALTELLER = '11624')) and ((t.state

='00'  and t.transteller != '11624') or (t.state ='10' and

t.TRANSTELLER !='11624') or (t.state ='01' and r.reviewteller1!='11624'

and  t.transteller != '11624')) union select t.type as TT1,t.TASKDETAIL

as TT2,t.* from BTOP_TASKINFO_AUTH a,AAAAA t where

a.tradetaskid=t.id  and  (((isdirectional = 0 and (ISFEWCROSS = 0 and

a.authlevel<=2 )or (ISFEWCROSS = 1 and (a.authlevel='2'  or

a.authlevel+ t.isfewcrosslevel='2' ))) or (isdirectional = 1 and

DIRECTIONALTELLER = '11624'))) and a.transteller!='11624' and

((a.orgid='0953' and a.authmode='2' ) or (a.orgid in ('') and a.au

Plan hash value: 2464281938

--------------------------------------------------------------------------------------------------------

| Id  | Operation             | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |                                |       |       | 22709 (100)|          |

|   1 |  SORT UNIQUE          |                                |   216 | 46264 | 22709   (1)| 00:04:33 |

|   2 |   UNION-ALL           |                                |       |       |            |          |

|*  3 |    FILTER             |                                |       |       |            |          |

|*  4 |     HASH JOIN         |                                |    89 | 25276 |  3537   (1)| 00:00:43 |

|*  5 |      TABLE ACCESS FULL| AAAAA            |   169 | 34814 |  2474   (1)| 00:00:30 |

|   6 |      TABLE ACCESS FULL| bbbbb           | 28471 |  2168K|  1063   (1)| 00:00:13 |

|   7 |     SORT AGGREGATE    |                                |     1 |    37 |            |          |

|*  8 |      TABLE ACCESS FULL| BTOP_TRADEREVIEW_PROHIBITLEVEL |     1 |    37 |     3   (0)| 00:00:01 |

|*  9 |    HASH JOIN          |                                |    35 |  8855 |  4301   (1)| 00:00:52 |

|* 10 |     TABLE ACCESS FULL | BTOP_TASKINFO_AUTH             |  1325 | 62275 |  1823   (1)| 00:00:22 |

|* 11 |     TABLE ACCESS FULL | AAAAA            | 45048 |  9062K|  2478   (1)| 00:00:30 |

|* 12 |    HASH JOIN          |                                |     1 |   251 |  2483   (1)| 00:00:30 |

|* 13 |     TABLE ACCESS FULL | BTOP_TASKINFO_AUDIT            |     1 |    45 |     3   (0)| 00:00:01 |

|* 14 |     TABLE ACCESS FULL | AAAAA            | 22562 |  4538K|  2479   (1)| 00:00:30 |

|* 15 |    TABLE ACCESS FULL  | AAAAA            |     3 |   618 |  2474   (1)| 00:00:30 |

|* 16 |    TABLE ACCESS FULL  | AAAAA            |   105 | 21630 |  2474   (1)| 00:00:30 |

|* 17 |    TABLE ACCESS FULL  | AAAAA            |    60 | 12360 |  2477   (1)| 00:00:30 |

|* 18 |    TABLE ACCESS FULL  | AAAAA            |     6 |  1236 |  2476   (1)| 00:00:30 |

|* 19 |    TABLE ACCESS FULL  | AAAAA            |     5 |  1030 |  2478   (1)| 00:00:30 |

--------------------------------------------------------------------------------------------------------

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

-------------------------------------------------------------

   1 - SET$1

   3 - SEL$1

   5 - SEL$1 / T@SEL$1

   6 - SEL$1 / R@SEL$1

   7 - SEL$2

   8 - SEL$2 / BTOP_TRADEREVIEW_PROHIBITLEVEL@SEL$2

   9 - SEL$3

  10 - SEL$3 / A@SEL$3

  11 - SEL$3 / T@SEL$3

  12 - SEL$4

  13 - SEL$4 / A@SEL$4

  14 - SEL$4 / T@SEL$4

  15 - SEL$5 / T@SEL$5

  16 - SEL$6 / T@SEL$6

  17 - SEL$7 / T@SEL$7

  18 - SEL$8 / T@SEL$8

  19 - SEL$9 / T@SEL$9

Outline Data

-------------

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

      DB_VERSION('11.2.0.4')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$2")

      OUTLINE_LEAF(@"SEL$1")

      OUTLINE_LEAF(@"SEL$3")

      OUTLINE_LEAF(@"SEL$4")

      OUTLINE_LEAF(@"SEL$5")

      OUTLINE_LEAF(@"SEL$6")

      OUTLINE_LEAF(@"SEL$7")

      OUTLINE_LEAF(@"SEL$8")

      OUTLINE_LEAF(@"SEL$9")

      OUTLINE_LEAF(@"SET$1")

      FULL(@"SEL$9" "T"@"SEL$9")

      FULL(@"SEL$8" "T"@"SEL$8")

      FULL(@"SEL$7" "T"@"SEL$7")

      FULL(@"SEL$6" "T"@"SEL$6")

      FULL(@"SEL$5" "T"@"SEL$5")

      FULL(@"SEL$4" "A"@"SEL$4")

      FULL(@"SEL$4" "T"@"SEL$4")

      LEADING(@"SEL$4" "A"@"SEL$4" "T"@"SEL$4")

      USE_HASH(@"SEL$4" "T"@"SEL$4")

      FULL(@"SEL$3" "A"@"SEL$3")

      FULL(@"SEL$3" "T"@"SEL$3")

      LEADING(@"SEL$3" "A"@"SEL$3" "T"@"SEL$3")

      USE_HASH(@"SEL$3" "T"@"SEL$3")

      FULL(@"SEL$1" "T"@"SEL$1")

      FULL(@"SEL$1" "R"@"SEL$1")

      LEADING(@"SEL$1" "T"@"SEL$1" "R"@"SEL$1")

      USE_HASH(@"SEL$1" "R"@"SEL$1")

      FULL(@"SEL$2" "BTOP_TRADEREVIEW_PROHIBITLEVEL"@"SEL$2")

      END_OUTLINE_DATA

  */

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter((("ISDIRECTIONAL"=0 AND =0 AND TO_NUMBER("R"."REVIEWLEVEL")<=2) OR

              ("ISDIRECTIONAL"=1 AND "DIRECTIONALTELLER"='11624')))

   4 - access("T"."ID"="R"."TRADETASKID")

       filter((("T"."STATE"='00' AND "T"."TRANSTELLER"<>'11624') OR ("T"."STATE"='10' AND

              "T"."TRANSTELLER"<>'11624') OR ("T"."STATE"='01' AND "R"."REVIEWTELLER1"<>'11624' AND

              "T"."TRANSTELLER"<>'11624')))

   5 - filter(("T"."ORGID"='0953' AND "T"."TYPE"='R'))

   8 - filter(("PROHIBITLEVEL"='2' AND "REVIEWPARAMRULEID"=:B1))

   9 - access("A"."TRADETASKID"="T"."ID")

       filter((("ISDIRECTIONAL"=0 AND "ISFEWCROSS"=0 AND TO_NUMBER("A"."AUTHLEVEL")<=2) OR

              ("ISFEWCROSS"=1 AND ("A"."AUTHLEVEL"='2' OR TO_NUMBER("A"."AUTHLEVEL")+"T"."ISFEWCROSSLEVEL"=2))

               OR ("ISDIRECTIONAL"=1 AND "DIRECTIONALTELLER"='11624')))

  10 - filter(("A"."ORGID"='0953' AND "A"."AUTHMODE"='2' AND "A"."TRANSTELLER"<>'11624'))

  11 - filter(("T"."STATE"='00' OR "T"."STATE"='10'))

  12 - access("A"."TRADETASKID"="T"."ID")

  13 - filter(("A"."ORGID"='0953' AND "A"."TRANSTELLER"<>'11624'))

  14 - filter(("T"."STATE"='00' OR ("T"."TASKHANDLETELLER"='11624' AND "T"."STATE"='10')))

  15 - filter(("T"."TRANSTELLER"='11624' AND "T"."WORKDATE"='20160203' AND "T"."STATE"<>'21'

              AND "T"."STATE"<>'22'))

  16 - filter(("T"."TRANSTELLER"='11624' AND INTERNAL_FUNCTION("T"."STATE")))

  17 - filter(((("T"."ORGID"='0953' AND "T"."ISDIRECTIONAL"=0) OR

              ("T"."DIRECTIONALTELLER"='11624' AND "T"."ISDIRECTIONAL"=1)) AND ("T"."TRANSTELLER" IS NULL OR

              "T"."TYPE"='I' OR "T"."TRANSTELLER"='11624') AND INTERNAL_FUNCTION("T"."STATE") AND

              "T"."TYPE"<>'A' AND "T"."TYPE"<>'R' AND "T"."TYPE"<>'S' AND "T"."TYPE"<>'0'))

  18 - filter(("T"."TYPE"='0' AND ("T"."TRANSTELLER" IS NULL OR "T"."TRANSTELLER"='11624') AND

              (("T"."ORGID"='0953' AND "T"."ISDIRECTIONAL"=0) OR ("T"."DIRECTIONALTELLER"='11624' AND

              "T"."ISDIRECTIONAL"=1)) AND INTERNAL_FUNCTION("T"."STATE")))

  19 - filter((("T"."LASTMODIFYDATE"='20160203' AND (("T"."TASKHANDLETELLER"='11624' AND

              "T"."STATE"<>'00' AND "T"."STATE"<>'10' AND "T"."TYPE"<>'I') OR ("T"."TASKHANDLETELLER"='11624'

              AND "T"."TYPE"='I' AND "T"."STATE"<>'00'))) OR ("T"."TRANSTELLER"='11624' AND

              "T"."LASTMODIFYDATE"='20160203' AND "T"."STATE"='20')))

Column Projection Information (identified by operation id):

-----------------------------------------------------------

   1 - (#keys=47) STRDEF[32], STRDEF[32], STRDEF[144], STRDEF[32], STRDEF[32], STRDEF[32],

       STRDEF[16], STRDEF[32], STRDEF[140], STRDEF[32], STRDEF[32], STRDEF[32], STRDEF[32],

       STRDEF[32], STRDEF[32], STRDEF[32], STRDEF[20], STRDEF[30], STRDEF[22], STRDEF[32], STRDEF[22],

       STRDEF[22], STRDEF[32], STRDEF[32], STRDEF[32], STRDEF[22], STRDEF[22], STRDEF[32], STRDEF[22],

       STRDEF[144], STRDEF[32], STRDEF[400], STRDEF[32], STRDEF[32], STRDEF[32], STRDEF[32],

       STRDEF[200], STRDEF[200], STRDEF[200], STRDEF[2400], STRDEF[200], STRDEF[200], STRDEF[200],

       STRDEF[200], STRDEF[200], STRDEF[2400], STRDEF[256]

   2 - STRDEF[32], STRDEF[32], STRDEF[144], STRDEF[32], STRDEF[32], STRDEF[32], STRDEF[16],

       STRDEF[32], STRDEF[140], STRDEF[32], STRDEF[32], STRDEF[32], STRDEF[32], STRDEF[32],

       STRDEF[32], STRDEF[32], STRDEF[20], STRDEF[30], STRDEF[22], STRDEF[32], STRDEF[22], STRDEF[22],

       STRDEF[32], STRDEF[32], STRDEF[32], STRDEF[22], STRDEF[22], STRDEF[32], STRDEF[22],

       STRDEF[144], STRDEF[32], STRDEF[400], STRDEF[32], STRDEF[32], STRDEF[32], STRDEF[32],

       STRDEF[200], STRDEF[200], STRDEF[200], STRDEF[2400], STRDEF[200], STRDEF[200], STRDEF[200],

       STRDEF[200], STRDEF[200], STRDEF[2400], STRDEF[256]

   3 - "T"."ID"[VARCHAR2,144], "T"."SSERIALNO"[VARCHAR2,256], "T"."WORKDATE"[VARCHAR2,32],

       "T"."TYPE"[VARCHAR2,32], "T"."TASKDETAIL"[VARCHAR2,32], "T"."STATE"[VARCHAR2,16],

       "T"."CHNDATE"[VARCHAR2,32], "T"."CSERIALNO"[VARCHAR2,140], "T"."MENUCODE"[VARCHAR2,32],

       "T"."ORGID"[VARCHAR2,32], "T"."TRANSTELLER"[VARCHAR2,32], "T"."CREATEDATE"[VARCHAR2,32],

       "T"."CREATETIME"[VARCHAR2,32], "T"."LASTMODIFYDATE"[VARCHAR2,32],

       "T"."LASTMODIFYTIME"[VARCHAR2,32], "T"."AMT"[VARCHAR2,20], "T"."CUSTOMERACCT"[VARCHAR2,30],

       "ISDIRECTIONAL"[NUMBER,22], "DIRECTIONALTELLER"[VARCHAR2,32], "T"."ISFEWCROSS"[NUMBER,22],

       "T"."ISFEWCROSSLEVEL"[NUMBER,22], "T"."OPENMENU"[VARCHAR2,32], "T"."TRADETYPE"[VARCHAR2,32],

       "T"."AFRESHMENU"[VARCHAR2,32], "T"."ISALLOWDIRECTIONAL"[NUMBER,22],

       "T"."TASKMANAGELEVEL"[NUMBER,22], "T"."TASKHANDLETELLER"[VARCHAR2,32],

       "T"."ISADMINHANDLE"[NUMBER,22], "T"."MSGDETAILFLOW"[VARCHAR2,144],

       "T"."ADMINDATE"[VARCHAR2,32], "T"."TRADENAME"[VARCHAR2,400], "T"."SNDRCVFLG"[VARCHAR2,32],

       "T"."TBILLTYP"[VARCHAR2,32], "T"."AUTHDT"[VARCHAR2,32], "T"."CHANID"[VARCHAR2,32],

       "T"."NOTE01"[VARCHAR2,200], "T"."NOTE02"[VARCHAR2,200], "T"."NOTE03"[VARCHAR2,200],

       "T"."NOTE04"[VARCHAR2,2400], "T"."NOTE05"[VARCHAR2,200], "T"."NOTE06"[VARCHAR2,200],

       "T"."NOTE07"[VARCHAR2,200], "T"."SENDBANKCODE"[VARCHAR2,200], "T"."MSGID"[VARCHAR2,200],

       "T"."RMKINFO"[VARCHAR2,2400]

   4 - (#keys=1) "T"."ID"[VARCHAR2,144], "T"."SSERIALNO"[VARCHAR2,256],

       "T"."WORKDATE"[VARCHAR2,32], "T"."TYPE"[VARCHAR2,32], "T"."TASKDETAIL"[VARCHAR2,32],

       "T"."STATE"[VARCHAR2,16], "T"."CHNDATE"[VARCHAR2,32], "T"."CSERIALNO"[VARCHAR2,140],

       "T"."MENUCODE"[VARCHAR2,32], "T"."ORGID"[VARCHAR2,32], "T"."TRANSTELLER"[VARCHAR2,32],

       "T"."CREATEDATE"[VARCHAR2,32], "T"."CREATETIME"[VARCHAR2,32],

       "T"."LASTMODIFYDATE"[VARCHAR2,32], "T"."LASTMODIFYTIME"[VARCHAR2,32], "T"."AMT"[VARCHAR2,20],

       "T"."CUSTOMERACCT"[VARCHAR2,30], "ISDIRECTIONAL"[NUMBER,22], "DIRECTIONALTELLER"[VARCHAR2,32],

       "T"."ISFEWCROSS"[NUMBER,22], "T"."ISFEWCROSSLEVEL"[NUMBER,22], "T"."OPENMENU"[VARCHAR2,32],

       "T"."TRADETYPE"[VARCHAR2,32], "T"."AFRESHMENU"[VARCHAR2,32],

       "T"."ISALLOWDIRECTIONAL"[NUMBER,22], "T"."TASKMANAGELEVEL"[NUMBER,22],

       "T"."TASKHANDLETELLER"[VARCHAR2,32], "T"."ISADMINHANDLE"[NUMBER,22],

       "T"."MSGDETAILFLOW"[VARCHAR2,144], "T"."ADMINDATE"[VARCHAR2,32], "T"."TRADENAME"[VARCHAR2,400],

       "T"."SNDRCVFLG"[VARCHAR2,32], "T"."TBILLTYP"[VARCHAR2,32], "T"."AUTHDT"[VARCHAR2,32],

       "T"."CHANID"[VARCHAR2,32], "T"."NOTE01"[VARCHAR2,200], "T"."NOTE02"[VARCHAR2,200],

       "T"."NOTE03"[VARCHAR2,200], "T"."NOTE04"[VARCHAR2,2400], "T"."NOTE05"[VARCHAR2,200],

       "T"."NOTE06"[VARCHAR2,200], "T"."NOTE07"[VARCHAR2,200], "T"."SENDBANKCODE"[VARCHAR2,200],

       "T"."MSGID"[VARCHAR2,200], "T"."RMKINFO"[VARCHAR2,2400], "R"."REVIEWLEVEL"[VARCHAR2,32],

       "R"."REVIEWPARAMRULEID"[VARCHAR2,144]

   5 - "T"."ID"[VARCHAR2,144], "T"."WORKDATE"[VARCHAR2,32], "T"."TYPE"[VARCHAR2,32],

       "T"."TASKDETAIL"[VARCHAR2,32], "T"."STATE"[VARCHAR2,16], "T"."CHNDATE"[VARCHAR2,32],

       "T"."CSERIALNO"[VARCHAR2,140], "T"."MENUCODE"[VARCHAR2,32], "T"."ORGID"[VARCHAR2,32],

       "T"."TRANSTELLER"[VARCHAR2,32], "T"."CREATEDATE"[VARCHAR2,32], "T"."CREATETIME"[VARCHAR2,32],

       "T"."LASTMODIFYDATE"[VARCHAR2,32], "T"."LASTMODIFYTIME"[VARCHAR2,32], "T"."AMT"[VARCHAR2,20],

       "T"."CUSTOMERACCT"[VARCHAR2,30], "ISDIRECTIONAL"[NUMBER,22], "DIRECTIONALTELLER"[VARCHAR2,32],

       "T"."ISFEWCROSS"[NUMBER,22], "T"."ISFEWCROSSLEVEL"[NUMBER,22], "T"."OPENMENU"[VARCHAR2,32],

       "T"."TRADETYPE"[VARCHAR2,32], "T"."AFRESHMENU"[VARCHAR2,32],

       "T"."ISALLOWDIRECTIONAL"[NUMBER,22], "T"."TASKMANAGELEVEL"[NUMBER,22],

       "T"."TASKHANDLETELLER"[VARCHAR2,32], "T"."ISADMINHANDLE"[NUMBER,22],

       "T"."MSGDETAILFLOW"[VARCHAR2,144], "T"."ADMINDATE"[VARCHAR2,32], "T"."TRADENAME"[VARCHAR2,400],

       "T"."SNDRCVFLG"[VARCHAR2,32], "T"."TBILLTYP"[VARCHAR2,32], "T"."AUTHDT"[VARCHAR2,32],

       "T"."CHANID"[VARCHAR2,32], "T"."NOTE01"[VARCHAR2,200], "T"."NOTE02"[VARCHAR2,200],

       "T"."NOTE03"[VARCHAR2,200], "T"."NOTE04"[VARCHAR2,2400], "T"."NOTE05"[VARCHAR2,200],

       "T"."NOTE06"[VARCHAR2,200], "T"."NOTE07"[VARCHAR2,200], "T"."SENDBANKCODE"[VARCHAR2,200],

       "T"."MSGID"[VARCHAR2,200], "T"."RMKINFO"[VARCHAR2,2400], "T"."SSERIALNO"[VARCHAR2,256]

   6 - "R"."TRADETASKID"[VARCHAR2,144], "R"."REVIEWPARAMRULEID"[VARCHAR2,144],

       "R"."REVIEWTELLER1"[VARCHAR2,32], "R"."REVIEWLEVEL"[VARCHAR2,32]

   7 - (#keys=0) COUNT(*)[22]

   9 - (#keys=1) "T"."ID"[VARCHAR2,144], "T"."SSERIALNO"[VARCHAR2,256],

       "T"."WORKDATE"[VARCHAR2,32], "T"."TYPE"[VARCHAR2,32], "T"."TASKDETAIL"[VARCHAR2,32],

       "T"."STATE"[VARCHAR2,16], "T"."CHNDATE"[VARCHAR2,32], "T"."CSERIALNO"[VARCHAR2,140],

       "T"."MENUCODE"[VARCHAR2,32], "T"."ORGID"[VARCHAR2,32], "T"."TRANSTELLER"[VARCHAR2,32],

       "T"."CREATEDATE"[VARCHAR2,32], "T"."CREATETIME"[VARCHAR2,32],

       "T"."LASTMODIFYDATE"[VARCHAR2,32], "T"."LASTMODIFYTIME"[VARCHAR2,32], "T"."AMT"[VARCHAR2,20],

       "T"."CUSTOMERACCT"[VARCHAR2,30], "ISDIRECTIONAL"[NUMBER,22], "DIRECTIONALTELLER"[VARCHAR2,32],

       "ISFEWCROSS"[NUMBER,22], "T"."ISFEWCROSSLEVEL"[NUMBER,22], "T"."OPENMENU"[VARCHAR2,32],

       "T"."TRADETYPE"[VARCHAR2,32], "T"."AFRESHMENU"[VARCHAR2,32],

       "T"."ISALLOWDIRECTIONAL"[NUMBER,22], "T"."TASKMANAGELEVEL"[NUMBER,22],

       "T"."TASKHANDLETELLER"[VARCHAR2,32], "T"."ISADMINHANDLE"[NUMBER,22],

       "T"."MSGDETAILFLOW"[VARCHAR2,144], "T"."ADMINDATE"[VARCHAR2,32], "T"."TRADENAME"[VARCHAR2,400],

       "T"."SNDRCVFLG"[VARCHAR2,32], "T"."TBILLTYP"[VARCHAR2,32], "T"."AUTHDT"[VARCHAR2,32],

       "T"."CHANID"[VARCHAR2,32], "T"."NOTE01"[VARCHAR2,200], "T"."NOTE02"[VARCHAR2,200],

       "T"."NOTE03"[VARCHAR2,200], "T"."NOTE04"[VARCHAR2,2400], "T"."NOTE05"[VARCHAR2,200],

       "T"."NOTE06"[VARCHAR2,200], "T"."NOTE07"[VARCHAR2,200], "T"."SENDBANKCODE"[VARCHAR2,200],

       "T"."MSGID"[VARCHAR2,200], "T"."RMKINFO"[VARCHAR2,2400]

  10 - "A"."TRADETASKID"[VARCHAR2,144], "A"."AUTHLEVEL"[VARCHAR2,32]

  11 - "T"."ID"[VARCHAR2,144], "T"."WORKDATE"[VARCHAR2,32], "T"."TYPE"[VARCHAR2,32],

       "T"."TASKDETAIL"[VARCHAR2,32], "T"."STATE"[VARCHAR2,16], "T"."CHNDATE"[VARCHAR2,32],

       "T"."CSERIALNO"[VARCHAR2,140], "T"."MENUCODE"[VARCHAR2,32], "T"."ORGID"[VARCHAR2,32],

       "T"."TRANSTELLER"[VARCHAR2,32], "T"."CREATEDATE"[VARCHAR2,32], "T"."CREATETIME"[VARCHAR2,32],

       "T"."LASTMODIFYDATE"[VARCHAR2,32], "T"."LASTMODIFYTIME"[VARCHAR2,32], "T"."AMT"[VARCHAR2,20],

       "T"."CUSTOMERACCT"[VARCHAR2,30], "ISDIRECTIONAL"[NUMBER,22], "DIRECTIONALTELLER"[VARCHAR2,32],

       "ISFEWCROSS"[NUMBER,22], "T"."ISFEWCROSSLEVEL"[NUMBER,22], "T"."OPENMENU"[VARCHAR2,32],

       "T"."TRADETYPE"[VARCHAR2,32], "T"."AFRESHMENU"[VARCHAR2,32],

       "T"."ISALLOWDIRECTIONAL"[NUMBER,22], "T"."TASKMANAGELEVEL"[NUMBER,22],

       "T"."TASKHANDLETELLER"[VARCHAR2,32], "T"."ISADMINHANDLE"[NUMBER,22],

       "T"."MSGDETAILFLOW"[VARCHAR2,144], "T"."ADMINDATE"[VARCHAR2,32], "T"."TRADENAME"[VARCHAR2,400],

       "T"."SNDRCVFLG"[VARCHAR2,32], "T"."TBILLTYP"[VARCHAR2,32], "T"."AUTHDT"[VARCHAR2,32],

       "T"."CHANID"[VARCHAR2,32], "T"."NOTE01"[VARCHAR2,200], "T"."NOTE02"[VARCHAR2,200],

       "T"."NOTE03"[VARCHAR2,200], "T"."NOTE04"[VARCHAR2,2400], "T"."NOTE05"[VARCHAR2,200],

       "T"."NOTE06"[VARCHAR2,200], "T"."NOTE07"[VARCHAR2,200], "T"."SENDBANKCODE"[VARCHAR2,200],

       "T"."MSGID"[VARCHAR2,200], "T"."RMKINFO"[VARCHAR2,2400], "T"."SSERIALNO"[VARCHAR2,256]

  12 - (#keys=1) "T"."ID"[VARCHAR2,144], "T"."SSERIALNO"[VARCHAR2,256],

       "T"."WORKDATE"[VARCHAR2,32], "T"."TYPE"[VARCHAR2,32], "T"."TASKDETAIL"[VARCHAR2,32],

       "T"."STATE"[VARCHAR2,16], "T"."CHNDATE"[VARCHAR2,32], "T"."CSERIALNO"[VARCHAR2,140],

       "T"."MENUCODE"[VARCHAR2,32], "T"."ORGID"[VARCHAR2,32], "T"."TRANSTELLER"[VARCHAR2,32],

       "T"."CREATEDATE"[VARCHAR2,32], "T"."CREATETIME"[VARCHAR2,32],

       "T"."LASTMODIFYDATE"[VARCHAR2,32], "T"."LASTMODIFYTIME"[VARCHAR2,32], "T"."AMT"[VARCHAR2,20],

       "T"."CUSTOMERACCT"[VARCHAR2,30], "T"."ISDIRECTIONAL"[NUMBER,22],

       "T"."DIRECTIONALTELLER"[VARCHAR2,32], "T"."ISFEWCROSS"[NUMBER,22],

       "T"."ISFEWCROSSLEVEL"[NUMBER,22], "T"."OPENMENU"[VARCHAR2,32], "T"."TRADETYPE"[VARCHAR2,32],

       "T"."AFRESHMENU"[VARCHAR2,32], "T"."ISALLOWDIRECTIONAL"[NUMBER,22],

       "T"."TASKMANAGELEVEL"[NUMBER,22], "T"."TASKHANDLETELLER"[VARCHAR2,32],

       "T"."ISADMINHANDLE"[NUMBER,22], "T"."MSGDETAILFLOW"[VARCHAR2,144],

       "T"."ADMINDATE"[VARCHAR2,32], "T"."TRADENAME"[VARCHAR2,400], "T"."SNDRCVFLG"[VARCHAR2,32],

       "T"."TBILLTYP"[VARCHAR2,32], "T"."AUTHDT"[VARCHAR2,32], "T"."CHANID"[VARCHAR2,32],

       "T"."NOTE01"[VARCHAR2,200], "T"."NOTE02"[VARCHAR2,200], "T"."NOTE03"[VARCHAR2,200],

       "T"."NOTE04"[VARCHAR2,2400], "T"."NOTE05"[VARCHAR2,200], "T"."NOTE06"[VARCHAR2,200],

       "T"."NOTE07"[VARCHAR2,200], "T"."SENDBANKCODE"[VARCHAR2,200], "T"."MSGID"[VARCHAR2,200],

       "T"."RMKINFO"[VARCHAR2,2400]

  13 - "A"."TRADETASKID"[VARCHAR2,144]

  14 - "T"."ID"[VARCHAR2,144], "T"."WORKDATE"[VARCHAR2,32], "T"."TYPE"[VARCHAR2,32],

       "T"."TASKDETAIL"[VARCHAR2,32], "T"."STATE"[VARCHAR2,16], "T"."CHNDATE"[VARCHAR2,32],

       "T"."CSERIALNO"[VARCHAR2,140], "T"."MENUCODE"[VARCHAR2,32], "T"."ORGID"[VARCHAR2,32],

       "T"."TRANSTELLER"[VARCHAR2,32], "T"."CREATEDATE"[VARCHAR2,32], "T"."CREATETIME"[VARCHAR2,32],

       "T"."LASTMODIFYDATE"[VARCHAR2,32], "T"."LASTMODIFYTIME"[VARCHAR2,32], "T"."AMT"[VARCHAR2,20],

       "T"."CUSTOMERACCT"[VARCHAR2,30], "T"."ISDIRECTIONAL"[NUMBER,22],

       "T"."DIRECTIONALTELLER"[VARCHAR2,32], "T"."ISFEWCROSS"[NUMBER,22],

       "T"."ISFEWCROSSLEVEL"[NUMBER,22], "T"."OPENMENU"[VARCHAR2,32], "T"."TRADETYPE"[VARCHAR2,32],

       "T"."AFRESHMENU"[VARCHAR2,32], "T"."ISALLOWDIRECTIONAL"[NUMBER,22],

       "T"."TASKMANAGELEVEL"[NUMBER,22], "T"."TASKHANDLETELLER"[VARCHAR2,32],

       "T"."ISADMINHANDLE"[NUMBER,22], "T"."MSGDETAILFLOW"[VARCHAR2,144],

       "T"."ADMINDATE"[VARCHAR2,32], "T"."TRADENAME"[VARCHAR2,400], "T"."SNDRCVFLG"[VARCHAR2,32],

       "T"."TBILLTYP"[VARCHAR2,32], "T"."AUTHDT"[VARCHAR2,32], "T"."CHANID"[VARCHAR2,32],

       "T"."NOTE01"[VARCHAR2,200], "T"."NOTE02"[VARCHAR2,200], "T"."NOTE03"[VARCHAR2,200],

       "T"."NOTE04"[VARCHAR2,2400], "T"."NOTE05"[VARCHAR2,200], "T"."NOTE06"[VARCHAR2,200],

       "T"."NOTE07"[VARCHAR2,200], "T"."SENDBANKCODE"[VARCHAR2,200], "T"."MSGID"[VARCHAR2,200],

       "T"."RMKINFO"[VARCHAR2,2400], "T"."SSERIALNO"[VARCHAR2,256]

  15 - "T"."ID"[VARCHAR2,144], "T"."WORKDATE"[VARCHAR2,32], "T"."TYPE"[VARCHAR2,32],

       "T"."TASKDETAIL"[VARCHAR2,32], "T"."STATE"[VARCHAR2,16], "T"."CHNDATE"[VARCHAR2,32],

       "T"."CSERIALNO"[VARCHAR2,140], "T"."MENUCODE"[VARCHAR2,32], "T"."ORGID"[VARCHAR2,32],

       "T"."TRANSTELLER"[VARCHAR2,32], "T"."CREATEDATE"[VARCHAR2,32], "T"."CREATETIME"[VARCHAR2,32],

       "T"."LASTMODIFYDATE"[VARCHAR2,32], "T"."LASTMODIFYTIME"[VARCHAR2,32], "T"."AMT"[VARCHAR2,20],

       "T"."CUSTOMERACCT"[VARCHAR2,30], "T"."ISDIRECTIONAL"[NUMBER,22],

       "T"."DIRECTIONALTELLER"[VARCHAR2,32], "T"."ISFEWCROSS"[NUMBER,22],

       "T"."ISFEWCROSSLEVEL"[NUMBER,22], "T"."OPENMENU"[VARCHAR2,32], "T"."TRADETYPE"[VARCHAR2,32],

       "T"."AFRESHMENU"[VARCHAR2,32], "T"."ISALLOWDIRECTIONAL"[NUMBER,22],

       "T"."TASKMANAGELEVEL"[NUMBER,22], "T"."TASKHANDLETELLER"[VARCHAR2,32],

       "T"."ISADMINHANDLE"[NUMBER,22], "T"."MSGDETAILFLOW"[VARCHAR2,144],

       "T"."ADMINDATE"[VARCHAR2,32], "T"."TRADENAME"[VARCHAR2,400], "T"."SNDRCVFLG"[VARCHAR2,32],

       "T"."TBILLTYP"[VARCHAR2,32], "T"."AUTHDT"[VARCHAR2,32], "T"."CHANID"[VARCHAR2,32],

       "T"."NOTE01"[VARCHAR2,200], "T"."NOTE02"[VARCHAR2,200], "T"."NOTE03"[VARCHAR2,200],

       "T"."NOTE04"[VARCHAR2,2400], "T"."NOTE05"[VARCHAR2,200], "T"."NOTE06"[VARCHAR2,200],

       "T"."NOTE07"[VARCHAR2,200], "T"."SENDBANKCODE"[VARCHAR2,200], "T"."MSGID"[VARCHAR2,200],

       "T"."RMKINFO"[VARCHAR2,2400], "T"."SSERIALNO"[VARCHAR2,256]

  16 - "T"."ID"[VARCHAR2,144], "T"."WORKDATE"[VARCHAR2,32], "T"."TYPE"[VARCHAR2,32],

       "T"."TASKDETAIL"[VARCHAR2,32], "T"."STATE"[VARCHAR2,16], "T"."CHNDATE"[VARCHAR2,32],

       "T"."CSERIALNO"[VARCHAR2,140], "T"."MENUCODE"[VARCHAR2,32], "T"."ORGID"[VARCHAR2,32],

       "T"."TRANSTELLER"[VARCHAR2,32], "T"."CREATEDATE"[VARCHAR2,32], "T"."CREATETIME"[VARCHAR2,32],

       "T"."LASTMODIFYDATE"[VARCHAR2,32], "T"."LASTMODIFYTIME"[VARCHAR2,32], "T"."AMT"[VARCHAR2,20],

       "T"."CUSTOMERACCT"[VARCHAR2,30], "T"."ISDIRECTIONAL"[NUMBER,22],

       "T"."DIRECTIONALTELLER"[VARCHAR2,32], "T"."ISFEWCROSS"[NUMBER,22],

       "T"."ISFEWCROSSLEVEL"[NUMBER,22], "T"."OPENMENU"[VARCHAR2,32], "T"."TRADETYPE"[VARCHAR2,32],

       "T"."AFRESHMENU"[VARCHAR2,32], "T"."ISALLOWDIRECTIONAL"[NUMBER,22],

       "T"."TASKMANAGELEVEL"[NUMBER,22], "T"."TASKHANDLETELLER"[VARCHAR2,32],

       "T"."ISADMINHANDLE"[NUMBER,22], "T"."MSGDETAILFLOW"[VARCHAR2,144],

       "T"."ADMINDATE"[VARCHAR2,32], "T"."TRADENAME"[VARCHAR2,400], "T"."SNDRCVFLG"[VARCHAR2,32],

       "T"."TBILLTYP"[VARCHAR2,32], "T"."AUTHDT"[VARCHAR2,32], "T"."CHANID"[VARCHAR2,32],

       "T"."NOTE01"[VARCHAR2,200], "T"."NOTE02"[VARCHAR2,200], "T"."NOTE03"[VARCHAR2,200],

       "T"."NOTE04"[VARCHAR2,2400], "T"."NOTE05"[VARCHAR2,200], "T"."NOTE06"[VARCHAR2,200],

       "T"."NOTE07"[VARCHAR2,200], "T"."SENDBANKCODE"[VARCHAR2,200], "T"."MSGID"[VARCHAR2,200],

       "T"."RMKINFO"[VARCHAR2,2400], "T"."SSERIALNO"[VARCHAR2,256]

  17 - "T"."ID"[VARCHAR2,144], "T"."WORKDATE"[VARCHAR2,32], "T"."TYPE"[VARCHAR2,32],

       "T"."TASKDETAIL"[VARCHAR2,32], "T"."STATE"[VARCHAR2,16], "T"."CHNDATE"[VARCHAR2,32],

       "T"."CSERIALNO"[VARCHAR2,140], "T"."MENUCODE"[VARCHAR2,32], "T"."ORGID"[VARCHAR2,32],

       "T"."TRANSTELLER"[VARCHAR2,32], "T"."CREATEDATE"[VARCHAR2,32], "T"."CREATETIME"[VARCHAR2,32],

       "T"."LASTMODIFYDATE"[VARCHAR2,32], "T"."LASTMODIFYTIME"[VARCHAR2,32], "T"."AMT"[VARCHAR2,20],

       "T"."CUSTOMERACCT"[VARCHAR2,30], "T"."ISDIRECTIONAL"[NUMBER,22],

       "T"."DIRECTIONALTELLER"[VARCHAR2,32], "T"."ISFEWCROSS"[NUMBER,22],

       "T"."ISFEWCROSSLEVEL"[NUMBER,22], "T"."OPENMENU"[VARCHAR2,32], "T"."TRADETYPE"[VARCHAR2,32],

       "T"."AFRESHMENU"[VARCHAR2,32], "T"."ISALLOWDIRECTIONAL"[NUMBER,22],

       "T"."TASKMANAGELEVEL"[NUMBER,22], "T"."TASKHANDLETELLER"[VARCHAR2,32],

       "T"."ISADMINHANDLE"[NUMBER,22], "T"."MSGDETAILFLOW"[VARCHAR2,144],

       "T"."ADMINDATE"[VARCHAR2,32], "T"."TRADENAME"[VARCHAR2,400], "T"."SNDRCVFLG"[VARCHAR2,32],

       "T"."TBILLTYP"[VARCHAR2,32], "T"."AUTHDT"[VARCHAR2,32], "T"."CHANID"[VARCHAR2,32],

       "T"."NOTE01"[VARCHAR2,200], "T"."NOTE02"[VARCHAR2,200], "T"."NOTE03"[VARCHAR2,200],

       "T"."NOTE04"[VARCHAR2,2400], "T"."NOTE05"[VARCHAR2,200], "T"."NOTE06"[VARCHAR2,200],

       "T"."NOTE07"[VARCHAR2,200], "T"."SENDBANKCODE"[VARCHAR2,200], "T"."MSGID"[VARCHAR2,200],

       "T"."RMKINFO"[VARCHAR2,2400], "T"."SSERIALNO"[VARCHAR2,256]

  18 - "T"."ID"[VARCHAR2,144], "T"."WORKDATE"[VARCHAR2,32], "T"."TYPE"[VARCHAR2,32],

       "T"."TASKDETAIL"[VARCHAR2,32], "T"."STATE"[VARCHAR2,16], "T"."CHNDATE"[VARCHAR2,32],

       "T"."CSERIALNO"[VARCHAR2,140], "T"."MENUCODE"[VARCHAR2,32], "T"."ORGID"[VARCHAR2,32],

       "T"."TRANSTELLER"[VARCHAR2,32], "T"."CREATEDATE"[VARCHAR2,32], "T"."CREATETIME"[VARCHAR2,32],

       "T"."LASTMODIFYDATE"[VARCHAR2,32], "T"."LASTMODIFYTIME"[VARCHAR2,32], "T"."AMT"[VARCHAR2,20],

       "T"."CUSTOMERACCT"[VARCHAR2,30], "T"."ISDIRECTIONAL"[NUMBER,22],

       "T"."DIRECTIONALTELLER"[VARCHAR2,32], "T"."ISFEWCROSS"[NUMBER,22],

       "T"."ISFEWCROSSLEVEL"[NUMBER,22], "T"."OPENMENU"[VARCHAR2,32], "T"."TRADETYPE"[VARCHAR2,32],

       "T"."AFRESHMENU"[VARCHAR2,32], "T"."ISALLOWDIRECTIONAL"[NUMBER,22],

       "T"."TASKMANAGELEVEL"[NUMBER,22], "T"."TASKHANDLETELLER"[VARCHAR2,32],

       "T"."ISADMINHANDLE"[NUMBER,22], "T"."MSGDETAILFLOW"[VARCHAR2,144],

       "T"."ADMINDATE"[VARCHAR2,32], "T"."TRADENAME"[VARCHAR2,400], "T"."SNDRCVFLG"[VARCHAR2,32],

       "T"."TBILLTYP"[VARCHAR2,32], "T"."AUTHDT"[VARCHAR2,32], "T"."CHANID"[VARCHAR2,32],

       "T"."NOTE01"[VARCHAR2,200], "T"."NOTE02"[VARCHAR2,200], "T"."NOTE03"[VARCHAR2,200],

       "T"."NOTE04"[VARCHAR2,2400], "T"."NOTE05"[VARCHAR2,200], "T"."NOTE06"[VARCHAR2,200],

       "T"."NOTE07"[VARCHAR2,200], "T"."SENDBANKCODE"[VARCHAR2,200], "T"."MSGID"[VARCHAR2,200],

       "T"."RMKINFO"[VARCHAR2,2400], "T"."SSERIALNO"[VARCHAR2,256]

  19 - "T"."ID"[VARCHAR2,144], "T"."WORKDATE"[VARCHAR2,32], "T"."TYPE"[VARCHAR2,32],

       "T"."TASKDETAIL"[VARCHAR2,32], "T"."STATE"[VARCHAR2,16], "T"."CHNDATE"[VARCHAR2,32],

       "T"."CSERIALNO"[VARCHAR2,140], "T"."MENUCODE"[VARCHAR2,32], "T"."ORGID"[VARCHAR2,32],

       "T"."TRANSTELLER"[VARCHAR2,32], "T"."CREATEDATE"[VARCHAR2,32], "T"."CREATETIME"[VARCHAR2,32],

       "T"."LASTMODIFYDATE"[VARCHAR2,32], "T"."LASTMODIFYTIME"[VARCHAR2,32], "T"."AMT"[VARCHAR2,20],

       "T"."CUSTOMERACCT"[VARCHAR2,30], "T"."ISDIRECTIONAL"[NUMBER,22],

       "T"."DIRECTIONALTELLER"[VARCHAR2,32], "T"."ISFEWCROSS"[NUMBER,22],

       "T"."ISFEWCROSSLEVEL"[NUMBER,22], "T"."OPENMENU"[VARCHAR2,32], "T"."TRADETYPE"[VARCHAR2,32],

       "T"."AFRESHMENU"[VARCHAR2,32], "T"."ISALLOWDIRECTIONAL"[NUMBER,22],

       "T"."TASKMANAGELEVEL"[NUMBER,22], "T"."TASKHANDLETELLER"[VARCHAR2,32],

       "T"."ISADMINHANDLE"[NUMBER,22], "T"."MSGDETAILFLOW"[VARCHAR2,144],

       "T"."ADMINDATE"[VARCHAR2,32], "T"."TRADENAME"[VARCHAR2,400], "T"."SNDRCVFLG"[VARCHAR2,32],

       "T"."TBILLTYP"[VARCHAR2,32], "T"."AUTHDT"[VARCHAR2,32], "T"."CHANID"[VARCHAR2,32],

       "T"."NOTE01"[VARCHAR2,200], "T"."NOTE02"[VARCHAR2,200], "T"."NOTE03"[VARCHAR2,200],

       "T"."NOTE04"[VARCHAR2,2400], "T"."NOTE05"[VARCHAR2,200], "T"."NOTE06"[VARCHAR2,200],

       "T"."NOTE07"[VARCHAR2,200], "T"."SENDBANKCODE"[VARCHAR2,200], "T"."MSGID"[VARCHAR2,200],

       "T"."RMKINFO"[VARCHAR2,2400], "T"."SSERIALNO"[VARCHAR2,256]

368 rows selected.

四、总结建议与应急处理方案

4.1 问题总结

结合2015/12月与当前的AWR报告进行分析:与2015/12月对比,当前的业务量确实大量增加,同时涉及的表中的数据量也在不断增长;同时对数据库的TOP SQL进行分析,TOP SQL均为相同SQL;此SQL未使用绑定变量,同时此SQL执行计划的所有步骤均使用全表扫描;这会带来大量的逻辑读及SQL解析相差开销,也会引起相关的shared pool latch/library cache latch等争用,从而引起大量的CPU消耗;

4.2 该故障应急处理方案

当前的应急解决方案是针对出现问题的SQL,进行相应的索引创建,从而降低逻辑读(逻辑读会消耗CPU资源),提高SQL执行效率,缓解当前CPU使用率高的问题。

4.3 长效解决方案建议

4.3.1 从应用角度来看的处理办法

当前SQL未使用绑定变量,通过AWR报告可以发现大量相同的SQL语句在执行,仅WHERE条件值不同;这会导致SQL无法重用带来SQL解析开销、占用大量共享池内存、以及相差的共享池LATCH/library cache latch/pin等问题;因此建议改写SQL,使用绑定变量。

同时表中数据量在不断增加,建议是定期对数据进行归档、清理。

4.3.2 从数据库运维角度来看的处理方法

当前数据库版本为11.2.0.4,11gR2数据库的一些新特性会带来性能隐患;因此建议按照ORACLE最佳实践,结合我们多年的运维管理经验,对一些参数进行调整。

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/haibusuanyun/article/details/130824428