Home » RDBMS Server » Performance Tuning » Oracle 9i Cost very high (Oracle 9i)
Oracle 9i Cost very high [message #404890] |
Mon, 25 May 2009 04:07 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
manoj12
Messages: 210 Registered: March 2008 Location: India
|
Senior Member |
|
|
Dear Sir
Above query cost is very high.The query is having concatenated indexes on three tables COABALANCESM,FUNCTION BASED INDEX ON COATREE,concatenated ON STATEMENT TABLE.I have analyze table to make the statistics up-to date.But could you please tell me why the cost of the query is very high.However the query is taking lesser amount of records to process 247 records.
SELECT
r.item_name,
r.value,
r.mdlid,
r.rowsort,
r.year,
r.stmtid
FROM
( SELECT t.item_grpflag,
t.item_name AS item_name,
ROUND(t.value,
0) AS value,
t.mdlid,
t.item_id AS rowsort,
t.year,
t.stmtid FROM( SELECT l.item_grpflag,
l.item_name AS item_name,
l.value,
l.mdlid,
l.item_id,
l.stmtid,
m.year FROM ( SELECT a.item_grpflag,
NVL(b.stmtid,
0) AS stmtid,
a.fm_row,
d.mdlid,
LPAD(a.item_name,
LENGTH(a.item_name)+((a.tr_lvl-1)*5),
' ') AS item_name,
b.value,
a.item_id FROM coaitems a,
coabalances b,
coatree c,
model d
WHERE
a.coaid = b.coaid (+) AND
a.tr_id = b.tr_id (+) AND
a.item_id = b.item_id (+) AND
a.fm_row = b.coarow (+) AND
a.coaid = c.coaid AND
a.tr_id = c.tr_id AND
a.coaid = d.mdlid AND
trim(LOWER(c.tr_name)) = 'balance sheet' AND
a.item_id > 0 AND
d.mdlid =1 AND
b.borrid (+) = 3280 AND
b.user_id (+) = 24 ORDER BY a.coaid, a.fm_row ) l, ( SELECT 0 AS stmtid, '0' AS year FROM dual UNION ALL SELECT n.stmtid, '(' || ROWNUM || ')' || CHR(13) || n.year FROM ( SELECT x.stmtid, z.auditmethod_name||' '||TO_CHAR(x.stmtdt) || ' [ ' || x.stmtperiod || 'M ]' AS year FROM STATEMENT x, model y, audit_method z WHERE x.audit_method_id = z.id AND
x.coaid = y.coaid AND
y.mdlid = 1 AND
x.borrid = 3280 AND
x.user_id = 24 AND
x.stmtid IN (SELECT stmtid FROM (SELECT stmtid, ROWNUM FROM STATEMENT WHERE borrid = 3280 AND
coaid = 1 AND
user_id = 24 ORDER BY stmtid DESC) WHERE ROWNUM <= 3) ) n ) m WHERE(l.stmtid = m.stmtid) ORDER BY l.fm_row, l.stmtid)t WHERE t.stmtid IN (SELECT 0 AS stmtid FROM dual UNION ALL SELECT stmtid FROM (SELECT stmtid, ROWNUM FROM (SELECT DISTINCT a.stmtid FROM STATEMENT a WHERE A.BORRID = 3280 AND
A.coaid = 1 AND
A.USER_ID = 24 ORDER BY a.stmtid DESC )) WHERE ROWNUM <= 3) ORDER BY t.stmtid ASC )r
Below is the explain plan of the above query
Elapsed: 00:00:00.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=21399039 Card=669617
7 Bytes=14296337895)
1 0 VIEW (Cost=21399039 Card=6696177 Bytes=14296337895)
2 1 SORT (ORDER BY) (Cost=21399039 Card=6696177 Bytes=143699
95842)
3 2 HASH JOIN (Cost=17665 Card=6696177 Bytes=14369995842)
4 3 VIEW OF 'VW_NSO_1' (Cost=16 Card=8169 Bytes=106197)
5 4 SORT (UNIQUE) (Cost=16 Card=8169 Bytes=13)
6 5 UNION-ALL
7 6 TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Card=81
68)
8 6 COUNT (STOPKEY)
9 8 VIEW (Cost=5 Card=1 Bytes=13)
10 9 COUNT
11 10 VIEW (Cost=5 Card=1 Bytes=13)
12 11 SORT (UNIQUE) (Cost=4 Card=1 Bytes=10)
13 12 TABLE ACCESS (BY INDEX ROWID) OF 'ST
ATEMENT' (Cost=2 Card=1 Bytes=10)
14 13 INDEX (RANGE SCAN) OF 'IDX_BORRID_
USER_COAID' (NON-UNIQUE) (Cost=1 Card=1)
15 3 VIEW (Cost=17341 Card=13935 Bytes=29723355)
16 15 SORT (ORDER BY) (Cost=17341 Card=13935 Bytes=30085
665)
17 16 HASH JOIN (Cost=46 Card=13935 Bytes=30085665)
18 17 VIEW (Cost=21 Card=29 Bytes=60001)
19 18 SORT (ORDER BY) (Cost=21 Card=29 Bytes=11368
)
20 19 HASH JOIN (OUTER) (Cost=18 Card=29 Bytes=1
1368)
21 20 HASH JOIN (Cost=15 Card=29 Bytes=10759)
22 21 MERGE JOIN (CARTESIAN) (Cost=3 Card=1
Bytes=168)
23 22 TABLE ACCESS (BY INDEX ROWID) OF 'CO
ATREE' (Cost=1 Card=1 Bytes=155)
24 23 INDEX (RANGE SCAN) OF 'IDX_TR_NAME
' (NON-UNIQUE) (Cost=1 Card=1)
25 22 BUFFER (SORT) (Cost=2 Card=1 Bytes=1
3)
26 25 TABLE ACCESS (FULL) OF 'MODEL' (Co
st=2 Card=1 Bytes=13)
27 21 TABLE ACCESS (FULL) OF 'COAITEMS' (Cos
t=11 Card=258 Bytes=52374)
28 20 TABLE ACCESS (BY INDEX ROWID) OF 'COABAL
ANCES' (Cost=2 Card=1 Bytes=21)
29 28 INDEX (RANGE SCAN) OF 'IDX_BORRID_USER
_ID' (NON-UNIQUE) (Cost=1 Card=5)
30 17 VIEW (Cost=22 Card=8169 Bytes=735210)
31 30 UNION-ALL
32 31 TABLE ACCESS (FULL) OF 'DUAL' (Cost=11 Car
d=8168)
33 31 COUNT
34 33 HASH JOIN (Cost=11 Card=1 Bytes=100)
35 34 TABLE ACCESS (BY INDEX ROWID) OF 'STAT
EMENT' (Cost=2 Card=1 Bytes=21)
36 35 NESTED LOOPS (Cost=8 Card=1 Bytes=60
)
37 36 MERGE JOIN (CARTESIAN) (Cost=6 Car
d=1 Bytes=39)
38 37 TABLE ACCESS (FULL) OF 'MODEL' (
Cost=2 Card=1 Bytes=26)
39 37 BUFFER (SORT) (Cost=4 Card=1 Byt
es=13)
40 39 VIEW OF 'VW_NSO_2' (Cost=4 Car
d=1 Bytes=13)
41 40 SORT (UNIQUE)
42 41 COUNT (STOPKEY)
43 42 VIEW (Cost=4 Card=1 Byte
s=13)
44 43 SORT (ORDER BY STOPKEY
) (Cost=4 Card=1 Bytes=10)
45 44 COUNT
46 45 TABLE ACCESS (BY I
NDEX ROWID) OF 'STATEMENT' (Cost=2 Card=1 Bytes=10)
47 46 INDEX (RANGE SCA
N) OF 'IDX_BORRID_USER_COAID' (NON-UNIQUE) (Cost=1 Card=1)
48 36 INDEX (RANGE SCAN) OF 'IDX_STATEME
NT_4FLDS' (NON-UNIQUE) (Cost=1 Card=1)
49 34 TABLE ACCESS (FULL) OF 'AUDIT_METHOD'
(Cost=2 Card=82 Bytes=3280)
Statistics
----------------------------------------------------------
2699 recursive calls
0 db block gets
872 consistent gets
189 physical reads
0 redo size
11942 bytes sent via SQL*Net to client
689 bytes received via SQL*Net from client
18 SQL*Net roundtrips to/from client
39 sorts (memory)
0 sorts (disk)
247 rows processed
Appreciate your help on the above as to why the cost of the query is very high.?
Regards
|
|
|
Re: Oracle 9i Cost very high [message #404953 is a reply to message #404890] |
Mon, 25 May 2009 13:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
manoj12 wrote on Mon, 25 May 2009 10:07 |
The query is having concatenated indexes on three tables COABALANCESM,FUNCTION BASED INDEX ON COATREE,concatenated ON STATEMENT TABLE.
|
Are we supposed to guess which columns are indexed?
Quote: |
However the query is taking lesser amount of records to process 247 records.
|
What does that mean?
You really need to format that query. Code tags alone aren't going to make a query that complex understandable. Indentation is also required.
|
|
|
Goto Forum:
Current Time: Thu Aug 22 08:07:41 CDT 2024
|