Home » RDBMS Server » Performance Tuning » sql tuning
sql tuning [message #299518] Tue, 12 February 2008 05:29 Go to next message
guyj
Messages: 31
Registered: September 2005
Member
Hi guys,

Please help me to fine tune the attached query. Database version is 10.2.0.3.

Thanks
  • Attachment: bad_sql.txt
    (Size: 2.50KB, Downloaded 254 times)
Re: sql tuning [message #299538 is a reply to message #299518] Tue, 12 February 2008 06:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Help us to help you.
Read OraFAQ Forum Guide and How to Identify Performance Problem and Bottleneck and provide the requested information.

Regards
Michel
Re: sql tuning [message #299575 is a reply to message #299518] Tue, 12 February 2008 08:05 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
What do you expect?

You don't have any conditions in WHERE clause (except JOINs),
so optimizer performs FULL table/index scan for each table.

Without knowing how many rows are in each table , what indexes exist, etc. it's hard to give any advice.

Michael
Re: sql tuning [message #299669 is a reply to message #299575] Tue, 12 February 2008 23:06 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Yeap!
michael_bialik

You don't have any conditions in WHERE clause (except JOINs),
so optimizer performs FULL table/index scan for each table.



M.Bialik was right, you have any conditions to use to avoid full table scan many times.
I think some columns have Index like branch_id, merchant_id in RS_MERCHANT_INFO table, where're indexes in the others?


Re: sql tuning [message #300063 is a reply to message #299669] Thu, 14 February 2008 02:31 Go to previous messageGo to next message
guyj
Messages: 31
Registered: September 2005
Member
Hi Guys,

Thanks for the reply. where condition contains only joins. problem is accessing rs_transaction_2008_jan table and rs_subscriber table. rs_transaction_2008_jan contains more than 5 million records and rs_subscriber contains 1 million records. Other tables contain less than 5ooo records. We have created indexes for all the columns which exist in the where clause. Stats are up to date.
Re: sql tuning [message #300279 is a reply to message #300063] Thu, 14 February 2008 20:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
How many rows does it return?
How long does it take to return ALL the rows (not just the first one)?
How long do you think it should take?

Ross Leishman
Re: sql tuning [message #300358 is a reply to message #299518] Fri, 15 February 2008 03:30 Go to previous messageGo to next message
guyj
Messages: 31
Registered: September 2005
Member
1) It returns 100 records.
2)It takes 10 minutes.
3)We need to reduce it to less than 3 minutes.
Re: sql tuning [message #300985 is a reply to message #300358] Mon, 18 February 2008 22:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Excellent. So this is the SQL:
SELECT t.tx_date txdate, t.tx_serial, m.merchant_id, b.branch_id,
       s.mobile_number beneficiary_no, t.tx_type, t.tx_status, t.point_value,
       t.tx_rupee_value, t.bill_value, t.bill_number,
       c.mobile_number counter_no, t.redemption_commission
FROM rs_transaction_2008_jan t,
     rs_merchant_info m,
     rs_merch_branch b,
     rs_merch_counter c,
     rs_subscriber s
WHERE t.merchent_counter_id = c.counter_id
AND c.branch_id = b.branch_id
AND b.merchant_id = m.merchant_id
AND t.subscriber_id = s.subscriber_id


Now lets be clear about this:
- You have NO filter predicates - only join predicates
- The fact that it takes 10 minutes indicates that at least ONE of tables is quite large.
- The fact that the query returns 100 rows WITHOUT filter clauses means that ONE OF THE JOINS IS FILTERING THE RESULTS.

ie. One or more of these join clauses FAILS for the vast majority of rows.

The thing is - Oracle assumes - in the absence of other information - that joins will generally succeed. It thinks that there are 8M or so rows in RS_TRANSACTION_2008_JAN and all of them will successfully join to the other 4 tables, giving 8M or so rows output. The plan (full scans and hash joins) is perfect for this scenario. So we to instruct it differently.

My next question:
- Which of the join cluases is failing for the vast bulk of the 8M rows leaving only 100 successfully joined?

Ross Leishman
Re: sql tuning [message #301531 is a reply to message #299518] Thu, 21 February 2008 00:04 Go to previous messageGo to next message
guyj
Messages: 31
Registered: September 2005
Member
t.subscriber_id = s.subscriber_id is the condition.
Re: sql tuning [message #301676 is a reply to message #299518] Thu, 21 February 2008 05:50 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Assuming that it's "filtering" condition that selects about 100 rows only - try following query:

SELECT /*+ ORDERED USE_NL(t) USE_NL(c) USE_NL(b) USE_NL(m) */
   t.tx_date txdate, t.tx_serial, m.merchant_id, b.branch_id,
   s.mobile_number beneficiary_no, t.tx_type, t.tx_status, t.point_value,
       t.tx_rupee_value, t.bill_value, t.bill_number,
       c.mobile_number counter_no, t.redemption_commission
FROM rs_subscriber s,
     rs_transaction_2008_jan t,
     rs_merch_counter c,
     rs_merch_branch b,
     rs_merchant_info m
WHERE t.merchent_counter_id = c.counter_id
AND c.branch_id = b.branch_id
AND b.merchant_id = m.merchant_id
AND t.subscriber_id = s.subscriber_id


I assumed index existance for all join columns,
so following indexes must exist:
1. Table rs_transaction_2008_jan - Index on subscriber_id
2. Table rs_merch_counter - Index on counter_id
3. Table rs_merch_branch - Index on branch_id
4. Table rs_merchant_info - Index on merchant_id

You are supposed to get following EXPLAIN:
SELECT STATEMENT
  NESTED LOOP
    NESTED LOOP
      NESTED LOOP
        NESTED LOOP
          TABLE ACCESS FULL rs_subscriber
          TABLE ACCESS BY INDEX ROWID rs_transaction_2008_jan
            INDEX RANGE SCAN <Index on subscriber_id>
        TABLE  ACCESS BY INDEX ROWID rs_merch_counter 
          INDEX RANGE SCAN <Index on counter_id>
      TABLE  ACCESS BY INDEX ROWID rs_merch_branch 
        INDEX RANGE SCAN <Index on branch_id>
    TABLE  ACCESS BY INDEX ROWID rs_merchant_info 
      INDEX RANGE SCAN <Index on merchant_id>


HTH.
Michael

Re: sql tuning [message #301699 is a reply to message #301676] Thu, 21 February 2008 07:12 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Damn you Michael you glory-hound. Mad I do all the hard set-up, you take the chequered flag. Exactly what I would have recommended (but perhaps not so elegantly). Wink
Re: sql tuning [message #301881 is a reply to message #299518] Fri, 22 February 2008 02:51 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Hopefully it will work also.

Michael
Previous Topic: SQL getting timed out.
Next Topic: Temp tablespace resizing
Goto Forum:
  


Current Time: Fri Dec 09 09:28:06 CST 2016

Total time taken to generate the page: 0.09921 seconds