Home » RDBMS Server » Performance Tuning » Please help me how to improve the performance of this query further. (Oracle 11g)
Please help me how to improve the performance of this query further. [message #600494] Thu, 07 November 2013 06:44 Go to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Hi All,

Please help me how to improve the performance of this query further.

 SELECT   TAM.MANF AS MANF,
               TAM.MAJOR_LOCATION AS OPTION_LOCATION,
               (SELECT   LOCATION_DESCRIPTION
                  FROM   BLUEB_TAM_DESCRIPTION
                 WHERE   NT_NUM = TAM.MAJOR_LOCATION AND ROWNUM = 1)
                  AS LOCATION_DESCRIPTION,
               (SELECT   LOCATION_DESCRIPTION
                  FROM   BLUEB_TAM_DESCRIPTION
                WHERE   NT_NUM = TAM.MANF AND ROWNUM = 1)
                 AS MANF_DESCRIPTION,
              TAM.NT_NUM AS SKU,
              TAM.REBEAT_GROUP,
              TAM_DESCRIPTION.SHORT_DESC AS DESCRIPTION,
              (SELECT   SHORT_DESC
                 FROM   BLUEB_TAM_DESCRIPTION
                WHERE       LOCATION_CODE = 'EN'
                        AND NT_NUM = TAM.NT_NUM
                        AND LOCATION = TAM.LOCATION
                        AND ROWNUM = 1)
                AS EN_DESCRIPTION,
              TAM.DESCRIPTION AS LONG_DESCRIPTION,
              PRP.LIST_PRICE,
              PRP.COST_PRICE,
              TCS.TOT_RANGE AS TOT_RANGE,
              TCS.AVG_RANGE AS AVG_RANGE,
              REBEATS.DISCOUNTABLE AS DISCOUNTABLE,
              TCCC.TCCCIBUTE_KEYWORD AS TCCCIBUTE_KEYWORD,
              TCCC.TCCCIBUTE_TERM AS TCCCIBUTE_TERM,
              TCCC.QTY AS QTY,
              TAM.TAX_CODE,
              TAM.MAT_LOCATION,
              TAM.OPTICAL_NAME,
              TAM.MAT_TYPE,
              TAM.MAT_SOURCE
       FROM                  BLUEB_TAM TAM
                         INNER JOIN
                             BLUEB_TAM_DESCRIPTION TAM_DESCRIPTION
                          ON     TAM.NT_NUM = TAM_DESCRIPTION.NT_NUM
                             AND TAM.LOCATION = TAM_DESCRIPTION.LOCATION
                             AND TAM_DESCRIPTION.LOCATION_CODE = :LOCATION_CODE
                       INNER JOIN
                         PRICE_LIST PRP
                       ON     TAM.NT_NUM = PRP.NT_NUM
                          AND PRP.cates = :cates
                          AND PRP.MONEY_UNIT = :MONEY_UNIT
                    INNER JOIN
                       TAM_REBEAT REBEATS
                    ON TAM.REBEAT_GROUP = REBEATS.REBEAT_GROUP
                       AND REBEATS.REBEAT_NAME = :REBEAT_NAME
                 INNER JOIN
                    TAM_CATEGORY_STATS TCS
                 ON     TAM.LOCATION = TCS.LOCATION
                    AND TAM.MANF = TCS.MANF
                    AND PRP.MONEY_UNIT = TCS.MONEY_UNIT
                    AND TCS.cates = PRP.cates
              LEFT JOIN
                 TAM_CLASSIFICATIONS TCCC
              ON TAM.NT_NUM = TCCC.NT_NUM
      WHERE   NVL (TAM.DISABLED, 'N') = 'N'
              AND SYSDATE BETWEEN NVL (TAM.STRT_DATE, DATE '0001-01-01')
              AND  NVL (TAM.END_DATE, DATE '3999-12-31')
              AND TAM.MANF = :MANF
              AND TAM.NT_NUM IN(:NT_NUM)
   ORDER BY   TAM.NT_NUM;


Plan hash value: 4137741654

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                                |     1 |   450 |    18   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                       |                                |       |       |         |             |
|   2 |   TABLE ACCESS BY INDEX ROWID        | BLUEB_TAM_DESCRIPTION          |     2 |   134 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | BLUEB_TAM_DESCRIPTION_N2       |     7 |       |     3   (0)| 00:00:01 |
|*  4 |  COUNT STOPKEY                       |                                |       |       |         |             |
|   5 |   TABLE ACCESS BY INDEX ROWID        | BLUEB_TAM_DESCRIPTION          |     2 |   134 |     4   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN                  | BLUEB_TAM_DESCRIPTION_N2       |     7 |       |     3   (0)| 00:00:01 |
|*  7 |  COUNT STOPKEY                       |                                |       |       |         |             |
|   8 |   TABLE ACCESS BY INDEX ROWID        | BLUEB_TAM_DESCRIPTION          |     1 |    82 |     4   (0)| 00:00:01 |
|*  9 |    INDEX RANGE SCAN                  | BLUEB_TAM_DESCRIPTION_U1_MARKT |     1 |       |     3   (0)| 00:00:01 |
|  10 |  NESTED LOOPS                        |                                |       |       |         |             |
|  11 |   NESTED LOOPS                       |                                |     1 |   450 |    18   (0)| 00:00:01 |
|  12 |    NESTED LOOPS OUTER                |                                |     1 |   433 |    16   (0)| 00:00:01 |
|  13 |     NESTED LOOPS                     |                                |     1 |   407 |    15   (0)| 00:00:01 |
|  14 |      MERGE JOIN OUTER                |                                |     1 |   375 |    12   (0)| 00:00:01 |
|  15 |       NESTED LOOPS                   |                                |       |       |         |             |
|  16 |        NESTED LOOPS                  |                                |     1 |   343 |    10   (0)| 00:00:01 |
|  17 |         MERGE JOIN CARTESIAN         |                                |     1 |   261 |     7   (0)| 00:00:01 |
|* 18 |          TABLE ACCESS BY INDEX ROWID | BLUEB_TAM                      |     1 |   234 |     4   (0)| 00:00:01 |
|* 19 |           INDEX RANGE SCAN           | BLUEB_TAM_N4                   |     1 |       |     3   (0)| 00:00:01 |
|  20 |          BUFFER SORT                 |                                |     1 |    27 |     3   (0)| 00:00:01 |
|  21 |           TABLE ACCESS BY INDEX ROWID| PRICE_LIST                     |     1 |    27 |     3   (0)| 00:00:01 |
|* 22 |            INDEX RANGE SCAN          | PRICE_LIST                     |     1 |       |     2   (0)| 00:00:01 |
|* 23 |         INDEX RANGE SCAN             | BLUEB_TAM_DESCRIPTION_U1_MARKT |     1 |       |     2   (0)| 00:00:01 |
|  24 |        TABLE ACCESS BY INDEX ROWID   | BLUEB_TAM_DESCRIPTION          |     1 |    82 |     3   (0)| 00:00:01 |
|  25 |       BUFFER SORT                    |                                |     8 |   256 |     9   (0)| 00:00:01 |
|* 26 |        INDEX RANGE SCAN              | TAM_CLASSIFICATIONS_N5         |     8 |   256 |     2   (0)| 00:00:01 |
|  27 |      TABLE ACCESS BY INDEX ROWID     | TAM_CATEGORY_STATS_B           |     1 |    32 |     3   (0)| 00:00:01 |
|* 28 |       INDEX RANGE SCAN               | TAM_CATEGORY_STATS_N1          |     1 |       |     2   (0)| 00:00:01 |
|* 29 |     TABLE ACCESS BY INDEX ROWID      | TAM_MAIN_CLASSIFICATIONS       |     1 |    26 |     1   (0)| 00:00:01 |
|* 30 |      INDEX UNIQUE SCAN               | TAM_MAIN_CLASSIFICATIONS_U1    |     1 |       |     0   (0)| 00:00:01 |
|* 31 |    INDEX RANGE SCAN                  | TAM_REBEAT_N1                  |     1 |       |     1   (0)| 00:00:01 |
|  32 |   TABLE ACCESS BY INDEX ROWID        | TAM_REBEAT                     |     1 |    17 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access("NT_NUM"=:B1)
   4 - filter(ROWNUM=1)
   6 - access("NT_NUM"=:B1)
   7 - filter(ROWNUM=1)
   9 - access("NT_NUM"=:B1 AND "LOCATION"=:B2 AND "LOCATION_CODE"='EN')
  18 - filter(NVL("TAM"."DISABLED",'N')='N')
  19 - access("TAM"."MANF"=:MANF AND "TAM"."NT_NUM"=:NT_NUM)
       filter(NVL("STRT_DATE",TO_DATE(' 0001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=SYSDATE@!
              AND NVL("END_DATE",TO_DATE(' 3999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))>=SYSDATE@!)
  22 - access("PRP"."cates"=:cates AND "PRP"."MONEY_UNIT"=:MONEY_UNIT AND
              "PRP"."NT_NUM"=:NT_NUM)
  23 - access("TAM_DESCRIPTION"."NT_NUM"=:NT_NUM AND "TAM"."LOCATION"="TAM_DESCRIPTION"."LOCATION" AND
              "TAM_DESCRIPTION"."LOCATION_CODE"=:LOCATION_CODE)
  26 - access("TCCC"."NT_NUM"(+)=:NT_NUM)
  28 - access("CSB"."MANF"=:MANF AND "TAM"."LOCATION"="CSB"."LOCATION" AND "CSB"."MONEY_UNIT"=:MONEY_UNIT AND
              "CSB"."cates"=:cates)
  29 - filter("TMC"."VALUE"(+)='M')
  30 - access("TMC"."LOCATION"(+)=SUBSTR("CSB"."LOCATION",1,INSTR("CSB"."LOCATION",'-')-1) AND
              "TMC"."CLASSIFICATION"(+)='SECOND_CLASS')
  31 - access("REBEATS"."REBEAT_NAME"=:REBEAT_NAME AND "TAM"."REBEAT_GROUP"="REBEATS"."REBEAT_GROUP")


TAM_CATEGORY_STATS is a view.

Script for this view is

SELECT   CSB.CATES,
            CSB.MONEY_UNIT,
            CSB.MANF,
            CSB.LOCATION,
            DECODE (TMC.LOCATION, NULL, CSB.TOT_RANGE, 0) TOT_RANGE,
            DECODE (TMC.LOCATION, NULL, CSB.AVG_RANGE, 0) AVG_RANGE
     FROM   TAM_CATEGORY_STATS_B CSB, TAM_MAIN_CLASSIFICATIONS TMC
    WHERE   SUBSTR (CSB.LOCATION, 1, INSTR (CSB.LOCATION, '-') - 1) = TMC.LOCATION(+)
            AND TMC.CLASSIFICATION(+) = 'SECOND_CLASS'
            AND TMC.VALUE(+) = 'M';

Indexes created

CREATE INDEX BLUEB_TAM_N4 ON BLUEB_TAM(MANF, NT_NUM, STRT_DATE, END_DATE);

CREATE INDEX BLUEB_TAM_DESCRIPTION_N2 ON BLUEB_TAM_DESCRIPTION(NT_NUM);

CREATE INDEX BLUEB_TAM_DESCRIPTION_U1_MARKT ON BLUEB_TAM_DESCRIPTION(NT_NUM, LOCATION, LOCATION_CODE);

CREATE INDEX PRICE_LIST ON PRICE_LIST(cates, MONEY_UNIT, NT_NUM);

CREATE INDEX TAM_REBEAT_N1 ON TAM_REBEAT(REBEAT_NAME, REBEAT_GROUP);

CREATE INDEX TAM_CLASSIFICATIONS_N5 ON TAM_CLASSIFICATIONS(NT_NUM, CLASSIFICATION_TERM, CLASSIFICATION_KEYWORD, QTY);

CREATE INDEX TAM_CATEGORY_STATS_N1 ON TAM_CATEGORY_STATS_B(MANF, LOCATION, MONEY_UNIT, cates);

CREATE UNIQUE INDEX TAM_MAIN_CLASSIFICATIONS_U1 ON TAM_MAIN_CLASSIFICATIONS(LOCATION, CLASSIFICATION);



Thanks.
Re: Please help me how to improve the performance of this query further. [message #600495 is a reply to message #600494] Thu, 07 November 2013 06:51 Go to previous messageGo to next message
cookiemonster
Messages: 10842
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well if the explain plan is to be believed that query should be really fast.
Did you get the explain plan from a production DB with a lot of data or a dev one with very little?
If it was the later then you need to get the explain from prod.

How long does the query take?
Re: Please help me how to improve the performance of this query further. [message #600496 is a reply to message #600495] Thu, 07 November 2013 06:57 Go to previous messageGo to next message
cookiemonster
Messages: 10842
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also why have you got sub-queries with rownum restrictions?
That implies that either there is something wrong with your data model or the joins are wrong.
Re: Please help me how to improve the performance of this query further. [message #600498 is a reply to message #600496] Thu, 07 November 2013 06:59 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Can you please look at my join conditions.

Thanks.
Re: Please help me how to improve the performance of this query further. [message #600501 is a reply to message #600498] Thu, 07 November 2013 07:38 Go to previous messageGo to next message
cookiemonster
Messages: 10842
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well we can but they're not going to tell us much - we don't have your tables or data and we don't know the relationships between the tables.
Without that information we have no way of knowing if the joins are correct or not.

If you want help you need to start answering some questions.
Re: Please help me how to improve the performance of this query further. [message #600507 is a reply to message #600501] Thu, 07 November 2013 07:58 Go to previous messageGo to next message
gazzag
Messages: 275
Registered: November 2010
Location: Bristol, UK
Senior Member
Please provide a Test Case
Re: Please help me how to improve the performance of this query further. [message #600528 is a reply to message #600501] Thu, 07 November 2013 12:25 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Hi,

I have created this new index but not used by this query why?

CREATE INDEX BLUEB_TAM_IDX_NEW ON BLUEB_TAM( NVL ( NVL (TAM.DISABLED, 'N'),TAM.STRT_DATE, DATE '0001-01-01'),
NVL (TAM.END_DATE, DATE '3999-12-31'),MANF, NT_NUM);

Can you please help me.

Thanks.
Re: Please help me how to improve the performance of this query further. [message #600529 is a reply to message #600528] Thu, 07 November 2013 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
for all tables & indexes involved are statistics current?
why do you need to tune a query that completes within 1 second?
Re: Please help me how to improve the performance of this query further. [message #600530 is a reply to message #600529] Thu, 07 November 2013 12:36 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Stats are up to date.

If I create the index without STRT_DATE and END_DATE columns as below index is using by the query.

CREATE INDEX BLUEB_TAM_IDX_NEW ON BLUEB_TAM( NVL ( NVL (TAM.DISABLED, 'N'),MANF, NT_NUM);

Please help me how to create the index along all the columns which are using in WHERE clause.

Thanks.

[Updated on: Thu, 07 November 2013 12:38]

Report message to a moderator

Re: Please help me how to improve the performance of this query further. [message #600531 is a reply to message #600528] Thu, 07 November 2013 12:37 Go to previous messageGo to next message
John Watson
Messages: 4369
Registered: January 2010
Location: Global Village
Senior Member
You need to provide some very basic information. How many rows does this query return? How long does it take?
Re: Please help me how to improve the performance of this query further. [message #600532 is a reply to message #600531] Thu, 07 November 2013 12:40 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
It's taking 30 seconds.
It's returning 15000 records.

Re: Please help me how to improve the performance of this query further. [message #600533 is a reply to message #600532] Thu, 07 November 2013 12:41 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
ramya_162 wrote on Thu, 07 November 2013 10:40
It's taking 30 seconds.
It's returning 15000 records.


why does posted EXPLAIN PLAN report different than what you claim above?
Re: Please help me how to improve the performance of this query further. [message #600534 is a reply to message #600532] Thu, 07 November 2013 12:42 Go to previous messageGo to next message
John Watson
Messages: 4369
Registered: January 2010
Location: Global Village
Senior Member
Can I get this in before anyone else? Your statistics are rubbish. Oracle expects one row, but delivers 15000. You need to analyze your database, with estimate_percent=>100
--
Update: Damn it! I wasn't quick enough.

[Updated on: Thu, 07 November 2013 12:43]

Report message to a moderator

Re: Please help me how to improve the performance of this query further. [message #600535 is a reply to message #600534] Thu, 07 November 2013 12:47 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Hi,

I have provided the plan with variables like :LOCATION_CODE
not with the values so that it's different.

thanks,
Re: Please help me how to improve the performance of this query further. [message #600537 is a reply to message #600535] Thu, 07 November 2013 12:50 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
ramya_162 wrote on Thu, 07 November 2013 10:47
Hi,

I have provided the plan with variables like :LOCATION_CODE
not with the values so that it's different.

thanks,


are you saying is that you posted one version of some SQL statement & expect us to tune some other SQL that we know nothing about?

PLEASE stop wasting our time with your nonsense!
Re: Please help me how to improve the performance of this query further. [message #600538 is a reply to message #600535] Thu, 07 November 2013 12:51 Go to previous messageGo to next message
John Watson
Messages: 4369
Registered: January 2010
Location: Global Village
Senior Member
ramya_162 wrote on Thu, 07 November 2013 18:47
Hi,

I have provided the plan with variables like :LOCATION_CODE
not with the values so that it's different.

thanks,
Well, if you don't want to tell the truth, neither do I.
Re: Please help me how to improve the performance of this query further. [message #600539 is a reply to message #600538] Thu, 07 November 2013 12:55 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Hi,

The values for those variables are passing from front end.
It is not static so that I can't check for specific values.
If I check for some values,for some other values the plan may different.
Due to that I have taken the plan only with variables.

Thanks.

[Updated on: Thu, 07 November 2013 12:57]

Report message to a moderator

Re: Please help me how to improve the performance of this query further. [message #600541 is a reply to message #600539] Thu, 07 November 2013 13:03 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
SQL_TRACE can show both bind variable values & EXPLAIN PLAN
Re: Please help me how to improve the performance of this query further. [message #600542 is a reply to message #600539] Thu, 07 November 2013 13:10 Go to previous messageGo to next message
cookiemonster
Messages: 10842
Registered: September 2008
Location: Rainy Manchester
Senior Member
One of the following is true:
a) your stats are horrible wrong
b) you've got really skewed data
c) that explain plan wasn't taken on a db where the query is actually slow.

which is it?
Re: Please help me how to improve the performance of this query further. [message #600657 is a reply to message #600542] Mon, 11 November 2013 00:35 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Hi All,

Is there any possibility to rewrite this query.

Please help me.

Thanks.
Re: Please help me how to improve the performance of this query further. [message #600659 is a reply to message #600657] Mon, 11 November 2013 03:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1777
Registered: May 2013
Location: World Wide on the Web
Senior Member
How about first you let us know the trace event results. Since you have bind variables, enable the 10046 trace with level 12.

Follow these steps :
1.
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

2. Give a tracefile identifier to find the file easily :
alter session set tracefile_identifier = 'test_plan1';

3. Execute your SQL.
4. Turn the trace event off :
alter session set events '10046 trace name context off'; 

5. Find the trace file, follow the VALUE:
SHOW PARAMETER user_dump_dest;

6. Get the tkprof output.

And post the output.
Re: Please help me how to improve the performance of this query further. [message #600669 is a reply to message #600659] Mon, 11 November 2013 03:40 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Hi,

I am sending the plan with values.
SELECT   TAM.MANF AS MANF,
               TAM.MAJOR_LOCATION AS OPTION_LOCATION,
               (SELECT   LOCATION_DESCRIPTION
                  FROM   BLUEB_TAM_DESCRIPTION
                 WHERE   NT_NUM = TAM.MAJOR_LOCATION AND ROWNUM = 1)
                  AS LOCATION_DESCRIPTION,
               (SELECT   LOCATION_DESCRIPTION
                  FROM   BLUEB_TAM_DESCRIPTION
                WHERE   NT_NUM = TAM.MANF AND ROWNUM = 1)
                 AS MANF_DESCRIPTION,
              TAM.NT_NUM AS SKU,
              TAM.REBEAT_GROUP,
              TAM_DESCRIPTION.SHORT_DESC AS DESCRIPTION,
              (SELECT   SHORT_DESC
                 FROM   BLUEB_TAM_DESCRIPTION
                WHERE       LOCATION_CODE = 'AB'
                        AND NT_NUM = TAM.NT_NUM
                        AND LOCATION = TAM.LOCATION
                        AND ROWNUM = 1)
                AS EN_DESCRIPTION,
              TAM.DESCRIPTION AS LONG_DESCRIPTION,
              PRP.LIST_PRICE,
              PRP.COST_PRICE,
              TCS.TOT_RANGE AS TOT_RANGE,
              TCS.AVG_RANGE AS AVG_RANGE,
              REBEATS.DISCOUNTABLE AS DISCOUNTABLE,
              TCCC.TCCCIBUTE_KEYWORD AS TCCCIBUTE_KEYWORD,
              TCCC.TCCCIBUTE_TERM AS TCCCIBUTE_TERM,
              TCCC.QTY AS QTY,
              TAM.TAX_CODE,
              TAM.MAT_LOCATION,
              TAM.OPTICAL_NAME,
              TAM.MAT_TYPE,
              TAM.MAT_SOURCE
       FROM                  BLUEB_TAM TAM
                         INNER JOIN
                             BLUEB_TAM_DESCRIPTION TAM_DESCRIPTION
                          ON     TAM.NT_NUM = TAM_DESCRIPTION.NT_NUM
                             AND TAM.LOCATION = TAM_DESCRIPTION.LOCATION
                             AND TAM_DESCRIPTION.LOCATION_CODE = 'AB'
                       INNER JOIN
                         PRICE_LIST PRP
                       ON     TAM.NT_NUM = PRP.NT_NUM
                          AND PRP.cates = 'MTR'
                          AND PRP.MONEY_UNIT = 'INR'
                    INNER JOIN
                       TAM_REBEAT REBEATS
                    ON TAM.REBEAT_GROUP = REBEATS.REBEAT_GROUP
                       AND REBEATS.REBEAT_NAME = '567'
                 INNER JOIN
                    TAM_CATEGORY_STATS TCS
                 ON     TAM.LOCATION = TCS.LOCATION
                    AND TAM.MANF = TCS.MANF
                    AND PRP.MONEY_UNIT = TCS.MONEY_UNIT
                    AND TCS.cates = PRP.cates
              LEFT JOIN
                 TAM_CLASSIFICATIONS TCCC
              ON TAM.NT_NUM = TCCC.NT_NUM
      WHERE   NVL (TAM.DISABLED, 'N') = 'N'
              AND SYSDATE BETWEEN NVL (TAM.STRT_DATE, DATE '0001-01-01')
              AND  NVL (TAM.END_DATE, DATE '3999-12-31')
              AND TAM.MANF = 'U125'
              AND TAM.NT_NUM IN((SELECT  /*+ CARDINALITY(t, 1) */
                           COLUMN_VALUE
                       FROM   table (Split('9000,8000,7000,6000', ',')) t))
   ORDER BY   TAM.NT_NUM;

Plan hash value: 3517061744

-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                |      1 |        |      0 |00:00:00.01 |       3 |       |       |          |
|*  1 |  COUNT STOPKEY                      |                                |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID       | BLUEB_TAM_DESCRIPTION          |      0 |      2 |      0 |00:00:00.01 |       0 |       |       |          |
|*  3 |    INDEX RANGE SCAN                 | BLUEB_TAM_DESCRIPTION_N2       |      0 |      2 |      0 |00:00:00.01 |       0 |       |       |          |
|*  4 |  COUNT STOPKEY                      |                                |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   5 |   TABLE ACCESS BY INDEX ROWID       | BLUEB_TAM_DESCRIPTION          |      0 |      2 |      0 |00:00:00.01 |       0 |       |       |          |
|*  6 |    INDEX RANGE SCAN                 | BLUEB_TAM_DESCRIPTION_N2       |      0 |      2 |      0 |00:00:00.01 |       0 |       |       |          |
|*  7 |  COUNT STOPKEY                      |                                |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   8 |   TABLE ACCESS BY INDEX ROWID       | BLUEB_TAM_DESCRIPTION          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  9 |    INDEX RANGE SCAN                 | BLUEB_TAM_DESCRIPTION_U1_MARKT |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  10 |  SORT ORDER BY                      |                                |      1 |      1 |      0 |00:00:00.01 |       3 |  1024 |  1024 |          |
|* 11 |   HASH JOIN SEMI                    |                                |      1 |      1 |      0 |00:00:00.01 |       3 |   694K|   694K|  182K (0)|
|  12 |    NESTED LOOPS OUTER               |                                |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|  13 |     NESTED LOOPS                    |                                |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|  14 |      NESTED LOOPS                   |                                |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|  15 |       NESTED LOOPS OUTER            |                                |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|  16 |        NESTED LOOPS                 |                                |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|  17 |         NESTED LOOPS                |                                |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|  18 |          TABLE ACCESS BY INDEX ROWID| PRICE_LIST                     |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|* 19 |           INDEX RANGE SCAN          | PRICE_LIST                     |      1 |      1 |      0 |00:00:00.01 |       3 |       |       |          |
|* 20 |          TABLE ACCESS BY INDEX ROWID| BLUEB_TAM                      |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 21 |           INDEX RANGE SCAN          | BLUEB_TAM_N4                   |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  22 |         TABLE ACCESS BY INDEX ROWID | TAM_CATEGORY_STATS_B           |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 23 |          INDEX RANGE SCAN           | TAM_CATEGORY_STATS_N1          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 24 |        TABLE ACCESS BY INDEX ROWID  | TAM_MAIN_CLASSIFICATIONS       |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 25 |         INDEX UNIQUE SCAN           | TAM_MAIN_CLASSIFICATIONS_U1    |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  26 |       TABLE ACCESS BY INDEX ROWID   | TAM_REBEAT                     |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 27 |        INDEX RANGE SCAN             | TAM_REBEAT_N1                  |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  28 |      TABLE ACCESS BY INDEX ROWID    | BLUEB_TAM_DESCRIPTION          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 29 |       INDEX RANGE SCAN              | BLUEB_TAM_DESCRIPTION_U1_MARKT |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 30 |     INDEX RANGE SCAN                | TAM_CLASSIFICATIONS_N5         |      0 |      3 |      0 |00:00:00.01 |       0 |       |       |          |
|  31 |    COLLECTION ITERATOR PICKLER FETCH| SPLIT                          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access("NT_NUM"=:B1)
   4 - filter(ROWNUM=1)
   6 - access("NT_NUM"=:B1)
   7 - filter(ROWNUM=1)
   9 - access("NT_NUM"=:B1 AND "LOCATION"=:B2 AND "LOCATION_CODE"='AB')
  11 - access("TAM"."NT_NUM"=VALUE(KOKBF$))
  19 - access("PRP"."cates"='MTR' AND "PRP"."MONEY_UNIT"='INR')
  20 - filter(NVL("TAM"."DISABLED",'N')='N')
  21 - access("TAM"."MANF"='U125' AND "TAM"."NT_NUM"="PRP"."NT_NUM")
       filter((NVL("STRT_DATE",TO_DATE(' 0001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=SYSDATE@! AND NVL("END_DATE",TO_DATE('
              3999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))>=SYSDATE@!))
  23 - access("CSB"."MANF"='U125' AND "TAM"."LOCATION"="CSB"."LOCATION" AND"CSB"."MONEY_UNIT"='INR' AND  "CSB"."cates"='MTR')
  24 - filter("TMC"."VALUE"='Y')
  25 - access("TMC"."CLASS"=SUBSTR("CSB"."LOCATION",1,INSTR("CSB"."LOCATION",'-')-1) AND "TMC"."CLASSIFICATION"(+)='SECOND_CLASS')
  27 - access("REBEATS"."REBEAT_NAME"='567' AND "TAM"."REBEAT_GROUP"="REBEATS"."REBEAT_GROUP")
  29 - access("TAM"."NT_NUM"="TAM_DESCRIPTION"."NT_NUM" AND "TAM"."LOCATION"="TAM_DESCRIPTION"."LOCATION" AND "TAM_DESCRIPTION"."LOCATION_CODE"='AB')
  30 - access("TAM"."NT_NUM"="TCCC"."NT_NUM")

1 - filter(ROWNUM=1)
   3 - access("NT_NUM"=:B1)
   4 - filter(ROWNUM=1)
   6 - access("NT_NUM"=:B1)
   7 - filter(ROWNUM=1)
   9 - access("NT_NUM"=:B1 AND "LOCATION"=:B2 AND "LOCATION_CODE"='EN')
  18 - filter(NVL("TAM"."DISABLED",'N')='N')
  19 - access("TAM"."MANF"=:MANF AND "TAM"."NT_NUM"=:NT_NUM)
       filter(NVL("STRT_DATE",TO_DATE(' 0001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<=SYSDATE@!
              AND NVL("END_DATE",TO_DATE(' 3999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))>=SYSDATE@!)
  22 - access("PRP"."cates"=:cates AND "PRP"."MONEY_UNIT"=:MONEY_UNIT AND
              "PRP"."NT_NUM"=:NT_NUM)
  23 - access("TAM_DESCRIPTION"."NT_NUM"=:NT_NUM AND "TAM"."LOCATION"="TAM_DESCRIPTION"."LOCATION" AND
              "TAM_DESCRIPTION"."LOCATION_CODE"=:LOCATION_CODE)
  26 - access("TCCC"."NT_NUM"(+)=:NT_NUM)
  28 - access("CSB"."MANF"=:MANF AND "TAM"."LOCATION"="CSB"."LOCATION" AND "CSB"."MONEY_UNIT"=:MONEY_UNIT AND
              "CSB"."cates"=:cates)
  29 - filter("TMC"."VALUE"(+)='M')
  30 - access("TMC"."LOCATION"(+)=SUBSTR("CSB"."LOCATION",1,INSTR("CSB"."LOCATION",'-')-1) AND
              "TMC"."CLASSIFICATION"(+)='SECOND_CLASS')
  31 - access("REBEATS"."REBEAT_NAME"=:REBEAT_NAME AND "TAM"."REBEAT_GROUP"="REBEATS"."REBEAT_GROUP")


Please help me.

Thanks.
Re: Please help me how to improve the performance of this query further. [message #600678 is a reply to message #600669] Mon, 11 November 2013 03:52 Go to previous messageGo to next message
cookiemonster
Messages: 10842
Registered: September 2008
Location: Rainy Manchester
Senior Member
That plan has an actual time of 0.1 seconds.

You didn't get that from the DB where it's running slow did you?
Re: Please help me how to improve the performance of this query further. [message #600685 is a reply to message #600678] Mon, 11 November 2013 04:27 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
No Same I have taken.
Re: Please help me how to improve the performance of this query further. [message #600686 is a reply to message #600685] Mon, 11 November 2013 04:30 Go to previous messageGo to next message
gazzag
Messages: 275
Registered: November 2010
Location: Bristol, UK
Senior Member
You have to get the plan, like Lalit explained, on the database that is experiencing the problem. A 0.1 second response is not slow!
Re: Please help me how to improve the performance of this query further. [message #600687 is a reply to message #600685] Mon, 11 November 2013 04:31 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
We don't have any data for these values becoz of that it's coming in .01 minutes.
Re: Please help me how to improve the performance of this query further. [message #600688 is a reply to message #600687] Mon, 11 November 2013 04:34 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Can you please look at the Operation and name column of the plan
whether the joining is proper or not while accessing

Thanks.
Re: Please help me how to improve the performance of this query further. [message #600689 is a reply to message #600688] Mon, 11 November 2013 04:39 Go to previous messageGo to next message
cookiemonster
Messages: 10842
Registered: September 2008
Location: Rainy Manchester
Senior Member
Can you, please, get an explain plan or trace that actually shows evidence of a slow query.
Otherwise we can not help you.
Re: Please help me how to improve the performance of this query further. [message #600705 is a reply to message #600687] Mon, 11 November 2013 06:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1777
Registered: May 2013
Location: World Wide on the Web
Senior Member
ramya_162 wrote on Mon, 11 November 2013 16:01
We don't have any data for these values becoz of that it's coming in .01 minutes.


Are you struggling to get the values for the bind variables? And then trying to generate the execution plan by using those bind values in the query?
If yes, then no need to do that. just follow the steps I have mentioned to generate the trace and with level 12 it will include the bind variables as well.
Re: Please help me how to improve the performance of this query further. [message #600773 is a reply to message #600705] Tue, 12 November 2013 06:41 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member
Hi,

I am sending the tkprof file for bind variables.

Please help me.

Thanks.
  • Attachment: tkprof.txt
    (Size: 29.04KB, Downloaded 27 times)
Re: Please help me how to improve the performance of this query further. [message #600774 is a reply to message #600773] Tue, 12 November 2013 06:45 Go to previous messageGo to next message
cookiemonster
Messages: 10842
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not everyone can/will download attachments. Please post the tkprof output directly in the post in code tags.
Re: Please help me how to improve the performance of this query further. [message #600785 is a reply to message #600774] Tue, 12 November 2013 08:52 Go to previous messageGo to next message
ramya_162
Messages: 96
Registered: August 2013
Location: Banglore
Member

Trace file: wedb1_ora_8123_test_plan1.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SELECT   TAM.MANF AS MANF,
               TAM.MAJOR_LOCATION AS OPTION_LOCATION,
               (SELECT   LOCATION_DESCRIPTION
                  FROM   BLUEB_TAM_DESCRIPTION
                 WHERE   NT_NUM = TAM.MAJOR_LOCATION AND ROWNUM = 1)
                  AS LOCATION_DESCRIPTION,
               (SELECT   LOCATION_DESCRIPTION
                  FROM   BLUEB_TAM_DESCRIPTION
                WHERE   NT_NUM = TAM.MANF AND ROWNUM = 1)
                 AS MANF_DESCRIPTION,
              TAM.NT_NUM AS SKU,
              TAM.REBEAT_GROUP,
              TAM_DESCRIPTION.SHORT_DESC AS DESCRIPTION,
              (SELECT   SHORT_DESC
                 FROM   BLUEB_TAM_DESCRIPTION
                WHERE       LOCATION_CODE = 'AB'
                        AND NT_NUM = TAM.NT_NUM
                        AND LOCATION = TAM.LOCATION
                        AND ROWNUM = 1)
                AS EN_DESCRIPTION,
              TAM.DESCRIPTION AS LONG_DESCRIPTION,
              PRP.LIST_PRICE,
              PRP.COST_PRICE,
              TCS.TOT_RANGE AS TOT_RANGE,
              TCS.AVG_RANGE AS AVG_RANGE,
              REBEATS.DISCOUNTABLE AS DISCOUNTABLE,
              TCCC.TCCCIBUTE_KEYWORD AS TCCCIBUTE_KEYWORD,
              TCCC.TCCCIBUTE_TERM AS TCCCIBUTE_TERM,
              TCCC.QTY AS QTY,
              TAM.TAX_CODE,
              TAM.MAT_LOCATION,
              TAM.OPTICAL_NAME,
              TAM.MAT_TYPE,
              TAM.MAT_SOURCE
       FROM                  BLUEB_TAM TAM
                         INNER JOIN
                             BLUEB_TAM_DESCRIPTION TAM_DESCRIPTION
                          ON     TAM.NT_NUM = TAM_DESCRIPTION.NT_NUM
                             AND TAM.LOCATION = TAM_DESCRIPTION.LOCATION
                             AND TAM_DESCRIPTION.LOCATION_CODE = :LOCATION_CODE
                       INNER JOIN
                         PRICE_LIST PRP
                       ON     TAM.NT_NUM = PRP.NT_NUM
                          AND PRP.cates = :cates
                          AND PRP.MONEY_UNIT = :MONEY_UNIT
                    INNER JOIN
                       TAM_REBEAT REBEATS
                    ON TAM.REBEAT_GROUP = REBEATS.REBEAT_GROUP
                       AND REBEATS.REBEAT_NAME = :REBEAT_NAME
                 INNER JOIN
                    TAM_CATEGORY_STATS TCS
                 ON     TAM.LOCATION = TCS.LOCATION
                    AND TAM.MANF = TCS.MANF
                    AND PRP.MONEY_UNIT = TCS.MONEY_UNIT
                    AND TCS.cates = PRP.cates
              LEFT JOIN
                 TAM_CLASSIFICATIONS TCCC
              ON TAM.NT_NUM = TCCC.NT_NUM
      WHERE   NVL (TAM.DISABLED, 'N') = 'N'
              AND SYSDATE BETWEEN NVL (TAM.STRT_DATE, DATE '0001-01-01')
              AND  NVL (TAM.END_DATE, DATE '3999-12-31')
              AND TAM.MANF = :MANF
              AND TAM.NT_NUM IN((SELECT  /*+ CARDINALITY(t, 1) */
                           COLUMN_VALUE
                       FROM   table (Split(:NTNUM_LIST,:NTNUM_LIST_delim)) t)
   ORDER BY   TAM.NT_NUM

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.32       0.32          0         48          0           0
Fetch        1      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.32       0.32          0         48          0           0

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

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
         0          0          0   TABLE ACCESS BY INDEX ROWID BLUEB_TAM_DESCRIPTION (cr=0 pr=0 pw=0 time=0 us cost=5 size=134 card=2)
         0          0          0    INDEX RANGE SCAN BLUEB_TAM_DESCRIPTION_N2 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=2)(object id 112788)
         0          0          0  COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
         0          0          0   TABLE ACCESS BY INDEX ROWID BLUEB_TAM_DESCRIPTION (cr=0 pr=0 pw=0 time=0 us cost=5 size=134 card=2)
         0          0          0    INDEX RANGE SCAN BLUEB_TAM_DESCRIPTION_N2 (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=2)(object id 112788)
         0          0          0  COUNT STOPKEY (cr=0 pr=0 pw=0 time=0 us)
         0          0          0   TABLE ACCESS BY INDEX ROWID BLUEB_TAM_DESCRIPTION (cr=0 pr=0 pw=0 time=0 us cost=4 size=81 card=1)
         0          0          0    INDEX RANGE SCAN BLUEB_TAM_DESCRIPTION_U1_MARKT (cr=0 pr=0 pw=0 time=0 us cost=3 size=0 card=1)(object id 112789)
         0          0          0  SORT ORDER BY (cr=0 pr=0 pw=0 time=584 us cost=48 size=2432 card=1)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=562 us)
         0          0          0    NESTED LOOPS  (cr=0 pr=0 pw=0 time=559 us cost=47 size=2432 card=1)
         0          0          0     NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=555 us cost=44 size=2351 card=1)
         0          0          0      NESTED LOOPS  (cr=0 pr=0 pw=0 time=550 us cost=42 size=2320 card=1)
         0          0          0       NESTED LOOPS  (cr=0 pr=0 pw=0 time=546 us cost=39 size=2292 card=1)
         0          0          0        NESTED LOOPS OUTER (cr=0 pr=0 pw=0 time=545 us cost=37 size=2276 card=1)
         0          0          0         NESTED LOOPS  (cr=0 pr=0 pw=0 time=541 us cost=36 size=2250 card=1)
         0          0          0          NESTED LOOPS  (cr=0 pr=0 pw=0 time=538 us cost=33 size=2218 card=1)
         0          0          0           VIEW  VW_NSO_1 (cr=0 pr=0 pw=0 time=533 us cost=29 size=2002 card=1)
         0          0          0            HASH UNIQUE (cr=0 pr=0 pw=0 time=526 us)
         0          0          0             COLLECTION ITERATOR PICKLER FETCH SPLIT (cr=0 pr=0 pw=0 time=401 us cost=29 size=2 card=1)
         0          0          0           TABLE ACCESS BY INDEX ROWID BLUEB_TAM (cr=0 pr=0 pw=0 time=0 us cost=3 size=216 card=1)
         0          0          0            INDEX RANGE SCAN BLUEB_TAM_N4 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 112794)
         0          0          0          TABLE ACCESS BY INDEX ROWID TAM_CATEGORY_STATS_B (cr=0 pr=0 pw=0 time=0 us cost=3 size=32 card=1)
         0          0          0           INDEX RANGE SCAN TAM_CATEGORY_STATS_N1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 112800)
         0          0          0         TABLE ACCESS BY INDEX ROWID TAM_MAIN_CLASSIFICATIONS (cr=0 pr=0 pw=0 time=0 us cost=1 size=26 card=1)
         0          0          0          INDEX UNIQUE SCAN GEDIS_TAM_MAIN_CLASSIFICATIONS_U1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 112799)
         0          0          0        TABLE ACCESS BY INDEX ROWID TAM_REBEAT (cr=0 pr=0 pw=0 time=0 us cost=2 size=16 card=1)
         0          0          0         INDEX RANGE SCAN TAM_REBEAT_N1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 112817)
         0          0          0       TABLE ACCESS BY INDEX ROWID PRICE_LIST (cr=0 pr=0 pw=0 time=0 us cost=3 size=28 card=1)
         0          0          0        INDEX RANGE SCAN PRICE_LIST_U1_MARKT (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 112841)
         0          0          0      INDEX RANGE SCAN TAM_CLASSIFICATIONS_N5 (cr=0 pr=0 pw=0 time=0 us cost=2 size=93 card=3)(object id 112785)
         0          0          0     INDEX RANGE SCAN BLUEB_TAM_DESCRIPTION_U1_MARKT (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)(object id 112789)
         0          0          0    TABLE ACCESS BY INDEX ROWID BLUEB_TAM_DESCRIPTION (cr=0 pr=0 pw=0 time=0 us cost=3 size=81 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  library cache lock                              2        0.00          0.00
  library cache pin                               3        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       13.52         13.52
********************************************************************************
Re: Please help me how to improve the performance of this query further. [message #600786 is a reply to message #600785] Tue, 12 November 2013 08:53 Go to previous messageGo to next message
BlackSwan
Messages: 22481
Registered: January 2009
Senior Member
sub-second response to return zero rows.
I don't see any problem here.
Re: Please help me how to improve the performance of this query further. [message #600796 is a reply to message #600786] Tue, 12 November 2013 11:15 Go to previous message
cookiemonster
Messages: 10842
Registered: September 2008
Location: Rainy Manchester
Senior Member
You need to run the trace when it's actually returning data.
Previous Topic: forms 6i too slow when database have approximately 1 million records
Next Topic: Oracle DB Performance Tests
Goto Forum:
  


Current Time: Wed Jul 23 02:59:53 CDT 2014

Total time taken to generate the page: 0.23709 seconds