Home » SQL & PL/SQL » SQL & PL/SQL » Query execute time versus fetch time (11.2.1.0)
Query execute time versus fetch time [message #644807] Wed, 18 November 2015 04:13 Go to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear All,

I am facing a situation where a query that used to run in less than 1 second is not taking 9 seconds.

The current execute time is 8.7 and fetch time is 0.3

What should I be looking for? (buffer Cache or dig into execution plan knowing that I can safely assume that nothing has changed regarding the query or the function that it uses,...etc.)

Thanks,
Ferro

[Updated on: Wed, 18 November 2015 04:14]

Report message to a moderator

Re: Query execute time versus fetch time [message #644808 is a reply to message #644807] Wed, 18 November 2015 04:18 Go to previous messageGo to next message
John Watson
Messages: 8981
Registered: January 2010
Location: Global Village
Senior Member
Execution plan.

Report message to a moderator

Re: Query execute time versus fetch time [message #644810 is a reply to message #644808] Wed, 18 November 2015 04:29 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Thanks John, will dig into this but can you or anyone pass me a document that describes the difference between execute and fetch times and the possible causes of very slow execute time compared to fetch time?

Thanks,
Ferro

Report message to a moderator

Re: Query execute time versus fetch time [message #644811 is a reply to message #644810] Wed, 18 November 2015 04:34 Go to previous messageGo to next message
John Watson
Messages: 8981
Registered: January 2010
Location: Global Village
Senior Member
High execute time is usually for DML (working in buffer cache), high fetch time is usually for SELECT (working in PGA). How do you know whether the time is being spent in execute or fetch? Have you traced the query?

Report message to a moderator

Re: Query execute time versus fetch time [message #644812 is a reply to message #644811] Wed, 18 November 2015 04:57 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi John,
- My query uses no DML, just a select query
- I am using dbforge SQL client that returns execute and fetch time with every query I run
- I used timing start and timing stop with same result.

Quote:

Execute succeeded [< 0,001s]
Query opened in 9.269s [9.266s exec, 0.003s fetch]
Execute succeeded [< 0,001s]


Thanks,
Ferro

Report message to a moderator

Re: Query execute time versus fetch time [message #644820 is a reply to message #644807] Wed, 18 November 2015 06:29 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
OraFerro wrote on Wed, 18 November 2015 10:13
I am facing a situation where a query that used to run in less than 1 second is not taking 9 seconds.

What's changed? (apart from the time it takes to execute Smile )

Report message to a moderator

Re: Query execute time versus fetch time [message #644824 is a reply to message #644812] Wed, 18 November 2015 07:04 Go to previous messageGo to next message
John Watson
Messages: 8981
Registered: January 2010
Location: Global Village
Senior Member
Look at this example of tracing two queries. The SELECT does nothing in execute, it is all in fetch; the DML is the reverse:
********************************************************************************

SQL ID: 5bc0v4my7dvr5 Plan Hash: 3724264953

select count(*)
from
 t1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.02       1520       1523          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.03       1520       1523          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 133
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=1523 pr=1520 pw=0 time=28602 us)
     90338      90338      90338   TABLE ACCESS FULL T1 (cr=1523 pr=1520 pw=0 time=8033 us cost=424 size=0 card=90338)

********************************************************************************

SQL ID: fhy49n7dqbdgf Plan Hash: 2927627013

update t1 set object_id=object_id


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.43       0.66       1520       3370      97595       90338
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.43       0.66       1520       3370      97595       90338

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 133
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  UPDATE  T1 (cr=3063 pr=1173 pw=0 time=616840 us)
    200189     200189     200189   TABLE ACCESS FULL T1 (cr=3360 pr=1520 pw=0 time=75380 us cost=424 size=451690 card=90338)

********************************************************************************


I would think that this dbforge is telling you a load of rubbish.

Report message to a moderator

Re: Query execute time versus fetch time [message #644829 is a reply to message #644824] Wed, 18 November 2015 08:03 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
A select for update can spend time in the execute phase while it locks the records.
But really we need the execution plan to see if dbforge knows what it's talking about

Report message to a moderator

Re: Query execute time versus fetch time [message #644859 is a reply to message #644829] Thu, 19 November 2015 02:10 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear All,

Thanks to your valuable comments. I located the reason of delay
(of course my assumption was not valid and something has changed in
the query), however:
1- The query does not include DML
2- I could not go around my change to avoid the delay

The case is:
- I have a function that returns a summary figure based on several
parameters (code included)
- Inside the function there is a right outer join with another table
to get all of its values in the output
- All what I added is an extra condition inside the right outer join
query (sector_id) which is indexed (market "-->> the added part start" inside the function body)

Please find below the code of my function,query, and execution plan before
and after the change for your kind advice:

CREATE OR REPLACE TYPE OBJ_AGR_TRN_TYPE_INFO AS OBJECT
(
  /*
  FERRO: 16-11-2015
  */
   COUNTRY_CODE NUMBER(15,3),-- 
   COUNTRY_NAME_A VARCHAR2(50),-- 
   COUNTRY_NAME_E VARCHAR2(50),-- 
   cont_SERIAL NUMBER(15,3),-- cont SERIAL
   cont_NO VARCHAR2(50),-- cont NUMBER0 + SUB NUMBER
   AMOUNT NUMBER(15,3) -- AMOUNT
);


CREATE TYPE TBL_AGR_TRN_TYPE_INFO AS
    TABLE OF OBJ_AGR_TRN_TYPE_INFO;


CREATE OR REPLACE FUNCTION F_AGR_TRN_TYPE_INFO
  (
    I_TYPE_ID IN NUMBER,
    D_VALUE_DATE IN DATE DEFAULT SYSDATE, 
    I_cont_LEVEL IN NUMBER DEFAULT 1, 
    I_p_SECTOR IN NUMBER DEFAULT 3 
  ) 
RETURN TBL_AGR_TRN_TYPE_INFO AS
AGR_TRN_TYPE_INFO TBL_AGR_TRN_TYPE_INFO;

BEGIN
  CASE I_cont_LEVEL
    WHEN 0 THEN -- COUNTRY LEVEL 
         SELECT OBJ_AGR_TRN_TYPE_INFO(
                                 MAX(COUNTRY_CODE) ,    -- COUNTRY CODE
                                 COUNTRY_NAME_A,  -- ARABIC COUNTRY NAME
                                 COUNTRY_NAME_E,  -- ENGLISH COUNTRY NAME
                                 0,-- cont SERIAL
                                 0,    -- cont NUMBER0 + SUB NUMBER
                                 TO_CHAR(SUM(NVL(AMOUNT,0)),'999999999999999.999') 
                                )
        BULK COLLECT INTO AGR_TRN_TYPE_INFO
        FROM  
          (
            SELECT FK_cont_SERIAL,  TO_CHAR(SUM(AGT.AMOUNT),'999999999999.999') AMOUNT
            FROM TRN_cont_TRANSACTION AGT
            WHERE AGT.TYPE_ID = I_TYPE_ID
              AND AGT.VALUE_DATE <= D_VALUE_DATE 
            GROUP BY  AGT.FK_cont_SERIAL
          ) TRN
          RIGHT OUTER JOIN
          (
            SELECT AGR.FK_COUNTRYCODE COUNTRY_CODE, CNT.SHORT_NAME_A COUNTRY_NAME_A, 
            CNT.SHORT_NAME_E COUNTRY_NAME_E,  AGR.cont_SERIAL,  
            AGR.NUMBER0||AGR.SUB_NUMBER cont_NO
              FROM N_cont AGR, N_COUNTRY CNT
              WHERE AGR.FK_COUNTRYCODE = CNT.CODE
                -->> the added part start
		AND (
                      (I_p_SECTOR = 3)
                      OR
                      (I_p_SECTOR = 1 AND AGR.SECTOR_ID <> 2) 
                      OR
                      (I_p_SECTOR = 2 AND AGR.SECTOR_ID = 2)
                    )
		--<< the added part end
                AND AGR.TYPE_ID = 2
          ) CNT
          ON CNT.cont_SERIAL = TRN.FK_cont_SERIAL
      GROUP BY COUNTRY_NAME_A, COUNTRY_NAME_E
        ;
    END CASE;

  RETURN  (AGR_TRN_TYPE_INFO);
 END ;
/




---DISB0009
EXPLAIN PLAN
   INTO plan_table
    FOR
SELECT V1.COUNTRY_CODE, V1.COUNTRY_NAME_A, V1.COUNTRY_NAME_E, LOAN_COUNT,
       V1.AMOUNT ORIGINAL_AMOUNT_1,
       ROUND(V2A.AMOUNT + V2B.AMOUNT + V2C.AMOUNT,3) CANCEL_DISCOUNT_AMOUNT_2,
       ROUND(V1.AMOUNT - (V2A.AMOUNT + V2B.AMOUNT + V2C.AMOUNT),3) NET_AMOUNT_3, --(1-2)
       V4.AMOUNT WITHDRAWAL_AMOUNT_4,
       ROUND( (V1.AMOUNT - (V2A.AMOUNT + V2B.AMOUNT + V2C.AMOUNT))-V4.AMOUNT,3) 
BALANCE_AMOUNT_5, --(4-3)
       V6.AMOUNT PAID_INSTALLEMENT_AMOUNT_6
  FROM
    (
      SELECT MAX(FK_COUNTRYCODE) COUNTRY_CODE, CNT.SHORT_NAME_A, COUNT(*) LOAN_COUNT
        FROM N_cont AGR, N_COUNTRY CNT 
        WHERE TYPE_ID =2
          AND AGR.FK_COUNTRYCODE = CNT.CODE
          AND (
                (3 = 3)
                OR
                (0 = 1 AND AGR.SECTOR_ID <> 2)
                OR
                (0 = 2 AND AGR.SECTOR_ID = 2)
              )
        GROUP BY SHORT_NAME_A
    )  V0,
    TABLE(F_AGR_TRN_TYPE_INFO(1 ,SYSDATE, 0, 3)) V1,
    TABLE(F_AGR_TRN_TYPE_INFO(9 ,SYSDATE, 0, 3)) V2A,
    TABLE(F_AGR_TRN_TYPE_INFO(10,SYSDATE, 0, 3)) V2B,    
    TABLE(F_AGR_TRN_TYPE_INFO(11,SYSDATE, 0, 3)) V2C,
    TABLE(F_AGR_TRN_TYPE_INFO(2 ,SYSDATE, 0, 3)) V4,
    TABLE(F_AGR_TRN_TYPE_INFO(6 ,SYSDATE, 0, 3)) V6
  WHERE
    V1.COUNTRY_CODE = V2A.COUNTRY_CODE
    AND V1.COUNTRY_CODE = V2B.COUNTRY_CODE 
    AND V1.COUNTRY_CODE = V2C.COUNTRY_CODE
    AND V1.COUNTRY_CODE = V4.COUNTRY_CODE
    AND V1.COUNTRY_CODE = V6.COUNTRY_CODE
    AND V1.COUNTRY_CODE = V0.COUNTRY_CODE
 ;

explain plan before the change:
Quote:

PLAN_TABLE_OUTPUT
Plan hash value: 599037222

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56T| 1939T| 309M(100)|999:59:59 |
|* 1 | HASH JOIN | | 56T| 1939T| 309M(100)|999:59:59 |
| 2 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 687G| 22T| 3788K(100)| 12:37:41 |
| 4 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 8412M| 266G| 46515 (100)| 00:09:19 |
| 6 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 102M| 3143M| 674 (85)| 00:00:09 |
| 8 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 1260K| 36M| 84 (12)| 00:00:02 |
| 10 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 15438 | 422K| 47 (5)| 00:00:01 |
| 12 | VIEW | | 189 | 4914 | 18 (12)| 00:00:01 |
| 13 | HASH GROUP BY | | 189 | 4347 | 18 (12)| 00:00:01 |
|* 14 | HASH JOIN | | 643 | 14789 | 17 (6)| 00:00:01 |
| 15 | TABLE ACCESS FULL | N_COUNTRY | 190 | 2470 | 4 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | N_AGREEMENT | 643 | 6430 | 12 (0)| 00:00:01 |
| 17 | COLLECTION ITERATOR PICKLER FETCH| F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

1 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
3 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
5 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
7 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
9 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
11 - access("V0"."COUNTRY_CODE"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
14 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
16 - filter("TYPE_ID"=2)


explain plan after the change
Quote:


PLAN_TABLE_OUTPUT
Plan hash value: 599037222

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56T| 1939T| 309M(100)|999:59:59 |
|* 1 | HASH JOIN | | 56T| 1939T| 309M(100)|999:59:59 |
| 2 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 687G| 22T| 3788K(100)| 12:37:41 |
| 4 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 8412M| 266G| 46515 (100)| 00:09:19 |
| 6 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 102M| 3143M| 674 (85)| 00:00:09 |
| 8 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 1260K| 36M| 84 (12)| 00:00:02 |
| 10 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 15438 | 422K| 47 (5)| 00:00:01 |
| 12 | VIEW | | 189 | 4914 | 18 (12)| 00:00:01 |
| 13 | HASH GROUP BY | | 189 | 4347 | 18 (12)| 00:00:01 |
|* 14 | HASH JOIN | | 643 | 14789 | 17 (6)| 00:00:01 |
| 15 | TABLE ACCESS FULL | N_COUNTRY | 190 | 2470 | 4 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | N_AGREEMENT | 643 | 6430 | 12 (0)| 00:00:01 |
| 17 | COLLECTION ITERATOR PICKLER FETCH| F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

1 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
3 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
5 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
7 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
9 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
11 - access("V0"."COUNTRY_CODE"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
14 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
16 - filter("TYPE_ID"=2)


Many thanks,
Ferro

[Updated on: Thu, 19 November 2015 02:12]

Report message to a moderator

Re: Query execute time versus fetch time [message #644860 is a reply to message #644859] Thu, 19 November 2015 02:25 Go to previous messageGo to next message
John Watson
Messages: 8981
Registered: January 2010
Location: Global Village
Senior Member
It is good of you to post the solution, but you accidentally posted the same plan twice. How many rows do the queries return? Surely not the expected fifty six trillion?

Report message to a moderator

Re: Query execute time versus fetch time [message #644861 is a reply to message #644860] Thu, 19 November 2015 03:00 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear John,

I am re-posting the explain plan after:
- compiling the function without the added part
- execute plan of (same SQL statement)
- it took 0.211 seconds (Query opened in 0.211s [0.208s exec, 0.003s fetch])
- then SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
Plan hash value: 599037222
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                     |    56T|  1939T|   309M(100)|999:59:59 |
|*  1 |  HASH JOIN                              |                     |    56T|  1939T|   309M(100)|999:59:59 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH     | F_AGR_TRN_TYPE_INFO |  8168 | 16336 |    29   (0)| 00:00:01 |
|*  3 |   HASH JOIN                             |                     |   687G|    22T|  3788K(100)| 12:37:41 |
|   4 |    COLLECTION ITERATOR PICKLER FETCH    | F_AGR_TRN_TYPE_INFO |  8168 | 16336 |    29   (0)| 00:00:01 |
|*  5 |    HASH JOIN                            |                     |  8412M|   266G| 46515 (100)| 00:09:19 |
|   6 |     COLLECTION ITERATOR PICKLER FETCH   | F_AGR_TRN_TYPE_INFO |  8168 | 16336 |    29   (0)| 00:00:01 |
|*  7 |     HASH JOIN                           |                     |   102M|  3143M|   674  (85)| 00:00:09 |
|   8 |      COLLECTION ITERATOR PICKLER FETCH  | F_AGR_TRN_TYPE_INFO |  8168 | 16336 |    29   (0)| 00:00:01 |
|*  9 |      HASH JOIN                          |                     |  1260K|    36M|    84  (12)| 00:00:02 |
|  10 |       COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO |  8168 | 16336 |    29   (0)| 00:00:01 |
|* 11 |       HASH JOIN                         |                     | 15438 |   422K|    47   (5)| 00:00:01 |
|  12 |        VIEW                             |                     |   189 |  4914 |    18  (12)| 00:00:01 |
|  13 |         HASH GROUP BY                   |                     |   189 |  4347 |    18  (12)| 00:00:01 |
|* 14 |          HASH JOIN                      |                     |   643 | 14789 |    17   (6)| 00:00:01 |
|  15 |           TABLE ACCESS FULL             | N_COUNTRY           |   190 |  2470 |     4   (0)| 00:00:01 |
|* 16 |           TABLE ACCESS FULL             | N_AGREEMENT         |   643 |  6430 |    12   (0)| 00:00:01 |
|  17 |        COLLECTION ITERATOR PICKLER FETCH| F_AGR_TRN_TYPE_INFO |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
   3 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
   5 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
   7 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
   9 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
  11 - access("V0"."COUNTRY_CODE"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
  14 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
  16 - filter("TYPE_ID"=2)


Many thanks,
Ferro



--moderator update: corrected the [quote] tags to [code] tags.

[Updated on: Thu, 19 November 2015 03:05] by Moderator

Report message to a moderator

Re: Query execute time versus fetch time [message #644862 is a reply to message #644861] Thu, 19 November 2015 03:10 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear John,

Sorry for the wrong tags, just noted your question both queries only return only 17 (seventeen) rows every time.

Thanks,
Ferro

Report message to a moderator

Re: Query execute time versus fetch time [message #644863 is a reply to message #644862] Thu, 19 November 2015 03:23 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
The plan is a mess because oracle can't estimate how many rows F_AGR_TRN_TYPE_INFO returns so it's sticking a default guess in for rows returned.
I'd ditch the function and merge what it's doing into the main query.

Report message to a moderator

Re: Query execute time versus fetch time [message #644865 is a reply to message #644863] Thu, 19 November 2015 03:36 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Thanks cookeimonster,

This solution is what I am trying now however I am not convinced that the extra AND in the right outer join should cause all this delay and force this change!

As far as I understand:
- the right outer join query is executed one time only with every function call
- there is 0 extra table scan after adding my change
- so the actual extra cost is just evaluating the extra AND with its 3 ORed parts using an indexed field SECTOR_ID

Where is the problem coming from?!!

Many thanks,
Ferro

Report message to a moderator

Re: Query execute time versus fetch time [message #644872 is a reply to message #644865] Thu, 19 November 2015 04:25 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're looking at the wrong plan. Plans don't include lookups functions do and it's the function that's been changed. So you need to do a before and after on the query the function is using. I wouldn't be surprised if you've gone from an index scan to a full table on n_cont.

Report message to a moderator

Re: Query execute time versus fetch time [message #644881 is a reply to message #644872] Thu, 19 November 2015 05:02 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Hi Cookiemonster,

I agree with you I am looking in the wrong place.

I repeated explain plan for the function query. For my surprise the time is almost the same before and after the change, which makes since as I understand the change should cause no delay! I wonder what goes wrong when I call the function more than once as a table from an external query! Or is it the use of "BULK COLLECT INTO" the object table, as this is the only difference between the function and the function query?!
I appreciate your help

The explain plan (with the modification that causes the slowness):
PLAN_TABLE_OUTPUT
Plan hash value: 3059933873
 
----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                           |   638 | 32538 |   611   (2)| 00:00:08 |
|   1 |  HASH GROUP BY         |                           |   638 | 32538 |   611   (2)| 00:00:08 |
|*  2 |   HASH JOIN            |                           |   638 | 32538 |   610   (2)| 00:00:08 |
|   3 |    TABLE ACCESS FULL   | N_COUNTRY                 |   190 |  4370 |     4   (0)| 00:00:01 |
|*  4 |    HASH JOIN OUTER     |                           |   638 | 17864 |   606   (2)| 00:00:08 |
|*  5 |     TABLE ACCESS FULL  | N_AGREEMENT               |   638 |  8932 |    12   (0)| 00:00:01 |
|   6 |     VIEW               |                           |   873 | 12222 |   593   (2)| 00:00:08 |
|   7 |      HASH GROUP BY     |                           |   873 | 18333 |   593   (2)| 00:00:08 |
|*  8 |       TABLE ACCESS FULL| TRN_AGREEMENT_TRANSACTION | 45559 |   934K|   590   (1)| 00:00:08 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
   4 - access("AGR"."AGREEMENT_SERIAL"="TRN"."FK_AGREEMENT_SERIAL"(+))
   5 - filter("AGR"."TYPE_ID"=2 AND "AGR"."SECTOR_ID"<>2)
   8 - filter("AGT"."TYPE_ID"=2 AND "AGT"."VALUE_DATE"<=SYSDATE@!)


The explain plan after commenting the part that is causing the slowness:
PLAN_TABLE_OUTPUT
Plan hash value: 3059933873
 
----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                           |   643 | 30864 |   611   (2)| 00:00:08 |
|   1 |  HASH GROUP BY         |                           |   643 | 30864 |   611   (2)| 00:00:08 |
|*  2 |   HASH JOIN            |                           |   643 | 30864 |   610   (2)| 00:00:08 |
|   3 |    TABLE ACCESS FULL   | N_COUNTRY                 |   190 |  4370 |     4   (0)| 00:00:01 |
|*  4 |    HASH JOIN OUTER     |                           |   643 | 16075 |   606   (2)| 00:00:08 |
|*  5 |     TABLE ACCESS FULL  | N_AGREEMENT               |   643 |  7073 |    12   (0)| 00:00:01 |
|   6 |     VIEW               |                           |   873 | 12222 |   593   (2)| 00:00:08 |
|   7 |      HASH GROUP BY     |                           |   873 | 18333 |   593   (2)| 00:00:08 |
|*  8 |       TABLE ACCESS FULL| TRN_AGREEMENT_TRANSACTION | 45559 |   934K|   590   (1)| 00:00:08 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
   4 - access("AGR"."AGREEMENT_SERIAL"="TRN"."FK_AGREEMENT_SERIAL"(+))
   5 - filter("AGR"."TYPE_ID"=2)
   8 - filter("AGT"."TYPE_ID"=2 AND "AGT"."VALUE_DATE"<=SYSDATE@!)


Many thanks,
Ferro

[Updated on: Thu, 19 November 2015 05:05]

Report message to a moderator

Re: Query execute time versus fetch time [message #644884 is a reply to message #644881] Thu, 19 November 2015 05:19 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
That first plan you're running doesn't look right. It should have the whole of the added block in the predicate information.

Report message to a moderator

Re: Query execute time versus fetch time [message #644891 is a reply to message #644881] Thu, 19 November 2015 06:55 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
OraFerro wrote on Thu, 19 November 2015 06:02
I wonder what goes wrong when I call the function more than once as a table from an external query


Context switches, most likely. For example:

SQL> select max(runid) from sys.plsql_trace_events
  2  /

MAX(RUNID)
----------
         3

SQL> alter session set events '10938 trace name context forever, level 1'
  2  /

Session altered.

SQL> select regexp_replace(ename,'A','a') from emp;

REGEXP_REPLACE(ENAME,'A','A')
------------------------------------------------------------------------------------------
SMITH
aLLEN
WaRD
JONES
MaRTIN
BLaKE
CLaRK
SCOTT
KING
TURNER
aDaMS

REGEXP_REPLACE(ENAME,'A','A')
------------------------------------------------------------------------------------------
JaMES
FORD
MILLER

14 rows selected.

SQL> alter session set events '10938 trace name context off'
  2  /

Session altered.

SQL> select max(runid) from sys.plsql_trace_events
  2  /

MAX(RUNID)
----------
         3

SQL> alter session set events '10938 trace name context forever, level 1'
  2  /

Session altered.

SQL> select sys.standard.regexp_replace(ename,'A','a') from emp;

SYS.STANDARD.REGEXP_REPLACE(ENAME,'A','A')
------------------------------------------------------------------------------------------
SMITH
aLLEN
WaRD
JONES
MaRTIN
BLaKE
CLaRK
SCOTT
KING
TURNER
aDaMS

SYS.STANDARD.REGEXP_REPLACE(ENAME,'A','A')
------------------------------------------------------------------------------------------
JaMES
FORD
MILLER

14 rows selected.

SQL> alter session set events '10938 trace name context off'
  2  /

Session altered.

SQL> select max(runid) from sys.plsql_trace_events
  2  /

MAX(RUNID)
----------
         4

SQL> set linesize 132
SQL> col event_unit for a15
SQL> col event_unit_kind for a15
SQL> col event_comment for a30
SQL> select  event_seq,
  2          event_unit,
  3          event_unit_kind,
  4          event_comment,
  5          sum(decode(event_comment,'PL/SQL Virtual Machine started', 1))
  6            over(partition by runid order by event_seq) context_switch
  7    from  sys.plsql_trace_events
  8    where runid = (select max(runid) from sys.plsql_trace_events)
  9    order by 1
 10  /

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
         1                                 PL/SQL Trace Tool started
         2                                 Trace flags changed
         3 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started              1
         4                                 PL/SQL Virtual Machine stopped              1
         5 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started              2
         6                                 PL/SQL Virtual Machine stopped              2
         7 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started              3
         8                                 PL/SQL Virtual Machine stopped              3
         9 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started              4
        10                                 PL/SQL Virtual Machine stopped              4
        11 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started              5

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        12                                 PL/SQL Virtual Machine stopped              5
        13 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started              6
        14                                 PL/SQL Virtual Machine stopped              6
        15 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started              7
        16                                 PL/SQL Virtual Machine stopped              7
        17 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started              8
        18                                 PL/SQL Virtual Machine stopped              8
        19 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started              9
        20                                 PL/SQL Virtual Machine stopped              9
        21 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started             10
        22                                 PL/SQL Virtual Machine stopped             10

 EVENT_SEQ EVENT_UNIT      EVENT_UNIT_KIND EVENT_COMMENT                  CONTEXT_SWITCH
---------- --------------- --------------- ------------------------------ --------------
        23 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started             11
        24                                 PL/SQL Virtual Machine stopped             11
        25 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started             12
        26                                 PL/SQL Virtual Machine stopped             12
        27 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started             13
        28                                 PL/SQL Virtual Machine stopped             13
        29 <anonymous>     ANONYMOUS BLOCK PL/SQL Virtual Machine started             14
        30                                 PL/SQL Virtual Machine stopped             14

30 rows selected.

SQL>


As you can see calling SQL built-in function REGEXP_REPLACE didn't produce any context switches (sys.plsql_trace_eventsmax(runid) from didn't change). While calling ir's PL/SQL counterpart did cause context switch for every fetched row. And context switch can be costly. Same example:

SQL> set timing on
SQL> select  max(x)
  2  from  (select regexp_replace(ename,'A','a') x from emp connect by level <= 5)
  3  /

MAX(X)
------------------------------------------------------------------------------------------------
aLLEN

Elapsed: 00:00:02.40
SQL> set timing on
SQL> select  max(x)
  2  from  (select sys.standard.regexp_replace(ename,'A','a') x from emp connect by level <= 5)
  3  /

MAX(X)
------------------------------------------------------------------------------------------------
aLLEN

Elapsed: 00:00:10.50
SQL>


SY.

Report message to a moderator

Re: Query execute time versus fetch time [message #644898 is a reply to message #644891] Thu, 19 November 2015 08:17 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Thanks a lot SY.
I will read about context switching and ways to avoid this effect on my case. Meanwhile i aspreciate any suggestion regarding my query

Report message to a moderator

Re: Query execute time versus fetch time [message #644899 is a reply to message #644891] Thu, 19 November 2015 08:21 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Quote:

 Members  Search  Help  Register  Login  Home

Home » SQL & PL/SQL » SQL & PL/SQL » Query execute time versus fetch time (11.2.1.0)Show: Today's Messages :: Show Polls :: Message Navigator 
E-mail to friend    Query execute time versus fetch time [message #644807]Wed, 18 November 2015 04:13 OraFerro 
Messages: 201
Registered: July 2011

Senior Member

Dear All,

I am facing a situation where a query that used to run in less than 1 second is not taking 9 seconds.

The current execute time is 8.7 and fetch time is 0.3

What should I be looking for? (buffer Cache or dig into execution plan knowing that I can safely assume that nothing has changed regarding the query or the function that it uses,...etc.)

Thanks,
Ferro

[Updated on: Wed, 18 November 2015 04:14]

Report message to a moderator

     Re: Query execute time versus fetch time [message #644808 is a reply to message #644807]Wed, 18 November 2015 04:18 John Watson 
Messages: 5599
Registered: January 2010 
Location: Global Village

Senior Member

Execution plan.

Report message to a moderator

     Re: Query execute time versus fetch time [message #644810 is a reply to message #644808]Wed, 18 November 2015 04:29 OraFerro 
Messages: 201
Registered: July 2011

Senior Member

Thanks John, will dig into this but can you or anyone pass me a document that describes the difference between execute and fetch times and the possible causes of very slow execute time compared to fetch time?

Thanks,
Ferro

Report message to a moderator

     Re: Query execute time versus fetch time [message #644811 is a reply to message #644810]Wed, 18 November 2015 04:34 John Watson 
Messages: 5599
Registered: January 2010 
Location: Global Village

Senior Member

High execute time is usually for DML (working in buffer cache), high fetch time is usually for SELECT (working in PGA). How do you know whether the time is being spent in execute or fetch? Have you traced the query?

Report message to a moderator

     Re: Query execute time versus fetch time [message #644812 is a reply to message #644811]Wed, 18 November 2015 04:57 OraFerro 
Messages: 201
Registered: July 2011

Senior Member

Hi John, 
- My query uses no DML, just a select query 
- I am using dbforge SQL client that returns execute and fetch time with every query I run
- I used timing start and timing stop with same result.

Quote:


Execute succeeded [< 0,001s]
Query opened in 9.269s [9.266s exec, 0.003s fetch]
Execute succeeded [< 0,001s]



Thanks,
Ferro

Report message to a moderator

     Re: Query execute time versus fetch time [message #644820 is a reply to message #644807]Wed, 18 November 2015 06:29 pablolee 
Messages: 2748
Registered: May 2007 
Location: Scotland

Senior Member

OraFerro wrote on Wed, 18 November 2015 10:13

I am facing a situation where a query that used to run in less than 1 second is not taking 9 seconds.


What's changed? (apart from the time it takes to execute  )

Report message to a moderator

     Re: Query execute time versus fetch time [message #644824 is a reply to message #644812]Wed, 18 November 2015 07:04 John Watson 
Messages: 5599
Registered: January 2010 
Location: Global Village

Senior Member

Look at this example of tracing two queries. The SELECT does nothing in execute, it is all in fetch; the DML is the reverse:

Code: [Select all] [Show/ hide]

******************************************************************************** SQL ID: 5bc0v4my7dvr5 Plan Hash: 3724264953 select count(*) from t1 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.01 0.02 1520 1523 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.01 0.03 1520 1523 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 133 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 SORT AGGREGATE (cr=1523 pr=1520 pw=0 time=28602 us) 90338 90338 90338 TABLE ACCESS FULL T1 (cr=1523 pr=1520 pw=0 time=8033 us cost=424 size=0 card=90338) ******************************************************************************** SQL ID: fhy49n7dqbdgf Plan Hash: 2927627013 update t1 set object_id=object_id call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.43 0.66 1520 3370 97595 90338 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.43 0.66 1520 3370 97595 90338 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 133 Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 0 0 0 UPDATE T1 (cr=3063 pr=1173 pw=0 time=616840 us) 200189 200189 200189 TABLE ACCESS FULL T1 (cr=3360 pr=1520 pw=0 time=75380 us cost=424 size=451690 card=90338) ********************************************************************************



I would think that this dbforge is telling you a load of rubbish.

Report message to a moderator

     Re: Query execute time versus fetch time [message #644829 is a reply to message #644824]Wed, 18 November 2015 08:03 cookiemonster 
Messages: 11889
Registered: September 2008 
Location: Rainy Manchester

Senior Member

A select for update can spend time in the execute phase while it locks the records.
But really we need the execution plan to see if dbforge knows what it's talking about

Report message to a moderator

     Re: Query execute time versus fetch time [message #644859 is a reply to message #644829]Thu, 19 November 2015 02:10 OraFerro 
Messages: 201
Registered: July 2011

Senior Member

Dear All,

Thanks to your valuable comments. I located the reason of delay 
(of course my assumption was not valid and something has changed in 
the query), however:
1- The query does not include DML
2- I could not go around my change to avoid the delay

The case is:
- I have a function that returns a summary figure based on several 
parameters (code included)
- Inside the function there is a right outer join with another table 
to get all of its values in the output
- All what I added is an extra condition inside the right outer join 
query (sector_id) which is indexed (market "-->> the added part start" inside the function body)

Please find below the code of my function,query, and execution plan before 
and after the change for your kind advice:

Code: [Select all] [Show/ hide]

CREATE OR REPLACE TYPE OBJ_AGR_TRN_TYPE_INFO AS OBJECT ( /* FERRO: 16-11-2015 */ COUNTRY_CODE NUMBER(15,3),-- COUNTRY_NAME_A VARCHAR2(50),-- COUNTRY_NAME_E VARCHAR2(50),-- cont_SERIAL NUMBER(15,3),-- cont SERIAL cont_NO VARCHAR2(50),-- cont NUMBER0 + SUB NUMBER AMOUNT NUMBER(15,3) -- AMOUNT ); CREATE TYPE TBL_AGR_TRN_TYPE_INFO AS TABLE OF OBJ_AGR_TRN_TYPE_INFO; CREATE OR REPLACE FUNCTION F_AGR_TRN_TYPE_INFO ( I_TYPE_ID IN NUMBER, D_VALUE_DATE IN DATE DEFAULT SYSDATE, I_cont_LEVEL IN NUMBER DEFAULT 1, I_p_SECTOR IN NUMBER DEFAULT 3 ) RETURN TBL_AGR_TRN_TYPE_INFO AS AGR_TRN_TYPE_INFO TBL_AGR_TRN_TYPE_INFO; BEGIN CASE I_cont_LEVEL WHEN 0 THEN -- COUNTRY LEVEL SELECT OBJ_AGR_TRN_TYPE_INFO( MAX(COUNTRY_CODE) , -- COUNTRY CODE COUNTRY_NAME_A, -- ARABIC COUNTRY NAME COUNTRY_NAME_E, -- ENGLISH COUNTRY NAME 0,-- cont SERIAL 0, -- cont NUMBER0 + SUB NUMBER TO_CHAR(SUM(NVL(AMOUNT,0)),'999999999999999.999') ) BULK COLLECT INTO AGR_TRN_TYPE_INFO FROM ( SELECT FK_cont_SERIAL, TO_CHAR(SUM(AGT.AMOUNT),'999999999999.999') AMOUNT FROM TRN_cont_TRANSACTION AGT WHERE AGT.TYPE_ID = I_TYPE_ID AND AGT.VALUE_DATE <= D_VALUE_DATE GROUP BY AGT.FK_cont_SERIAL ) TRN RIGHT OUTER JOIN ( SELECT AGR.FK_COUNTRYCODE COUNTRY_CODE, CNT.SHORT_NAME_A COUNTRY_NAME_A, CNT.SHORT_NAME_E COUNTRY_NAME_E, AGR.cont_SERIAL, AGR.NUMBER0||AGR.SUB_NUMBER cont_NO FROM N_cont AGR, N_COUNTRY CNT WHERE AGR.FK_COUNTRYCODE = CNT.CODE -->> the added part start AND ( (I_p_SECTOR = 3) OR (I_p_SECTOR = 1 AND AGR.SECTOR_ID <> 2) OR (I_p_SECTOR = 2 AND AGR.SECTOR_ID = 2) ) --<< the added part end AND AGR.TYPE_ID = 2 ) CNT ON CNT.cont_SERIAL = TRN.FK_cont_SERIAL GROUP BY COUNTRY_NAME_A, COUNTRY_NAME_E ; END CASE; RETURN (AGR_TRN_TYPE_INFO); END ; / ---DISB0009 EXPLAIN PLAN INTO plan_table FOR SELECT V1.COUNTRY_CODE, V1.COUNTRY_NAME_A, V1.COUNTRY_NAME_E, LOAN_COUNT, V1.AMOUNT ORIGINAL_AMOUNT_1, ROUND(V2A.AMOUNT + V2B.AMOUNT + V2C.AMOUNT,3) CANCEL_DISCOUNT_AMOUNT_2, ROUND(V1.AMOUNT - (V2A.AMOUNT + V2B.AMOUNT + V2C.AMOUNT),3) NET_AMOUNT_3, --(1-2) V4.AMOUNT WITHDRAWAL_AMOUNT_4, ROUND( (V1.AMOUNT - (V2A.AMOUNT + V2B.AMOUNT + V2C.AMOUNT))-V4.AMOUNT,3) BALANCE_AMOUNT_5, --(4-3) V6.AMOUNT PAID_INSTALLEMENT_AMOUNT_6 FROM ( SELECT MAX(FK_COUNTRYCODE) COUNTRY_CODE, CNT.SHORT_NAME_A, COUNT(*) LOAN_COUNT FROM N_cont AGR, N_COUNTRY CNT WHERE TYPE_ID =2 AND AGR.FK_COUNTRYCODE = CNT.CODE AND ( (3 = 3) OR (0 = 1 AND AGR.SECTOR_ID <> 2) OR (0 = 2 AND AGR.SECTOR_ID = 2) ) GROUP BY SHORT_NAME_A ) V0, TABLE(F_AGR_TRN_TYPE_INFO(1 ,SYSDATE, 0, 3)) V1, TABLE(F_AGR_TRN_TYPE_INFO(9 ,SYSDATE, 0, 3)) V2A, TABLE(F_AGR_TRN_TYPE_INFO(10,SYSDATE, 0, 3)) V2B, TABLE(F_AGR_TRN_TYPE_INFO(11,SYSDATE, 0, 3)) V2C, TABLE(F_AGR_TRN_TYPE_INFO(2 ,SYSDATE, 0, 3)) V4, TABLE(F_AGR_TRN_TYPE_INFO(6 ,SYSDATE, 0, 3)) V6 WHERE V1.COUNTRY_CODE = V2A.COUNTRY_CODE AND V1.COUNTRY_CODE = V2B.COUNTRY_CODE AND V1.COUNTRY_CODE = V2C.COUNTRY_CODE AND V1.COUNTRY_CODE = V4.COUNTRY_CODE AND V1.COUNTRY_CODE = V6.COUNTRY_CODE AND V1.COUNTRY_CODE = V0.COUNTRY_CODE ;


explain plan before the change:
Quote:


PLAN_TABLE_OUTPUT
Plan hash value: 599037222

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56T| 1939T| 309M(100)|999:59:59 |
|* 1 | HASH JOIN | | 56T| 1939T| 309M(100)|999:59:59 |
| 2 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 687G| 22T| 3788K(100)| 12:37:41 |
| 4 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 8412M| 266G| 46515 (100)| 00:09:19 |
| 6 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 102M| 3143M| 674 (85)| 00:00:09 |
| 8 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 1260K| 36M| 84 (12)| 00:00:02 |
| 10 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 15438 | 422K| 47 (5)| 00:00:01 |
| 12 | VIEW | | 189 | 4914 | 18 (12)| 00:00:01 |
| 13 | HASH GROUP BY | | 189 | 4347 | 18 (12)| 00:00:01 |
|* 14 | HASH JOIN | | 643 | 14789 | 17 (6)| 00:00:01 |
| 15 | TABLE ACCESS FULL | N_COUNTRY | 190 | 2470 | 4 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | N_AGREEMENT | 643 | 6430 | 12 (0)| 00:00:01 |
| 17 | COLLECTION ITERATOR PICKLER FETCH| F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

1 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
3 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
5 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
7 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
9 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
11 - access("V0"."COUNTRY_CODE"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
14 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
16 - filter("TYPE_ID"=2)



explain plan after the change
Quote:



PLAN_TABLE_OUTPUT
Plan hash value: 599037222

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56T| 1939T| 309M(100)|999:59:59 |
|* 1 | HASH JOIN | | 56T| 1939T| 309M(100)|999:59:59 |
| 2 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 3 | HASH JOIN | | 687G| 22T| 3788K(100)| 12:37:41 |
| 4 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 5 | HASH JOIN | | 8412M| 266G| 46515 (100)| 00:09:19 |
| 6 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 102M| 3143M| 674 (85)| 00:00:09 |
| 8 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 9 | HASH JOIN | | 1260K| 36M| 84 (12)| 00:00:02 |
| 10 | COLLECTION ITERATOR PICKLER FETCH | F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 15438 | 422K| 47 (5)| 00:00:01 |
| 12 | VIEW | | 189 | 4914 | 18 (12)| 00:00:01 |
| 13 | HASH GROUP BY | | 189 | 4347 | 18 (12)| 00:00:01 |
|* 14 | HASH JOIN | | 643 | 14789 | 17 (6)| 00:00:01 |
| 15 | TABLE ACCESS FULL | N_COUNTRY | 190 | 2470 | 4 (0)| 00:00:01 |
|* 16 | TABLE ACCESS FULL | N_AGREEMENT | 643 | 6430 | 12 (0)| 00:00:01 |
| 17 | COLLECTION ITERATOR PICKLER FETCH| F_AGR_TRN_TYPE_INFO | 8168 | 16336 | 29 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

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

1 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
3 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
5 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
7 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
9 - access(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
11 - access("V0"."COUNTRY_CODE"=SYS_OP_ATG(VALUE(KOKBF$),1,2,2))
14 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
16 - filter("TYPE_ID"=2)



Many thanks,
Ferro

[Updated on: Thu, 19 November 2015 02:12]

Report message to a moderator

     Re: Query execute time versus fetch time [message #644860 is a reply to message #644859]Thu, 19 November 2015 02:25 John Watson 
Messages: 5599
Registered: January 2010 
Location: Global Village


Can you please explain to me what should i deduce from your comment?
Is the way i am displaying the explain plan wrong or there is something wrong with the query?
Thanks,
Ferro

Report message to a moderator

Re: Query execute time versus fetch time [message #644900 is a reply to message #644899] Thu, 19 November 2015 08:27 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Which comment?
You've quoted half the thread there.

Report message to a moderator

Re: Query execute time versus fetch time [message #644901 is a reply to message #644898] Thu, 19 November 2015 08:29 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
OraFerro wrote on Thu, 19 November 2015 14:17
Thanks a lot SY.
I will read about context switching and ways to avoid this effect on my case. Meanwhile i aspreciate any suggestion regarding my query


Rewrite it without the function. The function is doing you no favours whatsoever. It causes performance issues on multiple levels - context switching and preventing oracle from accurately calculating stats being the most obvious ones.

Report message to a moderator

Re: Query execute time versus fetch time [message #644970 is a reply to message #644901] Mon, 23 November 2015 00:19 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Dear Cookiemonster,

Thanks for your valuable advice. replacing the function enhanced the performance (0.5 seconds now) as per the below updated query and explain plan.
However:
1- I need to use the function for code readability, maintenance, and unification of the code across reports done by more than one developer.
2- I still do not understand why would Oracle get confused if I am encapsulating query code in function body.
3- Is there any way to go around that and control the context switching problem while using the function?

From the below you will see how lengthy the query has become from the repeated part of the function!
explain plan for
SELECT V1.COUNTRY_CODE, V1.COUNTRY_NAME_A, V1.COUNTRY_NAME_E, LOAN_COUNT,
       V1.AMOUNT ORIGINAL_AMOUNT_1,
       ROUND(V2A.AMOUNT + V2B.AMOUNT + V2C.AMOUNT,3) CANCEL_DISCOUNT_AMOUNT_2,
       ROUND(V1.AMOUNT - (V2A.AMOUNT + V2B.AMOUNT + V2C.AMOUNT),3) NET_AMOUNT_3, --(1-2)
       V4.AMOUNT WITHDRAWAL_AMOUNT_4,
       ROUND( (V1.AMOUNT - (V2A.AMOUNT + V2B.AMOUNT + V2C.AMOUNT))-V4.AMOUNT,3) BALANCE_AMOUNT_5, --(4-3)
       V6.AMOUNT PAID_INSTALLEMENT_AMOUNT_6
  FROM
    (
      SELECT MAX(FK_COUNTRYCODE) COUNTRY_CODE, CNT.SHORT_NAME_A, COUNT(*) LOAN_COUNT
        FROM N_cont AGR, N_COUNTRY CNT 
        WHERE TYPE_ID =2
          AND AGR.FK_COUNTRYCODE = CNT.CODE
          AND (
                (0 = 1 AND TRIM(AGR.SUB_NUMBER) = 'P')
                OR
                (0 = 2 AND (TRIM(AGR.SUB_NUMBER) IS NULL OR TRIM(AGR.SUB_NUMBER) <> 'P'))
                OR
                (3 = 3 AND AGR.SUB_NUMBER = AGR.SUB_NUMBER)
              )
        GROUP BY SHORT_NAME_A
    )  V0,
(
       SELECT
         MAX(COUNTRY_CODE) COUNTRY_CODE ,    -- COUNTRY CODE
         COUNTRY_NAME_A,  -- ARABIC COUNTRY NAME
         COUNTRY_NAME_E,  -- ENGLISH COUNTRY NAME
         TO_CHAR(SUM(NVL(AMOUNT,0)),'999999999999999.999') AMOUNT -- AMOUNT
        FROM  
          (
            SELECT FK_cont_SERIAL,  TO_CHAR(SUM(AGT.AMOUNT),'999999999999.999') AMOUNT
            FROM TRN_cont_TRANSACTION AGT
            WHERE AGT.TYPE_ID = 1
              AND AGT.VALUE_DATE <= sysdate 
            GROUP BY  AGT.FK_cont_SERIAL
          ) TRN
          RIGHT OUTER JOIN
          (
            SELECT AGR.FK_COUNTRYCODE COUNTRY_CODE, CNT.SHORT_NAME_A COUNTRY_NAME_A, CNT.SHORT_NAME_E COUNTRY_NAME_E,  AGR.cont_SERIAL,  AGR.NUMBER0||AGR.SUB_NUMBER cont_NO
              FROM N_cont AGR, N_COUNTRY CNT
              WHERE AGR.FK_COUNTRYCODE = CNT.CODE
                AND (
                      (0 = 1 AND (TRIM(AGR.SUB_NUMBER) IS NULL OR TRIM(AGR.SUB_NUMBER) <> 'P')) 
                      OR
                      (0 = 2 AND TRIM(AGR.SUB_NUMBER) = 'P')
                      OR
                      (3 = 3 AND AGR.SUB_NUMBER = AGR.SUB_NUMBER)
                    )
                AND AGR.TYPE_ID = 2
          ) CNT
          ON CNT.cont_SERIAL = TRN.FK_cont_SERIAL
      GROUP BY COUNTRY_NAME_A, COUNTRY_NAME_E
  ) v1,
    --TABLE(F_AGR_TRN_TYPE_INFO(1 ,SYSDATE, 0, 3)) V1,
(
         SELECT
         MAX(COUNTRY_CODE) COUNTRY_CODE ,    -- COUNTRY CODE
         COUNTRY_NAME_A,  -- ARABIC COUNTRY NAME
         COUNTRY_NAME_E,  -- ENGLISH COUNTRY NAME
         TO_CHAR(SUM(NVL(AMOUNT,0)),'999999999999999.999') AMOUNT -- AMOUNT
        FROM  
          (
            SELECT FK_cont_SERIAL,  TO_CHAR(SUM(AGT.AMOUNT),'999999999999.999') AMOUNT
            FROM TRN_cont_TRANSACTION AGT
            WHERE AGT.TYPE_ID = 9
              AND AGT.VALUE_DATE <= sysdate 
            GROUP BY  AGT.FK_cont_SERIAL
          ) TRN
          RIGHT OUTER JOIN
          (
            SELECT AGR.FK_COUNTRYCODE COUNTRY_CODE, CNT.SHORT_NAME_A COUNTRY_NAME_A, CNT.SHORT_NAME_E COUNTRY_NAME_E,  AGR.cont_SERIAL,  AGR.NUMBER0||AGR.SUB_NUMBER cont_NO
              FROM N_cont AGR, N_COUNTRY CNT
              WHERE AGR.FK_COUNTRYCODE = CNT.CODE
                AND (
                      (0 = 1 AND (TRIM(AGR.SUB_NUMBER) IS NULL OR TRIM(AGR.SUB_NUMBER) <> 'P')) 
                      OR
                      (0 = 2 AND TRIM(AGR.SUB_NUMBER) = 'P')
                      OR
                      (3 = 3 AND AGR.SUB_NUMBER = AGR.SUB_NUMBER)
                    )
                AND AGR.TYPE_ID = 2
          ) CNT
          ON CNT.cont_SERIAL = TRN.FK_cont_SERIAL
      GROUP BY COUNTRY_NAME_A, COUNTRY_NAME_E 
  ) v2a,
    --TABLE(F_AGR_TRN_TYPE_INFO(9 ,SYSDATE, 0, 3)) V2A,
(
         SELECT
         MAX(COUNTRY_CODE) COUNTRY_CODE ,    -- COUNTRY CODE
         COUNTRY_NAME_A,  -- ARABIC COUNTRY NAME
         COUNTRY_NAME_E,  -- ENGLISH COUNTRY NAME
         TO_CHAR(SUM(NVL(AMOUNT,0)),'999999999999999.999') AMOUNT -- AMOUNT
        FROM  
          (
            SELECT FK_cont_SERIAL,  TO_CHAR(SUM(AGT.AMOUNT),'999999999999.999') AMOUNT
            FROM TRN_cont_TRANSACTION AGT
            WHERE AGT.TYPE_ID = 10
              AND AGT.VALUE_DATE <= sysdate 
            GROUP BY  AGT.FK_cont_SERIAL
          ) TRN
          RIGHT OUTER JOIN
          (
            SELECT AGR.FK_COUNTRYCODE COUNTRY_CODE, CNT.SHORT_NAME_A COUNTRY_NAME_A, CNT.SHORT_NAME_E COUNTRY_NAME_E,  AGR.cont_SERIAL,  AGR.NUMBER0||AGR.SUB_NUMBER cont_NO
              FROM N_cont AGR, N_COUNTRY CNT
              WHERE AGR.FK_COUNTRYCODE = CNT.CODE
                AND (
                      (0 = 1 AND (TRIM(AGR.SUB_NUMBER) IS NULL OR TRIM(AGR.SUB_NUMBER) <> 'P')) 
                      OR
                      (0 = 2 AND TRIM(AGR.SUB_NUMBER) = 'P')
                      OR
                      (3 = 3 AND AGR.SUB_NUMBER = AGR.SUB_NUMBER)
                    )
                AND AGR.TYPE_ID = 2
          ) CNT
          ON CNT.cont_SERIAL = TRN.FK_cont_SERIAL
      GROUP BY COUNTRY_NAME_A, COUNTRY_NAME_E 
) v2b,
    --TABLE(F_AGR_TRN_TYPE_INFO(10,SYSDATE, 0, 3)) V2B,    
(
           SELECT
         MAX(COUNTRY_CODE) COUNTRY_CODE ,    -- COUNTRY CODE
         COUNTRY_NAME_A,  -- ARABIC COUNTRY NAME
         COUNTRY_NAME_E,  -- ENGLISH COUNTRY NAME
         TO_CHAR(SUM(NVL(AMOUNT,0)),'999999999999999.999') AMOUNT -- AMOUNT
        FROM  
          (
            SELECT FK_cont_SERIAL,  TO_CHAR(SUM(AGT.AMOUNT),'999999999999.999') AMOUNT
            FROM TRN_cont_TRANSACTION AGT
            WHERE AGT.TYPE_ID = 11
              AND AGT.VALUE_DATE <= sysdate 
            GROUP BY  AGT.FK_cont_SERIAL
          ) TRN
          RIGHT OUTER JOIN
          (
            SELECT AGR.FK_COUNTRYCODE COUNTRY_CODE, CNT.SHORT_NAME_A COUNTRY_NAME_A, CNT.SHORT_NAME_E COUNTRY_NAME_E,  AGR.cont_SERIAL,  AGR.NUMBER0||AGR.SUB_NUMBER cont_NO
              FROM N_cont AGR, N_COUNTRY CNT
              WHERE AGR.FK_COUNTRYCODE = CNT.CODE
                AND (
                      (0 = 1 AND (TRIM(AGR.SUB_NUMBER) IS NULL OR TRIM(AGR.SUB_NUMBER) <> 'P')) 
                      OR
                      (0 = 2 AND TRIM(AGR.SUB_NUMBER) = 'P')
                      OR
                      (3 = 3 AND AGR.SUB_NUMBER = AGR.SUB_NUMBER)
                    )
                AND AGR.TYPE_ID = 2
          ) CNT
          ON CNT.cont_SERIAL = TRN.FK_cont_SERIAL
      GROUP BY COUNTRY_NAME_A, COUNTRY_NAME_E 
  )v2c,
    --TABLE(F_AGR_TRN_TYPE_INFO(11,SYSDATE, 0, 3)) V2C,
(
           SELECT
         MAX(COUNTRY_CODE) COUNTRY_CODE ,    -- COUNTRY CODE
         COUNTRY_NAME_A,  -- ARABIC COUNTRY NAME
         COUNTRY_NAME_E,  -- ENGLISH COUNTRY NAME
         TO_CHAR(SUM(NVL(AMOUNT,0)),'999999999999999.999') AMOUNT -- AMOUNT
        FROM  
          (
            SELECT FK_cont_SERIAL,  TO_CHAR(SUM(AGT.AMOUNT),'999999999999.999') AMOUNT
            FROM TRN_cont_TRANSACTION AGT
            WHERE AGT.TYPE_ID = 2
              AND AGT.VALUE_DATE <= sysdate 
            GROUP BY  AGT.FK_cont_SERIAL
          ) TRN
          RIGHT OUTER JOIN
          (
            SELECT AGR.FK_COUNTRYCODE COUNTRY_CODE, CNT.SHORT_NAME_A COUNTRY_NAME_A, CNT.SHORT_NAME_E COUNTRY_NAME_E,  AGR.cont_SERIAL,  AGR.NUMBER0||AGR.SUB_NUMBER cont_NO
              FROM N_cont AGR, N_COUNTRY CNT
              WHERE AGR.FK_COUNTRYCODE = CNT.CODE
                AND (
                      (0 = 1 AND (TRIM(AGR.SUB_NUMBER) IS NULL OR TRIM(AGR.SUB_NUMBER) <> 'P')) 
                      OR
                      (0 = 2 AND TRIM(AGR.SUB_NUMBER) = 'P')
                      OR
                      (3 = 3 AND AGR.SUB_NUMBER = AGR.SUB_NUMBER)
                    )
                AND AGR.TYPE_ID = 2
          ) CNT
          ON CNT.cont_SERIAL = TRN.FK_cont_SERIAL
      GROUP BY COUNTRY_NAME_A, COUNTRY_NAME_E 
  ) v4,
    --TABLE(F_AGR_TRN_TYPE_INFO(2 ,SYSDATE, 0, 3)) V4,
(
         SELECT
         MAX(COUNTRY_CODE) COUNTRY_CODE ,    -- COUNTRY CODE
         COUNTRY_NAME_A,  -- ARABIC COUNTRY NAME
         COUNTRY_NAME_E,  -- ENGLISH COUNTRY NAME
         TO_CHAR(SUM(NVL(AMOUNT,0)),'999999999999999.999') AMOUNT -- AMOUNT
        FROM  
          (
            SELECT FK_cont_SERIAL,  TO_CHAR(SUM(AGT.AMOUNT),'999999999999.999') AMOUNT
            FROM TRN_cont_TRANSACTION AGT
            WHERE AGT.TYPE_ID = 6
              AND AGT.VALUE_DATE <= sysdate 
            GROUP BY  AGT.FK_cont_SERIAL
          ) TRN
          RIGHT OUTER JOIN
          (
            SELECT AGR.FK_COUNTRYCODE COUNTRY_CODE, CNT.SHORT_NAME_A COUNTRY_NAME_A, CNT.SHORT_NAME_E COUNTRY_NAME_E,  AGR.cont_SERIAL,  AGR.NUMBER0||AGR.SUB_NUMBER cont_NO
              FROM N_cont AGR, N_COUNTRY CNT
              WHERE AGR.FK_COUNTRYCODE = CNT.CODE
                AND (
                      (0 = 1 AND (TRIM(AGR.SUB_NUMBER) IS NULL OR TRIM(AGR.SUB_NUMBER) <> 'P')) 
                      OR
                      (0 = 2 AND TRIM(AGR.SUB_NUMBER) = 'P')
                      OR
                      (3 = 3 AND AGR.SUB_NUMBER = AGR.SUB_NUMBER)
                    )
                AND AGR.TYPE_ID = 2
          ) CNT
          ON CNT.cont_SERIAL = TRN.FK_cont_SERIAL
      GROUP BY COUNTRY_NAME_A, COUNTRY_NAME_E 
)    V6
    --TABLE(F_AGR_TRN_TYPE_INFO(6 ,SYSDATE, 0, 3)) V6
  WHERE
    V1.COUNTRY_CODE = V2A.COUNTRY_CODE
    AND V1.COUNTRY_CODE = V2B.COUNTRY_CODE 
    AND V1.COUNTRY_CODE = V2C.COUNTRY_CODE
    AND V1.COUNTRY_CODE = V4.COUNTRY_CODE
    AND V1.COUNTRY_CODE = V6.COUNTRY_CODE
    AND V1.COUNTRY_CODE = V0.COUNTRY_CODE
 ;


PLAN_TABLE_OUTPUT
Plan hash value: 1714910370
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                           |    56M|    12G|  1389  (26)| 00:00:17 |
|*  1 |  HASH JOIN                             |                           |    56M|    12G|  1389  (26)| 00:00:17 |
|   2 |   VIEW                                 |                           |  2731 | 68275 |   316   (1)| 00:00:04 |
|   3 |    HASH GROUP BY                       |                           |  2731 |   112K|   316   (1)| 00:00:04 |
|   4 |     VIEW                               | VM_NWVW_1                 |  2731 |   112K|   316   (1)| 00:00:04 |
|   5 |      HASH GROUP BY                     |                           |  2731 |   232K|   316   (1)| 00:00:04 |
|*  6 |       HASH JOIN OUTER                  |                           |  2731 |   232K|   315   (1)| 00:00:04 |
|*  7 |        HASH JOIN                       |                           |   643 | 42438 |    17   (6)| 00:00:01 |
|   8 |         TABLE ACCESS FULL              | N_COUNTRY                 |   190 |  6650 |     4   (0)| 00:00:01 |
|*  9 |         TABLE ACCESS FULL              | N_cont               |   643 | 19933 |    12   (0)| 00:00:01 |
|* 10 |        TABLE ACCESS BY INDEX ROWID     | TRN_cont_TRANSACTION |  7246 |   148K|   298   (1)| 00:00:04 |
|* 11 |         INDEX RANGE SCAN               | AGT_TYPEID_INDX           |  7265 |       |    15   (0)| 00:00:01 |
|* 12 |   HASH JOIN                            |                           |  2077K|   406M|   763   (6)| 00:00:10 |
|  13 |    VIEW                                |                           |   643 | 16075 |   611   (2)| 00:00:08 |
|  14 |     HASH GROUP BY                      |                           |   643 | 36008 |   611   (2)| 00:00:08 |
|* 15 |      HASH JOIN OUTER                   |                           |   643 | 36008 |   610   (2)| 00:00:08 |
|* 16 |       HASH JOIN                        |                           |   643 | 27006 |    17   (6)| 00:00:01 |
|  17 |        TABLE ACCESS FULL               | N_COUNTRY                 |   190 |  4370 |     4   (0)| 00:00:01 |
|* 18 |        TABLE ACCESS FULL               | N_cont               |   643 | 12217 |    12   (0)| 00:00:01 |
|  19 |       VIEW                             |                           |  1726 | 24164 |   593   (2)| 00:00:08 |
|  20 |        HASH GROUP BY                   |                           |  1726 | 36246 |   593   (2)| 00:00:08 |
|* 21 |         TABLE ACCESS FULL              | TRN_cont_TRANSACTION | 44587 |   914K|   590   (1)| 00:00:08 |
|* 22 |    HASH JOIN                           |                           |   323K|    55M|   140  (13)| 00:00:02 |
|  23 |     VIEW                               |                           |   643 | 16075 |    22  (14)| 00:00:01 |
|  24 |      HASH GROUP BY                     |                           |   643 | 36008 |    22  (14)| 00:00:01 |
|* 25 |       HASH JOIN RIGHT OUTER            |                           |   643 | 36008 |    21  (10)| 00:00:01 |
|  26 |        VIEW                            |                           |    28 |   392 |     4  (25)| 00:00:01 |
|  27 |         HASH GROUP BY                  |                           |    28 |   588 |     4  (25)| 00:00:01 |
|* 28 |          TABLE ACCESS BY INDEX ROWID   | TRN_cont_TRANSACTION |    28 |   588 |     3   (0)| 00:00:01 |
|* 29 |           INDEX RANGE SCAN             | AGT_TYPEID_INDX           |    28 |       |     1   (0)| 00:00:01 |
|* 30 |        HASH JOIN                       |                           |   643 | 27006 |    17   (6)| 00:00:01 |
|  31 |         TABLE ACCESS FULL              | N_COUNTRY                 |   190 |  4370 |     4   (0)| 00:00:01 |
|* 32 |         TABLE ACCESS FULL              | N_cont               |   643 | 12217 |    12   (0)| 00:00:01 |
|* 33 |     HASH JOIN                          |                           | 50245 |  7605K|   116  (12)| 00:00:02 |
|  34 |      VIEW                              |                           |   643 | 16075 |    25  (12)| 00:00:01 |
|  35 |       HASH GROUP BY                    |                           |   643 | 36008 |    25  (12)| 00:00:01 |
|* 36 |        HASH JOIN RIGHT OUTER           |                           |   643 | 36008 |    24   (9)| 00:00:01 |
|  37 |         VIEW                           |                           |   108 |  1512 |     7  (15)| 00:00:01 |
|  38 |          HASH GROUP BY                 |                           |   108 |  2268 |     7  (15)| 00:00:01 |
|* 39 |           TABLE ACCESS BY INDEX ROWID  | TRN_cont_TRANSACTION |   112 |  2352 |     6   (0)| 00:00:01 |
|* 40 |            INDEX RANGE SCAN            | AGT_TYPEID_INDX           |   112 |       |     1   (0)| 00:00:01 |
|* 41 |         HASH JOIN                      |                           |   643 | 27006 |    17   (6)| 00:00:01 |
|  42 |          TABLE ACCESS FULL             | N_COUNTRY                 |   190 |  4370 |     4   (0)| 00:00:01 |
|* 43 |          TABLE ACCESS FULL             | N_cont               |   643 | 12217 |    12   (0)| 00:00:01 |
|* 44 |      HASH JOIN                         |                           |  7814 |   992K|    90  (10)| 00:00:02 |
|  45 |       VIEW                             |                           |   643 | 16075 |    25  (12)| 00:00:01 |
|  46 |        HASH GROUP BY                   |                           |   643 | 36008 |    25  (12)| 00:00:01 |
|* 47 |         HASH JOIN RIGHT OUTER          |                           |   643 | 36008 |    24   (9)| 00:00:01 |
|  48 |          VIEW                          |                           |   114 |  1596 |     7  (15)| 00:00:01 |
|  49 |           HASH GROUP BY                |                           |   114 |  2394 |     7  (15)| 00:00:01 |
|* 50 |            TABLE ACCESS BY INDEX ROWID | TRN_cont_TRANSACTION |   118 |  2478 |     6   (0)| 00:00:01 |
|* 51 |             INDEX RANGE SCAN           | AGT_TYPEID_INDX           |   118 |       |     1   (0)| 00:00:01 |
|* 52 |          HASH JOIN                     |                           |   643 | 27006 |    17   (6)| 00:00:01 |
|  53 |           TABLE ACCESS FULL            | N_COUNTRY                 |   190 |  4370 |     4   (0)| 00:00:01 |
|* 54 |           TABLE ACCESS FULL            | N_cont               |   643 | 12217 |    12   (0)| 00:00:01 |
|* 55 |       HASH JOIN                        |                           |  1215 |   124K|    64   (8)| 00:00:01 |
|  56 |        VIEW                            |                           |   189 |  4914 |    18  (12)| 00:00:01 |
|  57 |         HASH GROUP BY                  |                           |   189 |  5292 |    18  (12)| 00:00:01 |
|* 58 |          HASH JOIN                     |                           |   643 | 18004 |    17   (6)| 00:00:01 |
|  59 |           TABLE ACCESS FULL            | N_COUNTRY                 |   190 |  2470 |     4   (0)| 00:00:01 |
|* 60 |           TABLE ACCESS FULL            | N_cont               |   643 |  9645 |    12   (0)| 00:00:01 |
|  61 |        VIEW                            |                           |   643 | 50797 |    46   (7)| 00:00:01 |
|  62 |         HASH GROUP BY                  |                           |   643 | 36008 |    46   (7)| 00:00:01 |
|* 63 |          HASH JOIN RIGHT OUTER         |                           |   643 | 36008 |    45   (5)| 00:00:01 |
|  64 |           VIEW                         |                           |   527 |  7378 |    28   (4)| 00:00:01 |
|  65 |            HASH GROUP BY               |                           |   527 | 11067 |    28   (4)| 00:00:01 |
|* 66 |             TABLE ACCESS BY INDEX ROWID| TRN_cont_TRANSACTION |   627 | 13167 |    27   (0)| 00:00:01 |
|* 67 |              INDEX RANGE SCAN          | AGT_TYPEID_INDX           |   628 |       |     2   (0)| 00:00:01 |
|* 68 |           HASH JOIN                    |                           |   643 | 27006 |    17   (6)| 00:00:01 |
|  69 |            TABLE ACCESS FULL           | N_COUNTRY                 |   190 |  4370 |     4   (0)| 00:00:01 |
|* 70 |            TABLE ACCESS FULL           | N_cont               |   643 | 12217 |    12   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("V1"."COUNTRY_CODE"="V6"."COUNTRY_CODE")
   6 - access("AGR"."cont_SERIAL"="FK_cont_SERIAL"(+))
   7 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
   9 - filter("AGR"."TYPE_ID"=2)
  10 - filter("AGT"."VALUE_DATE"(+)<=SYSDATE@!)
  11 - access("AGT"."TYPE_ID"(+)=6)
  12 - access("V1"."COUNTRY_CODE"="V4"."COUNTRY_CODE")
  15 - access("AGR"."cont_SERIAL"="TRN"."FK_cont_SERIAL"(+))
  16 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
  18 - filter("AGR"."TYPE_ID"=2)
  21 - filter("AGT"."TYPE_ID"=2 AND "AGT"."VALUE_DATE"<=SYSDATE@!)
  22 - access("V1"."COUNTRY_CODE"="V2C"."COUNTRY_CODE")
  25 - access("AGR"."cont_SERIAL"="TRN"."FK_cont_SERIAL"(+))
  28 - filter("AGT"."VALUE_DATE"<=SYSDATE@!)
  29 - access("AGT"."TYPE_ID"=11)
  30 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
  32 - filter("AGR"."TYPE_ID"=2)
  33 - access("V1"."COUNTRY_CODE"="V2B"."COUNTRY_CODE")
  36 - access("AGR"."cont_SERIAL"="TRN"."FK_cont_SERIAL"(+))
  39 - filter("AGT"."VALUE_DATE"<=SYSDATE@!)
  40 - access("AGT"."TYPE_ID"=10)
  41 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
  43 - filter("AGR"."TYPE_ID"=2)
  44 - access("V1"."COUNTRY_CODE"="V2A"."COUNTRY_CODE")
  47 - access("AGR"."cont_SERIAL"="TRN"."FK_cont_SERIAL"(+))
  50 - filter("AGT"."VALUE_DATE"<=SYSDATE@!)
  51 - access("AGT"."TYPE_ID"=9)
  52 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
  54 - filter("AGR"."TYPE_ID"=2)
  55 - access("V1"."COUNTRY_CODE"="V0"."COUNTRY_CODE")
  58 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
  60 - filter("TYPE_ID"=2)
  63 - access("AGR"."cont_SERIAL"="TRN"."FK_cont_SERIAL"(+))
  66 - filter("AGT"."VALUE_DATE"<=SYSDATE@!)
  67 - access("AGT"."TYPE_ID"=1)
  68 - access("AGR"."FK_COUNTRYCODE"="CNT"."CODE")
  70 - filter("AGR"."TYPE_ID"=2)



I appreciate your advice,
Many thanks,
Ferro

[Updated on: Mon, 23 November 2015 00:20]

Report message to a moderator

Re: Query execute time versus fetch time [message #644985 is a reply to message #644970] Mon, 23 November 2015 02:51 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
OraFerro wrote on Mon, 23 November 2015 06:19
Dear Cookiemonster,

Thanks for your valuable advice. replacing the function enhanced the performance (0.5 seconds now) as per the below updated query and explain plan.
However:
1- I need to use the function for code readability, maintenance, and unification of the code across reports done by more than one developer.

Well the query can be shortened a bit at least. This:
          AND (
                (0 = 1 AND TRIM(AGR.SUB_NUMBER) = 'P')
                OR
                (0 = 2 AND (TRIM(AGR.SUB_NUMBER) IS NULL OR TRIM(AGR.SUB_NUMBER) <> 'P'))
                OR
                (3 = 3 AND AGR.SUB_NUMBER = AGR.SUB_NUMBER)
              )

Can be replaced with:
AND AGR.SUB_NUMBER = AGR.SUB_NUMBER

All those to_char's should be dropped - if you really need the result as char so the to_char on the outer-most select, not the inner ones.
This can be moved into a WITH clause:
            SELECT AGR.FK_COUNTRYCODE COUNTRY_CODE, CNT.SHORT_NAME_A COUNTRY_NAME_A, CNT.SHORT_NAME_E COUNTRY_NAME_E,  AGR.cont_SERIAL,  AGR.NUMBER0||AGR.SUB_NUMBER cont_NO
              FROM N_cont AGR, N_COUNTRY CNT
              WHERE AGR.FK_COUNTRYCODE = CNT.CODE
                AND (
                      (0 = 1 AND (TRIM(AGR.SUB_NUMBER) IS NULL OR TRIM(AGR.SUB_NUMBER) <> 'P')) 
                      OR
                      (0 = 2 AND TRIM(AGR.SUB_NUMBER) = 'P')
                      OR
                      (3 = 3 AND AGR.SUB_NUMBER = AGR.SUB_NUMBER)
                    )
                AND AGR.TYPE_ID = 2
          ) CNT
          ON CNT.cont_SERIAL = TRN.FK_cont_SERIAL


OraFerro wrote on Mon, 23 November 2015 06:19

2- I still do not understand why would Oracle get confused if I am encapsulating query code in function body.

Because oracle can't optimize function selects as part of the select that calls them.
OraFerro wrote on Mon, 23 November 2015 06:19

3- Is there any way to go around that and control the context switching problem while using the function?

Context switching is an inherent part of using a function in a select.

Report message to a moderator

Re: Query execute time versus fetch time [message #644986 is a reply to message #644985] Mon, 23 November 2015 02:57 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
It may be possible to shrink it further still but we'ed need to know the table structures before making further suggestions.
It seems strange that you're applying max to the country_code but not the country names

Report message to a moderator

Re: Query execute time versus fetch time [message #644987 is a reply to message #644970] Mon, 23 November 2015 03:01 Go to previous messageGo to next message
John Watson
Messages: 8981
Registered: January 2010
Location: Global Village
Senior Member
Quote:
2- I still do not understand why would Oracle get confused if I am encapsulating query code in function body.
My understanding of the issue (which may not be correct) is that the context switch between SQL and PL/SQL makes it impossible to merge the queries. PL/SQL is a statically compiled language, so there is no way that at runtime any SQL it includes can be pulled out and merged into the calling query.

Report message to a moderator

Re: Query execute time versus fetch time [message #644991 is a reply to message #644987] Mon, 23 November 2015 04:33 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Thanks Cookiemonster and John,

I am trying to enhance it from my side but appreciate your support or any suggestion.

Quote:

AND (
(0 = 1 AND TRIM(AGR.SUB_NUMBER) = 'P')
OR
(0 = 2 AND (TRIM(AGR.SUB_NUMBER) IS NULL OR TRIM(AGR.SUB_NUMBER) <> 'P'))
OR
(3 = 3 AND AGR.SUB_NUMBER = AGR.SUB_NUMBER)
)
Can be replaced with:

AND AGR.SUB_NUMBER = AGR.SUB_NUMBER



The left-hand-side of each statement should be I_P_sector parameter but was removed to simplify the case.
Actually, as far as understand, this is the main problem and the main cause of delay. In the second case, I need to get totals after filtering with this parameter, so there is a context switch (3 times) with each row!


Quote:

It seems strange that you're applying max to the country_code but not the country names


I agree it is strange Smile the report requires merging the result of two countries together based on their short name.


Thanks,
Ferro

[Updated on: Mon, 23 November 2015 04:41]

Report message to a moderator

Re: Query execute time versus fetch time [message #644992 is a reply to message #644991] Mon, 23 November 2015 05:02 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Never replace variables with hard-coded parameters when posting code in forums, it just confuses matters.

Report message to a moderator

Re: Query execute time versus fetch time [message #645017 is a reply to message #644807] Mon, 23 November 2015 13:29 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Sorry for that. This was the only i could think of to show an example without the function.
Sorry again
thanks,
Ferro

Report message to a moderator

Re: Query execute time versus fetch time [message #645056 is a reply to message #645017] Wed, 25 November 2015 02:07 Go to previous messageGo to next message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Thanks a lot Cookiemonster and John Watson,

Its a context switch problem indeed.

A simple modification enhanced the performance from 9-10 seconds down to 0.25 to 0.5 seconds by minimizing PL\SQL parameter calls as below:


 BEGIN
    SELECT OBJ_AGR_TRN_TYPE_INFO(
                                 COUNTRY_CODE,    
                                 COUNTRY_NAME_A,  
                                 COUNTRY_NAME_E,  
                                 cont_SERIAL,
                                 cont_NO,    
                                 NVL(AMOUNT,0)    
                                )
      BULK COLLECT INTO AGR_TRN_TYPE_INFO
      FROM  
          (
            SELECT FK_cont_SERIAL,  TO_CHAR(SUM(NVL(AGT.AMOUNT,0)),'999999999999.999') AMOUNT
            FROM TRN_cont_TRANSACTION AGT
            WHERE AGT.TYPE_ID = I_TYPE_ID
              AND AGT.VALUE_DATE <=  D_VALUE_DATE 
            GROUP BY  AGT.FK_cont_SERIAL
          ) TRN
          RIGHT OUTER JOIN
          (
            SELECT AGR.FK_COUNTRYCODE COUNTRY_CODE, CNT.SHORT_NAME_A COUNTRY_NAME_A, 
                   CNT.SHORT_NAME_E COUNTRY_NAME_E,  AGR.cont_SERIAL,  
                   AGR.NUMBER0||AGR.SUB_NUMBER cont_NO, AGR.SECTOR_ID cont_SECTOR
              FROM N_cont AGR, N_COUNTRY CNT
              WHERE AGR.FK_COUNTRYCODE = CNT.CODE
                AND AGR.TYPE_ID = 2
          ) CNT
          ON CNT.cont_SERIAL = TRN.FK_cont_SERIAL
 /* -->>>>>>  THE PARAMETER FILTER ADDED AS A WHERE CONDITION AFTER THe JOIN TO BE
      CALLED ONCE INSTEAD OF being called 3 TIMES WITH EACH ROW in the CNT where clause*/    
          WHERE CNT.cont_SECTOR =DECODE(I_p_SECTOR,1,1,2,2,CNT.cont_SECTOR);
  RETURN  (AGR_TRN_TYPE_INFO);
 END ;



Man thanks,
Ferro

Report message to a moderator

Re: Query execute time versus fetch time [message #645059 is a reply to message #645056] Wed, 25 November 2015 03:03 Go to previous messageGo to next message
John Watson
Messages: 8981
Registered: January 2010
Location: Global Village
Senior Member
I've noticed that you have an OUTER JOIN which serves no purpose, because you also have a filter predicate against the outer joined row source. An outer join forces the join order, which limits the optimizer's options hugely. Of course in any one case it may not make a difference, but this is a basic programmer error that you should correct.

Report message to a moderator

Re: Query execute time versus fetch time [message #645061 is a reply to message #645059] Wed, 25 November 2015 03:20 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not sure how you decided that outer join serves no purpose John - looks valid to me.

Report message to a moderator

Re: Query execute time versus fetch time [message #645062 is a reply to message #645061] Wed, 25 November 2015 03:22 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's TRN that's outer joined, not CNT

Report message to a moderator

Re: Query execute time versus fetch time [message #645064 is a reply to message #645062] Wed, 25 November 2015 03:56 Go to previous messageGo to next message
cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester
Senior Member
I really don't see how that decode reduces the context switches. It also doesn't appear to do the same thing as the code it replaced.
It says that if the parameter is 1 or 2 then CNT.cont_SECTOR must equal the parameter, otherwise the column can equal itself (ie no restriction).
That's not the same as:
		AND (
                      (I_p_SECTOR = 3)
                      OR
                      (I_p_SECTOR = 1 AND AGR.SECTOR_ID <> 2) 
                      OR
                      (I_p_SECTOR = 2 AND AGR.SECTOR_ID = 2)
                    )

Unless sector_id can only be 1 or 2

Report message to a moderator

Re: Query execute time versus fetch time [message #645065 is a reply to message #645062] Wed, 25 November 2015 04:06 Go to previous messageGo to next message
John Watson
Messages: 8981
Registered: January 2010
Location: Global Village
Senior Member
cookiemonster wrote on Wed, 25 November 2015 09:22
It's TRN that's outer joined, not CNT
Silly of me. Sorry about that, Ferro.

Report message to a moderator

Re: Query execute time versus fetch time [message #645208 is a reply to message #645064] Mon, 30 November 2015 00:03 Go to previous message
OraFerro
Messages: 433
Registered: July 2011
Senior Member
Yes Cookiemonster, sector id can only be 1 or 2, three means both. The decode did not reduce the context switching, its moving the where outside the CNT select and made it once instead of 3 times in each CNT row. Please correct me as this is the way I think it worked.

No problem at all John Watson, thanks a lot for your help.

[Updated on: Mon, 30 November 2015 00:06]

Report message to a moderator

Previous Topic: Display Employee details with min & max hired date
Next Topic: Flattening out a table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Aug 10 16:17:42 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.