[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query taking longer time to execute in oracle 11g

Posted on 2011-09-19
14
Medium Priority
?
887 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 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 78

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 78

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 78

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 78

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

873 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