炼数成金 门户 大数据 Oracle 查看内容

一个Oracle调优举例

2013-11-26 15:58| 发布者: hanmeres| 查看: 17909| 评论: 2|来自: 网络

摘要: 64W数据表,查询数据执行时间为139s,优化为1s 。AFC_TXN.TXN_DATA_FUNC.fetchbatch包中的SQL查询语句过慢 ,cut_batch表大约有64W数据,其中执行参数为substr(t.BATCH_ID,1,2)='01'的语句为139s。

SQL 基础 Oracle RAC

64W数据表,查询数据执行时间为139s,优化为1s

       AFC_TXN.TXN_DATA_FUNC.fetchbatch包中的SQL查询语句过慢

  cut_batch表大约有64W数据,其中执行参数为substr(t.BATCH_ID,1,2)='01'的语句为139s

  给cut_batch表添加索引create index idx_cut_batch2 on cut_batch(substr(batch_id,1,2))后,执行时间为54s

  修改语句为以下写法:

  SELECT

  os.AFC_DEVICE_ID      AS AFC_DEVICE_ID,

  os.BATCH_ID           AS BATCH_ID,

  os.DATA_DIRECTORY     AS DATA_DIRECTORY,

  os.RECORD_COUNT       AS RECORD_COUNT

  FROM (

  SELECT

  t.AFC_DEVICE_ID      AS AFC_DEVICE_ID,

  t.BATCH_ID           AS BATCH_ID,

  t.DATA_DIRECTORY     AS DATA_DIRECTORY,

  t.RECORD_COUNT       AS RECORD_COUNT,

  SUM(t.RECORD_COUNT) OVER (ORDER BY BATCH_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTAL_TXN

  FROM

  (SELECT

  t.AFC_DEVICE_ID      AS AFC_DEVICE_ID,

  t.BATCH_ID           AS BATCH_ID,

  t.DATA_DIRECTORY     AS DATA_DIRECTORY,

  t.RECORD_COUNT       AS RECORD_COUNT,

  t.SC_BATCH_ID AS SC_BATCH_ID

  FROM cut_batch_test t

  WHERE substr(t.BATCH_ID,1,2) = :"SYS_B_0") t

  WHERE  t.SC_BATCH_ID IS NULL

  ) os

  WHERE os.TOTAL_TXN < 300;

  其执行速度达到45s

  修改以下oracle系统参数

  alter system set db_cache_size=200M scope=both;

  alter system set large_pool_size=50M scope=both;

  alter system set sort_area_size=10485760 scope=spfile;

  其SQL执行速度未改变。

  10046 事件按照收集信息内容,可以分成4个级别:

  Level 1: 等同于SQL_TRACE 的功能

  Level 4: 在Level 1的基础上增加收集绑定变量的信息

  Level 8: 在Level 1 的基础上增加等待事件的信息

  Level 12:等同于Level 4+Level 8, 即同时收集绑定变量信息和等待事件信息

  通过开启会话调试查询其SQL具体在oracle内部做了什么操作:

  SQL>alter session set events = '10046 trace name context forever,level 12';

  SQL>执行查询语句SQL

  SQL>alter session set events = '10046 trace name context off';

  [root@localhost:/app/oracle/diag/rdbms/xxx/xxx/trace/]tkprof AFC010C1_ora_18100.trc /root/AFC010C1_ora_18100.txt

  查看AFC010C1_ora_18100.txt内容如下:

  SELECT

  os.AFC_DEVICE_ID      AS AFC_DEVICE_ID,

  os.BATCH_ID           AS BATCH_ID,

  os.DATA_DIRECTORY     AS DATA_DIRECTORY,

  os.RECORD_COUNT       AS RECORD_COUNT

  FROM (

  SELECT

  t.AFC_DEVICE_ID      AS AFC_DEVICE_ID,

  t.BATCH_ID           AS BATCH_ID,

  t.DATA_DIRECTORY     AS DATA_DIRECTORY,

  t.RECORD_COUNT       AS RECORD_COUNT,

  SUM(t.RECORD_COUNT) OVER (ORDER BY BATCH_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TOTAL_TXN

  FROM

  (SELECT

  t.AFC_DEVICE_ID      AS AFC_DEVICE_ID,

  t.BATCH_ID           AS BATCH_ID,

  t.DATA_DIRECTORY     AS DATA_DIRECTORY,

  t.RECORD_COUNT       AS RECORD_COUNT,

  t.SC_BATCH_ID AS SC_BATCH_ID

  FROM cut_batch_test t

 

      WHERE substr(t.BATCH_ID,:"SYS_B_0",:"SYS_B_1") = :"SYS_B_2") t

  WHERE  t.SC_BATCH_ID IS NULL

  ) os

  WHERE os.TOTAL_TXN < :"SYS_B_3"

  call     count       cpu    elapsed       disk      query    current        rows

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

  Parse        1      0.00       0.00          0          0          0           0

  Execute      2      0.00       0.00          0          1          0           0

  Fetch        1      0.95      46.97        266      16819         20         100

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

  total        4      0.95      46.98        266      16820         20         100

  Misses in library cache during parse: 1

  Misses in library cache during execute: 1

  Optimizer mode: ALL_ROWS

  Parsing user id: 87

  Number of plan statistics captured: 1

  Rows (1st) Rows (avg) Rows (max)  Row Source Operation

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

  100        100        100  VIEW  (cr=16819 pr=266 pw=4795 time=46976922 us cost=484 size=319485 card=1805)

  100        100        100   WINDOW SORT (cr=16819 pr=266 pw=4795 time=46976616 us cost=484 size=332120 card=1805)

  210483     210483     210483    TABLE ACCESS BY INDEX ROWID CUT_BATCH_TEST (cr=16819 pr=0 pw=0 time=479564 us cost=409 size=332120 card=1805)

  328719     328719     328719     INDEX RANGE SCAN IDX_CUT_BATCH_TEST1 (cr=645 pr=0 pw=0 time=704811 us cost=257 size=0 card=2518)(object id 82729)

  Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  SQL*Net message to client                       2        0.00          0.00

  SQL*Net message from client                     2        0.00          0.00

  direct path write temp                        182        0.69         46.11

  direct path read temp                          19        0.01          0.12

  SQL*Net more data to client                     7        0.00          0.00

  ********************************************************************************

  在以上trace文件中,其direct path write temp等待时间占到了整个查询时间的99%.

  为了排序工作在临时区域读写时,等待direct path read temp、direct path write temp事件。

  这个等待事件收从oracle 10g起被分类的,oracle 9i为止是通过direct path read、direct path write等待观察的。

  排序段上的 direct path I/O是在需要排序的数据比排序所分配的PGA内存区大时发生的。

  因此在排序工作时若大量发生direct path read temp、direct path write temp等待,就可以通过追加分配内存区域而避免等待。

  SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'direct path read temp';

  EVENT# NAME                           PARAMETER1      PARAMETER2      PARAMETER3

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

  195 direct path read temp          file number     first dba       block cnt

  SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'direct path write temp';

  EVENT# NAME                           PARAMETER1      PARAMETER2      PARAMETER3

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

  197 direct path write temp         file number     first dba       block cnt

  1、应用程序层

  检查需要排序的sql语句是否已经最优化。不必要的排序操作会导致CPU浪费、PGA区域浪费、磁盘I/O浪费。从UNION和UNION ALL的性能差异上可以得知,只靠减少不必要的排序操作,也能解决许多问题。

  2、oracle内存层

  在进程上分配的工作区大小内一次性实现的排序称为One pass sort.与此相反的情况称为Multi pass sort.

  发生Multi pass sort时,排序工作过程中将排序结果读写到排序段(sort segment)区域,因此发生direct path read temp、direct path write temp等待。

  如果该等待大量发生,就可以适当提高pga_aggregate_target值,以此消除问题。

  oracle在调优指南上推荐如下设定pga_aggregate_target值。

  OLTP:pga_aggregate_target=(total_mem * 80%) * 20%

  OLAP:pga_aggregate_target=(total_mem * 80%) * 50%

  上述的意思是,假设OS本身使用20%左右的内存,OLTP系统上使用剩余内存的20%左右,OLAP系统因为排序工作较多,所以使用剩余内存的50%左右。

  解决方案:

  SQL>alter system set pag_aggregate_target=300M scope=both;

  再次查询SQL其执行时间不到1s

4

鲜花
1

握手

雷人

路过

鸡蛋

刚表态过的朋友 (5 人)

发表评论

最新评论

引用 lengyoufang 2013-12-10 17:18
用10046来trace详细的等待花在什么上,确实是一个很有力的办法。

但是有一个问题:oracle 11g 以后,不是有自动内存管理了吗?如果用自动内存调整,oracle会不会自动解决上述问题呢?
引用 15017077086 2013-11-26 16:30
SUBSTR(T.BATCH_ID, 1, 2) = :"SYS_B_0"?這樣不會用到索引吧,自己修改為T.BATCH_ID LIKE不是更好啊[可爱]

查看全部评论(2)

热门频道

  • 大数据
  • 商业智能
  • 量化投资
  • 科学探索
  • 创业

即将开课

 

GMT+8, 2019-9-16 18:36 , Processed in 0.116459 second(s), 22 queries .