Home » RDBMS Server » Performance Tuning » Urgent Please
Urgent Please [message #279606] Fri, 09 November 2007 06:23 Go to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi Guys,

I have created an index for a table in order to increase the performance of the below query

 SELECT count(pa.rowid)        
    FROM product_account pa
   WHERE pa.org_no IN (100, 200)
     AND NVL(pa.blk_rsn_2_cd,'Q') NOT IN ('D','F','G','N','Y')
     AND NVL(pa.blk_rsn_1_cd,'Q') NOT IN ('D','F','G','N','Y') 
     AND (  pa.cur_os_bal_am > pa.crln_am  
        OR  pa.past_due_am > 0
        OR  pa.status_cd = 'Z' 
        OR (pa.past_due_am > 0 AND pa.ppc_in = 'Y') 
        OR (pa.blk_rsn_1_cd = 'Q' OR pa.blk_rsn_2_cd = 'Q') 
        OR (pa.cur_dlq_stg_cd IN ('2','3','4') AND pa.cur_os_bal_am > 5000) );



CREATE INDEX XYZ.PAC_INDEX_TESTING ON XYZ.PRODUCT_ACCOUNT (org_no,blk_rsn_2_cd,blk_rsn_1_cd,cur_os_bal_am,past_due_am) TABLESPACE INDEX_M;



i have tried using all th possibilities of columns. But my table doesnot do an index select for the above query.

Number of records in the table : 585297
Number of records fetched by the query : 36226

Its very urgent please... I need a solution for this.. I want to tune the performance.

Thanks and Regards,
Balaji
Re: Urgent Please [message #279608 is a reply to message #279606] Fri, 09 November 2007 06:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your index is of relatively little use to the query.
The only field in the index that the optimiser can use for your query is the first one - ORG_NO.
The next field in the index, blk_rsn_2_cd isn't used in the query - instead you use a function NVL(pa.blk_rsn_2_cd,'Q').

You could try recreating the index as a function based index on (for example) Org_no, NVL(pa.blk_rsn_2_cd,'Q'),NVL(pa.blk_rsn_1_cd,'Q').

Re: Urgent Please [message #279623 is a reply to message #279608] Fri, 09 November 2007 07:30 Go to previous messageGo to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi,

Thank You very much. the cost has come down from 35750 to 2798. Its working very fine. Im in the final phase of testing. Will update the entire performance shortly. Smile

Regards,
Balaji
Re: Urgent Please [message #279628 is a reply to message #279623] Fri, 09 November 2007 07:43 Go to previous messageGo to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi,

Initially the product_account table had an index PAC_INDEX with the column names "ORG_NO", "CRACCT_NO", "PROD_NO".

In order to increase the performance of the query I have created an function index on the same product_account table and the cost of the query has come down. But there is another procedure in the same package that inserts and updated the product_account table using the index PAC_INDEX.

So im rebuilding my application with the PAC_INDEX_TESTING index. will this rebuild cause insert and updates performances affected? Nor the optimiser going to choose the best index out of those two and does the insert and update??

Thanks,
Balaji

[Updated on: Fri, 09 November 2007 07:44]

Report message to a moderator

Re: Urgent Please [message #279737 is a reply to message #279628] Fri, 09 November 2007 23:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hmmm. Over 5% of the rows being returned. I would have thought even the most efficient index scan (ie. where every row scanned was kept) would be at best only marginally faster than a Full Table Scan.

Forget the cost. How about the run-time with and without the function-based index?

Also, and this is important, how does the performance compare after you flush the buffer-cache? If you are running it with an index time and again, the first run will be slow but subsequent runs faster. If you time only the subsequent runs you are not getting a true picture of the performance.

Get your DBA to flush the buffer-cache before each test.

The upshot is: I don't think an index will help (much) unless the query is run frequently. When you are pulling out large proportions of a table (like >1%), the only way to make a REAL difference in run-time is to partition the table. That's a pretty serious step though - your DBA would have to look into it.

Ross Leishman
Re: Urgent Please [message #280088 is a reply to message #279737] Mon, 12 November 2007 07:45 Go to previous messageGo to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi rleishman,

Will keep you updated on the performance comparison as I'm still preparing the environment to execute.

Thanks
Balaji
Re: Urgent Please [message #280439 is a reply to message #280088] Tue, 13 November 2007 10:53 Go to previous messageGo to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi rleishman,

When i test the above cases in reality i don't get much performance increase. sometimes my performance is affected. I'm in a mess. i have done the test with new index for three times and two times i have got a poor performance when compared to the performance that is obtained with index PAC_INDEX.

Help me..

Regards,
Balaji
Re: Urgent Please [message #280492 is a reply to message #279737] Tue, 13 November 2007 20:03 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Sat, 10 November 2007 16:01

The upshot is: I don't think an index will help (much) unless the query is run frequently. When you are pulling out large proportions of a table (like >1%), the only way to make a REAL difference in run-time is to partition the table. That's a pretty serious step though - your DBA would have to look into it.


Ross Leishman
Re: Urgent Please [message #280613 is a reply to message #280492] Wed, 14 November 2007 04:14 Go to previous messageGo to next message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi rleishman,

Thank you so much. I have proposed this to my DBA. Finally the query has come to me on what criteria you wanted the partition to be done? there are few other batch jobs that fetches the records from the produc_account table.so need to analyse all those to better tune every thing on a whole.

Regards,
Balaji
Re: Urgent Please [message #280819 is a reply to message #280613] Wed, 14 November 2007 21:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
No offense intended to your DBA, but if they asked that question, then it's a good sign that you shouldn't do it.

DBA's are awfully busy people who get hounded with tons of stupid questions leaving them only minutes of their day to do some real work.

The answer should be obvious. This DBAs response tells me that they don't have time to take responsibility for the problem.

Partitioning strategy for a database is NOT the domain of a developer. It is the responsibility of the DBA or a designated architect.

The question you need to ask yourself is: are you willing to take responsibility for whatever happens as a result of restructuring your database on the advice of an unknown person on the Web?

If the answer is YES, then I would suggest
  • LIST partition on ORG_NO.
  • Place 100 and 200 in a single partition, and all other values in the DEFAULT partition.
  • Depending on other queries on the table, you might split out 100 and 200 into different partitions, and/or split out other values from the DEFAULT partition.
  • To minimise performance impact on other queries, all indexes would be rebuilt as GLOBAL NON-PARTITIONED.
  • Ask for a pay-rise. If you are going to do the DBA's job, at least you should be paid like one.


Ross Leishman
Re: Urgent Please [message #280926 is a reply to message #280819] Thu, 15 November 2007 03:36 Go to previous message
balajisundar
Messages: 29
Registered: October 2007
Junior Member
Hi rleishman,

The last point what you have mentioned sounds good. Cool

thank you very much for all your help that you do. Let me finalise who is going to own the responsibility and get back to you I'm stuck any where(if ball is in my court).


Regards,
Balaji
Previous Topic: Why such a difference in explain plans
Next Topic: Index
Goto Forum:
  


Current Time: Mon Dec 05 15:20:22 CST 2016

Total time taken to generate the page: 0.12821 seconds