You Asked
Dear Tom,
Thanks for your valuable info which is being provided by you for various questions on oracle to all the users which is also helping us in lot of ways...
Now we have a problem with a report query which is almost taking 2 minutes 40 seconds to run which is demonstrated as below. Before I give all the information on tables and query will let you the process where we are using this query.
we have a reports process which runs every month for almost 200 clients at a time.
Each client report contains 24 months data to be processed.
Each month contains 13 state codes .
And lastly each client runs 2 times for 2 different category codes.
So totally 24*13*2 is the number of iterations it needs to process for every client.
For each iteration we are in process of touching below query which is taking 2 minutes and 40 seconds .Overall if we calculate the time for whole process 24*13*2*2.40 = 1497 minutes. Also equivalent to 24 hours.
we want to reduce this time and need help in tunning below query.
DDL's of all the tables being used:
CREATE TABLE SA_FA
(
SA_OUTLET_KEY NUMBER NOT NULL,
SA_PROD_PACK_KEY NUMBER NOT NULL,
SA_WAREHOUSE NUMBER(4) NOT NULL,
SA_DATE NUMBER(6) NOT NULL,
SA_DATA_STAGE VARCHAR2(10 BYTE),
SA_WAREHSE_TYPE VARCHAR2(1 BYTE),
SA_OUTLET_TYPE VARCHAR2(1 BYTE),
SA_EXT_CATG VARCHAR2(2 BYTE),
SA_DOLLARS NUMBER(17,8),
SA_WAREHSE_DOLLARS NUMBER(17,8),
SA_UNITS NUMBER(18,8),
SA_PACK_UNITS NUMBER(18,8),
SA_VOLUME_UNITS NUMBER(18,8),
SA_STRENGTH_UNITS NUMBER(18,8),
SA_STATE VARCHAR2(2 BYTE),
SA_OUTLET_CODE VARCHAR2(8 BYTE) NOT NULL,
SA_PROD_PACK NUMBER(10) NOT NULL,
SA_CONF_PROD_PACK_KEY NUMBER,
SA_CONF_OUTLET_KEY NUMBER,
SA_state_DOLLARS NUMBER(17,8),
SA_state_DOLLARS_AC NUMBER(3)
)
PARTITION BY RANGE (SA_DATE)
SUBPARTITION BY LIST (SA_STATE)
(
PARTITION DM_201410 VALUES LESS THAN ('201411')
( SUBPARTITION DM_201410_XX1 VALUES ('XX1'),
SUBPARTITION DM_201410_XX2 VALUES ('XX2'),
SUBPARTITION DM_201410_XX3 VALUES ('XX3'),
SUBPARTITION DM_201410_XX4 VALUES ('XX4'),
SUBPARTITION DM_201410_XX5 VALUES ('XX5'),
SUBPARTITION DM_201410_XX6 VALUES ('XX6'),
SUBPARTITION DM_201410_XX7 VALUES ('XX7'),
SUBPARTITION DM_201410_XX8 VALUES ('XX8'),
SUBPARTITION DM_201410_XX9 VALUES ('XX9'),
SUBPARTITION DM_201410_XX10 VALUES ('XX10'),
SUBPARTITION DM_201410_XX11 VALUES ('XX11'),
SUBPARTITION DM_201410_XX12 VALUES ('XX12'),
SUBPARTITION DM_201410_XX13 VALUES ('XX13'),
SUBPARTITION DM_201410_XX14 VALUES ('XX14'))); --3918981 records for the STATE provided in query
CREATE TABLE SA_CLI_FA
(
SA_OUTLET_KEY NUMBER NOT NULL,
SA_PROD_PACK_KEY NUMBER NOT NULL,
SA_WAREHOUSE NUMBER(4) NOT NULL,
SA_DATE NUMBER(6) NOT NULL,
SA_DATA_STAGE VARCHAR2(10 BYTE),
SA_WAREHSE_TYPE VARCHAR2(1 BYTE),
SA_OUTLET_TYPE VARCHAR2(1 BYTE),
SA_EXT_CATG VARCHAR2(2 BYTE),
SA_DOLLARS NUMBER(17,8),
SA_WAREHSE_DOLLARS NUMBER(17,8),
SA_UNITS NUMBER(18,8),
SA_PACK_UNITS NUMBER(18,8),
SA_VOLUME_UNITS NUMBER(18,8),
SA_STRENGTH_UNITS NUMBER(18,8),
SA_STATE VARCHAR2(2 BYTE),
SA_OUTLET_CODE VARCHAR2(8 BYTE) NOT NULL,
SA_PROD_PACK NUMBER(10) NOT NULL,
SA_CONF_PROD_PACK_KEY NUMBER,
SA_CONF_OUTLET_KEY NUMBER,
SA_state_DOLLARS NUMBER(17,8),
SA_state_DOLLARS_AC NUMBER(3)
)
PARTITION BY RANGE (SA_DATE)
SUBPARTITION BY LIST (SA_STATE)
(
PARTITION DM_201410 VALUES LESS THAN ('201411')
( SUBPARTITION DM_201410_XX1 VALUES ('XX1'),
SUBPARTITION DM_201410_XX2 VALUES ('XX2'),
SUBPARTITION DM_201410_XX3 VALUES ('XX3'),
SUBPARTITION DM_201410_XX4 VALUES ('XX4'),
SUBPARTITION DM_201410_XX5 VALUES ('XX5'),
SUBPARTITION DM_201410_XX6 VALUES ('XX6'),
SUBPARTITION DM_201410_XX7 VALUES ('XX7'),
SUBPARTITION DM_201410_XX8 VALUES ('XX8'),
SUBPARTITION DM_201410_XX9 VALUES ('XX9'),
SUBPARTITION DM_201410_XX10 VALUES ('XX10'),
SUBPARTITION DM_201410_XX11 VALUES ('XX11'),
SUBPARTITION DM_201410_XX12 VALUES ('XX12'),
SUBPARTITION DM_201410_XX13 VALUES ('XX13'),
SUBPARTITION DM_201410_XX14 VALUES ('XX14'))); --46114 rows
CREATE TABLE TEST_WO_24303_PROD_STG_BASIC
(
PROD_CONF_KEY NUMBER,
ORIGINAL_CMF10_NO NUMBER(10),
PRODUCT_GROUP_CODE VARCHAR2(4 BYTE),
PRODUCT_GROUP_DESCRIPTION VARCHAR2(30 BYTE),
MANUFACTURER_NO NUMBER(5),
MANUFACTURER_NAME VARCHAR2(30 BYTE),
PRODUCT_TYPE_CODE VARCHAR2(5 BYTE),
USC_CODE NUMBER(5)
) -- 75 rows
CREATE TABLE TEST_SA_PRVN_PRC_DYN_FNL
(
BASE_PRICE_CONTROL_ID NUMBER NOT NULL,
MONTH_PRICE_CONTROL_ID NUMBER NOT NULL,
PROCESSING_MONTH NUMBER(6) NOT NULL,
DATA_MONTH NUMBER(6) NOT NULL,
STATE_CODE VARCHAR2(2 BYTE) NOT NULL,
DATA_MONTH_STATE VARCHAR2(8 BYTE) NOT NULL,
OUTLET_TYPE_CODE VARCHAR2(1 BYTE),
WAREHOUSE_TYPE_CODE VARCHAR2(1 BYTE),
PROD_PACK_KEY NUMBER,
T_PRICE NUMBER(17,8),
UNITS NUMBER(17,8) NOT NULL,
PRICE NUMBER(17,8) NOT NULL,
OVERRIDE_PRICE NUMBER(17,8),
OVERRIDE_YN_IND VARCHAR2(1 BYTE),
PRICE_AC NUMBER(3)
) --28394 rows
CREATE TABLE TEST_WO_24303_OLP_OTLT_STG
(
SALES_CATG_NBR VARCHAR2(3 BYTE),
SALES_CATG_TYPE VARCHAR2(3 BYTE),
SALES_CATG_DESC VARCHAR2(25 BYTE),
EXTD_CATG VARCHAR2(2 BYTE)
) --71 rows present
CREATE TABLE TEST_WO_24303_OLP_WHSE_STG
(
SALES_CATG_NBR VARCHAR2(3 BYTE),
SALES_CATG_TYPE VARCHAR2(3 BYTE),
SALES_CATG_DESC VARCHAR2(25 BYTE),
WAREHOUSE_ID VARCHAR2(5 BYTE)
) --1042 rows present
Query being fired:
SELECT /*gather_plan_statistics*//*parallel(fu,8)*/
fu.sa_date,
fu.sa_conf_outlet_key,
fu.sa_conf_prod_pack_key,
LPAD (d.product_group_code, 9, '0'),
d.product_group_description,
LPAD (fu.sa_warehouse, 5, '0') sa_warehouse,
fu.sa_warehse_type,
fu.sa_outlet_type,
d.manufacturer_no,
d.manufacturer_name,
fu.sa_ext_catg,
fu.sa_dollars,
fu.sa_warehse_dollars,
fu.sa_units,
fu.sa_pack_units,
fu.sa_volume_units,
fu.sa_strength_units,
fu.sa_STATE,
fu.sa_outlet_code,
d.original_cmf10_no,
fu.sa_data_stage,
'001',
'M01',
TRIM ('RETAIL '),
SUBSTR (d.original_cmf10_no, 1, 7) cmf7,
SUBSTR (d.original_cmf10_no, 8) pack_num,
d.usc_code usc5,
fu.sa_state_dollars prvn_dollars,
1 bus_rule
FROM (SELECT f.sa_date,
f.sa_conf_outlet_key,
f.sa_conf_prod_pack_key,
f.sa_warehouse,
f.sa_warehse_type,
f.sa_outlet_type,
f.sa_ext_catg,
f.sa_dollars,
f.sa_warehse_dollars,
f.sa_units,
f.sa_pack_units,
f.sa_volume_units,
f.sa_strength_units,
f.sa_STATE,
f.sa_outlet_code,
f.sa_data_stage,
sa_state_dollars
FROM sa.sa_FA f
WHERE f.sa_date = 201410 AND f.sa_STATE = 'XX5'
UNION ALL
SELECT f.sa_date,
f.sa_conf_outlet_key,
f.sa_conf_prod_pack_key,
f.sa_warehouse,
f.sa_warehse_type,
f.sa_outlet_type,
f.sa_ext_catg,
f.sa_dollars,
f.sa_warehse_dollars,
f.sa_units,
f.sa_pack_units,
f.sa_volume_units,
f.sa_strength_units,
f.sa_STATE,
f.sa_outlet_code,
f.sa_data_stage,
sa_state_dollars
FROM caling.test_sa_cli_fa f
WHERE f.sa_date = 201410
AND f.sa_STATE = 'XX5'
AND f.sa_Client IN ('WMAT')) fu
JOIN caling.test_WO_24303_PROD_STG_BASIC d
ON d.prod_conf_key = fu.sa_conf_prod_pack_key
LEFT OUTER JOIN ( SELECT data_month
,prod_pack_key
,outlet_type_code
,warehouse_type_code
,override_price
,price
FROM caling.test_sa_PRVN_PRC_DYN_FNL) n
ON fu.sa_date = n.data_month
AND fu.sa_conf_prod_pack_key = n.prod_pack_key
AND fu.sa_outlet_type = n.outlet_type_code
AND fu.sa_warehse_type = n.warehouse_type_code
where fu.sa_ext_catg IN (SELECT o.extd_catg
FROM caling.test_WO_24303_OLP_OTLT_STG o
WHERE o.sales_catg_nbr = '001'
AND o.sales_catg_type = 'R01')
AND fu.sa_warehouse IN (SELECT LTRIM(w.warehouse_id,'0')
FROM caling.test_WO_24303_OLP_WHSE_STG w
WHERE w.sales_catg_nbr = '001'
AND w.sales_catg_type = 'R01')
AND (fu.sa_data_stage IN ('T','C')
or fu.sa_data_stage like 'CO%')
Overall plan which is extracted through display cursor by making use of gather_plan_statistics:
SQL_ID a2ykn14vaajt3, child number 0
-------------------------------------
SELECT /*gather_plan_statistics*//*parallel(fu,8)*/ fu.sa_date,
fu.sa_conf_outlet_key, fu.sa_conf_prod_pack_key,
LPAD (d.product_group_code, 9, '0'),
d.product_group_description, LPAD (fu.sa_warehouse, 5, '0')
sa_warehouse, fu.sa_warehse_type, fu.sa_outlet_type,
d.manufacturer_no, d.manufacturer_name,
fu.sa_ext_catg, fu.sa_dollars, fu.sa_warehse_dollars,
fu.sa_units, fu.sa_pack_units,
fu.sa_volume_units, fu.sa_strength_units,
fu.sa_STATE, fu.sa_outlet_code, d.original_cmf10_no,
fu.sa_data_stage, '001', 'M01', TRIM
('RETAIL '), SUBSTR (d.original_cmf10_no, 1, 7) cmf7,
SUBSTR (d.original_cmf10_no, 8) pack_num, d.usc_code usc5,
fu.sa_state_dollars prvn_dollars, 1 bus_rule FROM
(SELECT f.sa_date, f.sa_conf_outlet_key,
f.sa_
Plan hash value: 530101932
------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 |00:02:24.10 | 47587 | 41068 | | | |
|* 1 | HASH JOIN OUTER | | 1 | 258 | 8 |00:02:24.10 | 47587 | 41068 | 632K| 632K| 979K (0)|
|* 2 | HASH JOIN RIGHT SEMI | | 1 | 258 | 8 |00:02:22.92 | 47456 | 40994 | 1245K| 1245K| 1094K (0)|
|* 3 | TABLE ACCESS FULL | TEST_WO_24303_OLP_OTLT_STG | 1 | 25 | 19 |00:00:00.01 | 2 | 0 | | | |
|* 4 | HASH JOIN | | 1 | 387 | 245 |00:02:25.21 | 47454 | 40994 | 686K| 686K| 1193K (0)|
| 5 | TABLE ACCESS FULL | TEST_WO_24303_PROD_STG_BASIC | 1 | 75 | 75 |00:00:00.01 | 2 | 0 | | | |
|* 6 | HASH JOIN RIGHT SEMI | | 1 | 18426 | 83193 |00:02:26.04 | 47452 | 40994 | 1012K| 1012K| 1198K (0)|
|* 7 | TABLE ACCESS FULL | TEST_WO_24303_OLP_WHSE_STG | 1 | 232 | 180 |00:00:00.01 | 3 | 0 | | | |
| 8 | VIEW | | 1 | 4109K| 3761K|00:02:03.32 | 47449 | 40994 | | | |
| 9 | UNION-ALL | | 1 | | 3761K|00:01:55.23 | 47449 | 40994 | | | |
| 10 | PARTITION RANGE SINGLE| | 1 | 4059K| 3715K|00:00:51.92 | 24970 | 24935 | | | |
| 11 | PARTITION LIST SINGLE| | 1 | 4059K| 3715K|00:00:44.38 | 24970 | 24935 | | | |
|* 12 | TABLE ACCESS FULL | SA_FA | 1 | 4059K| 3715K|00:00:34.65 | 24970 | 24935 | | | |
|* 13 | TABLE ACCESS FULL | TEST_SA_CLI_FA | 1 | 49337 | 46110 |00:00:42.69 | 22479 | 16059 | | | |
| 14 | TABLE ACCESS FULL | TEST_SA_PRVN_PRC_DYN_FNL | 1 | 28394 | 28394 |00:00:01.14 | 131 | 74 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("FU"."SA_WAREHSE_TYPE"="WAREHOUSE_TYPE_CODE" AND "FU"."SA_OUTLET_TYPE"="OUTLET_TYPE_CODE" AND
"FU"."SA_CONF_PROD_PACK_KEY"="PROD_PACK_KEY" AND "FU"."SA_DATE"="DATA_MONTH")
2 - access("FU"."SA_EXT_CATG"="O"."EXTD_CATG")
3 - filter(("O"."SALES_CATG_NBR"='001' AND "O"."SALES_CATG_TYPE"='R01'))
4 - access("D"."PROD_CONF_KEY"="FU"."SA_CONF_PROD_PACK_KEY")
6 - access("FU"."SA_WAREHOUSE"=TO_NUMBER(LTRIM("W"."WAREHOUSE_ID",'0')))
7 - filter(("W"."SALES_CATG_NBR"='001' AND "W"."SALES_CATG_TYPE"='R01'))
12 - filter(("F"."SA_DATE"=201410 AND (INTERNAL_FUNCTION("F"."SA_DATA_STAGE") OR "F"."SA_DATA_STAGE" LIKE 'CO%')))
13 - filter(("F"."SA_DATE"=201410 AND "F"."SA_STATE"='ON' AND "F"."SA_CLIENT"='WMAT' AND (INTERNAL_FUNCTION("F"."SA_DATA_STAGE") OR
"F"."SA_DATA_STAGE" LIKE 'CO%')))
and Chris said...
Oracle spent more than two minutes executing full scans of the tables in the union-all. This is the majority of the time.
But you're eliminating nearly all of the ~4 million rows this returns! So this is where you want to focus your attention.
Some thoughts:
- It's taking 42s to read 46,110 rows from TEST_SA_CLI_FA (step 13). This seems rather high to me. Particularly given the previous step reads nearly 100x more rows in less time! Check the disks this is on to see how they're performing.
- The subqueries on test_wo_24303_olp_whse_stg and test_wo_24303_olp_otlt_stg are eliminating many rows.
Do you have indexes on the columns these join to in SA_FA & TEST_SA_CLI_FA? If not, have you tried creating them?
Also, I would try placing the subqueries for both inside the union all. e.g.:
select f.sa_date, f.sa_conf_outlet_key, f.sa_conf_prod_pack_key,
f.sa_warehouse, f.sa_warehse_type, f.sa_outlet_type,
f.sa_ext_catg, f.sa_dollars, f.sa_warehse_dollars,
f.sa_units, f.sa_pack_units, f.sa_volume_units,
f.sa_strength_units, f.sa_state, f.sa_outlet_code,
f.sa_data_stage, sa_state_dollars
from sa.sa_fa f
where f.sa_date = 201410
and f.sa_state = 'XX5'
and f.sa_warehouse in
(select ltrim ( w.warehouse_id,'0' )
from caling.test_wo_24303_olp_whse_stg w
where w.sales_catg_nbr = '001'
and w.sales_catg_type = 'R01'
)
and f.sa_ext_catg in
(select o.extd_catg
from caling.test_wo_24303_olp_otlt_stg o
where o.sales_catg_nbr = '001'
and o.sales_catg_type = 'R01'
)
union all
select f.sa_date, f.sa_conf_outlet_key, f.sa_conf_prod_pack_key,
f.sa_warehouse, f.sa_warehse_type, f.sa_outlet_type,
f.sa_ext_catg, f.sa_dollars, f.sa_warehse_dollars,
f.sa_units, f.sa_pack_units, f.sa_volume_units,
f.sa_strength_units, f.sa_state, f.sa_outlet_code,
f.sa_data_stage, sa_state_dollars
from caling.test_sa_cli_fa f
where f.sa_date = 201410
and f.sa_state = 'XX5'
and f.sa_client in ( 'WMAT' )
and f.sa_warehouse in
(select ltrim ( w.warehouse_id,'0' )
from caling.test_wo_24303_olp_whse_stg w
where w.sales_catg_nbr = '001'
and w.sales_catg_type = 'R01'
)
and f.sa_ext_catg in
(select o.extd_catg
from caling.test_wo_24303_olp_otlt_stg o
where o.sales_catg_nbr = '001'
and o.sales_catg_type = 'R01'
)
These tables are both tiny. So the effect of accessing them twice should be small.
- Finally you have a parallel "hint" in the query. But it's actually a comment! What difference does this make to the performance?
PS - Thanks for providing an details of your query and tables along with the execution plan!