Solved

Query taking longer time to execute in oracle 11g

Posted on 2011-09-19
14
844 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
  • 6
  • 4
  • 4
14 Comments
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 

Author Comment

by:dba_shashi
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
re http:#a36561941

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

Expert Comment

by:sdstuber
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:dba_shashi
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
Comment Utility

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

Expert Comment

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

Author Comment

by:dba_shashi
Comment Utility
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 76

Accepted Solution

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

Assisted Solution

by:sdstuber
sdstuber earned 125 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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 post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now