This discussion is archived
7 Replies Latest reply: Feb 12, 2013 3:09 AM by Yoav RSS

Performance problem involve with hugh plan table

Yoav Newbie
Currently Being Moderated
Hi,
Version 11202.

I have a statement that is running more than 8 hours.
I ran dbms_xplan.display_cursor against this statment.
The Plan is realy hugh and contain hundreds of lines.
What interesting is that almost all of the rows returned by this plan is about 1 or 2 (Please see column "Rows" in the execution plan) ,
and only line 226 , has an access of ~2.5 millions rows.
I am assume that performance problem is there. Can you assist to confirm my finding ?
Also i saw that there is only an access to lines 226+227 and no filter is available against table "U" (IBY_PMT_INSTR_USES_ALL) , as you can see at the end of this section.
How would you recommend me to continue with investigating this issue ?
 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
| Id  | Operation                                                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
--------------------------------------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                                             |                                |       |       |  1052 (100)|          |       |       | 
|   1 |  HASH UNIQUE                                                 |                                |     1 |  1326 |  1052  (27)| 00:00:03 |       |       | 
..... 
| 206 |     VIEW PUSHED PREDICATE                                    | IBY_TRXN_EXTENSIONS_V          |     1 |    88 |    50   (6)| 00:00:01 |       |       | 
| 207 |      NESTED LOOPS OUTER                                      |                                |     2 |   578 |    50   (6)| 00:00:01 |       |       | 
|*208 |       HASH JOIN OUTER                                        |                                |     1 |   279 |    49   (7)| 00:00:01 |       |       | 
|*209 |        HASH JOIN OUTER                                       |                                |     1 |   272 |    37   (6)| 00:00:01 |       |       | 
| 210 |         NESTED LOOPS                                         |                                |       |       |            |          |       |       | 
| 211 |          NESTED LOOPS                                        |                                |     1 |   252 |    28   (4)| 00:00:01 |       |       | 
| 212 |           NESTED LOOPS OUTER                                 |                                |     1 |   237 |    27   (4)| 00:00:01 |       |       | 
| 213 |            NESTED LOOPS OUTER                                |                                |     1 |   212 |    24   (5)| 00:00:01 |       |       | 
| 214 |             NESTED LOOPS OUTER                               |                                |     1 |   205 |    12   (0)| 00:00:01 |       |       | 
| 215 |              NESTED LOOPS OUTER                              |                                |     1 |   198 |    12   (0)| 00:00:01 |       |       | 
| 216 |               NESTED LOOPS OUTER                             |                                |     1 |   192 |    11   (0)| 00:00:01 |       |       | 
| 217 |                NESTED LOOPS OUTER                            |                                |     1 |   121 |    11   (0)| 00:00:01 |       |       | 
| 218 |                 NESTED LOOPS OUTER                           |                                |     1 |   115 |    10   (0)| 00:00:01 |       |       | 
| 219 |                  NESTED LOOPS OUTER                          |                                |     1 |   109 |     9   (0)| 00:00:01 |       |       | 
| 220 |                   NESTED LOOPS                               |                                |     1 |    81 |     7   (0)| 00:00:01 |       |       | 
| 221 |                    NESTED LOOPS OUTER                        |                                |     1 |    59 |     6   (0)| 00:00:01 |       |       | 
| 222 |                     NESTED LOOPS                             |                                |     1 |    36 |     4   (0)| 00:00:01 |       |       | 
| 223 |                      TABLE ACCESS BY INDEX ROWID             | IBY_FNDCPT_TX_EXTENSIONS       |     1 |    32 |     4   (0)| 00:00:01 |       |       | 
|*224 |                       INDEX UNIQUE SCAN                      | IBY_FNDCPT_TX_EXTENSIONS_U1    |     1 |       |     3   (0)| 00:00:01 |       |       | 
|*225 |                      INDEX UNIQUE SCAN                       | FND_APPLICATION_U1             |   282 |  1128 |     0   (0)|          |       |       | 
| 226 |                     TABLE ACCESS BY INDEX ROWID              | IBY_PMT_INSTR_USES_ALL         |  2428K|    53M|     2   (0)| 00:00:01 |       |       | 
|*227 |                      INDEX UNIQUE SCAN                       | IBY_PMT_INSTR_USES_ALL_U1      |     1 |       |     1   (0)| 00:00:01 |       |       | 
| 228 |                    TABLE ACCESS BY INDEX ROWID               | IBY_FNDCPT_PMT_CHNNLS_B        |     9 |   198 |     1   (0)| 00:00:01 |       |       | 
|*229 |                     INDEX UNIQUE SCAN                        | IBY_FNDCPT_PMT_CHNNLS_B_U1     |     1 |       |     0   (0)|          |       |       | 
| 230 |                   TABLE ACCESS BY INDEX ROWID                | IBY_EXT_BANK_ACCOUNTS          |     1 |    28 |     2   (0)| 00:00:01 |       |       | 
|*231 |                    INDEX UNIQUE SCAN                         | IBY_EXT_BANK_ACCOUNTS_U1       |     1 |       |     1   (0)| 00:00:01 |       |       | 
|*232 |                  INDEX UNIQUE SCAN                           | HZ_PARTIES_U1                  |     1 |     6 |     1   (0)| 00:00:01 |       |       | 
|*233 |                 INDEX UNIQUE SCAN                            | HZ_PARTIES_U1                  |     1 |     6 |     1   (0)| 00:00:01 |       |       | 
| 234 |                TABLE ACCESS BY INDEX ROWID                   | IBY_CREDITCARD                 |     1 |    71 |     0   (0)|          |       |       | 
|*235 |                 INDEX UNIQUE SCAN                            | IBY_CREDITCARD_INSTRID_U1      |     1 |       |     0   (0)|          |       |       | 
|*236 |               INDEX UNIQUE SCAN                              | HZ_PARTIES_U1                  |     1 |     6 |     1   (0)| 00:00:01 |       |       | 
|*237 |              INDEX UNIQUE SCAN                               | IBY_CREDITCARD_ISSUERS_B_U1    |     1 |     7 |     0   (0)|          |       |       | 
|*238 |             VIEW                                             |                                |     1 |     7 |    11   (0)| 00:00:01 |       |       | 
..... 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
........ 
PLAN_TABLE_OUTPUT 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
208 - access("X"."TRXN_EXTENSION_ID"="AUTHTXN"."TRXN_EXTENSION_ID") 
209 - access("X"."TRXN_EXTENSION_ID"="SETTLETXN"."TRXN_EXTENSION_ID") 
224 - access("X"."TRXN_EXTENSION_ID"="PAYMENT_TRXN_EXTENSION_ID") 
225 - access("X"."ORIGIN_APPLICATION_ID"="A"."APPLICATION_ID") 
227 - access("X"."INSTR_ASSIGNMENT_ID"="U"."INSTRUMENT_PAYMENT_USE_ID") 
229 - access("X"."PAYMENT_CHANNEL_CODE"="P"."PAYMENT_CHANNEL_CODE") 
231 - access("B"."EXT_BANK_ACCOUNT_ID"=DECODE("U"."INSTRUMENT_TYPE",'BANKACCOUNT',"U"."INSTRUMENT_ID",NULL)) 
232 - access("B"."BRANCH_ID"="BRHZP"."PARTY_ID") 
233 - access("B"."BANK_ID"="BHZP"."PARTY_ID") 
235 - access("C"."INSTRID"=DECODE("U"."INSTRUMENT_TYPE",'CREDITCARD',"U"."INSTRUMENT_ID",NULL)) 
236 - access("C"."CARD_OWNER_ID"="HZP"."PARTY_ID") 
237 - access("C"."CARD_ISSUER_CODE"="I"."CARD_ISSUER_CODE") 
238 - filter("X"."TRXN_EXTENSION_ID"="RETURNTXN"."TRXN_EXTENSION_ID") 
245 - access("TRXN_EXTENSION_ID"="PAYMENT_TRXN_EXTENSION_ID") 
246 - filter("OP"."TRANSACTIONID"="MIN(TRANSACTIONID)") 
247 - access("OP"."TRXN_EXTENSION_ID"="PAYMENT_TRXN_EXTENSION_ID") 
       filter("ITEM_1"="OP"."TRXN_EXTENSION_ID") 
248 - access("SUMM"."TRANSACTIONID"="OP"."TRANSACTIONID") 
249 - filter(("REQTYPE"='ORAPMTRETURN' AND INTERNAL_FUNCTION("STATUS"))) 
250 - filter("ACTUAL_CONTENT_SOURCE"='SST') 
251 - access("BHZP"."PARTY_ID"="PARTY_ID") 
       filter((SYSDATE@!>=TRUNC(INTERNAL_FUNCTION("EFFECTIVE_START_DATE")) AND 
  • 1. Re: Performance problem involve with hugh plan table
    Iordan Iotzov Expert
    Currently Being Moderated
    My recommendation is to follow “Tuning by Cardinality Feedback” method -
    http://www.centrexcc.com/Tuning%20by%20Cardinality%20Feedback.pdf
    http://jonathanlewis.wordpress.com/2009/05/11/cardinality-feedback/

    In my opinion, huge queries are almost destined to get a suboptimal plan. The inevitable errors in estimating cardinalities of intermediate sets pile up as more tables are joined. The CBO is forced to work with “low confidence” estimates, which increases the chances of getting a bad plan.

    Also, the CBO may have issues reviewing all join permutation it needs - “Affect of Number of Tables on Join Order Permutations [ID 73489.1]”

    I have a presentation at Hotsos 2013 about this topic…

    Hinting the query to get the desired plan is an option, but it would probably require quite a bit of work.
    Splitting the query – either logically (NO_MERGE hint) or physically could be another option.

    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 2. Re: Performance problem involve with hugh plan table
    SalmanQureshi Expert
    Currently Being Moderated
    I am assume that performance problem is there. Can you assist to confirm my finding ?
    1. These are estimated number of rows which might not be 100% true.
    2. If this steps is really extracting a lot of rows, (suppose 2.5 million) out of total 20 million total records in the table IBY_PMT_INSTR_USES_ALL, then this step is doing its best.

    Apparently there is not visibile problem in your this plan portion. You would probably need to give this query to a good developer with understanding of the system for the tuning who may think of re-writing the query having following points in mind.
    1. Can query more restrict the data being fetched from the tables. Less data fetching may improve the overall performance.
    2. See other parts of plan. Is there any full table scan (which may need index).
    3. Any step from the query which can be eliminated
    etc.

    Salman
  • 3. Re: Performance problem involve with hugh plan table
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    do you have the diagnostic pack license? If yes, use Real-time SQL Monitoring to see where the problem lies. The plan you posted is of very little help for finding the problem, because it doesn't even see one. According to it, the total cost is so low that the query should complete within a second.

    Regarding steps 226-227. It appears to be an optimizer glitch, similar to the one caused by sanity checks for multi-column joins (http://oracle-randolf.blogspot.ru/2009/10/multi-column-joins.html): it can't be true that the step 227 returns 1 rowid, and TABLE ACCESS by this ROWID gives 2.428M rows. Plus, it's not clear where these rows went -- they weren't returned to the user (according to the plan), and I don't see where they could have been filtered out.

    So you options are:

    1) Real-time SQL Monitoring if you have the Diagnostic Pack License (by far the simplest)
    2) if not, you can still try to trace the statement, hoping that it would complete within reasonable time
    3) if it doesn't complete within reasonable time, then observe which object it reads from (V$SESSION displays parameters of wait events, for I/O waits they give you the file# and block# from which you can find which object is being read, e.g. http://dioncho.wordpress.com/2009/07/06/object-name-from-file-and-block/) , that will give you a good idea where in the plan the problem is, and then do manual cardinality feedback tuning, i.e. step by step calculate the rowcounts for various predicates and compare them with optimizer estimates

    Best regards,
    Nikolay
  • 4. Re: Performance problem involve with hugh plan table
    Suddhasatwa_Bhaumik Newbie
    Currently Being Moderated
    Hi,

    Apart from the above info, can you also let us know the output of "show parameter optimi" from sql-plus on this database where it is running slow?

    Also, can you please let us know how you are gathering statistics for your schema/tables in this database? Is it the automatic job which is running EOD everyday during the maintainence window, or you have your own script?

    Are you using SPM in 11G? If yes - Can you look for the plans baselined by Oracle 11g CBO for this particular query? You may use a query like below:
    SELECT plan_name, sql_text, sql_handle, optimizer_cost, 
    enabled, fixed, accepted
    FROM DBA_SQL_PLAN_BASELINES
    Regards,
    Suddhasatwa

    Edited by: SBhaumik_DBA on Feb 6, 2013 10:56 PM
  • 5. Re: Performance problem involve with hugh plan table
    Yoav Newbie
    Currently Being Moderated
    Hi All ,
    Thank you for your feedbacks.
    Regarding steps 226-227. It appears to be an optimizer glitch, similar to the one caused by sanity checks for multi-column joins (http://oracle-randolf.blogspot.ru/2009/10/multi-column-joins.html): it can't be true that the step 227 returns 1 rowid, and TABLE ACCESS by this ROWID gives 2.428M rows. Plus, it's not clear where these rows went -- they weren't returned to the user (according to the plan), and I don't see where they could have been filtered out.
    I am still having performance issues with this statment.
    I have gather fresh statstics on the tables and indexes involved on this hugh query.
    I also added the hint SELECT /*+ GATHER_PLAN_STATISTICS */ to the schema and rerun the statmnets.
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                                    | Name                           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers |  OMem |  1Mem |  O/1/M   |
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                             |                                |      1 |        |       |   465 (100)|          |       |       |     18 |00:00:00.05 |    3400 |       |       |          |
    |   1 |  HASH UNIQUE                                                 |                                |      1 |      1 |  1326 |   465  (10)| 00:00:02 |       |       |     18 |00:00:00.05 |    3400 |   711K|   711K|     1/0/0|
    |*  2 |   FILTER                                                     |                                |      1 |        |       |            |          |       |       |     18 |00:00:00.05 |    3202 |       |       |          |
    |   3 |    NESTED LOOPS OUTER                                        |                                |      1 |      1 |  1326 |   417  (10)| 00:00:02 |       |       |     23 |00:00:00.04 |    2367 |       |       |          |
    |   4 |     NESTED LOOPS                                             |                                |      1 |      1 |  1238 |   367  (11)| 00:00:01 |       |       |     23 |00:00:00.02 |    1309 |       |       |          |
    |   5 |      NESTED LOOPS                                            |                                |      1 |      1 |  1195 |   364  (11)| 00:00:01 |       |       |     23 |00:00:00.02 |    1295 |       |       |          |
    |   6 |       NESTED LOOPS OUTER                                     |                                |      1 |      1 |  1152 |   362  (11)| 00:00:01 |       |       |     23 |00:00:00.02 |    1281 |       |       |          |
    |   7 |        NESTED LOOPS                                          |                                |      1 |      1 |  1112 |   359  (11)| 00:00:01 |       |       |     23 |00:00:00.02 |    1268 |       |       |          |
    |   8 |         NESTED LOOPS OUTER                                   |                                |      1 |      1 |  1093 |   357  (11)| 00:00:01 |       |       |     23 |00:00:00.02 |    1251 |       |       |          |
    .....
    I am 100% sure that the problem is happen in lines : 227+228
    The E-ROWS is : 2431K while A-ROWS is : 23 , as well as the "Starts"


    The inolved statistics are as follow :

    i have gather 90% stats on table IBY_PMT_INSTR_USES_ALL , in order to give the optimizer more details , but it didnt helped :
       
    select num_rows,sample_size,last_analyzed from dba_tables where table_name='IBY_PMT_INSTR_USES_ALL'   
           2,431,853    2,188,668    09/02/2013 17:15:24
            
    select count(*) from iby.IBY_PMT_INSTR_USES_ALL  --> 2,432,240 --> which is similar  to the value that the optimized found : 2431K
    Statistics on the table indexes are as follows:
    select index_name, distinct_keys,clustering_factor,num_rows,sample_size,last_analyzed
    from dba_indexes 
    where table_name='IBY_PMT_INSTR_USES_ALL'
       
    index_name                   distinct_keys  clustering_factor   num_rows  sample_size  last_analyzed
    IBY_PMT_INSTR_USES_ALL_YOAV    2440393         1484519                   2440403    2196363    09/02/2013 17:15:26
    IBY_PMT_INSTR_USES_ALL_N2      316546         263238                   2439537    2195583    09/02/2013 17:15:27
    IBY_PMT_INSTR_USES_ALL_N1      2058927         1484046                   2436740    2193066    09/02/2013 17:15:30
    IBY_PMT_INSTR_USES_ALL_U1      2445809         210779                   2445809    2201228    09/02/2013 17:15:31
    The intersting section of the plan is:
      
    
    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | 207 |     VIEW PUSHED PREDICATE                                    | IBY_TRXN_EXTENSIONS_V          |     23 |      1 |    88 |    50   (6)| 00:00:01 |       |       |     23 |00:00:00.01 |    1058 |       |       |          |
    | 208 |      NESTED LOOPS OUTER                                      |                                |     23 |      2 |   578 |    50   (6)| 00:00:01 |       |       |     23 |00:00:00.01 |    1058 |       |       |          |
    |*209 |       HASH JOIN OUTER                                        |                                |     23 |      1 |   279 |    49   (7)| 00:00:01 |       |       |     23 |00:00:00.01 |    1058 |   855K|   855K|     1/0/0|
    |*210 |        HASH JOIN OUTER                                       |                                |     23 |      1 |   272 |    37   (6)| 00:00:01 |       |       |     23 |00:00:00.01 |     796 |   855K|   855K|     1/0/0|
    | 211 |         NESTED LOOPS                                         |                                |     23 |        |       |            |          |       |       |     23 |00:00:00.01 |     649 |       |       |          |
    | 212 |          NESTED LOOPS                                        |                                |     23 |      1 |   252 |    28   (4)| 00:00:01 |       |       |     46 |00:00:00.01 |     626 |       |       |          |
    | 213 |           NESTED LOOPS OUTER                                 |                                |     23 |      1 |   237 |    27   (4)| 00:00:01 |       |       |     23 |00:00:00.01 |     603 |       |       |          |
    | 214 |            NESTED LOOPS OUTER                                |                                |     23 |      1 |   212 |    24   (5)| 00:00:01 |       |       |     23 |00:00:00.01 |     547 |       |       |          |
    | 215 |             NESTED LOOPS OUTER                               |                                |     23 |      1 |   205 |    12   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |     344 |       |       |          |
    | 216 |              NESTED LOOPS OUTER                              |                                |     23 |      1 |   198 |    12   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |     344 |       |       |          |
    | 217 |               NESTED LOOPS OUTER                             |                                |     23 |      1 |   192 |    11   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |     344 |       |       |          |
    | 218 |                NESTED LOOPS OUTER                            |                                |     23 |      1 |   121 |    11   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |     344 |       |       |          |
    | 219 |                 NESTED LOOPS OUTER                           |                                |     23 |      1 |   115 |    10   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |     311 |       |       |          |
    | 220 |                  NESTED LOOPS OUTER                          |                                |     23 |      1 |   109 |     9   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |     278 |       |       |          |
    | 221 |                   NESTED LOOPS                               |                                |     23 |      1 |    81 |     7   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |     222 |       |       |          |
    | 222 |                    NESTED LOOPS OUTER                        |                                |     23 |      1 |    59 |     6   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |     176 |       |       |          |
    | 223 |                     NESTED LOOPS                             |                                |     23 |      1 |    36 |     4   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |      84 |       |       |          |
    | 224 |                      TABLE ACCESS BY INDEX ROWID             | IBY_FNDCPT_TX_EXTENSIONS       |     23 |      1 |    32 |     4   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |      61 |       |       |          |
    |*225 |                       INDEX UNIQUE SCAN                      | IBY_FNDCPT_TX_EXTENSIONS_U1    |     23 |      1 |       |     3   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |      38 |       |       |          |
    |*226 |                      INDEX UNIQUE SCAN                       | FND_APPLICATION_U1             |     23 |    282 |  1128 |     0   (0)|          |       |       |     23 |00:00:00.01 |      23 |       |       |          |
    | 227 |                     TABLE ACCESS BY INDEX ROWID              | IBY_PMT_INSTR_USES_ALL         |     23 |   2431K|    53M|     2   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |      92 |       |       |          |
    |*228 |                      INDEX UNIQUE SCAN                       | IBY_PMT_INSTR_USES_ALL_U1      |     23 |      1 |       |     1   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |      69 |       |       |          |
    | 229 |                    TABLE ACCESS BY INDEX ROWID               | IBY_FNDCPT_PMT_CHNNLS_B        |     23 |      9 |   198 |     1   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |      46 |       |       |          |
    |*230 |                     INDEX UNIQUE SCAN                        | IBY_FNDCPT_PMT_CHNNLS_B_U1     |     23 |      1 |       |     0   (0)|          |       |       |     23 |00:00:00.01 |      23 |       |       |          |
    | 231 |                   TABLE ACCESS BY INDEX ROWID                | IBY_EXT_BANK_ACCOUNTS          |     23 |      1 |    28 |     2   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |      56 |       |       |          |
    |*232 |                    INDEX UNIQUE SCAN                         | IBY_EXT_BANK_ACCOUNTS_U1       |     23 |      1 |       |     1   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |      33 |       |       |          |
    |*233 |                  INDEX UNIQUE SCAN                           | HZ_PARTIES_U1                  |     23 |      1 |     6 |     1   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |      33 |       |       |          |
    |*234 |                 INDEX UNIQUE SCAN                            | HZ_PARTIES_U1                  |     23 |      1 |     6 |     1   (0)| 00:00:01 |       |       |     23 |00:00:00.01 |      33 |       |       |          |
    | 235 |                TABLE ACCESS BY INDEX ROWID                   | IBY_CREDITCARD                 |     23 |      1 |    71 |     0   (0)|          |       |       |      0 |00:00:00.01 |       0 |       |       |          |
    |*236 |                 INDEX UNIQUE SCAN                            | IBY_CREDITCARD_INSTRID_U1      |     23 |      1 |       |     0   (0)|          |       |       |      0 |00:00:00.01 |       0 |       |       |          |
    |*237 |               INDEX UNIQUE SCAN                              | HZ_PARTIES_U1                  |     23 |      1 |     6 |     1   (0)| 00:00:01 |       |       |      0 |00:00:00.01 |       0 |       |       |          |
    |*238 |              INDEX UNIQUE SCAN                               | IBY_CREDITCARD_ISSUERS_B_U1    |     23 |      1 |     7 |     0   (0)|          |       |       |      0 |00:00:00.01 |       0 |       |       |          |
    |*239 |             VIEW                                             |                                |     23 |      1 |     7 |    11   (0)| 00:00:01 |       |       |      0 |00:00:00.01 |     203 |       |       |          |
    ......
    If you have more suggestions please advise
    Thanks
  • 6. Re: Performance problem involve with hugh plan table
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi Yoav,

    I'm confused -- what is the relationship of the plan you posted to your original performance problem? Originally, you mentioned a statement running for 8 hours, but the plan shows timings within 0.02 s (and buffer gets / disk reads stats that support this).

    Best regards,
    Nikolay
  • 7. Re: Performance problem involve with hugh plan table
    Yoav Newbie
    Currently Being Moderated
    Hi,
    I didnt want to wait few hours until the statment wil complete , so i execute the select statment aginst small amount of billing records , just to check if the plan is changed or not.
    Also i wrote , i have gather 90% stats. on the involved table , while it was only 10% at the previous post.
    Also this time have added the hint , so i can see what is the different between the E-ROWS and the A-ROWS.
    Also its took to the statment about 30 seconds to fetch total of 18 rows. The values 0.02 seconds is not what actualy happened. I can add set timig on to show it.
    Thanks
    Yoav

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points