| how to make it faster [message #181796] |
Tue, 11 July 2006 09:30  |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
Hello
I have this query and takes a long time.
Does anybody know how to rewrite this query?
Or how to split this query to smallest selects?
thanks a lot
----------------------------------------------------------------
SELECT /*+ ALL_ROWS */
su.x_external_id || CHR (9) || ofr.x_offer_id
FROM table_site_part su, table_site_part svc, table_x_csr_offer_cfg ofr
WHERE svc.site_part2site_part = su.objid
AND ofr.x_offer_id <> '457021455'
AND ofr.x_csr_offer_cfg2service = svc.site_part2part_info
AND ofr.x_export_event = 1
AND su.x_site_part2status_su = 268488358
AND EXISTS (
SELECT 'X'
FROM table_bus_org cu, table_site eu, table_site_part su
WHERE su.site_part2site = eu.objid
AND eu.x_site2customer = cu.objid
AND cu.x_bus_org2billcycle BETWEEN 268442453 AND 268442456
UNION ALL
SELECT 'X'
FROM table_bus_org cu, table_site eu, table_site_part su
WHERE su.site_part2site = eu.objid
AND eu.x_site2customer = cu.objid
AND cu.x_bus_org2billcycle = 268451297)
AND ( ofr.x_tariff_sensitive = 0
OR ( ofr.x_tariff_sensitive = 1
AND ofr.x_csr_offer_cfg2tariff =
(SELECT x_tariff_detail2tariff
FROM table_x_tariff_detail
WHERE x_tarif_detail2site_part = su.objid
AND x_active = 1)
OR ( ofr.x_tariff_sensitive = 1
AND ofr.x_csr_offer_cfg2tariff IS NULL
AND NOT EXISTS (
SELECT 'X'
FROM table_x_csr_offer_cfg ofr2,
table_site_part su,
table_site_part svc,
table_x_tariff_detail td
WHERE svc.site_part2part_info =
ofr2.x_csr_offer_cfg2service
AND ofr2.x_tariff_sensitive = 1
AND su.objid = td.x_tarif_detail2site_part
AND td.x_active = 1
AND ofr2.x_csr_offer_cfg2tariff =
td.x_tariff_detail2tariff
AND ofr2.x_tariff_sensitive = td.x_active)
)
)
)
AND ( ofr.x_param_sensitive = 0
OR EXISTS (
SELECT 'X'
FROM table_fa_site_part par, table_x_csr_param_value pv
WHERE par.fa_site_part2site_part = svc.objid
AND par.fa_site_part2flex_defn = ofr.x_param_def_objid
AND pv.objid = ofr.x_csr_offer_cfg2par_value
AND par.attribute_value = pv.x_value)
)
AND ( svc.x_site_part2status_su = 805307984
OR ( svc.x_site_part2status_su = 805307985
AND ofr.x_suspend_sensitive = 0
)
)
----------------------------------------------------------------
-
Attachment: 1.sql
(Size: 2.75KB, Downloaded 1693 times)
[Updated on: Tue, 11 July 2006 11:26] Report message to a moderator |
|
|
|
| Re: how to make it faster [message #181828 is a reply to message #181796] |
Tue, 11 July 2006 14:58  |
wagnerch
Messages: 58
Registered: July 2006
|
Member |
|
|
Perhaps you should look at folding the subqueries into the main body of the query. Additionally it is a bit difficult for anyone to give you advice beyond that without knowning a few things like:
a) what are the data types involved, i see a few places where you are using character literals and numeric literals. if your literals are actually using a numeric or character field then the database will do a conversion -- it may decide to do a TO_CHAR(x_offer_id) and invalidate the index.
b) what size are the tables
c) what is indexed
d) what is the relationship (1:many, 1:1, etc)
e) how long is long
f) what does the plan look like
In any event I would start with trying to fold the subqueries into the main body. I tend to believe that someone made that query far more complicated then it needed to be.
|
|
|
|
| Re: how to make it faster [message #181835 is a reply to message #181796] |
Tue, 11 July 2006 16:14 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
As was said, there is much information needed to correctly diagnose any problems. That said I note two things you might take interest in:
1) some of your correlated sub-queries don't make references back to tables in you main query. This might be correct, but might also indicate a correlated sub-query that is incorrectly written. Check those exists clauses to see if maybe you should be referring to one or more of the tables in your main query. As it is now, you have to basically scan all rows in these tables for every row considered by your main query. If indeed these correlated sub-queries are correct, then note that their answer will never change regardless of how many times they are executed and what row the main query is processing at any moment, so rewrite the query to pull these existential queries out (possibly with creative use of the WITH clause or nested selects in the from clause), and thus maybe save lots of time.
2) you use the ALL_ROWS hint. Get rid of it or find out why it is there. Additionally consider making some other changes to the query understanding that you could add the following tests to the where clause without changing the final result, but making it more clear where indexing might be of use...
and svc.x_site_part2status_su in (805307984,805307985)
and ofr.x_tariff_sensitive in (0,1)
If you don't understand why adding these will not change the results then look again. If you do understand then get yourself a query plan and do some data analysis to see if any of these phrases would dramatically limit the number of rows early on in your query. If so, get an index on the columns to support this.
I suspect there may be several things "wrong" with the query and thus there should be ample opportunity for improvement, good luck, Kevin
|
|
|
|
|
|
| Re: how to make it faster [message #181868 is a reply to message #181796] |
Wed, 12 July 2006 00:26 |
mape
Messages: 298
Registered: July 2006
Location: Slovakia
|
Senior Member |
|
|
Hello
The biggest table is table_site_part. Its about 1 GBytes.
Other tables are smaller, about 10 Mbytes.
Relationship is 1:many unfortunately.
This query run almost 4 hours.
The HINT ALL_ROWS is the best choice by comparing in explain plan. Other HINT for example INDEX (su) does not help.
Here is the explain plan:
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37152 | 3301K| 1380 |
| 1 | FILTER | | | | |
| 2 | FILTER | | | | |
| 3 | HASH JOIN | | 37152 | 3301K| 1380 |
| 4 | TABLE ACCESS FULL | TABLE_X_CSR_OFFER_CFG | 116 | 5452 | 2 |
| 5 | MERGE JOIN | | 118K| 5080K| 1354 |
| 6 | TABLE ACCESS BY INDEX ROWID| TABLE_SITE_PART | 284K| 7510K| 634 |
| 7 | INDEX FULL SCAN | IND_SITE_PART2SITE_PART | 284K| | 26 |
| 8 | SORT JOIN | | 37012 | 614K| 720 |
| 9 | TABLE ACCESS FULL | TABLE_SITE_PART | 37012 | 614K| 599 |
| 10 | UNION-ALL | | | | |
| 11 | HASH JOIN | | 23248 | 612K| 512 |
| 12 | HASH JOIN | | 35340 | 828K| 264 |
| 13 | TABLE ACCESS FULL | TABLE_BUS_ORG | 4777 | 57324 | 48 |
| 14 | TABLE ACCESS FULL | TABLE_SITE | 137K| 1608K| 132 |
| 15 | INDEX FAST FULL SCAN | IND_SITE_PART_SITE | 90301 | 264K| 3 |
| 16 | HASH JOIN | | 21553 | 568K| 508 |
| 17 | HASH JOIN | | 32765 | 767K| 264 |
| 18 | TABLE ACCESS FULL | TABLE_BUS_ORG | 4429 | 53148 | 48 |
| 19 | TABLE ACCESS FULL | TABLE_SITE | 137K| 1608K| 132 |
| 20 | INDEX FAST FULL SCAN | IND_SITE_PART_SITE | 90301 | 264K| 3 |
| 21 | TABLE ACCESS BY INDEX ROWID | TABLE_X_TARIFF_DETAIL | 1 | 17 | 2 |
| 22 | INDEX RANGE SCAN | X_SU_TARIFF | 1 | | 1 |
| 23 | NESTED LOOPS | | 217M| 9965M| 42M|
| 24 | NESTED LOOPS | | 20891 | 836K| 42M|
| 25 | MERGE JOIN CARTESIAN | | 21M| 480M| 1458 |
| 26 | TABLE ACCESS FULL | TABLE_X_CSR_OFFER_CFG | 56 | 952 | 2 |
| 27 | BUFFER SORT | | 375K| 2564K| 1456 |
| 28 | INDEX FULL SCAN | SITE_PART_OBJINDEX | 375K| 2564K| 26 |
| 29 | TABLE ACCESS BY INDEX ROWID | TABLE_X_TARIFF_DETAIL | 1 | 17 | 2 |
| 30 | INDEX RANGE SCAN | X_SU_TARIFF | 1 | | 1 |
| 31 | INDEX RANGE SCAN | IND_SITE_PART2PART_INFO | 10421 | 72947 | 1 |
| 32 | NESTED LOOPS | | 1 | 31 | 3 |
| 33 | TABLE ACCESS BY INDEX ROWID | TABLE_X_CSR_PARAM_VALUE | 1 | 11 | 1 |
| 34 | INDEX UNIQUE SCAN | X_CSR_PARAM_VALUE_OBJINDEX | 1 | | |
| 35 | TABLE ACCESS FULL | TABLE_FA_SITE_PART | 1 | 20 | 2 |
-----------------------------------------------------------------------------------------------
Anyway thanks for your help
|
|
|
|
| Re: how to make it faster [message #182071 is a reply to message #181796] |
Wed, 12 July 2006 18:54 |
wagnerch
Messages: 58
Registered: July 2006
|
Member |
|
|
Based on your plan, lines 23 to 31 should be of a HUGE interest. It is producing a cartesian join, usually this would be caused by a missing join criteria. This inner query is the one producing the cartesian join:
SELECT 'X'
FROM table_x_csr_offer_cfg ofr2,
table_site_part su,
table_site_part svc,
table_x_tariff_detail td
WHERE svc.site_part2part_info =
ofr2.x_csr_offer_cfg2service
AND ofr2.x_tariff_sensitive = 1
AND su.objid = td.x_tarif_detail2site_part
AND td.x_active = 1
AND ofr2.x_csr_offer_cfg2tariff =
td.x_tariff_detail2tariff
AND ofr2.x_tariff_sensitive = td.x_active
The second problem with this inner query is it doesn't relate to an outer query, so it is full scanning all of the tables listed. I don't personally think it is doing what you want, and if it is then it is doing it in a VERY inefficient manner.
Clearly based on the plan, Oracle is estimating that this filter query is returning 217M rows and it is probably doing this 37152 times. Get rid of that subquery, or relate it to the other query.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
