| sql tuning [message #299518] |
Tue, 12 February 2008 05:29  |
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 1536 times)
|
|
|
|
|
|
| Re: sql tuning [message #299575 is a reply to message #299518] |
Tue, 12 February 2008 08:05  |
michael_bialik
Messages: 621
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 |
trantuananh24hg
Messages: 744
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 |
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 |
rleishman
Messages: 3728
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 #300985 is a reply to message #300358] |
Mon, 18 February 2008 22:30 |
rleishman
Messages: 3728
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 #301676 is a reply to message #299518] |
Thu, 21 February 2008 05:50 |
michael_bialik
Messages: 621
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
I do all the hard set-up, you take the chequered flag. Exactly what I would have recommended (but perhaps not so elegantly). 