Home » RDBMS Server » Performance Tuning » Help- Tuning a Query
Help- Tuning a Query [message #247843] Wed, 27 June 2007 06:32 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
michael_bialik
Messages: 611
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 Go to previous message
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

Previous Topic: Help: Tuning Query
Next Topic: Execution Plan
Goto Forum:
  


Current Time: Sun Dec 11 00:37:36 CST 2016

Total time taken to generate the page: 0.14151 seconds