The following applies to oracle 10.2.0.5 and above
One query is slow, and the original SQL is as follows:
1 select 2 a.* 3 from (select 4 ssi.ID, 5 'small_station_info' TB, 6 (select sbi.name 7 from scene_base_info sbi 8 where sbi.id = ssi.antenna_selection) as antenna_selection, 9 ssi.antenna_height, 10 ssi.down_angle, 11 ssi.azimuth_angle, 12 ssi.ITI_ID, 13 sa.longitude, 14 sa.latitude, 15 sa.attach_id 16 from consolidation_demand cd 17 left join demand_test_info dti 18 on cd.id = dti.cd_id 19 left join demand_plan_info dpi 20 on dti.id = dpi.tdl_id 21 left join building_plan_info bpi 22 on dpi.id = bpi.dpi_id 23 left join NEAR_FAR_PLACE_INFO nfpi 24 on bpi.id = nfpi.bpi_id 25 left join SMALL_STATION_INFO ssi 26 on nfpi.id = ssi.nfpi_id 27 left join site_attachment sa 28 on TO_NUMBER(sa.longitude) is not null 29 AND TO_NUMBER(sa.latitude) > 26.074423 30 AND TO_NUMBER(sa.latitude) < 26.077573 31 AND TO_NUMBER(sa.longitude) > 119.191148 32 AND TO_NUMBER(sa.longitude) < 119.197649 33 AND sa.attach_name = 34 substr(ssi.AZIMUTH_ANGLE_PHOTO, 35 instr(ssi.AZIMUTH_ANGLE_PHOTO, '/', -1) + 1, 36 length(ssi.AZIMUTH_ANGLE_PHOTO))) a 37 where a.longitude is not null
The tables are not large and the implementation plan is as follows:
12 rows selected.
//Execution Plan
----------------------------------------------------------
Plan hash value: 1917963167
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 253 | 519 (2)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID | SCENE_BASE_INFO | 1 | 14 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SCENE_BASE_INFO_PK | 1 | | 0 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 253 | 519 (2)| 00:00:07 |
|* 4 | FILTER | | | | | |
|* 5 | HASH JOIN OUTER | | 1 | 251 | 519 (2)| 00:00:07 |
|* 6 | HASH JOIN OUTER | | 83 | 8134 | 505 (1)| 00:00:07 |
|* 7 | HASH JOIN OUTER | | 83 | 7304 | 501 (1)| 00:00:07 |
|* 8 | HASH JOIN OUTER | | 83 | 6391 | 493 (1)| 00:00:06 |
|* 9 | HASH JOIN OUTER | | 83 | 5478 | 271 (1)| 00:00:04 |
| 10 | MERGE JOIN CARTESIAN | | 36 | 2052 | 21 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| SITE_ATTACHMENT | 1 | 53 | 16 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX_SITE_ATTACHMENT_JWD | 1 | | 15 (0)| 00:00:01 |
| 13 | BUFFER SORT | | 6725 | 26900 | 5 (0)| 00:00:01 |
| 14 | INDEX FAST FULL SCAN | PK_CONSOLIDATION_DEMAND | 6725 | 26900 | 5 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | DEMAND_TEST_INFO | 15459 | 135K| 249 (1)| 00:00:03 |
| 16 | TABLE ACCESS FULL | DEMAND_PLAN_INFO | 8787 | 96657 | 221 (1)| 00:00:03 |
| 17 | TABLE ACCESS FULL | BUILDING_PLAN_INFO | 3244 | 35684 | 8 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | NEAR_FAR_PLACE_INFO | 389 | 3890 | 3 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | SMALL_STATION_INFO | 594 | 90882 | 13 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SBI"."ID"=:B1)
4 - filter("SA"."ATTACH_NAME"=SUBSTR("SSI"."AZIMUTH_ANGLE_PHOTO",INSTR("SSI"."AZIMUTH_ANGLE_PHOTO",'
/',-1)+1,LENGTH("SSI"."AZIMUTH_ANGLE_PHOTO")))
5 - access("NFPI"."ID"="SSI"."NFPI_ID"(+))
6 - access("BPI"."ID"="NFPI"."BPI_ID"(+))
7 - access("DPI"."ID"="BPI"."DPI_ID"(+))
8 - access("DTI"."ID"="DPI"."TDL_ID"(+))
9 - access("CD"."ID"="DTI"."CD_ID"(+))
11 - filter("SA"."LONGITUDE" IS NOT NULL)
12 - access(TO_NUMBER("LONGITUDE")>119.191148 AND TO_NUMBER("LATITUDE")>26.074423 AND
TO_NUMBER("LONGITUDE")<119.197649 AND TO_NUMBER("LATITUDE")<26.077573)
filter(TO_NUMBER("LONGITUDE") IS NOT NULL AND TO_NUMBER("LATITUDE")<26.077573 AND
TO_NUMBER("LATITUDE")>26.074423)
This execution plan, which looks perfectly normal, should have left join.
The key to the problem, however, is the 10-step MERGE JOIN CARTESIAN.Sort merge joins for Cartesian products, which takes a long time.
Waiting for this result can take tens of seconds, or even longer!
There are several ways to solve this problem:
- Re-collect statistics for each table--this has not been experimented with, but it may not work even if you do.In this case, however, it is likely that this is the cause.
- Enable leading tips, combined with other tips
- Use materialize prompt
Use leading tips
1 select /*+ no_merge(a) no_push_pred(a) */ 2 a.* 3 from (select 4 /*+ leading(cd dti dpi bpi ssi) */ 5 ...) a 6 where a.longitude is not null 7 /
Execution Plan
12 rows selected.
//Execution Plan
----------------------------------------------------------
Plan hash value: 1844304918
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 253 | 520 (2)| 00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID | SCENE_BASE_INFO | 1 | 14 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SCENE_BASE_INFO_PK | 1 | | 0 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 253 | 520 (2)| 00:00:07 |
|* 4 | HASH JOIN | | 1 | 251 | 520 (2)| 00:00:07 |
|* 5 | TABLE ACCESS BY INDEX ROWID| SITE_ATTACHMENT | 1 | 53 | 16 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IDX_SITE_ATTACHMENT_JWD | 1 | | 15 (0)| 00:00:01 |
|* 7 | HASH JOIN RIGHT OUTER | | 23606 | 4564K| 503 (2)| 00:00:07 |
| 8 | TABLE ACCESS FULL | SMALL_STATION_INFO | 594 | 90882 | 13 (0)| 00:00:01 |
|* 9 | HASH JOIN RIGHT OUTER | | 15459 | 679K| 490 (2)| 00:00:06 |
| 10 | TABLE ACCESS FULL | NEAR_FAR_PLACE_INFO | 389 | 3890 | 3 (0)| 00:00:01 |
|* 11 | HASH JOIN RIGHT OUTER | | 15459 | 528K| 486 (2)| 00:00:06 |
| 12 | TABLE ACCESS FULL | BUILDING_PLAN_INFO | 3244 | 35684 | 8 (0)| 00:00:01 |
|* 13 | HASH JOIN RIGHT OUTER | | 15459 | 362K| 477 (1)| 00:00:06 |
| 14 | TABLE ACCESS FULL | DEMAND_PLAN_INFO | 8787 | 96657 | 221 (1)| 00:00:03 |
|* 15 | HASH JOIN OUTER | | 15459 | 196K| 255 (1)| 00:00:04 |
| 16 | INDEX FAST FULL SCAN | PK_CONSOLIDATION_DEMAND | 6725 | 26900 | 5 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | DEMAND_TEST_INFO | 15459 | 135K| 249 (1)| 00:00:03 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SBI"."ID"=:B1)
4 - access("SA"."ATTACH_NAME"=SUBSTR("SSI"."AZIMUTH_ANGLE_PHOTO",INSTR("SSI"."AZIMUTH_ANGLE_PH
OTO",'/',-1)+1,LENGTH("SSI"."AZIMUTH_ANGLE_PHOTO")))
5 - filter("SA"."LONGITUDE" IS NOT NULL)
6 - access(TO_NUMBER("LONGITUDE")>119.191148 AND TO_NUMBER("LATITUDE")>26.074423 AND
TO_NUMBER("LONGITUDE")<119.197649 AND TO_NUMBER("LATITUDE")<26.077573)
filter(TO_NUMBER("LONGITUDE") IS NOT NULL AND TO_NUMBER("LATITUDE")<26.077573 AND
TO_NUMBER("LATITUDE")>26.074423)
7 - access("NFPI"."ID"="SSI"."NFPI_ID"(+))
9 - access("BPI"."ID"="NFPI"."BPI_ID"(+))
11 - access("DPI"."ID"="BPI"."DPI_ID"(+))
13 - access("DTI"."ID"="DPI"."TDL_ID"(+))
15 - access("CD"."ID"="DTI"."CD_ID"(+))
There is no Cartesian merge join. Step 4 is also a hash join.
Quick execution, about 0.17 seconds
Use materialize prompt
1 WITH A AS 2 (select /*+MATERIALIZE */ 3 .....) 4 select a.* from A WHERE a.longitude is not null
Execution Plan
12 rows selected.
//Execution Plan
----------------------------------------------------------
Plan hash value: 3536941173
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 23606 | 5832K| 700 (2)| 00:00:09 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | | | | | |
|* 3 | HASH JOIN RIGHT OUTER | | 23606 | 5786K| 520 (2)| 00:00:07 |
| 4 | TABLE ACCESS BY INDEX ROWID| SITE_ATTACHMENT | 1 | 53 | 16 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_SITE_ATTACHMENT_JWD | 1 | | 15 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 23606 | 4564K| 503 (2)| 00:00:07 |
| 7 | TABLE ACCESS FULL | SMALL_STATION_INFO | 594 | 90882 | 13 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | | 15459 | 679K| 490 (2)| 00:00:06 |
| 9 | TABLE ACCESS FULL | NEAR_FAR_PLACE_INFO | 389 | 3890 | 3 (0)| 00:00:01 |
|* 10 | HASH JOIN RIGHT OUTER | | 15459 | 528K| 486 (2)| 00:00:06 |
| 11 | TABLE ACCESS FULL | BUILDING_PLAN_INFO | 3244 | 35684 | 8 (0)| 00:00:01 |
|* 12 | HASH JOIN RIGHT OUTER | | 15459 | 362K| 477 (1)| 00:00:06 |
| 13 | TABLE ACCESS FULL | DEMAND_PLAN_INFO | 8787 | 96657 | 221 (1)| 00:00:03 |
|* 14 | HASH JOIN OUTER | | 15459 | 196K| 255 (1)| 00:00:04 |
| 15 | INDEX FAST FULL SCAN | PK_CONSOLIDATION_DEMAND | 6725 | 26900 | 5 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | DEMAND_TEST_INFO | 15459 | 135K| 249 (1)| 00:00:03 |
|* 17 | VIEW | | 23606 | 5832K| 180 (2)| 00:00:03 |
| 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68A2_721EF047 | 23606 | 4103K| 180 (2)| 00:00:03 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SA"."ATTACH_NAME"(+)=SUBSTR("SSI"."AZIMUTH_ANGLE_PHOTO",INSTR("SSI"."AZIMUTH_ANGLE_PHOT
O",'/',-1)+1,LENGTH("SSI"."AZIMUTH_ANGLE_PHOTO")))
5 - access(TO_NUMBER("LONGITUDE"(+))>119.191148 AND TO_NUMBER("LATITUDE"(+))>26.074423 AND
TO_NUMBER("LONGITUDE"(+))<119.197649 AND TO_NUMBER("LATITUDE"(+))<26.077573)
filter(TO_NUMBER("LONGITUDE"(+)) IS NOT NULL AND TO_NUMBER("LATITUDE"(+))<26.077573 AND
TO_NUMBER("LATITUDE"(+))>26.074423)
6 - access("NFPI"."ID"="SSI"."NFPI_ID"(+))
8 - access("BPI"."ID"="NFPI"."BPI_ID"(+))
10 - access("DPI"."ID"="BPI"."DPI_ID"(+))
12 - access("DTI"."ID"="DPI"."TDL_ID"(+))
14 - access("CD"."ID"="DTI"."CD_ID"(+))
17 - filter("A"."LONGITUDE" IS NOT NULL)
Very fast, about 0.19~0.2.
The main reason for the slowness is to generate the gt table SYS_TEMP_0FD9D68A2_721EF047.
summary
1. Better to collect statistics first
2. Consider using the leading hint when collecting statistics is invalid, followed by the materialize hint, which can disrupt some self-conscious plans of the oracle optimizer (inadequate optimizers, as oracle has already mentioned, which is why hint comes from)
3.dba is not an easy task to optimize a library and needs to do a lot of work.