Solved

Query taking longer time to execute in oracle 11g

Posted on 2011-09-19
14
860 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
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 73

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 76

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
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
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 73

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 76

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 73

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 73

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 76

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 76

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 73

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error executing command from server 6 49
How can I rollback insert statements after commit in oracle? 7 131
sort a spool into file output in oracle 1 28
PL SQL Developer 7 34
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
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

770 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