| Help - Tuning query [message #262001] |
Fri, 24 August 2007 05:53  |
orasaket
Messages: 70
Registered: November 2006
|
Member |
|
|
Hi,
I am using Oracle 9i R2 on Linux
Please help me tuning the following query.
SELECT COUNT(1)
FROM
FIN_VOU_HDR WHERE STRVOUTYPE = :B5 AND STRVOUSERIES = :B4 AND NFISCALYEAR =
:B3 AND STRBRANDCD = :B2 AND NCLUBPMT = :B1
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 SORT (AGGREGATE)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'FIN_VOU_HDR' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'XPKFIN_VOU_HDR'
(INDEX (UNIQUE))
At present it is using following index
CREATE UNIQUE INDEX HSASYS.XPKFIN_VOU_HDR
ON FIN_VOU_HDR(STRVOUTYPE, STRVOUSERIES, NFISCALYEAR, LVOUNBR, STRBRANDCD)
According to my understanding NCLUBPMT column is creating problem in the query
SQL> select NUM_DISTINCT from dba_tab_columns where table_name='FIN_VOU_HDR' AND COLUMN_NAME='NCLUBPMT';
NUM_DISTINCT
------------
2
SQL> select count(1) from FIN_VOU_HDR;
COUNT(1)
----------
67189924
SQL> SELECT COUNT(distinct NCLUBPMT) FROM FIN_VOU_HDR;
COUNT(DISTINCTNCLUBPMT)
-----------------------
1946
SQL> select count(NCLUBPMT) from fin_vou_hdr where NCLUBPMT is not null;
COUNT(NCLUBPMT)
---------------
39246591
The Data distribution for column in Primary key being used is as follows
PULSEDB1> select count(1),STRVOUTYPE,STRVOUSERIES,NFISCALYEAR from fin_vou_hdr group by STRVOUTYPE,STRVOUSERIES,NFISCALYEAR;
COUNT(1) ST STR NFISCALYEAR
---------- -- --- -----------
1 JV CHG 2003
47 JV CHG 2004
701895 JV CHG 2005
4349426 JV CHG 2006
5228228 JV CHG 2007
382056 JV CLM 2000
519 JV CLM 1986
1901 JV CLM 1987
2074 JV CLM 1988
1981 JV CLM 1989
2983 JV CLM 1990
4176 JV CLM 1991
5025 JV CLM 1992
5639 JV CLM 1993
28202 JV CLM 1994
35157 JV CLM 1995
40559 JV CLM 1996
297700 JV CLM 1997
362394 JV CLM 1998
374043 JV CLM 1999
399159 JV CLM 2001
3591949 JV CLM 2002
3674062 JV CLM 2003
3672382 JV CLM 2004
3445298 JV CLM 2005
3486758 JV CLM 2006
2315414 JV CLM 2007
24799 JV MAN 2005
297475 JV MAN 2006
363063 JV MAN 2007
42 JV MAN 2008
748 JV PPD 2005
13261 JV PPD 2006
14352 JV PPD 2007
5 JV PPD 2008
8725 JV PPE 2006
9460 JV PPE 2007
5758 JV PSE 2000
57 JV PSE 1992
4861 JV PSE 1993
4159 JV PSE 1994
7221 JV PSE 1995
6616 JV PSE 1996
5200 JV PSE 1997
6867 JV PSE 1998
3660 JV PSE 1999
6952 JV PSE 2001
6205 JV PSE 2002
6446 JV PSE 2003
6090 JV PSE 2004
4720 JV PSE 2005
5931 JV PSE 2006
6114 JV PSE 2007
476 JV TRF 2005
16090 JV TRF 2006
23535 JV TRF 2007
3687 PV AUT 2005
34665 PV AUT 2006
38957 PV AUT 2007
378252 PV CLM 2000
519 PV CLM 1986
1901 PV CLM 1987
2074 PV CLM 1988
1981 PV CLM 1989
2983 PV CLM 1990
4116 PV CLM 1991
4881 PV CLM 1992
5453 PV CLM 1993
27571 PV CLM 1994
34591 PV CLM 1995
39816 PV CLM 1996
294598 PV CLM 1997
359249 PV CLM 1998
370593 PV CLM 1999
395292 PV CLM 2001
3565664 PV CLM 2002
3658577 PV CLM 2003
3658895 PV CLM 2004
3432480 PV CLM 2005
3486443 PV CLM 2006
2318136 PV CLM 2007
5553 PV PSE 2000
57 PV PSE 1992
4626 PV PSE 1993
3961 PV PSE 1994
6912 PV PSE 1995
6256 PV PSE 1996
4951 PV PSE 1997
6545 PV PSE 1998
3467 PV PSE 1999
6584 PV PSE 2001
5832 PV PSE 2002
6364 PV PSE 2003
6157 PV PSE 2004
5202 PV PSE 2005
7458 PV PSE 2006
8057 PV PSE 2007
684986 RV AUT 2000
13183 RV AUT 1996
13418 RV AUT 1997
13785 RV AUT 1998
13912 RV AUT 1999
745730 RV AUT 2001
807410 RV AUT 2002
966073 RV AUT 2003
1076145 RV AUT 2004
1134723 RV AUT 2005
4079850 RV AUT 2006
4818190 RV AUT 2007
3804 RV CLM 2000
60 RV CLM 1991
144 RV CLM 1992
186 RV CLM 1993
631 RV CLM 1994
566 RV CLM 1995
743 RV CLM 1996
3102 RV CLM 1997
3145 RV CLM 1998
3450 RV CLM 1999
3867 RV CLM 2001
26285 RV CLM 2002
15485 RV CLM 2003
13487 RV CLM 2004
12209 RV CLM 2005
7896 RV CLM 2006
5629 RV CLM 2007
205 RV PSE 2000
235 RV PSE 1993
198 RV PSE 1994
309 RV PSE 1995
360 RV PSE 1996
249 RV PSE 1997
322 RV PSE 1998
193 RV PSE 1999
368 RV PSE 2001
372 RV PSE 2002
265 RV PSE 2003
277 RV PSE 2004
162 RV PSE 2005
318 RV PSE 2006
225 RV PSE 2007
141 rows selected.
Elapsed: 00:04:30.09
Will Function Based Index , or setting default values for NULLed values and creating index on NCLUBPMT with histogram will help here.
In case of histogram input here would be -> NCLUBPMT will never will NULL for transactions nowonwards and will have almost unique values.
Please suggest
Thanks and Regards,
OraSaket
|
|
|
|
|
|
| Re: Help - Tuning query [message #262076 is a reply to message #262001] |
Fri, 24 August 2007 07:57  |
orasaket
Messages: 70
Registered: November 2006
|
Member |
|
|
Hi Michel,
Apologies
..
Regarding makning the Post length larger, it was data which was crucial for my question.
And about the exact trace which i missed, i am correcting the mistake.
SELECT COUNT(1)
FROM
FIN_VOU_HDR WHERE STRVOUTYPE = :B5 AND STRVOUSERIES = :B4 AND NFISCALYEAR =
:B3 AND STRBRANDCD = :B2 AND NCLUBPMT = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 4 0.01 0.07 55 248 3 0
Fetch 4 242.46 1950.61 8325380 8328062 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 242.48 1950.68 8325435 8328310 3 4
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 37 (HSASYS) (recursive depth: 2)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 SORT (AGGREGATE)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'FIN_VOU_HDR' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'XPKFIN_VOU_HDR'
(INDEX (UNIQUE))
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 259 0.01 0.22
db file parallel read 4 0.05 0.11
reliable message 6 0.00 0.00
enq: KO - fast object checkpoint 4 0.00 0.00
PX Deq: Join ACK 4 0.00 0.00
enq: PS - contention 4 0.00 0.00
db file scattered read 508639 0.45 1792.51
latch: cache buffers chains 8 0.00 0.00
latch free 10 0.00 0.00
********************************************************************************
Thanks and Regards,
OraSaket
|
|
|
|
| Re: Help - Tuning query [message #262084 is a reply to message #262013] |
Fri, 24 August 2007 08:04 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If your stats say that there are 2 distinct values, and there are really lots more, then your sample was too small gathering stats. I doubt you would want Oracle to use that index anyway; 2000 distinct values over 39M rows is pretty coarse.
Histograms probably won't help because you are using bind variables. There is some support in 10g and more in 11g.
You're data is pretty badly skewed. For some combinations of the three search columns, and index will work well. For others, a full scan would probably be better. Oracle is going to struggle with this; it must choose 1 or the other.
Histograms wouldn't even help (necessarily) if you used literals instead of bind variables, because it is the combination of three variables on which the data is skewed. Histograms are only good for single columns. This is improved in 11g.
I don't see a solution to this using your current indexes. When there are not many rows for the combination of three columns, you want to use the concatenated index. When there are not many rows for NCLUBPMT, you want to use that index. When there are too many of both you want to use a full table scan.
The best you can hope for is to create an index on all 5 columns in the WHERE clause and use it. If it returns tens of thousands of rows, bad luck.
Ross Leishman
|
|
|
|
| Re: Help - Tuning query [message #265201 is a reply to message #262001] |
Wed, 05 September 2007 14:06 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi.
I have a couple of questions and suggestion:
1. How may distinct value do you have for LVOUNBR column?
2. It look a bit strange having a lot of waits on
db file scattered read 508639 0.45 1792.51
which is used by FULL table scans while explain show index access.
Are you sure that it's a "real" explain (I saw a number of times that the actual explain was different from the one shown by TKPROF.
Look at raw trace file.
Suggestion:
If LVOUNBR column have a large number of distinct values - try creating a index on (STRVOUTYPE, STRVOUSERIES, NFISCALYEAR, STRBRANDCD)
HTH.
Michael
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
