You Asked
Hi Tom,
I am inspired with your greate work on sql query and techniques being posted for different questions.Thanks for awesome support for all of us.
Could you please help me out with below query
1.We are in process of generating report for the last 24 months.
2.Each month process is taking around 3 minutes in which we are creating temperary dynamic tables for our loading process.
3.In processing each month we have a requirment of creating 13 tables
and loading the data of 1 million of data.
4.We have used beow techniques for our loading as of now and observed the performance.
partioning tables have been used
For inserts parallel dml has been enabled and append nologging is also used
For selects which are used for inserts have parallel hint with 16 threads
I have also seen the execution plan for each and every query.
But Could you please help me whether we can still tune the below query in reducing the response time.
SELECT
f.XXX_date
,f.tsa_conf_outlet_key
,f.tsa_conf_prod_pack_key
,LPAD(d.product_group_code,9,'0')
,d.product_group_description
,LPAD(f.tsa_warehouse,5,'0') tsa_warehouse
,f.tsa_warehse_type
,f.tsa_outlet_type
,d.manufacturer_no
,d.manufacturer_name
,f.tsa_ext_catg
,f.tsa_dollars
,f.tsa_warehse_dollars
,f.tsa_units
,f.tsa_pack_units
,f.tsa_volume_units
,f.tsa_strength_units
,f.tsa_province
,f.tsa_outlet_code
,d.original_cmf10_no
,f.tsa_data_stage
,'002'
,'R01'
,TRIM('HOSPITAL INDIRE')
,SUBSTR( d.original_cmf10_no, 1, 7) cmf7
,SUBSTR( d.original_cmf10_no, 8) pack_num
,d.usc_code usc5
,tsa_dollars prvn_dollars
,0 bus_rule
FROM TSA.XXX_fact f
JOIN TSA.TPP_ALCON_01_PROD_STG_BASIC d
ON d.prod_conf_key = f.tsa_conf_prod_pack_key
WHERE f.XXX_date = 201507
AND f.XXX_province = 'ON'
AND f.XXX_ext_catg IN (SELECT o.extd_catg
FROM TPP_ALCON_01_OLP_OTLT_STG o
WHERE o.sales_catg_nbr = '002'
AND o.sales_catg_type = 'R01')
AND f.XXX_warehouse IN (SELECT LTRIM(w.warehouse_id,'0')
FROM TPP_ALCON_01_OLP_WHSE_STG w
WHERE w.sales_catg_nbr = '002'
AND w.sales_catg_type = 'R01')
AND f.tsa_data_stage IN ('T','I')
Tables info:
XXX_fact table is very big which is range partion table for each month and further list subpartioned .
Remaining tables are very small tables with less than 200 records.
Below is the execution plan without parallel option
Plan hash value: 2777196818
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55 | 9735 | 8732 (1)| 00:02:03 | | |
|* 1 | HASH JOIN RIGHT SEMI | | 55 | 9735 | 8732 (1)| 00:02:03 | | |
|* 2 | TABLE ACCESS FULL | TPP_ALCON_01_OLP_OTLT_STG | 5 | 45 | 4 (0)| 00:00:01 | | |
|* 3 | HASH JOIN RIGHT SEMI | | 274 | 46032 | 8728 (1)| 00:02:03 | | |
|* 4 | TABLE ACCESS FULL | TPP_ALCON_01_OLP_WHSE_STG | 121 | 1210 | 5 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 30722 | 4740K| 8722 (1)| 00:02:03 | | |
| 6 | TABLE ACCESS FULL | TPP_ALCON_01_PROD_STG_BASIC | 120 | 10800 | 3 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE SINGLE| | 3503K| 227M| 8701 (1)| 00:02:02 | 78 | 78 |
| 8 | PARTITION LIST SINGLE| | 3503K| 227M| 8701 (1)| 00:02:02 | 9 | 9 |
|* 9 | TABLE ACCESS FULL | XXX_FACT | 3503K| 227M| 8701 (1)| 00:02:02 | 1089 | 1089 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("F"."TSA_EXT_CATG"="O"."EXTD_CATG")
2 - filter("O"."SALES_CATG_NBR"='002' AND "O"."SALES_CATG_TYPE"='R01')
3 - access("F"."TSA_WAREHOUSE"=TO_NUMBER(LTRIM("W"."WAREHOUSE_ID",'0')))
4 - filter("W"."SALES_CATG_NBR"='002' AND "W"."SALES_CATG_TYPE"='R01')
5 - access("D"."PROD_CONF_KEY"="F"."TSA_CONF_PROD_PACK_KEY")
9 - filter(("F"."TSA_DATA_STAGE"='I' OR "F"."TSA_DATA_STAGE"='T') AND "F"."TSA_DATE"=201507)
Note
-----
- dynamic sampling used for this statement (level=2)
Above query is for 1 execution in a month.Like this we have 12 more queries for the same month.so if you help us in tunning further it will be awesome help for us.
As of now above query after passing parallel hint it is taking 12 seconds. Before that it is taking more than 50 seconds.
Like this there are thousands of reports are existing in our application.If this query is tunned then it can be awesome help for us....
Thanks for the help in advance
As part of extra information to be added
There is no index for the pertaining columns in where clause in XXX_FACT table.
We cannot create either because it is very big and useful table for so many applications where so many inserts takes place ..so we cannot create the index.
Coming to gather_plan_stats below are the real time stats
SQL_ID ga6zdxx8wndjh, child number 0
-------------------------------------
SELECT f.tsa_date ,f.tsa_conf_outlet_key
,f.tsa_conf_prod_pack_key ,LPAD(d.product_group_code,9,'0')
,d.product_group_description
,LPAD(f.tsa_warehouse,5,'0') tsa_warehouse
,f.tsa_warehse_type ,f.tsa_outlet_type
,d.manufacturer_no ,d.manufacturer_name
,f.tsa_ext_catg ,f.tsa_dollars
,f.tsa_warehse_dollars ,f.tsa_units
,f.tsa_pack_units ,f.tsa_volume_units
,f.tsa_strength_units ,f.tsa_province
,f.tsa_outlet_code ,d.original_cmf10_no
,f.tsa_data_stage ,'002' ,'R01'
,TRIM('HOSPITAL INDIRE') ,SUBSTR( d.original_cmf10_no, 1, 7)
cmf7 ,SUBSTR( d.original_cmf10_no, 8) pack_num
,d.usc_code usc5 ,tsa_dollars prvn_dollars ,0
bus_rule FROM xxx_fact f JOIN
TSA.TPP_ALCON_01_PROD_STG_BASIC d
Plan hash value: 2777196818
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8732 (100)| | | |
|* 1 | HASH JOIN RIGHT SEMI | | 55 | 9735 | 8732 (1)| 00:02:03 | | |
|* 2 | TABLE ACCESS FULL | TPP_ALCON_01_OLP_OTLT_STG | 5 | 45 | 4 (0)| 00:00:01 | | |
|* 3 | HASH JOIN RIGHT SEMI | | 274 | 46032 | 8728 (1)| 00:02:03 | | |
|* 4 | TABLE ACCESS FULL | TPP_ALCON_01_OLP_WHSE_STG | 121 | 1210 | 5 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 30722 | 4740K| 8722 (1)| 00:02:03 | | |
| 6 | TABLE ACCESS FULL | TPP_ALCON_01_PROD_STG_BASIC | 120 | 10800 | 3 (0)| 00:00:01 | | |
| 7 | PARTITION RANGE SINGLE| | 3503K| 227M| 8701 (1)| 00:02:02 | 78 | 78 |
| 8 | PARTITION LIST SINGLE| | 3503K| 227M| 8701 (1)| 00:02:02 | 9 | 9 |
|* 9 | TABLE ACCESS FULL | XXX_FACT | 3503K| 227M| 8701 (1)| 00:02:02 | 1089 | 1089 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("F"."TSA_EXT_CATG"="O"."EXTD_CATG")
2 - filter(("O"."SALES_CATG_NBR"='002' AND "O"."SALES_CATG_TYPE"='R01'))
3 - access("F"."TSA_WAREHOUSE"=TO_NUMBER(LTRIM("W"."WAREHOUSE_ID",'0')))
4 - filter(("W"."SALES_CATG_NBR"='002' AND "W"."SALES_CATG_TYPE"='R01'))
5 - access("D"."PROD_CONF_KEY"="F"."TSA_CONF_PROD_PACK_KEY")
9 - filter((INTERNAL_FUNCTION("F"."TSA_DATA_STAGE") AND "F"."TSA_DATE"=201507))
Note
-----
- dynamic sampling used for this statement (level=2)
Or else any alternate solution that we can go overall to reduce the time.
Also please find the create table script of xxx_fact table.I cannot paste for all partitions but for one partiotion below is the script.
CREATE TABLE TSA.TSA_FACT
(
TSA_OUTLET_KEY NUMBER NOT NULL,
TSA_PROD_PACK_KEY NUMBER NOT NULL,
TSA_WAREHOUSE NUMBER(4) NOT NULL,
TSA_DATE NUMBER(6) NOT NULL,
TSA_DATA_STAGE VARCHAR2(1 BYTE),
TSA_WAREHSE_TYPE VARCHAR2(1 BYTE),
TSA_OUTLET_TYPE VARCHAR2(1 BYTE),
TSA_EXT_CATG VARCHAR2(2 BYTE),
TSA_DOLLARS NUMBER(17,8),
TSA_WAREHSE_DOLLARS NUMBER(17,8),
TSA_UNITS NUMBER(18,8),
TSA_PACK_UNITS NUMBER(18,8),
TSA_VOLUME_UNITS NUMBER(18,8),
TSA_STRENGTH_UNITS NUMBER(18,8),
TSA_PROVINCE VARCHAR2(2 BYTE),
TSA_OUTLET_CODE VARCHAR2(8 BYTE) NOT NULL,
TSA_PROD_PACK NUMBER(10) NOT NULL,
TSA_CONF_PROD_PACK_KEY NUMBER,
TSA_CONF_OUTLET_KEY NUMBER,
TSA_PROVINCIAL_DOLLARS NUMBER(17,8),
TSA_PROVINCIAL_DOLLARS_AC NUMBER(3)
)
NOCOMPRESS
TABLESPACE TSA_IPV_T
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
PARTITION BY RANGE (TSA_DATE)
SUBPARTITION BY LIST (TSA_PROVINCE)
(
PARTITION DM_200901 VALUES LESS THAN ('200902')
NOLOGGING
NOCOMPRESS
TABLESPACE TSA_IPV_T
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
MAXSIZE UNLIMITED
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
( SUBPARTITION DM_200901_AB VALUES ('AB') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_BC VALUES ('BC') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_MB VALUES ('MB') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_NB VALUES ('NB') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_NL VALUES ('NL') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_NS VALUES ('NS') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_NT VALUES ('NT') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_NU VALUES ('NU') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_ON VALUES ('ON') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_PE VALUES ('PE') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_QU VALUES ('QU') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_SK VALUES ('SK') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_YU VALUES ('YU') TABLESPACE TSA_IPV_T,
SUBPARTITION DM_200901_ZZ VALUES ('ZZ') TABLESPACE TSA_IPV_T )
Please help us in rewriting the above query and kindly tell us if any more information required.
and Chris said...
Hi Manoj,
If the query takes 12s without parallel, why are you trying to force parallel?
If you want help reducing the exeuction time, please update your question with execution plan generated using the gather_plan_statistics hint, e.g.:
select /*+ gather_plan_statistics */* from dual;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
Also include create table statements and indexes for the tables involved.
--
Thanks for the update, however the gather_plan_statistics hint is still missing from the query. You need to place it immediately after the select, like so:
select /*+ gather_plan_statistics */f.tsa_date ,f.tsa_conf_outlet_key ...
That said, if you're not able to create indexes on the table tsa_fact, then it'll be a challenge to improve the performance of this query further.
The explain plan you've posted suggests there 3.5 million rows in tsa_fact. If this in the right ballpark it's going to take a while to process all this data.
Depending on the data distributions, an index on some combination of
tas_ext_catg, tsa_conf_prod_pack_key, tsa_data_stage
is likely to be helpful. Alternatively, as this is a fact table you could create individual (bitmap) indexes on each of these. This can enable Oracle to perform a star transformation:
https://blogs.oracle.com/optimizer/entry/star_transformation http://docs.oracle.com/database/121/TGSQL/tgsql_transform.htm#TGSQL94897 Yes, creating these indexes will affect your inserts. You need to balance this against your need to improve query performance however.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment