?
Solved

Query taking longer time to execute in oracle 11g

Posted on 2011-09-19
14
Medium Priority
?
883 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
Industry Leaders: 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 500 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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 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