| Query Tuning ... [message #175915] |
Tue, 06 June 2006 01:33  |
rahul.priyadarshy
Messages: 28
Registered: December 2005
|
Junior Member |
|
|
Hi ,
We have a query which is firing on a partitioned table named VSIS_NNC_MEDIUM_131 . This table has 340000 records .Our query is taking more than 20 hrs to execute .actually its insert with select . This table has 2 indexes also.Here is the query which is running right now.
SELECT /*+ RULE */
t1.channels units
FROM ops$vsis.VSIS_NNC_MEDIUM_131 t1
WHERE
t1.nnc_ref_combined = 'NB514H' AND
t1.from_datetime =
(SELECT MAX (t2.from_datetime)
FROM ops$vsis.vsis_nnc_medium_131 t2
WHERE t1.nnc_ref_combined = t2.nnc_ref_combined
AND t2.from_datetime BETWEEN '01-apr-2006' AND '30-apr-2006')
ORDER BY 1 DESC
even when we remove this RULE hint its taking the same amount of time.we are using oracle 9i.Any body acn suggest anything .please its very urgent.
With Regards..
Rahul Priyadarshy
Xansa (India) Ltd.
+44(0)121 635 3369
rahul.priyadarshy@xansa.com
http://www.xansa.com
|
|
|
|
| Re: Query Tuning ... [message #175920 is a reply to message #175915] |
Tue, 06 June 2006 02:01  |
rahul.priyadarshy
Messages: 28
Registered: December 2005
|
Junior Member |
|
|
Hi All
Sorry ...
below is the explain plan for above query..
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 40 | 132K| | |
|* 1 | FILTER | | | | | | |
| 2 | SORT GROUP BY | | 1 | 40 | 132K| | |
| 3 | MERGE JOIN CARTESIAN | | 8643K| 329M| 103K| | |
| 4 | PARTITION RANGE ALL | | | | | 1 | 67 |
|* 5 | TABLE ACCESS FULL | VSIS_NNC_MEDIUM_131 | 10310 | 251K| 444 | 1 | 67 |
| 6 | BUFFER SORT | | 838 | 12570 | 132K| | |
| 7 | PARTITION RANGE ITERATOR| | | | | 1 | 6 |
|* 8 | INDEX RANGE SCAN | VSIS_NNC_MEDIUM_131_P_IX_1 | 838 | 12570 | 10 | 1 | 6 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T1"."FROM_DATETIME"=MAX("T2"."FROM_DATETIME"))
5 - filter("T1"."NNC_REF_COMBINED"='NB514H')
8 - access("T2"."NNC_REF_COMBINED"='NB514H' AND "T2"."FROM_DATETIME">=TO_DATE('2006-04-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss') AND "T2"."FROM_DATETIME"<=TO_DATE('2006-04-10 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
Note: cpu costing is off
[Updated on: Tue, 06 June 2006 04:36] by Moderator Report message to a moderator |
|
|
|
| Re: Query Tuning ... [message #176007 is a reply to message #175920] |
Tue, 06 June 2006 06:30 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
|
have you analyzed the tables with dbms_stats recently? if not , can u retry and see if that makes any difference...also can u post the table structure, tell on which columns it is partitioned and may be post 2 or 3 sample rows?
|
|
|
|
| Re: Query Tuning ... [message #176067 is a reply to message #176007] |
Tue, 06 June 2006 14:30 |
jrich
Messages: 35
Registered: February 2006
|
Member |
|
|
It looks like you lost the join predicate. One reason for this is if the parameter query_rewrite_enabled is set to false. If so, set it to true at the session level and rerun the query.
Also, I would suggest getting rid of the rule hint and making sure your tables have been analyzed recently.
You might also try the following query which I think is equivalent...
SELECT
channels units
FROM
(SELECT channels,
rank() over (partition by nnc_ref_combined order by from_datetime desc) rnk
FROM ops$vsis.VSIS_NNC_MEDIUM_131
WHERE from_datetime BETWEEN '01-apr-2006' AND '30-apr-2006' AND nnc_ref_combined = 'NB514H')
WHERE rnk = 1
ORDER BY 1 DESC
An index on nnc_ref_combined might help if the value NB514H doesn't appear that often.
JR
|
|
|
|
| Re: Query Tuning ... [message #176095 is a reply to message #176067] |
Tue, 06 June 2006 22:49 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You definitely need to get rid of the RULE hint, and check your OPTIMIZER_GOAL - make sure it is not RULE.
Providing you have an index on nnc_ref_combined and from_datetime, AND the index is analyzed, and you are using the COST based optimizer, then Oracle has a very efficient plan for dealing with this kind of query.
The Explain Plan you are looking for will be something like:
SELECT
FILTER
TABLE ACCESS FULL xxxx
SORT (AGGREGATE)
FIRST ROW
INDEX RANGE SCAN (MIN/MAX) xxxx
The key to look for is the FIRST ROW and MIN/MAX.
I'm not sure (and don't have time to test it right now), but it may also require the columns to be NOT NULL.
As directed by the other responses, gather statistics and use the CBO.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
