Sorry for the inadequate information. I did request the link to Blue shadow's SQL FAQ's to try to improve my postings for this type of material. I can promise you that whatever is incomplete/inaccurate etc. was not intentional.
Anyhow, I tried to use: When your query takes too long ... & https://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html as my template - I thought that I had done so.
Here is the actual query (which is transformed by the BI Server, based on the logical model; as an aside ... we haven't ruled out that the modeling itself is not the issue. It could be):
WITH SAWITH0 AS (select /*+ gather_plan_statistics */ sum(case when T2520043.ACTIVE_HOLD_STATUS_FLAG = 'Y' then 1 else 0 end ) as c1, sum(case when T2520043.ACTIVE_HOLD_STATUS_FLAG = 'Y' then T2520043.DOC_INVOICE_AMT else 0 end ) as c2, T2520043.INVOICE_NUM as c3, T2972724.PO_NUM as c4, T2519902.FULL_NAME as c5, case when T2519709.ROW_WID = 0 then NULL else T2519709.MCAL_DAY_DT end as c6, case when T2618846.ROW_WID = 0 then NULL else T2618846.MCAL_DAY_DT end as c7, T2522555.ORG_NAME as c8, T2622263.FULL_NAME as c9, T2639505.SPLR_ACCT_NAME as c10, T2534878.NAME as c11, T2616936.SUPPLIER_NUM as c12, T2591329.ROW_WID as c13, T2618846.ROW_WID as c14, T2519709.ROW_WID as c15, T2519877.W_HOLD_TYPE as c16, T2519877.HOLD_CODE as c17, T2519877.DATASOURCE_NUM_ID as c18 from W_PARTY_D T2534878 /* Dim_W_PARTY_D_Supplier */ , W_PARTY_ORG_D T2616936 /* Dim_W_PARTY_ORG_D_Supplier */ , W_EMPLOYEE_D T2622263 /* Dim_W_EMPLOYEE_D_Purchase_Requestors */ , W_SUPPLIER_ACCOUNT_D T2639505 /* Dim_W_SUPPLIER_ACCOUNT_D */ , W_USER_D T2519902 /* Dim_W_USER_D_Held_By */ , W_MCAL_DAY_D T2519709 /* Dim_W_MCAL_DAY_D_ Hold_Date_Fiscal_Calendar */ , W_MCAL_DAY_D T2618846 /* Dim_W_MCAL_DAY_D_Invoiced_Date_Fiscal_Calendar */ , (SELECT DATASOURCE_NUM_ID, INTEGRATION_ID, ORG_DESCR, ORG_NAME, LANGUAGE_CODE FROM W_INT_ORG_D_TL WHERE LANGUAGE_CODE = 'US') T2522555, W_INT_ORG_D T2591329 /* Dim_W_INT_ORG_D_Payables_Org */ , W_AP_HOLD_RELEASE_REASON_D T2519877 /* Dim_ W_AP_HOLD_RELEASE_REASON_D_Hold_Reason */ , W_AP_HOLDS_F T2520043 /* Fact_W_AP_HOLDS_F */ , (SELECT DISTINCT PURCH_INVOICE_NUM ,PURCH_ORDER_NUM as PO_NUM ,X_DISCARDED_FLAG FROM W_AP_XACT_F WHERE PURCH_ORDER_NUM IS NOT NULL UNION ALL SELECT DISTINCT PURCH_INVOICE_NUM ,PURCH_ORDER_NUM as PO_NUM ,X_DISCARDED_FLAG FROM W_AP_XACT_F WHERE PURCH_ORDER_NUM IS NULL AND PURCH_ORDER_DT_WID=0) T2972724 where ( T2520043.SUPPLIER_WID = T2534878.ROW_WID and T2519902.ROW_WID = T2520043.HELD_BY_WID and T2520043.SPLR_ACCT_WID = T2639505.ROW_WID and T2519709.ADJUSTMENT_PERIOD_FLG = 'N' and T2519709.MCAL_CAL_WID = T2520043.MCAL_CAL_WID and T2519709.MCAL_DAY_DT_WID = T2520043.HOLD_DATE_WID and T2520043.X_PURCH_REP_WID = T2622263.ROW_WID and T2522555.DATASOURCE_NUM_ID = T2591329.DATASOURCE_NUM_ID and T2520043.PAYABLES_ORG_WID = T2591329.SCD1_WID and T2519877.ROW_WID = T2520043.HOLD_REASON_WID and T2520043.INVOICE_NUM = T2972724.PURCH_INVOICE_NUM and T2522555.INTEGRATION_ID = T2591329.INTEGRATION_ID and T2520043.INVOICED_ON_DT_WID = T2618846.MCAL_DAY_DT_WID and T2520043.MCAL_CAL_WID = T2618846.MCAL_CAL_WID and T2520043.DELETE_FLG = 'N' and T2534878.SUPPLIER_FLG = 'Y' and T2534878.PARTY_ORG_WID = T2616936.ROW_WID and T2591329.CURRENT_FLG = 'Y' and T2618846.ADJUSTMENT_PERIOD_FLG = 'N' and (T2519877.TYPE_FLG in ('H', 'U') or T2519877.TYPE_FLG is null) and (T2591329.PAYABLES_ORG_FLG in ('U', 'Y')) and case when T2519709.ROW_WID = 0 then NULL else T2519709.MCAL_DAY_DT end between TO_DATE('2018-07-01 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') and TO_DATE('2018-10-24 00:00:00' , 'YYYY-MM-DD HH24:MI:SS') ) group by T2519709.ROW_WID, T2519877.DATASOURCE_NUM_ID, T2519877.W_HOLD_TYPE, T2519877.HOLD_CODE, T2519902.FULL_NAME, T2520043.INVOICE_NUM, T2522555.ORG_NAME, T2534878.NAME, T2591329.ROW_WID, T2616936.SUPPLIER_NUM, T2618846.ROW_WID, T2622263.FULL_NAME, T2639505.SPLR_ACCT_NAME, T2972724.PO_NUM, case when T2519709.ROW_WID = 0 then NULL else T2519709.MCAL_DAY_DT end , case when T2618846.ROW_WID = 0 then NULL else T2618846.MCAL_DAY_DT end ) select 0 as c1, D1.c3 as c2, D1.c4 as c3, D1.c5 as c4, D1.c6 as c5, D1.c7 as c6, D1.c8 as c7, D1.c9 as c8, D1.c10 as c9, D1.c11 as c10, D1.c12 as c11, D1.c1 as c12, D1.c2 as c13, D1.c13 as c14, D1.c14 as c15, D1.c15 as c16, D1.c16 as c17, D1.c17 as c18, D1.c18 as c19 from SAWITH0 D1 where ( 0 < D1.c1 ) order by c17;
CharlesM, make sure that the statistics for all objects referenced in the query are current. As if seems from one of your earlier replies if any of the tables and associated indexes show different statistics collection times you should probably recollect that table and its indexes to see if it makes a difference.
Were any database parameters changed just prior to this query becoming a problem?
HTH -- Mark D Powell --
Thanks Mark. I will give that I try. To the best of my knowledge, no database parameters have changed ... but, this was a new report created by our BI development team. So, it never ran any quicker than this. My understanding is that they were trying to solve a duplicate data issue in a data warehouse report. To do this, another join was added in the physical (database layer) of the BI model.
Here's what I'll use for each of the objects in the query:
OWNNAME => DW',
TABNAME => 'W_PARTY_D ',
ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
DEGREE => DBMS_STATS.DEFAULT_DEGREE,
METHOD_OPT=> 'FOR ALL COLUMNS SIZE AUTO',
CASCADE => TRUE;
That look ok? I am taking that from the code of the ETL tool (Oracle Data Integrator), because that is what it is using during the "gather stats" step (with the exception of CASCADE => FALSE in the code)
1 person found this helpful
CharlesM, from your post if the only difference is that you are also gathering the table indexes at the same time as the table then why ask, "look ok?" Either you have confidence in your idea or not. If you are worried the fresh statistics will get you a plan change but not for the better then capture the existing statistics first then update them.
If a change was made to the query and this is the first run the possibility exists that stale statistics or not that the plan is a good as it gets. All you can do is try to improve it. In my view the first place you start is making sure the statistics are good.
Make sure all useable indexes exist. Study the resulting actual (not explain) plan after the statistics are updated and either try to tune more or report that there is nothing else you can do.
HTH -- Mark D Powell --
I wasn't sure if those were the ideal options for the gather stats command, in this case. I have read through some notes on MOS to try and understand the parameters better.
Thanks for the suggestions. I will continue working on this from my end.