| Help- Tuning a Query [message #247843] |
Wed, 27 June 2007 06:32  |
orasaket
Messages: 70
Registered: November 2006
|
Member |
|
|
Hi,
I am using Oracle 9i Release2 on Linux
Following query is doing FTS on BSE_ACC_PAY table
The BSE_acc_pay table has 21932719 rows
Also we are passing sysdate (stored in a table) for cap.dtpay in above query.
and the distribution of data for npaytye and nrefind is as follows
COUNT(*) NREFIND
9 -23
352190 2
21456119 3
11917 4
COUNT(*) NPAYTYPE
9 -23
1 -9
349959 1
82653 2
21375068 3
1 6
9095 8
3449 11
The Index and table creation script is attached herewith
How can i avoid the FTS on BSE_acc_pay?It is taking 16-17 minutes for that on Live system.
If creating Bitmap segment on any of the columns - npaytye, nrefind will help here?Will it creae locking issue at table level?
Will setting any column in WHERE clause to NOT NULL and setting some default values will help here?
Please advice
Thanks and Regards,
OraSaket
select DISTINCT cap.strrefnbr strrefnbr,
NVL ( cap.naccpaystat, 0 ) naccpaystat, strbasedocnbr
FROM com_acc_pay cap
WHERE cap.naccpaystat IN ( 2, 3 )
AND nrefind = 3
AND npaytype = 3
AND cap.dtpay <= :b1
AND (
(
NVL ( cap.dfcamnt, 0 )
+ NVL ( cap.dpayintamnt, 0 )
+ NVL ( cap.damnttrsfrto, 0 )
)
- (
NVL ( cap.damntadj, 0 )
+ NVL ( cap.damntpaid, 0 )
+ NVL ( cap.damnttrsfrfrom, 0 )
)
) > 0
ORDER BY strbasedocnbr;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3575 | 198K| 98936 |
| 1 | SORT UNIQUE | | 3575 | 198K| 98935 |
| 2 | TABLE ACCESS FULL | COM_ACC_PAY | 3575 | 198K| 98933 |
---------------------------------------------------------------------
|
|
|
|
| Re: Help- Tuning a Query [message #247894 is a reply to message #247843] |
Wed, 27 June 2007 08:02  |
orasaket
Messages: 70
Registered: November 2006
|
Member |
|
|
Hi,
With reference to my earlier post..
The SELECT query mentioned in the post is executed in the Batch Process through an Oracle job.
when i traced the session (for Batch process entirely - almost 90 minutes) surprsingly i could not got plan exactly for the same statement.
What could be the reason?
I have attached the trace file for reference. The referred SQL statement starts at line no 903 in the file.
Thanks and Regards,
OraSaket
|
|
|
|
| Re: Help- Tuning a Query [message #247910 is a reply to message #247843] |
Wed, 27 June 2007 08:43 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Hi.
If I understood your data correctly then:
-- Table contains 21.9M rows
-- 21.45M rows have NREFIND = 3
-- 21.37M rows have NPAYTYPE = 3
-- Your are passing SYSDATE as parameter to "cap.dtpay <= :b1"
-- and most of data rows have cap.dtpay <= SYSDATE
It's not clear how many rows have "naccpaystat IN ( 2, 3 )".
If most of rows are selectes - then indexes will NOT help you and FTS is the best solution.
However - your statement uses SORT, so increasing sort_area_size may help. You may improve FTS a bit by setting multiblock_io_count (by ALTER SESSION command) to some high value.
HTH.
Michael
|
|
|
|
| Re: Help- Tuning a Query [message #247927 is a reply to message #247843] |
Wed, 27 June 2007 09:53 |
orasaket
Messages: 70
Registered: November 2006
|
Member |
|
|
Hi,
Michael thanks for quick reply
naccpaystat has following data distribution
COUNT(*) NACCPAYSTAT
11 -23
6 -1
2367 2
21467427 4
45 5
10 13
17 99
350353 null
Is it feasible to change the PL/SQL as following?
execute immediate 'alter session set db_file_multiblock_read_count=128';
..fire the select
..revert back the change
execute immediate 'alter session set db_file_multiblock_read_count=64';
Thanks and Regards,
OraSaket
|
|
|
|
| Re: Help- Tuning a Query [message #248009 is a reply to message #247843] |
Wed, 27 June 2007 15:22 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
1. I just looked at distribution of NACCPAYSTAT values.
-- Your query selects cap.naccpaystat IN ( 2, 3 ), however
-- you don't have any rows having value 3 and you have only 2367
-- rows with value 2.
-- If these numbers are about constant ( you have only a very
-- small portion of rows with these 2 values ) then I recommend a
-- new index on that column:
CREATE INDEX ... ON BSE_ACC_PAY (NACCPAYSTAT)...
-- It may happen that you will have either to gather histogram stats or to use hint to enforce the new index usage.
2. You can perform ALTER SESSION at the beginning, but you don't have "to revert" - as the session disconnects - everyting returns to normal.
HTH.
Michael
|
|
|
|
| Re: Help- Tuning a Query [message #248216 is a reply to message #247843] |
Thu, 28 June 2007 05:33 |
orasaket
Messages: 70
Registered: November 2006
|
Member |
|
|
Many Thanks Michael,
I will try creating index on as you have suggested and will let you know the results.
However with such uneven data distribution what size you would suggest for hitogram?
I am reverting the 'db_file_multiblock_read_count' since other processes in the same job of batch processes will not have impact because of this change.
Also can you please suggest how come the plan for the same statement wasn't there in trace file, when statistics were there in the same file for same statement?
Thanks and Regards,
OraSaket
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
