Home » RDBMS Server » Performance Tuning » I've got a slowly query (Oracle 10.2.0.1, Solaris 10)
I've got a slowly query [message #377756] Wed, 24 December 2008 21:56 Go to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
I have got a slowly query, now, I generated its statisitcs and analyze...
-- The query and its statisitcs
ccs_admin@VNP> alter session set sql_trace=true;

Session altered.

ccs_admin@VNP> alter session set events '10046 trace name context forever, level
 4';

Session altered.

ccs_admin@VNP> set autotrace traceonly
ccs_admin@VNP> SELECT   DECODE (GROUPING (ph.ma_bc), 0, MAX (bc.tenbuucuc)) buuc
uc,
  2           TO_CHAR (SUM (DECODE (ph.httt_id, 602, 0, tra.cuoctb)),
  3                    'fm999,999,999,999,999,999'
  4                   ) cuoctb,
  5           TO_CHAR (SUM (DECODE (ph.httt_id, 602, 0, tra.trongnuoc)),
  6                    'fm999,999,999,999,999,999'
  7                   ) trongnuoc,
  8           TO_CHAR (SUM (DECODE (ph.httt_id, 602, 0, tra.nhantin)),
  9                    'fm999,999,999,999,999,999'
 10                   ) nhantin,
 11           TO_CHAR (SUM (DECODE (ph.httt_id, 602, 0, tra.dichvu)),
 12                    'fm999,999,999,999,999,999'
 13                   ) dichvu,
 14           TO_CHAR (SUM (DECODE (ph.httt_id, 602, 0, tra.quocte)),
 15                    'fm999,999,999,999,999,999'
 16                   ) quocte,
 17           TO_CHAR (SUM (DECODE (ph.httt_id, 602, 0, tra.roaming)),
 18                    'fm999,999,999,999,999,999'
 19                   ) roaming,
 20           TO_CHAR (SUM (DECODE (ph.httt_id, 602, 0, tra.wap)),
 21                    'fm999,999,999,999,999,999'
 22                   ) wap,
 23           TO_CHAR (SUM (DECODE (ph.httt_id, 602, 0, tra.gprs)),
 24                    'fm999,999,999,999,999,999'
 25                   ) gprs,
 26           TO_CHAR (SUM (DECODE (ph.httt_id, 602, 0, tra.khac)),
 27                    'fm999,999,999,999,999,999'
 28                   ) khac,
 29           TO_CHAR (SUM (trathue), 'fm999,999,999,999,999,999') trathue,
 30           TO_CHAR (SUM (DECODE (ph.httt_id, 602, tra.tongtra, 0)),
 31                    'fm999,999,999,999,999,999'
 32                   ) chietkhau,
 33           TO_CHAR (SUM (tra.tongtra), 'fm999,999,999,999,999,999') tongtra
 34      FROM (SELECT a.ma_kh, a.httt_id, a.ngay_tt, a.phieu_id, a.sophieu,
 35                   a.ckn_chu, a.ckn, b.ma_bc
 36              FROM ccs_hcm.bangphieutra a, ccs_hcm.khachhang b
 37             WHERE a.ma_kh = b.ma_kh AND a.ckn_chu = b.ckn_chu) ph,
 38           ccs_hcm.buucucthus bc,
 39           (SELECT   ma_kh, ma_tb, chukyno, donviql_id, phieu_id,
 40                     SUM (DECODE (khoanmuctt_id, 1, tragoc, 210, tragoc, 0)
 41                         ) cuoctb,
 42                     SUM (DECODE (khoanmuctt_id, 200, tragoc, 0)) trongnuoc,
 43                     SUM (DECODE (khoanmuctt_id, 201, tragoc, 0)) nhantin,
 44                     SUM (DECODE (khoanmuctt_id,
 45                                  202, tragoc,
 46                                  207, tragoc,
 47                                  208, tragoc,
 48                                  0
 49                                 )
 50                         ) dichvu,
 51                     SUM (DECODE (khoanmuctt_id, 203, tragoc, 0)) quocte,
 52                     SUM (DECODE (khoanmuctt_id, 204, tragoc, 211, tragoc, 0)

 53                         ) roaming,
 54                     SUM (DECODE (khoanmuctt_id, 205, tragoc, 0)) wap,
 55                     SUM (DECODE (khoanmuctt_id, 206, tragoc, 0)) gprs,
 56                     SUM (DECODE (1,
 57                                  0, 200,
 58                                  0, 201,
 59                                  0, 202,
 60                                  0, 203,
 61                                  0, 204,
 62                                  0, 205,
 63                                  0, 206,
 64                                  0, 207,
 65                                  0, 208,
 66                                  0, 211,
 67                                  0, tragoc
 68                                 )
 69                         ) khac,
 70                     SUM (tragoc) nogoc, SUM (trathue) trathue,
 71                     SUM (tragoc + trathue) tongtra, ckn_chu
 72                FROM ccs_hcm.ct_tra
 73            GROUP BY ma_kh, ma_tb, chukyno, donviql_id, phieu_id, ckn_chu) tr
a
 74     WHERE ph.ma_bc = bc.ma_bc
 75       AND ph.phieu_id = tra.phieu_id
 76       AND ph.ckn_chu = tra.ckn_chu
 77       AND ph.httt_id != 61
 78       AND ph.ckn <= 26114
 79       AND ph.ckn >= 26114
 80  GROUP BY ROLLUP (ph.ma_bc)
 81  ORDER BY GROUPING (ph.ma_bc), MAX (bc.tenbuucuc)
 82  /

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8270 Card=1 Bytes=254)
   1    0   SORT (ORDER BY) (Cost=8270 Card=1 Bytes=254)
   2    1     SORT (GROUP BY ROLLUP) (Cost=8270 Card=1 Bytes=254)
   3    2       HASH JOIN (Cost=8268 Card=1 Bytes=254)
   4    3         NESTED LOOPS (Cost=2473 Card=1 Bytes=90)
   5    4           NESTED LOOPS (Cost=2472 Card=1 Bytes=64)
   6    5             VIEW OF 'BANGPHIEUTRA' (VIEW) (Cost=2355 Card=58 Bytes=2552)
   7    6               UNION-ALL
   8    7                 FILTER
   9    8                   TABLE ACCESS (FULL) OF 'BANGPHIEUTRA_082008' (TABLE)(Cost=673 Card=140347 Bytes=2806940)
  10    7                 FILTER
  11   10                   TABLE ACCESS (FULL) OF 'PHIEUTHU_082008' (TABLE) (Cost=184 Card=35186 Bytes=668534)
  12    7                 FILTER
  13   12                   TABLE ACCESS (FULL) OF 'BANGPHIEUTRA_092008' (TABLE)(Cost=443 Card=84742 Bytes=1694840)
  14    7                 FILTER
  15   14                   TABLE ACCESS (FULL) OF 'PHIEUTHU_092008' (TABLE) (Cost=180 Card=2391 Bytes=45429)
  16    7                 TABLE ACCESS (FULL) OF 'BANGPHIEUTRA_102008' (TABLE) (Cost=429 Card=84104 Bytes=1682080)
  17    7                 TABLE ACCESS (FULL) OF 'PHIEUTHU_102008' (TABLE) (Cost=184 Card=33117 Bytes=629223)
  18    7                 FILTER
  19   18                   TABLE ACCESS (FULL) OF 'BANGPHIEUTRA_112008' (TABLE)(Cost=300 Card=57146 Bytes=1142920)
  20    7                 FILTER
  21   20                   TABLE ACCESS (FULL) OF 'PHIEUTHU_112008' (TABLE) (Cost=8 Card=913 Bytes=16434)
  22    5             VIEW OF 'KHACHHANG' (VIEW) (Cost=2 Card=1 Bytes=20)
  23   22               UNION-ALL (PARTITION)
  24   23                 TABLE ACCESS (BY INDEX ROWID) OF 'KHACHHANGS_082008' (TABLE) (Cost=3 Card=1 Bytes=27)
  25   24                   INDEX (UNIQUE SCAN) OF 'MA_KHDDD_PK_082008' (INDEX (UNIQUE)) (Cost=2 Card=1)
  26   23                 TABLE ACCESS (BY INDEX ROWID) OF 'KHACHHANGS_092008' (TABLE) (Cost=3 Card=1 Bytes=27)
  27   26                   INDEX (UNIQUE SCAN) OF 'MA_KHDDD_PK_092008' (INDEX (UNIQUE)) (Cost=2 Card=1)
  28   23                 TABLE ACCESS (BY INDEX ROWID) OF 'KHACHHANGS_102008' (TABLE) (Cost=3 Card=1 Bytes=12)
  29   28                   INDEX (UNIQUE SCAN) OF 'MA_KHDDD_PK_102008' (INDEX (UNIQUE)) (Cost=2 Card=1)
  30   23                 TABLE ACCESS (BY INDEX ROWID) OF 'KHACHHANGS_112008' (TABLE) (Cost=3 Card=1 Bytes=12)
  31   30                   INDEX (UNIQUE SCAN) OF 'MA_KHDDD_PK_112008' (INDEX (UNIQUE)) (Cost=2 Card=1)
  32    4           TABLE ACCESS (BY INDEX ROWID) OF 'BUUCUCTHUS' (TABLE) (Cost=1 Card=1 Bytes=26)
  33   32             INDEX (UNIQUE SCAN) OF 'BUUCUCTHU_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
  34    3         VIEW (Cost=5773 Card=1215235 Bytes=199298540)
  35   34           HASH (GROUP BY) (Cost=5773 Card=1215235 Bytes=157980550)
  36   35             VIEW OF 'CT_TRA' (VIEW) (Cost=5568 Card=1215235 Bytes=157980550)
  37   36               UNION-ALL
  38   37                 HASH JOIN (Cost=2662 Card=371115 Bytes=22638015)
  39   38                   TABLE ACCESS (FULL) OF 'BANGPHIEUTRA_082008' (TABLE)(Cost=665 Card=145955 Bytes=1459550)
  40   38                   TABLE ACCESS (FULL) OF 'CT_TRA_082008' (TABLE) (Cost=705 Card=376121 Bytes=19182171)
  41   37                 TABLE ACCESS (FULL) OF 'CN_082008' (TABLE) (Cost=72 Card=3636 Bytes=170892)
  42   37                 HASH JOIN (Cost=1037 Card=312322 Bytes=18739320)
  43   42                   TABLE ACCESS (FULL) OF 'BANGPHIEUTRA_092008' (TABLE) (Cost=438 Card=92065 Bytes=920650)
  44   42                   TABLE ACCESS (FULL) OF 'CT_TRA_092008' (TABLE) (Cost=590 Card=314147 Bytes=15707350)
  45   37                 TABLE ACCESS (FULL) OF 'CN_092008' (TABLE) (Cost=8 Card=2273 Bytes=106831)
  46   37                 HASH JOIN (Cost=1008 Card=310024 Bytes=18601440)
  47   46                   TABLE ACCESS (FULL) OF 'BANGPHIEUTRA_102008' (TABLE)(Cost=424 Card=90245 Bytes=902450)
  48   46                   TABLE ACCESS (FULL) OF 'CT_TRA_102008' (TABLE) (Cost=575 Card=310990 Bytes=15549500)
  49   37                 TABLE ACCESS (FULL) OF 'CN_102008' (TABLE) (Cost=72 Card=1675 Bytes=78725)
  50   37                 HASH JOIN (Cost=706 Card=214184 Bytes=12851040)
  51   50                   TABLE ACCESS (FULL) OF 'BANGPHIEUTRA_112008' (TABLE)(Cost=297 Card=62309 Bytes=623090)
  52   50                   TABLE ACCESS (FULL) OF 'CT_TRA_112008' (TABLE) (Cost=403 Card=215482 Bytes=10774100)
  53   37                 TABLE ACCESS (FULL) OF 'CN_112008' (TABLE) (Cost=3 Card=6 Bytes=288)


Statistics
----------------------------------------------------------
       8499  recursive calls
          0  db block gets
    1876371  consistent gets
       5625  physical reads
          0  redo size
        531  bytes sent via SQL*Net to client
       2174  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
        133  sorts (memory)
          0  sorts (disk)
          0  rows processed

ccs_admin@VNP> alter session set sql_trace=false;

Session altered.

ccs_admin@VNP> set autotrace off


And analyzing through tkprof utility
ccs_admin@VNP> ! $ tkprof vnp_ora_8301.trc
output = 8301.lst

TKPROF: Release 10.2.0.1.0 - Production on Thu Dec 25 09:29:44 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

ccs_admin@VNP> ! $ more 8301.lst

TKPROF: Release 10.2.0.1.0 - Production on Thu Dec 25 09:29:44 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Trace file: vnp_ora_8301.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

alter session set sql_trace=true


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 534  
********************************************************************************

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
--More--(2%)
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.47       0.48          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      9.41       9.19       5624    1873154          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      9.88       9.68       5624    1873154          0           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 534  


Brief of tkprof:
-- 9.88(s) of total CPU time for Parsing+Fetching, 9.41(s) for Fetching
-- 9.68(s) of total Elapsed time for Parsing+Fetching, 9.19(s) for Fetching
-- 5624 physical block_size.
-- 1.873.154 consistents read.

-- Remain of tkprof trace file
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=1873154 pr=5624 pw=0 time=9197580 us)
      0   SORT GROUP BY ROLLUP (cr=1873154 pr=5624 pw=0 time=9197544 us)
      0    HASH JOIN  (cr=1873154 pr=5624 pw=0 time=9197481 us)
      0     NESTED LOOPS  (cr=1873154 pr=5624 pw=0 time=9197217 us)
 117298      NESTED LOOPS  (cr=1873154 pr=5624 pw=0 time=9032072 us)
 117298       VIEW  BANGPHIEUTRA (cr=2702 pr=0 pw=0 time=351999 us)
 117298        UNION-ALL  (cr=2702 pr=0 pw=0 time=234702 us)
      0         FILTER  (cr=0 pr=0 pw=0 time=2 us)
      0          TABLE ACCESS FULL BANGPHIEUTRA_082008 (cr=0 pr=0 pw=0 time=0 us)
      0         FILTER  (cr=0 pr=0 pw=0 time=2 us)
      0          TABLE ACCESS FULL PHIEUTHU_082008 (cr=0 pr=0 pw=0 time=0 us)
      0         FILTER  (cr=0 pr=0 pw=0 time=1 us)
      0          TABLE ACCESS FULL BANGPHIEUTRA_092008 (cr=0 pr=0 pw=0 time=0 us)
      0         FILTER  (cr=0 pr=0 pw=0 time=0 us)
      0          TABLE ACCESS FULL PHIEUTHU_092008 (cr=0 pr=0 pw=0 time=0 us)
  84180         TABLE ACCESS FULL BANGPHIEUTRA_102008 (cr=1887 pr=0 pw=0 time=84272 us)
  33118         TABLE ACCESS FULL PHIEUTHU_102008 (cr=815 pr=0 pw=0 time=33194 us)
      0         FILTER  (cr=0 pr=0 pw=0 time=4 us)
      0          TABLE ACCESS FULL BANGPHIEUTRA_112008 (cr=0 pr=0 pw=0 time=0 us)
      0         FILTER  (cr=0 pr=0 pw=0 time=2 us)
      0          TABLE ACCESS FULL PHIEUTHU_112008 (cr=0 pr=0 pw=0 time=0 us)
 117298       VIEW  KHACHHANG (cr=1870452 pr=5624 pw=0 time=8556261 us)
 462876        UNION-ALL PARTITION (cr=1870452 pr=5624 pw=0 time=7873438 us)
 110982         TABLE ACCESS BY INDEX ROWID KHACHHANGS_082008 (cr=462876 pr=2795 pw=0 time=1778065 us)
 110982          INDEX UNIQUE SCAN MA_KHDDD_PK_082008 (cr=351894 pr=4 pw=0 time=894469 us)(object id 444752)
 117298         TABLE ACCESS BY INDEX ROWID KHACHHANGS_092008 (cr=469192 pr=2817 pw=0 time=1771541 us)
 117298          INDEX UNIQUE SCAN MA_KHDDD_PK_092008 (cr=351894 pr=4 pw=0 time=881275 us)(object id 486397)
 117298         TABLE ACCESS BY INDEX ROWID KHACHHANGS_102008 (cr=469192 pr=12 pw=0 time=1594287 us)
 117298          INDEX UNIQUE SCAN MA_KHDDD_PK_102008 (cr=351894 pr=0 pw=0 time=895594 us)(object id 501973)
 117298         TABLE ACCESS BY INDEX ROWID KHACHHANGS_112008 (cr=469192 pr=0 pw=0 time=1630720 us)
 117298          INDEX UNIQUE SCAN MA_KHDDD_PK_112008 (cr=351894 pr=0 pw=0 time=890344 us)(object id 502945)
      0      TABLE ACCESS BY INDEX ROWID BUUCUCTHUS (cr=0 pr=0 pw=0 time=200811 us)
      0       INDEX UNIQUE SCAN BUUCUCTHU_PK (cr=0 pr=0 pw=0 time=81425 us)(object id 442395)
      0     VIEW  (cr=0 pr=0 pw=0 time=0 us)
      0      HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us)
      0       VIEW  CT_TRA (cr=0 pr=0 pw=0 time=0 us)
      0        UNION-ALL  (cr=0 pr=0 pw=0 time=0 us)
      0         HASH JOIN  (cr=0 pr=0 pw=0 time=0 us)
      0          TABLE ACCESS FULL BANGPHIEUTRA_082008 (cr=0 pr=0 pw=0 time=0 us)
      0          TABLE ACCESS FULL CT_TRA_082008 (cr=0 pr=0 pw=0 time=0 us)
      0         TABLE ACCESS FULL CN_082008 (cr=0 pr=0 pw=0 time=0 us)
      0         HASH JOIN  (cr=0 pr=0 pw=0 time=0 us)
      0          TABLE ACCESS FULL BANGPHIEUTRA_092008 (cr=0 pr=0 pw=0 time=0 us)
      0          TABLE ACCESS FULL CT_TRA_092008 (cr=0 pr=0 pw=0 time=0 us)
      0         TABLE ACCESS FULL CN_092008 (cr=0 pr=0 pw=0 time=0 us)
      0         HASH JOIN  (cr=0 pr=0 pw=0 time=0 us)
      0          TABLE ACCESS FULL BANGPHIEUTRA_102008 (cr=0 pr=0 pw=0 time=0 us)
      0          TABLE ACCESS FULL CT_TRA_102008 (cr=0 pr=0 pw=0 time=0 us)
      0         TABLE ACCESS FULL CN_102008 (cr=0 pr=0 pw=0 time=0 us)
      0         HASH JOIN  (cr=0 pr=0 pw=0 time=0 us)
      0          TABLE ACCESS FULL BANGPHIEUTRA_112008 (cr=0 pr=0 pw=0 time=0 us)
      0          TABLE ACCESS FULL CT_TRA_112008 (cr=0 pr=0 pw=0 time=0 us)
      0         TABLE ACCESS FULL CN_112008 (cr=0 pr=0 pw=0 time=0 us)


And this is the original trace file
PARSE #1:c=640000,e=634212,p=1,cr=3217,cu=0,mis=1,r=0,dep=0,og=1,tim=1048325067116
BINDS #1:
EXEC #1:c=0,e=362,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1048325068314
*** 2008-12-25 09:23:57.888
FETCH #1:c=9410000,e=9197526,p=5624,cr=1873154,cu=0,mis=0,r=0,dep=0,og=1,tim=1048334265963
*** SESSION ID:(995.18156) 2008-12-25 09:23:57.906


Oh, what's evil, so I think that!
Why did I think so?

1. What's the problem when fetching this sql?
Parsing: cr=3217, consistents read
Execution: cr=1.873.154 consistents read
I had total: 1876371 consistent gets - latch contentions

2. Physical I/O vs Logical I/O
0 db block gets = 0 logical block - that's okie, it's not evils
p=5624 physical block gets when fetching, that's right if measuring total size of those tables

3. Index vs FTS:
You see statisitics above, such as:
Eg:
-- TABLE ACCESS (FULL) OF 'BANGPHIEUTRA_082008' (TABLE)(Cost=665 Card=145955 Bytes=1459550)
~ 15MB,
-- TABLE ACCESS (BY INDEX ROWID) OF 'KHACHHANGS_082008' (TABLE) (Cost=3 Card=1 Bytes=27)

Due to our attribute of reporting statement, wee need to scan full of some tables, join them to anothers and display results.
But I can not rewrite this statement, may be I'm bad. However, if more than 10 sessions call this reporting statement, (in
database = 10, in Oracle AS ~ 120), the percentage of CPU's performance will achive to 100%. How do I do now?
May you guide me something?

Thank you very much!

[Updated on: Wed, 24 December 2008 22:00]

Report message to a moderator

Re: I've got a slowly query [message #377854 is a reply to message #377756] Thu, 25 December 2008 23:11 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
VIEW OF 'BANGPHIEUTRA' (VIEW) (Cost=2355 Card=58 Bytes=2552)

117298       VIEW  BANGPHIEUTRA (cr=2702 pr=0 pw=0 time=351999 us)

Oracle thinks that only 58 rows will be returned from this view, but in fact 117K rows are returned.

Have you tried gathering statistics on the underlying tables of the views.

Ross Leishman
Re: I've got a slowly query [message #377856 is a reply to message #377854] Thu, 25 December 2008 23:18 Go to previous message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
@rleishman:

First time, thank you!

I've got a job to gather statistics of those tables, system statistics. Maybe I must re-gather statistics manually.

And.. this is the statement that made me fear, bore and hate, this is the true statement in thread "Relate MView", in which, you've just answered for me.

Due to many clients who call it concurrently weekend, my DB's CPU was 100% busy time. So that, we've to design MView in other machine.

Thank you!

[Updated on: Thu, 25 December 2008 23:19]

Report message to a moderator

Previous Topic: Bitmap indexes "drop/create" or "disable/rebuild"
Next Topic: Relate question to MViews
Goto Forum:
  


Current Time: Sat Dec 03 04:13:34 CST 2016

Total time taken to generate the page: 0.15954 seconds