OraFAQ Forum: Performance Tuning » Please help me to improve the performance of this query.

Home » RDBMS Server » Performance Tuning » Please help me to improve the performance of this query. (Oracle 11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Please help me to improve the performance of this query. [message #589534]

Mon, 08 July 2013 06:37

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi All,

This is my main query as of now it's not returning any rows.
It's taking 1 minute to display the result for no rows.
The cost of the query is 617483.
In this query V_QUALITY_CONTROL_REPORT_DATA is a view.

SELECT   FAX_ID,
         FAX_OFFER_ID,
         HOLD_TYPE,
         ACTION_OWNER,
         ORDER_NUMBER,
         OMEGA_ORDER_NUMBER,
         COUNTRY_NAME,
         CUSTOMER_PREFIX,
         DEFAULT_COUNTRY_CODE AS COUNTRY_CODE,
         PO_NUMBER,
         OFFER_NUMBER,
         REVENUE,
         CREATED_BY,
         CREATION_DATE,
         CUSTOMER_NAME,
         CUSTOMER_NUMBER,
         SALESPERSON_NAME,
         SALESPERSON_EMAIL,
         PAYMENT_TYPE,
         AGED_DAYS,
         RESULT_OF_ORDER_DATA_CALL,
         ORDER_SEGMENT,
         ORDER_CREATOR,
         ORDER_CREATOR_EMAIL,
         CUSTOMER_BASE,
         OMEGA_CUSTOMER_NUMBER,
         CUSTOMER_BILL,
         LAST_TRANSITION_DATE,
         LAST_TRANSITION_CHANGED_BY,
         LAST_TRANSITION_WORKGROUP_FROM,
         LAST_TRANSITION_WORKGROUP_TO,
         LAST_TRANSITION_REASON,
         LAST_TRANSITION_COMMENT,
         TOTAL_SELLING_PRICE,
         CURRENCY_CODE
  FROM   ORL.V_QUALITY_CONTROL_REPORT_DATA
 WHERE   AGED_DAYS < 8;

--V_QUALITY_CONTROL_REPORT_DATA
The below query is the definition of the view.
This query is returning 2162761 records.
The cost of the query is 809487.
In this query FAX_LAST_TRANSITION_V is a view.

SELECT   FH.ID AS FAX_ID,
            FOD.ID AS FAX_OFFER_ID,
            'ORL Rejection' AS HOLD_TYPE,
            'Sales' AS ACTION_OWNER,
            FOD.ORDER_NUMBER,
            FOD.OMEGA_ORDER_NUMBER,
            BU_MAPPING.COUNTRY_NAME,
            BU_MAPPING.CUSTOMER_PREFIX,
            BU_MAPPING.DEFAULT_COUNTRY_CODE,
            FOD.PURCHASE_ORDER_NUMBER AS PO_NUMBER,
            FOD.OFFER_NUMBER,
            FOD.VALUE AS REVENUE,
            FOD.CREATED_BY,
            FOD.CREATION_DATE,
            FH.CUSTOMER_NAME,
            FH.CUSTOMER_NUMBER,
            SPV.salesperson_name,
            SPV.email AS SALESPERSON_EMAIL,
            FH.PAYMENT_TYPE,
            ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) AS AGED_DAYS,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_DATA (BU_MAPPING.BU_ID,
                                                           FOD.ORDER_NUMBER)
               AS RESULT_OF_ORDER_DATA_CALL,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_SEGMENT ()
               AS ORDER_SEGMENT,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_CREATOR ()
               AS ORDER_CREATOR,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_CREATOR_EMAIL ()
               AS ORDER_CREATOR_EMAIL,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_CUSTOMER_BASE ()
               AS CUSTOMER_BASE,
            REPLACE (
               ORL.ORL_QUALITY_CONTROL_HELPER.GET_OMEGA_CUSTOMER_NUMBER (),
               BU_MAPPING.CUSTOMER_PREFIX
            )
               AS OMEGA_CUSTOMER_NUMBER,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_CUSTOMER_BILL ()
               AS CUSTOMER_BILL,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_TOTAL_SELLING_PRICE ()
               AS TOTAL_SELLING_PRICE,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_CURRENCY_CODE ()
               AS CURRENCY_CODE,
            LTH.CHANGED_DATE AS LAST_TRANSITION_DATE,
            LTH.CHANGED_BY AS LAST_TRANSITION_CHANGED_BY,
            LTH.FROM_WORKGROUP_DESCRIPTION AS LAST_TRANSITION_WORKGROUP_FROM,
            LTH.TO_WORKGROUP_DESCRIPTION AS LAST_TRANSITION_WORKGROUP_TO,
            LTH.REASON_DESCRIPTION AS LAST_TRANSITION_REASON,
            LTH.COMMENTS AS LAST_TRANSITION_COMMENT
     FROM               ORL.FAX_OFFER_DETAIL FOD
                     INNER JOIN
                        ORL.FAX_HEADER FH
                     ON FH.ID = FOD.FAX_ID
                  INNER JOIN
                        ORL.FAX_SOURCE FS
                     INNER JOIN
                        APPS_GLOBAL.GLOBAL_BU_MAPPING BU_MAPPING
                     ON BU_MAPPING.BU_ID = FS.BUID
                  ON FS.ID = FH.FAX_SOURCE
               LEFT OUTER JOIN
                  ORL.FAX_LAST_TRANSITION_V LTH
               ON LTH.FAX_ID = FH.ID
            LEFT OUTER JOIN
               ORL.SALESPERSON_V SPV
            ON SPV.salesperson_id = FH.SALES_PERSON;

--FAX_LAST_TRANSITION_V

The below query is the definition of the view.
This query is returning 2377476 records.
The cost of the query is 69614.

   SELECT   FH.ID AS FAX_ID,
            FROMW.ID AS FROM_WORKGROUP_ID,
            FROMW.NAME AS FROM_WORKGROUP_NAME,
            FROMW.DESCRIPTION AS FROM_WORKGROUP_DESCRIPTION,
            TOW.ID AS TO_WORKGROUP_ID,
            TOW.NAME AS TO_WORKGROUP_NAME,
            TOW.DESCRIPTION AS TO_WORKGROUP_DESCRIPTION,
            WTR.ID AS REASON_ID,
            WTR.REASON AS REASON_DESCRIPTION,
            FTH.CHANGED_BY,
            FTH.CHANGED_DATE,
            FTH.COMMENTS,
            FTH.IMPERSONATED_BY
     FROM                  ORL.FAX_HEADER FH
                        INNER JOIN
                           ORL.FAX_TRANSITION_HISTORY FTH
                        ON FH.LAST_TRANSITION_ID = FTH.ID
                     INNER JOIN
                        ORL.WORKGROUP_TRANSITION_REASON WTR
                     ON WTR.ID = FTH.TRANSITION_REASON_ID
                  INNER JOIN
                     ORL.WORKGROUP_TRANSITION WT
                  ON WTR.WORKGROUP_TRANSITION_ID = WT.ID
               INNER JOIN
                  ORL.WORKGROUP FROMW
               ON WT.CURRENT_WORKGROUP_ID = FROMW.ID
            INNER JOIN
               ORL.WORKGROUP TOW
            ON WT.NEXT_WORKGROUP_ID = TOW.ID;

Total number of records in each table and view.

SELECT COUNT(*) FROM ORL.FAX_OFFER_DETAIL;--3210202
 
SELECT COUNT(*) FROM ORL.FAX_HEADER;--2423269
 
SELECT COUNT(*) FROM ORL.FAX_SOURCE;--2368
  
SELECT COUNT(*) FROM GLOBAL_BU_MAPPING;--9
 
SELECT COUNT(*) FROM ORL.FAX_LAST_TRANSITION_V;--2377476
 
SELECT COUNT(*) FROM ORL.SALESPERSON_V;--24639
  
SELECT COUNT(*) FROM ORL.FAX_TRANSITION_HISTORY--3019203
  
SELECT COUNT(*) FROM ORL.WORKGROUP_TRANSITION_REASON--10754
    
SELECT COUNT(*) FROM ORL.WORKGROUP_TRANSITION--6193
    
SELECT COUNT(*) FROM ORL.WORKGROUP--1388
    
SELECT COUNT(*) FROM ORL.V_QUALITY_CONTROL_REPORT_DATA--2162761

Please help how to improve the performance of this query.

Thanks in advance.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #589547 is a reply to message #589534]

Mon, 08 July 2013 08:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #589581 is a reply to message #589534]

Mon, 08 July 2013 22:53

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

yep, could be anything.

Could be your outer joins are preventing view merging.
Could be one or more of those function calls has gone bad (looks like someone's object design (Yuk!))
Could be your join syntax has a mistake (don't fancy those run on inner joins to fs and bu_mapping (no clue if it makes a difference but I don't like them))

Since you don't know how to use a query plan, but do know how to do SQL, your best bet is to break your query down into its sub parts to find out where all the time is going. It is called query decomposition and reconstruction and it is an old tried and true technique. Not fast but usually provides insight in tough cases.

Good luck. Kevin

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #589921 is a reply to message #589581]

Fri, 12 July 2013 01:13

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi Keven ,

Thanks for your response.

I am posting the indexes on all the tables being used in this query.

Indexes on FAX_HEADER table.
CREATE BITMAP INDEX APPS_GLOBAL.BITMAPINDX_WORKGROUP_ID_RAM ON ORL.FAX_HEADER(WORKGROUP_ID)
CREATE INDEX ORL.FAX_HEADER_FS_IDX ON ORL.FAX_HEADER(FAX_SOURCE)
CREATE INDEX ORL.FAX_HEADER_IDX2 ON ORL.FAX_HEADER(UPPER("CUSTOMER_NAME")) 
CREATE INDEX ORL.FAX_HEADER_IDX4 ON ORL.FAX_HEADER(DATE_RECEIVED)
CREATE INDEX ORL.FAX_HEADER_IDX5 ON ORL.FAX_HEADER(SALES_PERSON)
CREATE INDEX ORL.FAX_HEADER_IDX6 ON ORL.FAX_HEADER(CUSTOMER_OMEGA_NUMBER)
CREATE INDEX ORL.FAX_HEADER_SUBJ_IDX2 ON ORL.FAX_HEADER( REGEXP_SUBSTR (UPPER("SUBJECT"),U'(QUOTE|DEVIS)005CD{0,}005Cd{3,}',1,1,'i',1))
CREATE INDEX ORL.IDX_FAX_HEADER_LAST_TRANSITION ON ORL.FAX_HEADER(LAST_TRANSITION_ID)
CREATE BITMAP INDEX ORL.IDX_FAX_HEADER_SPECIAL_RULE ON ORL.FAX_HEADER(IS_SPECIAL_RULE_APPLIED)
CREATE INDEX ORL.IDX_FAX_HEADER_SUBJECT ON ORL.FAX_HEADER(SUBJECT)
CREATE INDEX ORL.IDX_FAX_HEADER_UPPER_SUBJECT ON ORL.FAX_HEADER(UPPER("SUBJECT"))
CREATE UNIQUE INDEX ORL.PK_FAX_HEADER ON ORL.FAX_HEADER(ID)
 
Indexes on FAX_OFFER_DETAIL table.
CREATE INDEX ORL.FAX_OFFER_DETAIL_IDX1 ON ORL.FAX_OFFER_DETAIL(FAX_ID)
CREATE INDEX ORL.FAX_OFFER_DETAIL_IDX2 ON ORL.FAX_OFFER_DETAIL(SOURCE_OFFER_NO)
CREATE INDEX ORL.FAX_OFFER_DETAIL_IDX3 ON ORL.FAX_OFFER_DETAIL(VERSION_NO)
CREATE INDEX ORL.IDX_OFFER_NUMBER ON ORL.FAX_OFFER_DETAIL(OFFER_NUMBER)
CREATE INDEX ORL.IDX_OMEGA_ORDER_NUMBER ON ORL.FAX_OFFER_DETAIL(OMEGA_ORDER_NUMBER)
CREATE INDEX ORL.IDX_ORDER_NUMBER ON ORL.FAX_OFFER_DETAIL(ORDER_NUMBER)
CREATE INDEX ORL.IDX_PURCHASE_ORDER_NUMBER ON ORL.FAX_OFFER_DETAIL(PURCHASE_ORDER_NUMBER)
CREATE UNIQUE INDEX ORL.PK_FAX_OFFER_DETAIL ON ORL.FAX_OFFER_DETAIL(ID)
 
Indexes on FAX_SOURCE table.
CREATE INDEX APPS_GLOBAL.BITMAPINDX_WORKFLOW_ID_RAM ON ORL.FAX_SOURCE(WORKFLOW_ID)
CREATE INDEX ORL.IDX_BUID_RAM ON ORL.FAX_SOURCE(BUID)
CREATE INDEX ORL.IDX_FAX_NUMBER_RAM ON ORL.FAX_SOURCE(UPPER("FAX_NUMBER")) 
CREATE UNIQUE INDEX ORL.PK_FAX_SOURCE ON ORL.FAX_SOURCE(ID)
CREATE UNIQUE INDEX ORL.UK_BU_FAX_SOURCE_NAME ON ORL.FAX_SOURCE(BUID, FAX_NUMBER)
 
Indexes on FAX_TRANSITION_HISTORY table.
CREATE INDEX ORL.FAX_TRANSITION_HISTORY_IDX1 ON ORL.FAX_TRANSITION_HISTORY(FAX_ID, ID)
CREATE INDEX ORL.FAX_TRANSITION_HISTORY_IDX2 ON ORL.FAX_TRANSITION_HISTORY(TRANSITION_REASON_ID)
CREATE INDEX ORL.FAX_TRANSITION_HISTORY_IDX3 ON ORL.FAX_TRANSITION_HISTORY(CHANGED_DATE)
CREATE UNIQUE INDEX ORL.PK_FAX_TRANSITION_HISTORY ON ORL.FAX_TRANSITION_HISTORY(ID)
 
Indexes on WORKGROUP_TRANSITION_REASON table.
 
CREATE UNIQUE INDEX ORL.PK_WORKGROUP_TRANSITION_REASON ON ORL.WORKGROUP_TRANSITION_REASON(ID)
CREATE UNIQUE INDEX ORL.UK_TRANSITION_REASON ON ORL.WORKGROUP_TRANSITION_REASON(WORKGROUP_TRANSITION_ID, REASON)
CREATE INDEX ORL.WORKGROUP_TRANS_REASON_IDX1 ON ORL.WORKGROUP_TRANSITION_REASON(IS_DELETED)
 
Indexes on WORKGROUP_TRANSITION table.
CREATE UNIQUE INDEX ORL.PK_WORKGROUP_TRANSITION ON ORL.WORKGROUP_TRANSITION(ID)
CREATE INDEX ORL.WORKGROUP_TRANSITION_IDX1 ON ORL.WORKGROUP_TRANSITION(CURRENT_WORKGROUP_ID)
CREATE INDEX ORL.WORKGROUP_TRANSITION_IDX2 ON ORL.WORKGROUP_TRANSITION(NEXT_WORKGROUP_ID)
CREATE INDEX ORL.WORKGROUP_TRANSITION_IDX3 ON ORL.WORKGROUP_TRANSITION(IS_DELETED)

Indexes on WORKGROUP table.
CREATE UNIQUE INDEX ORL.PK_WORKGROUP ON ORL.WORKGROUP(ID)
CREATE INDEX ORL.WORKGROUP_IDX1 ON ORL.WORKGROUP(BUID)
CREATE INDEX ORL.WORKGROUP_IDX2 ON ORL.WORKGROUP(IS_ACTIVE)
CREATE INDEX ORL.WORKGROUP_IDX3 ON ORL.WORKGROUP(IS_DELETED)
CREATE INDEX ORL.WORKGROUP_IDX4 ON ORL.WORKGROUP(IS_EMC)
CREATE INDEX ORL.WORKGROUP_IDX5 ON ORL.WORKGROUP(IS_BACKLOG) 
CREATE INDEX ORL.WORKGROUP_IDX6 ON ORL.WORKGROUP(DESCRIPTION)

I have attached the explain plan output also.

Please help me.

Thanks.

Attachment: explainplan.txt
(Size: 40.57KB, Downloaded 2538 times)

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #589982 is a reply to message #589921]

Fri, 12 July 2013 21:47

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

You can try below things as well:

1. View Merge / No Merge
2. Change the order in which tables are being joined "Leading" Hint (I highly discourage usage of hint if version is Oracle 11g or above), so that first few joins give you minimum number of rows
3. Hash join for large tables (requires more RAM)
4. Indexes may also make your query to run slow index and nested loops are deadly combination, in case you are selecting more than 50% data of tables, try not to use index and use hash joins instead (as Index are single block access)

As stated above as well "decomposition and reconstruction" is best technique.

Also cost doesn't matter, oracle apply many statistics functions to calculate the cost, cost may be a wrong indicative.

Manu

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #590333 is a reply to message #589982]

Wed, 17 July 2013 05:03

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi ,

I have changed the join order less number of records table I have used first.
But performance of the query is still bad.

Please help me.

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592169 is a reply to message #590333]

Mon, 05 August 2013 05:55

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi,

Any body please help me.
Still I am struggling with this query,

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592184 is a reply to message #589534]

Mon, 05 August 2013 07:36

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Something does not jive here.

why is your query plan showing UNION-ALL and a view names SALES_PERSON_V? I don't see any of this in the SQL code you provided.

is it me being dense, or are you keeping something from us?

So you have a view names sales_person_v? If so is that what you are querying? If so, why have you not given us the code.

If not then explain to me why there is a UNION-ALL in this query please.

Kevin

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592187 is a reply to message #592184]

Mon, 05 August 2013 09:03

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi,

Thanks for noticing.
I have scene explain plan individually only for SALESPERSON_V view.
It's looking good.SO that I didn't post the code for this view.
Please find the below code for SALESPERSON_V view.
Please find the attched explain plan for complete query.

   SELECT   DISTINCT 301,
                     5102,
                     'Ireland',
                     sp.salesperson_id,
                     UPPER (sp.fo_logon),
                     ur.role_name,
                     ugr.group_name,
                     ugr.GROUP_ID,
                     sp.email,
                     ds.department_id,
                     sp.sales_channel
     FROM   apps_ire.gedis_salesperson sp,
            apps_ire.gedis_user_group ugr,
            apps_ire.gedis_user_role ur,
            apps_ire.gedis_user_link ul,
            apps_ire.gedis_responsibility gr,
            orl.department_salesperson ds
    WHERE       sp.salesperson_id = ul.user_id
            AND ul.role_id = ur.role_id
            AND ul.GROUP_ID = ugr.GROUP_ID
            AND sp.responsibility_id = gr.responsibility_id
            AND gr.responsibility_key LIKE 'ORL%'
            AND UPPER (ur.role_name) = 'ORL_MEMBER'
            AND sp.salesperson_id = ds.user_id(+)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
                 OR ul.start_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
                 OR ul.end_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
                 OR sp.effective_start_date IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
                 OR sp.effective_end_date IS NULL)
   UNION ALL
   SELECT   DISTINCT 302,
                     202,
                     'United Kingdom',
                     sp.salesperson_id,
                     UPPER (sp.fo_logon),
                     ur.role_name,
                     ugr.group_name,
                     ugr.GROUP_ID,
                     sp.email,
                     ds.department_id,
                     sp.sales_channel
     FROM   apps_uk.gedis_salesperson sp,
            apps_uk.gedis_user_group ugr,
            apps_uk.gedis_user_role ur,
            apps_uk.gedis_user_link ul,
            apps_uk.gedis_responsibility gr,
            orl.department_salesperson ds
    WHERE       sp.salesperson_id = ul.user_id
            AND ul.role_id = ur.role_id
            AND ul.GROUP_ID = ugr.GROUP_ID
            AND sp.responsibility_id = gr.responsibility_id
            AND gr.responsibility_key LIKE 'ORL%'
            AND UPPER (ur.role_name) = 'ORL_MEMBER'
            AND sp.salesperson_id = ds.user_id(+)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
                 OR ul.start_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
                 OR ul.end_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
                 OR sp.effective_start_date IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
                 OR sp.effective_end_date IS NULL)
   UNION ALL
   SELECT   DISTINCT 323,
                     808,
                     'Germany',
                     sp.salesperson_id,
                     UPPER (sp.fo_logon),
                     ur.role_name,
                     ugr.group_name,
                     ugr.GROUP_ID,
                     sp.email,
                     ds.department_id,
                     sp.sales_channel
     FROM   apps_de.gedis_salesperson sp,
            apps_de.gedis_user_group ugr,
            apps_de.gedis_user_role ur,
            apps_de.gedis_user_link ul,
            apps_de.gedis_responsibility gr,
            orl.department_salesperson ds
    WHERE       sp.salesperson_id = ul.user_id
            AND ul.role_id = ur.role_id
            AND ul.GROUP_ID = ugr.GROUP_ID
            AND sp.responsibility_id = gr.responsibility_id
            AND gr.responsibility_key LIKE 'ORL%'
            AND UPPER (ur.role_name) = 'ORL_MEMBER'
            AND sp.salesperson_id = ds.user_id(+)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
                 OR ul.start_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
                 OR ul.end_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
                 OR sp.effective_start_date IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
                 OR sp.effective_end_date IS NULL)
   UNION ALL
   SELECT   DISTINCT 325,
                     2121,
                     'Netherlands',
                     sp.salesperson_id,
                     UPPER (sp.fo_logon),
                     ur.role_name,
                     ugr.group_name,
                     ugr.GROUP_ID,
                     sp.email,
                     ds.department_id,
                     sp.sales_channel
     FROM   apps_nl.gedis_salesperson sp,
            apps_nl.gedis_user_group ugr,
            apps_nl.gedis_user_role ur,
            apps_nl.gedis_user_link ul,
            apps_nl.gedis_responsibility gr,
            orl.department_salesperson ds
    WHERE       sp.salesperson_id = ul.user_id
            AND ul.role_id = ur.role_id
            AND ul.GROUP_ID = ugr.GROUP_ID
            AND sp.responsibility_id = gr.responsibility_id
            AND gr.responsibility_key LIKE 'ORL%'
            AND UPPER (ur.role_name) = 'ORL_MEMBER'
            AND sp.salesperson_id = ds.user_id(+)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
                 OR ul.start_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
                 OR ul.end_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
                 OR sp.effective_start_date IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
                 OR sp.effective_end_date IS NULL)
   UNION ALL
   SELECT   DISTINCT 328,
                     909,
                     'France',
                     sp.salesperson_id,
                     UPPER (sp.fo_logon),
                     ur.role_name,
                     ugr.group_name,
                     ugr.GROUP_ID,
                     sp.email,
                     ds.department_id,
                     sp.sales_channel
     FROM   apps_fr.gedis_salesperson sp,
            apps_fr.gedis_user_group ugr,
            apps_fr.gedis_user_role ur,
            apps_fr.gedis_user_link ul,
            apps_fr.gedis_responsibility gr,
            orl.department_salesperson ds
    WHERE       sp.salesperson_id = ul.user_id
            AND ul.role_id = ur.role_id
            AND ul.GROUP_ID = ugr.GROUP_ID
            AND sp.responsibility_id = gr.responsibility_id
            AND gr.responsibility_key LIKE 'ORL%'
            AND UPPER (ur.role_name) = 'ORL_MEMBER'
            AND sp.salesperson_id = ds.user_id(+)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
                 OR ul.start_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
                 OR ul.end_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
                 OR sp.effective_start_date IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
                 OR sp.effective_end_date IS NULL)
   UNION ALL
   SELECT   DISTINCT 329,
                     6161,
                     'Italy',
                     sp.salesperson_id,
                     UPPER (sp.fo_logon),
                     ur.role_name,
                     ugr.group_name,
                     ugr.GROUP_ID,
                     sp.email,
                     ds.department_id,
                     sp.sales_channel
     FROM   apps_it.gedis_salesperson sp,
            apps_it.gedis_user_group ugr,
            apps_it.gedis_user_role ur,
            apps_it.gedis_user_link ul,
            apps_it.gedis_responsibility gr,
            orl.department_salesperson ds
    WHERE       sp.salesperson_id = ul.user_id
            AND ul.role_id = ur.role_id
            AND ul.GROUP_ID = ugr.GROUP_ID
            AND sp.responsibility_id = gr.responsibility_id
            AND gr.responsibility_key LIKE 'ORL%'
            AND UPPER (ur.role_name) = 'ORL_MEMBER'
            AND sp.salesperson_id = ds.user_id(+)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
                 OR ul.start_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
                 OR ul.end_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
                 OR sp.effective_start_date IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
                 OR sp.effective_end_date IS NULL)
   UNION ALL
   SELECT   DISTINCT 332,
                     1212,
                     'Sweden',
                     sp.salesperson_id,
                     UPPER (sp.fo_logon),
                     ur.role_name,
                     ugr.group_name,
                     ugr.GROUP_ID,
                     sp.email,
                     ds.department_id,
                     sp.sales_channel
     FROM   apps_se.gedis_salesperson sp,
            apps_se.gedis_user_group ugr,
            apps_se.gedis_user_role ur,
            apps_se.gedis_user_link ul,
            apps_se.gedis_responsibility gr,
            orl.department_salesperson ds
    WHERE       sp.salesperson_id = ul.user_id
            AND ul.role_id = ur.role_id
            AND ul.GROUP_ID = ugr.GROUP_ID
            AND sp.responsibility_id = gr.responsibility_id
            AND gr.responsibility_key LIKE 'ORL%'
            AND UPPER (ur.role_name) = 'ORL_MEMBER'
            AND sp.salesperson_id = ds.user_id(+)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
                 OR ul.start_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
                 OR ul.end_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
                 OR sp.effective_start_date IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
                 OR sp.effective_end_date IS NULL)
   UNION ALL
   SELECT   DISTINCT 365,
                     592,
                     'Slovakia',
                     sp.salesperson_id,
                     UPPER (sp.fo_logon),
                     ur.role_name,
                     ugr.group_name,
                     ugr.GROUP_ID,
                     sp.email,
                     ds.department_id,
                     sp.sales_channel
     FROM   apps_sk.gedis_salesperson sp,
            apps_sk.gedis_user_group ugr,
            apps_sk.gedis_user_role ur,
            apps_sk.gedis_user_link ul,
            apps_sk.gedis_responsibility gr,
            orl.department_salesperson ds
    WHERE       sp.salesperson_id = ul.user_id
            AND ul.role_id = ur.role_id
            AND ul.GROUP_ID = ugr.GROUP_ID
            AND sp.responsibility_id = gr.responsibility_id
            AND gr.responsibility_key LIKE 'ORL%'
            AND UPPER (ur.role_name) = 'ORL_MEMBER'
            AND sp.salesperson_id = ds.user_id(+)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > ul.start_date_active
                 OR ul.start_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < ul.end_date_active
                 OR ul.end_date_active IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) > sp.effective_start_date
                 OR sp.effective_start_date IS NULL)
            AND (SYS_EXTRACT_UTC (SYSTIMESTAMP) < sp.effective_end_date
                 OR sp.effective_end_date IS NULL);

Please help me.

Thanks.

Attachment: explian.txt
(Size: 35.90KB, Downloaded 2431 times)

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592189 is a reply to message #592187]

Mon, 05 August 2013 09:20

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Thanks. That explains the UNION-ALL business.

the mains query uses this view ORL.V_QUALITY_CONTROL_REPORT_DATA

which itself uses the view ORL.SALESPERSON_V SPV

the ORL.SALESPERSON_V SPV contains the UNION-ALL business.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592191 is a reply to message #592187]

Mon, 05 August 2013 09:40

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

This is a bug,
ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) AS AGED_DAYS
You should not attempt to convert a date to a date, and it may be supressing index usage and causing forcing the FTS of fax_headers at operation id 156.
I think you could project fh.date_received in the view, and then replace your predicate
WHERE AGED_DAYS < 8;
with
WHERE ORL.V_QUALITY_CONTROL_REPORT_DATA.DATE_RECEIVED > sysdate -8.
--
Correction: above should read
WHERE DATE_RECEIVED > sysdate -8
sorry about that, too much copy-paste

[Updated on: Mon, 05 August 2013 09:54]

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592193 is a reply to message #592191]

Mon, 05 August 2013 09:48

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

And one minor point - with your next execution plan, plase can you SET PAGES 0 so that it doesn't get broken up by column headings every few lines?

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592235 is a reply to message #592193]

Mon, 05 August 2013 22:12

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi,

Thank you so much for your response.

The data type of the column is TIMESTAMP(6)
So that we are converting to date.

Our requirement is subtract DATE_RECEIVED from sysdate after that round the result it should be less than 8 days.

For that I have written the below logic

ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) AS AGED_DAYS.

As per Your suggestion WHERE DATE_RECEIVED > sysdate -8 will replace my above requirement.
In this there is no ROUND function also.

Please provide the alternative logic to above my requirement
which improves the performance of my query.

Please help me.
Your help is appreciated

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592237 is a reply to message #592235]

Mon, 05 August 2013 22:31

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi,

The data existed in the table as below.

DATE_RECEIVED

8/01/2013 12:18:56.095550 PM
8/02/2013 12:18:56.345030 PM
8/03/2013 12:45:55.671499 PM
3/30/2011 12:45:59.919893 PM
3/30/2011 12:47:10.856586 PM

Please help me.

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592246 is a reply to message #592237]

Tue, 06 August 2013 01:28

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

I'll try again. I think you need to re-write the predicate I pointed out in such a way that it will avoid this,

 156 - filter(ROUND(SYSDATE@!-CAST(INTERNAL_FUNCTION("FH"."DATE_RECEIVED") AS DATE))<8)

which may be causing problems.
I don't say "is", only "may be".

Why don't you just try what I suggested, and see if it helps?

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592248 is a reply to message #592246]

Tue, 06 August 2013 01:36

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi,

Thanks for your response.

I have tried whatever you suggested.
I have tested for some scenarios it's working fine.
But in my previous logic ROUND function is used.
How that will work in your logic.
I am not able to understand.
Please explain me.
If your logic take care of ROUND that's fine.

Please help me.

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592249 is a reply to message #592248]

Tue, 06 August 2013 01:41

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

And the new execution plan is what?

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592265 is a reply to message #592249]

Tue, 06 August 2013 03:11

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi,

Please see the explain plan after the changes.

Thanks.

Attachment: explain1.txt
(Size: 29.46KB, Downloaded 2169 times)

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592266 is a reply to message #592265]

Tue, 06 August 2013 03:20

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

There are still so many filter predicates with implicit data type conversion. All those operation ids which has INTERNAL_FUNCTION, are going for an implicit data type conversion. Not good in terms of performance.

Like,

filter(INTERNAL_FUNCTION("SP"."EFFECTIVE_START_DATE")<SYS_EXTRACT_UTC(SYSTIMESTAMP(6)) AND
              INTERNAL_FUNCTION("SP"."EFFECTIVE_END_DATE")>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))

You can try to rewrite the query, such that predicate does not have to go for an implicit conversion. For example, if you convert date again into date data type, it's like inventing the wheel again, and thus implementing the implicit data type conversion.

EDIT : fixed typo errors.

[Updated on: Tue, 06 August 2013 03:21]

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592267 is a reply to message #592265]

Tue, 06 August 2013 03:20

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Well, you have followed my advice, and the predicted execution time has dropped from 01:17:17 to 00:00:02.
I think that is worth a bit more than "Thanks for your response."

As for your remaining problem with ROUND, you will need to post the new version of the code. I havee no idea if the logic is the same, or if you need to adjust it further.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592268 is a reply to message #592265]

Tue, 06 August 2013 03:23

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi,

I agreed with john suggestion.
If I modified as John suggested performance got improved.
In my previous logic ROUND function is used to round the
values after doing subtraction and then comparing <8.
But my question are
will John logic works as per my above requirement?
Will it take care of ROUND scenario also?
I am not able to understand.
Please explain me.
If your logic take care of ROUND that's fine.

Please help me.

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592269 is a reply to message #592268]

Tue, 06 August 2013 03:30

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi,

I have changed the view ORL.V_QUALITY_CONTROL_REPORT_DATA as below

SELECT   FH.ID AS FAX_ID,
            FOD.ID AS FAX_OFFER_ID,
            'ORL Rejection' AS HOLD_TYPE,
            'Sales' AS ACTION_OWNER,
            FOD.ORDER_NUMBER,
            FOD.OMEGA_ORDER_NUMBER,
            BU_MAPPING.COUNTRY_NAME,
            BU_MAPPING.CUSTOMER_PREFIX,
            BU_MAPPING.DEFAULT_COUNTRY_CODE,
            FOD.PURCHASE_ORDER_NUMBER AS PO_NUMBER,
            FOD.OFFER_NUMBER,
            FOD.VALUE AS REVENUE,
            FOD.CREATED_BY,
            FOD.CREATION_DATE,
            FH.CUSTOMER_NAME,
            FH.CUSTOMER_NUMBER,
            SPV.salesperson_name,
            SPV.email AS SALESPERSON_EMAIL,
            FH.PAYMENT_TYPE,
            FH.DATE_RECEIVED,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_DATA (BU_MAPPING.BU_ID,
                                                           FOD.ORDER_NUMBER)
               AS RESULT_OF_ORDER_DATA_CALL,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_SEGMENT ()
               AS ORDER_SEGMENT,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_CREATOR ()
               AS ORDER_CREATOR,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_CREATOR_EMAIL ()
               AS ORDER_CREATOR_EMAIL,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_CUSTOMER_BASE ()
               AS CUSTOMER_BASE,
            REPLACE (
               ORL.ORL_QUALITY_CONTROL_HELPER.GET_OMEGA_CUSTOMER_NUMBER (),
               BU_MAPPING.CUSTOMER_PREFIX
            )
               AS OMEGA_CUSTOMER_NUMBER,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_CUSTOMER_BILL ()
               AS CUSTOMER_BILL,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_TOTAL_SELLING_PRICE ()
               AS TOTAL_SELLING_PRICE,
            ORL.ORL_QUALITY_CONTROL_HELPER.GET_CURRENCY_CODE ()
               AS CURRENCY_CODE,
            LTH.CHANGED_DATE AS LAST_TRANSITION_DATE,
            LTH.CHANGED_BY AS LAST_TRANSITION_CHANGED_BY,
            LTH.FROM_WORKGROUP_DESCRIPTION AS LAST_TRANSITION_WORKGROUP_FROM,
            LTH.TO_WORKGROUP_DESCRIPTION AS LAST_TRANSITION_WORKGROUP_TO,
            LTH.REASON_DESCRIPTION AS LAST_TRANSITION_REASON,
            LTH.COMMENTS AS LAST_TRANSITION_COMMENT
     FROM               ORL.FAX_OFFER_DETAIL FOD
                     INNER JOIN
                        ORL.FAX_HEADER FH
                     ON FH.ID = FOD.FAX_ID
                  INNER JOIN
                        ORL.FAX_SOURCE FS
                     INNER JOIN
                        APPS_GLOBAL.GLOBAL_BU_MAPPING BU_MAPPING
                     ON BU_MAPPING.BU_ID = FS.BUID
                  ON FS.ID = FH.FAX_SOURCE
               LEFT OUTER JOIN
                  ORL.FAX_LAST_TRANSITION_V LTH
               ON LTH.FAX_ID = FH.ID
            LEFT OUTER JOIN
               ORL.SALESPERSON_V SPV
            ON SPV.salesperson_id = FH.SALES_PERSON;

And the new query is as below.

SELECT   FAX_ID,
         FAX_OFFER_ID,
         HOLD_TYPE,
         ACTION_OWNER,
         ORDER_NUMBER,
         OMEGA_ORDER_NUMBER,
         COUNTRY_NAME,
         CUSTOMER_PREFIX,
         DEFAULT_COUNTRY_CODE AS COUNTRY_CODE,
         PO_NUMBER,
         OFFER_NUMBER,
         REVENUE,
         CREATED_BY,
         CREATION_DATE,
         CUSTOMER_NAME,
         CUSTOMER_NUMBER,
         SALESPERSON_NAME,
         SALESPERSON_EMAIL,
         PAYMENT_TYPE,
         DATE_RECEIVED,
         RESULT_OF_ORDER_DATA_CALL,
         ORDER_SEGMENT,
         ORDER_CREATOR,
         ORDER_CREATOR_EMAIL,
         CUSTOMER_BASE,
         OMEGA_CUSTOMER_NUMBER,
         CUSTOMER_BILL,
         LAST_TRANSITION_DATE,
         LAST_TRANSITION_CHANGED_BY,
         LAST_TRANSITION_WORKGROUP_FROM,
         LAST_TRANSITION_WORKGROUP_TO,
         LAST_TRANSITION_REASON,
         LAST_TRANSITION_COMMENT,
         TOTAL_SELLING_PRICE,
         CURRENCY_CODE
  FROM   ORL.V_QUALITY_CONTROL_REPORT_DATA
 WHERE   DATE_RECEIVED > sysdate -8;

Please help me.

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592284 is a reply to message #592269]

Tue, 06 August 2013 05:46

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi,

Your logic is not working properly.

For existed logic I didn't get any records.

SELECT DATE_RECEIVED,ROUND (SYSDATE - CAST (DATE_RECEIVED AS DATE)) AS AGED_DAYS
FROM ORL.FAX_HEADER
WHERE ROUND (SYSDATE - CAST (DATE_RECEIVED AS DATE))<8;

No records.

For your logic I got the output.

SELECT DATE_RECEIVED,ROUND (SYSDATE - CAST (DATE_RECEIVED AS DATE)) AS AGED_DAYS,sysdate-8
FROM ORL.FAX_HEADER
WHERE DATE_RECEIVED>sysdate-8;

DATE_RECEIVED                    AGED_DAYS  SYSDATE-8
------------------------------- ---------- ---------
29-JUL-13 11.55.42.823281 AM         8      7/29/2013 5:45:41 AM

1 row selected.

I think your logic is not working properly.

Please help me.

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592287 is a reply to message #592284]

Tue, 06 August 2013 05:53

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

So correct it. Smile

I'm not your personal, free of charge, consultant. I've already given you help worth a few hundred dollars, and all I get back is requests for more. Sad

You could try doing some work yourself Shocked

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592299 is a reply to message #592287]

Tue, 06 August 2013 06:57

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi John,

I am not able to correct it.
Can you please provide the correct alternative logic for my requirement.

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592303 is a reply to message #592284]

Tue, 06 August 2013 07:22

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

It is very hard to tune a piece of code when one does not have ready access to the databases on which it runs because one cannot execute test scripts and the like to test out ideas. So we have to guess a lot here.

If you goal is centered around making use of the AGED_DAYS < 8 then I note the math seems suspect.

1. aged_days = ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) according to your code. From this one could get 8.49999999999 days which would round to 8. Is this what you want? It is > 8 days. Your round expression really gives aged_days <= 8.49999999999.

A correct implementation would be trunc(sysdate) - trunc(cast(fh.date_received as date)) < 8 if you really wanted < 8 days.
Assuming < 8.49999999999 is what you want the you can code it as this: trunc(sysdate) - trunc(cast(fh.date_received as date)) < 8.49999999999.

In any event, using this logic trunc(sysdate) - trunc(cast(fh.date_received as date)) < 8 which matches your your commentary and not your code, we can rewrite this expression as was suggested earlier to trunc(sysdate) +8 < trunc(cast(fh.date_received as date)).

Using this rewrite I have three suggested changes which I believe is what John was getting at so I may be duplicating things here.

1 create a function based index: create index your_funk_i1 on ORL.FAX_HEADER (trunc(cast(date_received as date));

2 change your view FAX_LAST_TRANSITION_V to include the FH.DATE_RECEIVED column as an additional column.

3 use this expression in your main query: trunc(sysdate) +8 < trunc(cast(fh.date_received as date)).

This will allow your query to go directly to only those rows on FAX_HEADER that are less than eight days old. You will not it works when you see and INDEX RANGE SCAN on the index YOUR_FUNK1_I1.

Not sure this is your problem but it gets to the data your want as quick as possible.

Good luck. Kevin

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592312 is a reply to message #592303]

Tue, 06 August 2013 08:16

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi Kevin,

Thanks for your help.

However it's not meet my requirement.
The business logic is we should consider the time also not only date.
Is there any way to implement my requirement with good performance.
And also what is the use of
change your view FAX_LAST_TRANSITION_V to include the FH.DATE_RECEIVED column as an additional column.

Please help me.

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592315 is a reply to message #592312]

Tue, 06 August 2013 08:31

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

First, there is an error in my post. I was using +8 and I believe it should be -8.

HOwever, please re-read my post. I believe I answerd all these questions there. You need to understand what I said.

For example

I noted that your original logic is flawed because it does not return 8 days, it returns 8.5 days. I asked you what you wanted. Do you want 8 days or 8.49999999999 days?

Regardless of what you want, the example I showed will work. You just plug in the value for days you are interested in.

The view needs to feed the original column up through the query layers so you can reference it in the expression trunc(sysdate) - 8 < trunc(cast(...)).

Your main query was this:

         CURRENCY_CODE
  FROM   ORL.V_QUALITY_CONTROL_REPORT_DATA
 WHERE   AGED_DAYS < 8;

I want you to write this instead.

         CURRENCY_CODE
  FROM   ORL.V_QUALITY_CONTROL_REPORT_DATA
 WHERE   TRUNC(SYSDATE) - 8 < TRUNC(CAST(DATE_RECEIVED));

You are looking for this.

09:25:06 SQL> CREATE TABLE A (A CLOB,B TIMESTAMP);

Table created.

Elapsed: 00:00:00.15
  1* CREATE INDEX AI1 ON A(TRUNC(CAST(B AS DATE)))
09:25:36 SQL> /

Index created.

Elapsed: 00:00:00.09
09:27:06 SQL> EXPLain plan for select /*+ cardinality (a 1000000) */ * from a where trunc(sysdate) - 8 < trunc(cast(b as date));

Explained.

Elapsed: 00:00:00.06
09:27:42 SQL> @showplan9i

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |  1000K|  1921M|     1 |
|   1 |  TABLE ACCESS BY INDEX ROWID| A           |  1000K|  1921M|     1 |
|*  2 |   INDEX RANGE SCAN          | AI1         |  9000 |       |     2 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(TRUNC(CAST("A"."B" AS DATE))>TRUNC(SYSDATE@!)-8 AND
              TRUNC(CAST("A"."B" AS DATE)) IS NOT NULL)

Note: cpu costing is off

16 rows selected.

Please re-read my prior post and try to understand what it is doing.

Kevin

[Updated on: Tue, 06 August 2013 08:32]

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592316 is a reply to message #592315]

Tue, 06 August 2013 08:33

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

sorry, once again I used +. I have edited the above to use -. You may have received an email just a moment ago with the +.

Kevin

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592320 is a reply to message #592316]

Tue, 06 August 2013 09:18

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi Kevin,

Thanks for your detailed explanation.
For the below query the existed logic AGED_DAYS and your proposed logic PROPOSED_DAYS
is showing different result.If I use your logic the out will be different.
Business will not agree for this.
Is there any way to implement my existed logic in different way.
Else what we can do to improve the performance of the query.

SELECT DATE_RECEIVED,ROUND (SYSDATE - CAST (DATE_RECEIVED AS DATE)) AS AGED_DAYS,(SYSDATE - CAST (DATE_RECEIVED AS DATE))ACTUAL_AGEDS_DAYS,
trunc(sysdate)-trunc(cast(date_received as date)) PROPOSED_DAYS
FROM ORL.FAX_HEADER
WHERE ROUND (SYSDATE - CAST (DATE_RECEIVED AS DATE))=1137


DATE_RECEIVED                    AGED_DAYS ACTUAL_AGEDS_DAYS PROPOSED_DAYS
------------------------------- ---------- ----------------- -------------
25-JUN-10 09.41.10.909532 PM          1137        1137.48088          1138
25-JUN-10 09.45.25.723175 PM          1137        1137.47793          1138

2 rows selected.

Please help me.

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592340 is a reply to message #592320]

Tue, 06 August 2013 11:00

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Glad to see you are moving forward.

but there is still something you are not telling us.

DATE_RECEIVED                    AGED_DAYS ACTUAL_AGEDS_DAYS PROPOSED_DAYS
------------------------------- ---------- ----------------- -------------
25-JUN-10 09.41.10.909532 PM          1137        1137.48088          1138
25-JUN-10 09.45.25.723175 PM          1137        1137.47793          1138

I am glad to see that you computed "ACUTAL_AGED_DAYS"

what however is PROPOSED_DAYS? Are you telling us that you want 1137.48088 to round up to 1138? If so do you want all fractions to round up?

Kevin

[Updated on: Tue, 06 August 2013 11:01]

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592342 is a reply to message #592340]

Tue, 06 August 2013 11:11

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

OK my bad. PROPOSED_DAYS is my calculation.

Alright then, try this one.

         CURRENCY_CODE
  FROM   ORL.V_QUALITY_CONTROL_REPORT_DATA
 WHERE   TRUNC(SYSDATE) - 9 < TRUNC(CAST(DATE_RECEIVED))
 and aged_days < 8;

it looks stupid, but it gives you access to the index that eliminates almost all the data and then uses the original predicate to remove any "exta rows" we don't want.

See if you can get some variation of this to work for you.

Kevin

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592498 is a reply to message #592342]

Thu, 08 August 2013 21:44

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Any update on this one? Was hoping you would tell us if it worked out for you. I assume by the silence you found a solution? Please share it. Sharing is after all what makes this site work right?

Kevin

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592504 is a reply to message #592498]

Fri, 09 August 2013 01:24

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi Kevin,

Sorry for not providing update.
Your suggestion is not working for my requirement exactly.

Can we do some thing like this.

SELECT ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) AS AGED_DAYS
FROM orl.FAX_HEADER FH
WHERE FH.DATE_RECEIVED>sysdate-8
AND ROUND(SYSDATE - CAST (FH.DATE_RECEIVED AS DATE))<8;

We have index on DATE_RECEIVED column.

Will it be the correct logic for my requirement?
Please confirm.
Please provide any solution for problem.

Thanks.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592506 is a reply to message #592504]

Fri, 09 August 2013 02:45

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Hello again - I'm confused about what issue you are (still) facing. As I understand it, I solved your performance problem by adjusting that predicate so that it could use the existing index on fh.date_received, but you couldn't make the final adjustment to get whole days. Then Kevin gave you a function based index that would make your old predicate an indexable condition.
Did Kevin's solution solve the performance problem?
And can you explain, again, what your AGED_DAYS algorithm actually is? Like this, "if the date-time right now is X, then AGED_DAYS > 8 would include Y but not Z" ?

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592509 is a reply to message #592504]

Fri, 09 August 2013 03:03

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

That last line of the where clause does nothing. If date_received is greater than sysdate -8 then sysdate - date_received must be less than 8.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592510 is a reply to message #592509]

Fri, 09 August 2013 03:39

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Let us assume
date_received is greater than sysdate -8

And the value of SYSDATE - CAST (FH.DATE_RECEIVED AS DATE) is 8.75
If we do ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) the value is 9

As per this logic date_received is greater than sysdate -8 that record come in the output.
But as per the existed logic ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE))<8
That record should not come in the output.
This is what the last line is doing.

Please confirm my logic is correct or not compared to existed logic.

Thanks .

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592511 is a reply to message #592510]

Fri, 09 August 2013 03:44

John Watson
Messages: 8963
Registered: January 2010
Location: Global Village

Senior Member

Do you actually understand which rows you want, and which rows you do not want? You seem to be asking people to explain your existing logic: it is up to you do that. Just give examples of which dates you want, and which dates you do not want.

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592523 is a reply to message #592511]

Fri, 09 August 2013 07:02

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Confirming logic is all about thinking and testing. Only you can do that for your problem.

I may indeed have gotten a small amount of math wrong, or used a > where I should have used <. But these are things that you can figure out by desk checking your work and by building test cases.

The key points you needed were:

1. you needed to adjust your views and sql to allow you to pull the original date field up so that you could use it directly in your query without combining it with a system variable like sysdate.   This would open the door to using indexes on this item and thus allow you to reference a small number of rows from a large set quickly.

2. you needed a function based index in order to exploit fetching a very small number of rows from a large set given your search predicate is based on an expression using the date you just exposed.

3. it is OK to be a little in-exact in the use of the new function based predicate, because you can add your original predicate back into the query to ensure you return only those rows you really want.  This works as long as the function based predicate identifies a SUPERSET of the rows you want but is still sufficiently small to be performant.  In the function based predicate, I was extending the range of your search by one day to account for rounding in the math.

Good luck. Kevin

Report message to a moderator

Re: Please help me to improve the performance of this query. [message #592697 is a reply to message #592523]

Sun, 11 August 2013 04:29

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi Kevin,

Thanks for your explanation.

As per your comments

There are still so many filter predicates with implicit data type conversion.
All those operation ids which has INTERNAL_FUNCTION, are going for an implicit data type conversion.
Not good in terms of performance.

Like,

filter(INTERNAL_FUNCTION("SP"."EFFECTIVE_START_DATE")<SYS_EXTRACT_UTC(SYSTIMESTAMP(6)) AND
              INTERNAL_FUNCTION("SP"."EFFECTIVE_END_DATE")>SYS_EXTRACT_UTC(SYSTIMESTAMP(6)))

You can try to rewrite the query, such that predicate does not have to go for an implicit conversion.
For example, if you convert date again into date data type, it's like inventing the wheel again, and thus implementing the implicit data type conversion.

Can you please provide alternative for this.
To make the SQL query optimal.

Thanks.

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	Optimize Query using Explain Plan
Next Topic:	about index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 23 14:51:16 CST 2025