Solved

Query taking longer time to execute in oracle 11g

Posted on 2011-09-19
14
871 Views
Last Modified: 2013-11-11
Hi Experts..
Below is my concern..

The first query takes about 1min 40 Sec. Where as when I remove certain where condition its good.
Here is the query for your eyes:-
 SELECT DISTINCT "Account"."AccountID" "AccountID",
                  "Position"."HOLDING_DTE" "HoldingsDate",
                  "Position"."QTY_CQTY" "Quantity",
                  "Security"."CUSIPCode" "CUSIPCode",
                  "Security"."PreferredSecurityName" "PreferredSecurityName",
                  "Security"."TickerID" "TickerID"
    FROM (SELECT * FROM POSN_HIST) "Position"
         LEFT OUTER JOIN (SELECT "Account"."ACCT_ID" "AccountID",
                                 "Account"."ACCT_SOK" "ACCT_SOK"
                            FROM (SELECT * FROM ACCT_HIST) "Account") "Account"
            ON "Position"."ACCT_SOK" = "Account"."ACCT_SOK"
         LEFT OUTER JOIN (SELECT "Security"."CUSIP_ID" "CUSIPCode",
                                 "Security"."PREF_ISS_NME"
                                    "PreferredSecurityName",
                                 "Security"."TICKER_ID" "TickerID",
                                 "Security"."TICKER_ID" "TickerID2",
                                 "Security"."SEC_SOK" "SEC_SOK"
                            FROM (SELECT * FROM ISSU_HIST) "Security") "Security"
            ON "Position"."SEC_SOK" = "Security"."SEC_SOK"
   WHERE "Position"."HOLDING_DTE" >=
            TO_TIMESTAMP ('2008-10-14-00-00-00.000',
                          'YYYY-MM-DD-HH24-MI-SS.FF')
         AND "Position"."HOLDING_DTE" <
                (TO_TIMESTAMP ('2008-10-14-00-00-00.000',
                               'YYYY-MM-DD-HH24-MI-SS.FF')
                 + NUMTODSINTERVAL (1, 'DAY'))
         AND "Security"."TickerID2" = 'TKTM'
ORDER BY "AccountID",
         "HoldingsDate",
         "Quantity",
         "CUSIPCode",
         "PreferredSecurityName",
         "TickerID"


Explain Plan:


                                          Plan
                                          SELECT STATEMENT ALL_ROWS Cost: 243,126 Bytes: 4,465 Cardinality: 47
                                           10 SORT UNIQUE Cost: 243,125 Bytes: 4,465 Cardinality: 47
                                            9 FILTER
                                             8 HASH JOIN OUTER Cost: 243,124 Bytes: 4,465 Cardinality: 47
                                              6 HASH JOIN Cost: 243,120 Bytes: 3,760 Cardinality: 47
                                               2 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE GSDWRPTUSR.ISSU_HIST Cost: 15 Bytes: 880 Cardinality: 16 Partition #: 5 Partition access computed by row location
                                                1 INDEX RANGE SCAN INDEX GSDWRPTUSR.ISSU_HIST_P04 Cost: 1 Cardinality: 16
                                               5 PARTITION RANGE ALL Cost: 243,103 Bytes: 6,609,025 Cardinality: 264,361 Partition #: 7 Partitions accessed #1 - #414
                                                4 PARTITION HASH ALL Cost: 243,103 Bytes: 6,609,025 Cardinality: 264,361 Partition #: 8 Partitions accessed #1 - #LAST
                                                 3 TABLE ACCESS FULL TABLE GSDWRPTUSR.POSN_HIST Cost: 243,103 Bytes: 6,609,025 Cardinality: 264,361 Partition #: 8 Partitions accessed #1 - #1492
                                              7 INDEX FAST FULL SCAN INDEX GSDWRPTUSR.ACCT_HIST_PERF02 Cost: 4 Bytes: 28,920 Cardinality: 1,928


Same query but removed ticker Id condition and added account id condition:--

 SELECT DISTINCT "Account"."AccountID" "AccountID",
                  "Position"."HOLDING_DTE" "HoldingsDate",
                  "Position"."QTY_CQTY" "Quantity",
                  "Security"."CUSIPCode" "CUSIPCode",
                  "Security"."PreferredSecurityName" "PreferredSecurityName",
                  "Security"."TickerID" "TickerID"
    FROM (SELECT * FROM POSN_HIST) "Position"
         LEFT OUTER JOIN (SELECT "Account"."ACCT_ID" "AccountID",
                                 "Account"."ACCT_ID" "AccountID2",
                                 "Account"."ACCT_SOK" "ACCT_SOK"
                            FROM (SELECT * FROM ACCT_HIST) "Account") "Account"
            ON "Position"."ACCT_SOK" = "Account"."ACCT_SOK"
         LEFT OUTER JOIN (SELECT "Security"."CUSIP_ID" "CUSIPCode",
                                 "Security"."PREF_ISS_NME"
                                    "PreferredSecurityName",
                                 "Security"."TICKER_ID" "TickerID",
                                 "Security"."SEC_SOK" "SEC_SOK"
                            FROM (SELECT * FROM ISSU_HIST) "Security") "Security"
            ON "Position"."SEC_SOK" = "Security"."SEC_SOK"
   WHERE "Position"."HOLDING_DTE" >=
            TO_TIMESTAMP ('2009-11-17-00-00-00.000',
                          'YYYY-MM-DD-HH24-MI-SS.FF')
         AND "Position"."HOLDING_DTE" <
                (TO_TIMESTAMP ('2009-11-17-00-00-00.000',
                               'YYYY-MM-DD-HH24-MI-SS.FF')
                 + NUMTODSINTERVAL (1, 'DAY'))
         AND "Account"."AccountID2" = '709999'
ORDER BY "AccountID",
         "HoldingsDate",
         "Quantity",
         "CUSIPCode",
         "PreferredSecurityName",
         "TickerID"

Explain plan

                                    Plan
                                    SELECT STATEMENT ALL_ROWS Cost: 725 Bytes: 19,855 Cardinality: 209
                                     10 SORT UNIQUE Cost: 724 Bytes: 19,855 Cardinality: 209
                                      9 FILTER
                                       8 NESTED LOOPS OUTER Cost: 723 Bytes: 19,855 Cardinality: 209
                                        4 NESTED LOOPS Cost: 515 Bytes: 8,320 Cardinality: 208
                                         1 INDEX RANGE SCAN INDEX GSDWRPTUSR.ACCT_HIST_PERF02 Cost: 2 Bytes: 30 Cardinality: 2
                                         3 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE GSDWRPTUSR.POSN_HIST Cost: 257 Bytes: 2,925 Cardinality: 117 Partition #: 6 Partition access computed by row location
                                          2 INDEX RANGE SCAN INDEX GSDWRPTUSR.POSN_HIST_I01 Cost: 136 Cardinality: 138
                                        7 PARTITION HASH ITERATOR Cost: 1 Bytes: 55 Cardinality: 1 Partition #: 8 Partitions determined by Key Values
                                         6 TABLE ACCESS BY LOCAL INDEX ROWID TABLE GSDWRPTUSR.ISSU_HIST Cost: 1 Bytes: 55 Cardinality: 1 Partition #: 8 Partitions determined by Key Values
                                          5 INDEX UNIQUE SCAN INDEX (UNIQUE) GSDWRPTUSR.ISSU_HIST_I01U Cost: 0 Cardinality: 1 Partition #: 8 Partitions determined by Key Values

0
Comment
Question by:dba_shashi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
14 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 36561926
you have a different filter criteria and have a different index and plan usage.

why do you expect both queries to perform the same?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36561937
for tuning either or both,  are the cardinalities (number of rows) reasonable for each step?

use dbms_xplan.display to generate your plan and look at the filter conditions applied to each step.

you may need to update your statistics if the estimated rows aren't close
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36561941
I don't just see a simple removal between the two

One has:

AND "Security"."TickerID2" = 'TKTM'

the other:
AND "Account"."AccountID2" = '709999'

One allows the use of a global partition index.  The other forces a full table scan on:  POSN_HIST
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dba_shashi
ID: 36562495
Thanks...
Lets take first querry..for dbms_xplan.display..

Explain complete.

PLAN_TABLE_OUTPUT                                                                                                        
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1578831190                                                                                              

-------------------------------------------------------------------------------------------------------------------------
--                                                                                                                      
                                                                                                                         
| Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop
 |                                                                                                                      
                                                                                                                         
-------------------------------------------------------------------------------------------------------------------------
--                                                                                                                      
                                                                                                                         
|   0 | SELECT STATEMENT                       |                  |   209 | 19855 |   725   (1)| 00:00:11 |       |      
 |                                                                                                                      
                                                                                                                         
|   1 |  SORT UNIQUE                           |                  |   209 | 19855 |   724   (1)| 00:00:11 |       |      
 |                                                                                                                      
                                                                                                                         
|*  2 |   FILTER                               |                  |       |       |            |          |       |      
 |                                                                                                                      
                                                                                                                         
|   3 |    NESTED LOOPS OUTER                  |                  |   209 | 19855 |   723   (1)| 00:00:11 |       |      
 |                                                                                                                      

PLAN_TABLE_OUTPUT                                                                                                        
-------------------------------------------------------------------------------------------------------------------------
                                                                                                                         
|   4 |     NESTED LOOPS                       |                  |   208 |  8320 |   515   (1)| 00:00:08 |       |      
 |                                                                                                                      
                                                                                                                         
|*  5 |      INDEX RANGE SCAN                  | ACCT_HIST_PERF02 |     2 |    30 |     2   (0)| 00:00:01 |       |      
 |                                                                                                                      
                                                                                                                         
|   6 |      TABLE ACCESS BY GLOBAL INDEX ROWID| POSN_HIST        |   117 |  2925 |   257   (1)| 00:00:04 | ROWID | ROWID
 |                                                                                                                      
                                                                                                                         
|*  7 |       INDEX RANGE SCAN                 | POSN_HIST_I01    |   138 |       |   136   (2)| 00:00:02 |       |      
 |                                                                                                                      
                                                                                                                         
|   8 |     PARTITION HASH ITERATOR            |                  |     1 |    55 |     1   (0)| 00:00:01 |   KEY |   KEY
 |                                                                                                                      
                                                                                                                         
|   9 |      TABLE ACCESS BY LOCAL INDEX ROWID | ISSU_HIST        |     1 |    55 |     1   (0)| 00:00:01 |   KEY |   KEY
 |                                                                                                                      
                                                                                                                         
|* 10 |       INDEX UNIQUE SCAN                | ISSU_HIST_I01U   |     1 |       |     0   (0)| 00:00:01 |   KEY |   KEY
 |                                                                                                                      
                                                                                                                         

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

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

   2 - filter(TO_TIMESTAMP('2009-11-17-00-00-00.000','YYYY-MM-DD-HH24-MI-SS.FF')<TO_TIMESTAMP('2009-11-17-00-00-00.      
              000','YYYY-MM-DD-HH24-MI-SS.FF')+INTERVAL'+000000001 00:00:00.000000000' DAY(9) TO SECOND(9))              
   5 - access("ACCT_HIST"."ACCT_ID"='709999')                                                                            
   7 - access("POSN_HIST"."ACCT_SOK"="ACCT_HIST"."ACCT_SOK")                                                            
       filter(INTERNAL_FUNCTION("POSN_HIST"."HOLDING_DTE")>=TO_TIMESTAMP('2009-11-17-00-00-00.000','YYYY-MM-DD-HH24      
              -MI-SS.FF') AND INTERNAL_FUNCTION("POSN_HIST"."HOLDING_DTE")<TO_TIMESTAMP('2009-11-17-00-00-00.000','YYYY-M
M-DD-HH2                                                                                                                
                                                                                                                         
              4-MI-SS.FF')+INTERVAL'+000000001 00:00:00.000000000' DAY(9) TO SECOND(9))                                  
  10 - access("POSN_HIST"."SEC_SOK"="ISSU_HIST"."SEC_SOK"(+))                                                            

29 rows selected.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36562521
that's not the plan for the first query,  that's the plan for the second query


you can tell because of this...

   5 - access("ACCT_HIST"."ACCT_ID"='709999')        
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36562529
re http:#a36561941

You have two different where clauses.  The plans you posted show why one is taking longer than the other.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36562543
as stated in the first post and repeated above.

you are querying for 2 different results , why do you expect them to be the same?



0
 

Author Comment

by:dba_shashi
ID: 36562546
Pleaes ignore the last input from me..it was for the second statement..

This is for the first one:--

Explain complete.

PLAN_TABLE_OUTPUT                                                                                                        
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2442876608                                                                                              

-------------------------------------------------------------------------------------------------------------------------
--                                                                                                                      
                                                                                                                         
| Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop
 |                                                                                                                      
                                                                                                                         
-------------------------------------------------------------------------------------------------------------------------
--                                                                                                                      
                                                                                                                         
|   0 | SELECT STATEMENT                       |                  |    47 |  4465 |   243K  (2)| 00:56:44 |       |      
 |                                                                                                                      
                                                                                                                         
|   1 |  SORT UNIQUE                           |                  |    47 |  4465 |   243K  (2)| 00:56:44 |       |      
 |                                                                                                                      
                                                                                                                         
|*  2 |   FILTER                               |                  |       |       |            |          |       |      
 |                                                                                                                      
                                                                                                                         
|*  3 |    HASH JOIN OUTER                     |                  |    47 |  4465 |   243K  (2)| 00:56:44 |       |      
 |                                                                                                                      

PLAN_TABLE_OUTPUT                                                                                                        
-------------------------------------------------------------------------------------------------------------------------
                                                                                                                         
|*  4 |     HASH JOIN                          |                  |    47 |  3760 |   243K  (2)| 00:56:44 |       |      
 |                                                                                                                      
                                                                                                                         
|   5 |      TABLE ACCESS BY GLOBAL INDEX ROWID| ISSU_HIST        |    16 |   880 |    15   (0)| 00:00:01 | ROWID | ROWID
 |                                                                                                                      
                                                                                                                         
|*  6 |       INDEX RANGE SCAN                 | ISSU_HIST_P04    |    16 |       |     1   (0)| 00:00:01 |       |      
 |                                                                                                                      
                                                                                                                         
|   7 |      PARTITION RANGE ALL               |                  |   264K|  6454K|   243K  (2)| 00:56:44 |     1 |   414
 |                                                                                                                      
                                                                                                                         
|   8 |       PARTITION HASH ALL               |                  |   264K|  6454K|   243K  (2)| 00:56:44 |     1 |  LAST
 |                                                                                                                      
                                                                                                                         
|*  9 |        TABLE ACCESS FULL               | POSN_HIST        |   264K|  6454K|   243K  (2)| 00:56:44 |     1 |  1492
 |                                                                                                                      
                                                                                                                         
|  10 |     INDEX FAST FULL SCAN               | ACCT_HIST_PERF02 |  1928 | 28920 |     4   (0)| 00:00:01 |       |      
 |                                                                                                                      
                                                                                                                         

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

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

   2 - filter(TO_TIMESTAMP('2008-10-14-00-00-00.000','YYYY-MM-DD-HH24-MI-SS.FF')<TO_TIMESTAMP('2008-10-14-00-00-00.      
              000','YYYY-MM-DD-HH24-MI-SS.FF')+INTERVAL'+000000001 00:00:00.000000000' DAY(9) TO SECOND(9))              
   3 - access("POSN_HIST"."ACCT_SOK"="ACCT_HIST"."ACCT_SOK"(+))                                                          
   4 - access("POSN_HIST"."SEC_SOK"="ISSU_HIST"."SEC_SOK")                                                              
   6 - access("ISSU_HIST"."TICKER_ID"='TKTM')                                                                            
   9 - filter(INTERNAL_FUNCTION("POSN_HIST"."HOLDING_DTE")>=TO_TIMESTAMP('2008-10-14-00-00-00.000','YYYY-MM-DD-HH24      
              -MI-SS.FF') AND INTERNAL_FUNCTION("POSN_HIST"."HOLDING_DTE")<TO_TIMESTAMP('2008-10-14-00-00-00.000','YYYY-M
M-DD-HH2                                                                                                                
                                                                                                                         
              4-MI-SS.FF')+INTERVAL'+000000001 00:00:00.000000000' DAY(9) TO SECOND(9))                                  

29 rows selected.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36562574
as stated above,  your plans are different because your queries are different.

your execution time is different because your plans are different.

why do you expect them to be the same?
0
 

Author Comment

by:dba_shashi
ID: 36562622

considering the first query..what can be suggested..?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36562666
Add an index on POSN_HIST that can be used?
0
 

Author Comment

by:dba_shashi
ID: 36562705
One more thing to add..
There is no partition based on time...and I understand that it might have an impact on the final execution time.
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 125 total points
ID: 36562730
Individual partitions can help but proper indexing should make up for it.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 125 total points
ID: 36562785
I have to ask again,  are the cardinalities (rows) reasonable?

Your plan says that your current index scan of   ISSU_HIST_P04  
is expected to return 16 rows.

That seems pretty selective to me.  Is it correct?
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question