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  |
trantuananh24hg
Messages: 744
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  |
rleishman
Messages: 3728
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 |
trantuananh24hg
Messages: 744
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 |
|
|
|
Goto Forum:
Current Time: Sat Jun 29 04:03:53 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
