14 Replies Latest reply: Aug 29, 2014 6:38 PM by kastolom RSS

    Is "PLAN_TABLE is old version" the reason?

    user130038

      Env: Oracle 10gR2 10.2.0.3 64-bit on Solaris

       

      "select * from table(dbms_xplan.display);" command is returning following output - and the rest of the part (after the explain plan" portion) is not being reported.

       

      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------
      ------------------------------------------------------------------------------------------
      | Id  | Operation                         | Name                 | Rows  | Bytes | Cost  |
      ------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                  |                      |     1 | 30    |    65 |
      |   1 |  SORT AGGREGATE                   |                      |     1 | 30    |       |
      |   2 |   TABLE ACCESS BY INDEX ROWID     | XE_TRANSACTION       |     2 | 60    |    65 |
      |   3 |    BITMAP CONVERSION TO ROWIDS    |                      |       |       |       |
      |   4 |     BITMAP AND                    |                      |       |       |       |
      |   5 |      BITMAP CONVERSION FROM ROWIDS|                      |       |       |       |
      |   6 |       SORT ORDER BY               |                      |       |       |       |
      |   7 |        INDEX RANGE SCAN           | NU_XE_TRANSACTION_02 |       |       |     3 |
      |   8 |      BITMAP CONVERSION FROM ROWIDS|                      |       |       |       |
      |   9 |       SORT ORDER BY               |                      |       |       |       |
      |  10 |        INDEX RANGE SCAN           | NU_XE_TRANSACTION_03 |       |       |    55 |
      ------------------------------------------------------------------------------------------
       
       
      Note
      -----
         - 'PLAN_TABLE' is old version
       
      20 rows selected.
      
      
      

       

      Is "Old version" of plan_table the reason for missing information?

       

      Please advise!

       

      And is there anything apparent from this explain plan that can be improved? The query now takes 1.5Hrs instead of 15min.

       

      Regards

        • 1. Re: Is "PLAN_TABLE is old version" the reason?
          sybrand_b

          1 you need to re-run utlxplan.sql

          2 One can not tune a statement without seeing the SQL and without knowing which indices are there.

           

          Sybrand Bakker

          Senior Oracle DBA

          • 2. Re: Is "PLAN_TABLE is old version" the reason?
            sol.beach

            >ROWS=1

            based upon posted details either statistics are out of date & need to be re-collected, or  EXPLAIN PLAN  was run against non-production database/

            • 3. Re: Is "PLAN_TABLE is old version" the reason?
              user130038

              Thank you both!

               

              The results are from PROD database to which I have absolutely no access. "out of date" stats is what I am also suspecting. Let me find out some more info and will also provide the SQL and related info.

               

              Thanks again!

               

              Regards

              • 4. Re: Is "PLAN_TABLE is old version" the reason?
                Jonathan Lewis

                Yes, it's the old plan table: https://community.oracle.com/message/3189049#3189049

                 

                How many times does this query run to take 1.5 hours - just once (in which case the stats are probably wrong), or many thousands of times ?

                 

                Regards

                Jonathan Lewis

                • 5. Re: Is "PLAN_TABLE is old version" the reason?
                  user130038

                  The query executes two times and it is taking ~1.5hrs each time.

                   

                  I am trying to get the "utlxplan.sql" executed. Since this is PROD and it is going to take me looooooong time to have this get done due to the client's lengthy processes I have to follow :-)

                   

                  Should I also have them execute "catplan.sql"?

                   

                  Thanks Jonathan!

                  • 6. Re: Re: Is "PLAN_TABLE is old version" the reason?
                    user130038

                    I have requested the DBA to run the utlxplan.sql.

                     

                    Here is the SQL:

                     

                    
                    
                    SELECTDISTINCTa.ROWID txn_rowid,a.load_transaction_id transaction_id
                      FROM load_xe_transaction a, xe_transaction b, load_table_message_log m
                    WHERE m.table_nm = 'LOAD_XE_TRANSACTION'
                    AND a.load_transaction_id = m.load_table_id
                    AND m.error_id = 51106
                    AND a.action_cd = 'N'
                    AND a.load_process_state_cd = 'E'
                    AND NVL (a.BKOFF_MODIFY_BY, 0) <> NVL (b.BKOFF_MODIFY_BY, 0)
                    AND NVL (a.BKOFF_MODIFY_DT, TO_DATE ('01-JAN-1900', 'DD-MON-YYYY')) <>
                    NVL (b.BACKOFF_MODIFY_DT,
                    TO_DATE ('01-JAN-1900', 'DD-MON-YYYY'))
                    AND NVL (a.BKOFF_ACCOUNT_NO, 0) = NVL (b.BKOFF_ACCOUNT_NO, 0)
                    AND NVL (a.BKOFF_SECURITY_NO, 0) = NVL (b.BKOFF_SECURITY_NO, 0)
                    AND NVL (a.BKOFF_TRANSACTION_NO, 0) = NVL (b.BKOFF_TRANSACTION_NO, 0)
                    AND NVL (a.ENTRY_DT, TO_DATE ('01-JAN-1900', 'DD-MON-YYYY')) =
                    NVL (b.ENTRY_DT, TO_DATE ('01-JAN-1900', 'DD-MON-YYYY'))
                    AND NVL (a.BKOFF_ENTRY_CD, 0) = NVL (b.BKOFF_ENTRY_CD, 0)
                    AND NVL (a.TRADE_DT, TO_DATE ('01-JAN-1900', 'DD-MON-YYYY')) =
                    NVL (b.TRADE_DT, TO_DATE ('01-JAN-1900', 'DD-MON-YYYY'))
                    AND NVL (a.EXPECT_SETTLEMENT_DT,
                    TO_DATE ('01-JAN-1900', 'DD-MON-YYYY')) =
                    NVL (b.EXPECT_SETTLEMENT_DT,
                    TO_DATE ('01-JAN-1900', 'DD-MON-YYYY'))
                    AND NVL (a.ACTUAL_SETTLEMENT_DT,
                    TO_DATE ('01-JAN-1900', 'DD-MON-YYYY')) =
                    NVL (b.ACTUAL_SETTLEMENT_DT,
                    TO_DATE ('01-JAN-1900', 'DD-MON-YYYY'))
                    AND NVL (a.TXN_DESC_TXT, 0) = NVL (b.TXN_DESC_TXT, 0)
                    AND NVL (a.RR_NO, 0) = NVL (b.RR_NO, 0)
                    AND NVL (a.TRADE_BASIS_CD, 0) = NVL (b.TRADE_BASIS_CD, 0)
                    AND NVL (a.EXCHANGE_CD, 0) = NVL (b.EXCHANGE_CD, 0)
                    AND NVL (a.BKOFF_PROCESS_DT, TO_DATE ('01-JAN-1900', 'DD-MON-Y YYY')) =
                    NVL (b.BKOFF_PROCESS_DT,
                    TO_DATE ('01-JAN-1900', 'DD-MON-YYYY'))
                    AND NVL (a.NET_TRADE_AMT, 0) = NVL (b.NET_TRADE_AMT, 0)
                    AND NVL (a.NET_SETL_AMT, 0) = NVL (b.NET_SETL_AMT, 0)
                    AND NVL (a.NET_BASE_AMT, 0) = NVL (b.NET_BASE_AMT, 0)
                    AND NVL (a.TRADE_TO_BASE_FX_RT, 0) = NVL (b.TRADE_TO_BASE_FX_RT, 0)
                    AND NVL (a.FX_CONV_DIR_CD, 0) = NVL (b.FX_CONV_DIR_CD, 0)
                    AND NVL (a.SETL_TO_BASE_FX_RT, 0) = NVL (b.SETL_TO_BASE_FX_RT, 0)
                    AND NVL (a.SETL_TO_BASE_FX_CONV_DIR_CD, 0) =
                    NVL (b.SETL_TO_BASE_FX_CONV_DIR_CD, 0)
                    AND NVL (a.TRADE_CURRENCY_CD, 0) = NVL (b.TRADE_CURRENCY_CD, 0)
                    AND NVL (a.SETL_CURRENCY_CD, 0) = NVL (b.SETL_CURRENCY_CD, 0)
                    AND NVL (a.INSTR_QTY, 0) = NVL (b.INSTR_QTY, 0)
                    AND NVL (a.AVG_TRADE_PRICE, 0) = NVL (b.AVG_TRADE_PRICE, 0)
                    AND NVL (a.AVG_BASE_PRICE, 0) = NVL (b.AVG_BASE_PRICE, 0)
                    AND NVL (a.GROSS_TRADE_AMT, 0) = NVL (b.GROSS_TRADE_AMT, 0)
                    AND (   ( a.trade_currency_cd = b.base_currency_cd
                    AND ABS (
                    NVL (a.GROSS_BASE_AMT, 0) - NVL (b.GROSS_BASE_AMT, 0)) <=
                    0.01)
                    OR (    a.trade_currency_cd <> b.base_currency_cd
                    AND NVL (a.GROSS_BASE_AMT, 0) = NVL (b.GROSS_BASE_AMT, 0)))
                    AND NVL (a.ACCRUED_INT_TRADE_AMT, 0) =
                    NVL (b.ACCRUED_INT_TRADE_AMT, 0)
                    AND NVL (a.ACCRUED_INT_BASE_AMT, 0) = NVL (b.ACCRUED_INT_BASE_AMT, 0)
                    AND NVL (a.COMMISSION_TRADE_AMT, 0) = NVL (b.COMMISSION_TRADE_AMT, 0)
                    AND NVL (a.COMMISSION_BASE_AMT, 0) = NVL (b.COMMISSION_BASE_AMT, 0)
                    AND NVL (a.TOTAL_OTHER_CHARGE_TRADE_AMT, 0) =
                    NVL (b.TOTAL_OTHER_CHARGE_TRADE_AMT, 0)
                    AND NVL (a.TOTAL_OTHER_CHARGE_BASE_AMT, 0) =
                    NVL(b.TOTAL_OTHER_CHARGE_BASE_AMT,0);
                    
                    
                    
                    
                    
                    
                    
                    
                    
                    

                     

                     

                    Is following information enough regarding the tables, indexes and stats OR should I ask for more/additional info:

                     

                     

                    
                    select table_name, num_rows, last_analyzed, sample_size, global_stats, user_stats  from all_tables
                    where table_name in ('LOAD_XE_TRANSACTION', 'XE_TRANSACTION', 'LOAD_TABLE_MESSAGE_LOG' );
                    
                    
                    select  ind.table_name, ind.index_name,cols.column_name,ind.num_rows, ind.last_analyzed, ind.sample_size, ind.degree, ind.partitioned,ind.global_stats, ind.user_stats
                    from all_indexes ind, all_ind_columns cols
                    where ind.table_name in ('LOAD_XE_TRANSACTION', 'XE_TRANSACTION', 'LOAD_TABLE_MESSAGE_LOG' )
                    and ind.index_name = cols.index_name
                    and ind.table_name = cols.table_name
                    order by 1, 2;
                    
                    
                    

                     

                    Regards

                    • 7. Re: Re: Is "PLAN_TABLE is old version" the reason?
                      user130038

                      Here is some more info. Looks like stats for XE_TRANSACTION are out-dated perhaps the root cause of slowness:

                       

                       

                       

                      select table_name, num_rows, last_analyzed, sample_size, global_stats, user_stats from all_tables
                      where table_name in ('LOAD_XE_TRANSACTION', 'XE_TRANSACTION', 'LOAD_TABLE_MESSAGE_LOG' );
                      
                      
                      
                      

                       

                      TABLE_NAMENUM_ROWSLAST_ANALYZEDSAMPLE_SIZEGLOBAL_STATSUSER_STATS
                      XE_TRANSACTION1726068502-May-2014 11:25:45 PM17260685YESNO
                      LOAD_TABLE_MESSAGE_LOG850190413-Aug-2014 10:17:51 PM8501904YESNO
                      LOAD_XE_TRANSACTION487326-Aug-2014 10:00:57 PM4873YESNO

                       

                       

                       

                      select ind.table_name, ind.index_name,cols.column_name,ind.num_rows, ind.last_analyzed, ind.sample_size, ind.degree, ind.partitioned,ind.global_stats, ind.user_stats 
                      from all_indexes ind, all_ind_columns cols
                      where ind.table_name in ('LOAD_XE_TRANSACTION', 'XE_TRANSACTION', 'LOAD_TABLE_MESSAGE_LOG' )
                      and ind.index_name = cols.index_name
                      
                      
                      
                      

                       

                       

                      TABLE_NAMEINDEX_NAMECOLUMN_NAMENUM_ROWSLAST_ANALYZEDSAMPLE_SIZEDEGREEPARTITIONEDGLOBAL_STATSUSER_STATS
                      XE_TRANSACTIONPK_XE_TRANSACTIONTRANSACTION_ID1673217502-May-2014 11:26:05 PM5014241NOYESNO
                      XE_TRANSACTIONUK_XE_TRANSACTION_01BKOFF_TRANSACTION_NO1727061502-May-2014 11:26:13 PM1675871NOYESNO
                      XE_TRANSACTIONUK_XE_TRANSACTION_01ACCOUNT_ID1727061502-May-2014 11:26:13 PM1675871NOYESNO
                      XE_TRANSACTIONUK_XE_TRANSACTION_01INSTRUMENT_ID1727061502-May-2014 11:26:13 PM1675871NOYESNO
                      XE_TRANSACTIONUK_XE_TRANSACTION_01ENTRY_DT1727061502-May-2014 11:26:13 PM1675871NOYESNO
                      XE_TRANSACTIONNU_XE_TRANSACTION_04BKOFF_TRANSACTION_NO1795986802-May-2014 11:26:21 PM3481741NOYESNO
                      XE_TRANSACTIONNU_XE_TRANSACTION_05INSTRUMENT_ID1702315602-May-2014 11:26:27 PM3752851NOYESNO
                      XE_TRANSACTIONNU_XE_TRANSACTION_01BUS_UNIT_ID1846379502-May-2014 11:26:49 PM20308001NOYESNO
                      XE_TRANSACTIONNU_XE_TRANSACTION_01TRADE_DT1846379502-May-2014 11:26:49 PM20308001NOYESNO
                      XE_TRANSACTIONNU_XE_TRANSACTION_01PRIVILEGE_CD1846379502-May-2014 11:26:49 PM20308001NOYESNO
                      XE_TRANSACTIONNU_XE_TRANSACTION_02ACCOUNT_ID1760746802-May-2014 11:26:00 PM2803291NOYESNO
                      XE_TRANSACTIONNU_XE_TRANSACTION_02INSTRUMENT_ID1760746802-May-2014 11:26:00 PM2803291NOYESNO
                      LOAD_TABLE_MESSAGE_LOGNU_LOAD_TABLE_MESSAGE_LOGLOAD_TABLE_ID835296213-Aug-2014 10:17:57 PM1383191NOYESNO
                      LOAD_TABLE_MESSAGE_LOGNU_LOAD_TABLE_MESSAGE_LOGTABLE_NM835296213-Aug-2014 10:17:57 PM1383191NOYESNO
                      LOAD_TABLE_MESSAGE_LOGNU_LOAD_TABLE_MESSAGE_LOG_01TABLE_NM840244713-Aug-2014 10:18:05 PM1433541NOYESNO
                      LOAD_TABLE_MESSAGE_LOGNU_LOAD_TABLE_MESSAGE_LOG_01ERROR_ID840244713-Aug-2014 10:18:05 PM1433541NOYESNO
                      LOAD_TABLE_MESSAGE_LOGPK_LOAD_TABLE_MESSAGE_LOGLOAD_TABLE_MESSAGE_LOG_ID870980913-Aug-2014 10:18:09 PM4093191NOYESNO

                       

                       

                      Q1: Is this command good enough to Gather the Stats for this table (and all child tables):

                       

                       

                      dbms_stats.gather_table_stats(ownname=>'XEYE', tabname=>'XE_TRANSACTION', cascade=> true); 

                       

                       

                      Q2: Would this command also Gather Stats for the indexes on XE_TRANSACTION table?

                      EDIT: found the answer for this one in the docs: [ This procedure gathers table and column (and index) statistics.]

                       

                      Please advise!

                       

                      Regards

                      • 8. Re: Is "PLAN_TABLE is old version" the reason?
                        Jonathan Lewis

                        The execution plan you supplied doesn't seem to belong to the query you've supplied - the former references only one table, the latter is a three-table join with no prospect of the optimizer being able to do join elimination.  Moreover the plan references an index that doesn't exist in the list of indexes you've supplied. Until the query, plan, and supporting information are consistent there's no point in anyone trying to work out how to address your problem.


                        Regards

                        Jonathan Lewis

                        • 9. Re: Re: Is "PLAN_TABLE is old version" the reason?
                          user130038

                          My humble apologies!

                           

                          Here is the explain plan for the query (and related info) posted above (most recent post):

                           

                          Execution Plan
                          ----------------------------------------------------------
                          ----------------------------------------------------------------------------------------------------
                          | Id  | Operation                      | Name                        | Rows  |Bytes | Cost (%CPU)|
                          ----------------------------------------------------------------------------------------------------
                          |  0 | SELECT STATEMENT              |                              |    1 | 385 |  215K  (3)|
                          |  1 |  HASH UNIQUE                  |                              |    1 | 385 |  215K  (3)|
                          |  2 |  HASH JOIN                    |                              |    1 | 385 |  215K  (3)|
                          |  3 |    MERGE JOIN CARTESIAN       |                              |    1 | 198 |  215K  (3)|
                          |  4 |    TABLE ACCESS BY INDEX ROWID| LOAD_TABLE_MESSAGE_LOG       |    1 | 34  |     5  (0)|
                          |  5 |      INDEX RANGE SCAN         |  NU_LOAD_TABLE_MESSAGE_LOG_01|    1 |     |     4  (0)|
                          |  6 |    BUFFER SORT                |                              |   17M|2699M|  215K  (3)|
                          |  7 |      TABLE ACCESS FULL        | XE_TRANSACTION               |   17M|2699M|  215K  (3)|
                          |  8 |    TABLE ACCESS FULL          | LOAD_XE_TRANSACTION          |    1 | 187 |    26  (0)|
                          ----------------------------------------------------------------------------------------------------
                          
                          
                          Note
                          -----
                               - 'PLAN_TABLE' is old version
                          
                          
                          
                          
                          

                           

                          My apologies once again -  I hope all info is in Sync now :-)

                           

                          Regards

                          • 10. Re: Is "PLAN_TABLE is old version" the reason?
                            sol.beach

                            the SQL might go faster if parallel was used against XE_TRANSACTION  table, then again it might have no impact.

                            no index can be used to access XE_TRANSACTION   because of all the NVL (nullable columns) functions

                            • 11. Re: Is "PLAN_TABLE is old version" the reason?
                              Jonathan Lewis

                              How many rows from load_table_message_log actually match the predicates for just that table ?

                               

                              I am puzzled why Oracle should choose the merge join where it does, I would have expected a join order of (m, a, b) with two hash joins, not the join order that's appeared. Without the predicate section I don't see how Oracle can get a cardinality on the merge join of 1, but my best guess would be that the stats on load_table_message_log are sufficiently out of date that the values used in the predicates on that table appear to be far outside the known low/high ranges for the columns.

                               

                               

                              Regards

                              Jonathan Lewis

                              • 12. Re: Re: Is "PLAN_TABLE is old version" the reason?
                                user130038

                                Hi Jonathan

                                 

                                Stats on load_table_message_log were calculated on Aug 13 but it is the xe_transaction table stats that have not been refreshed since May 2 (as shown below).

                                 

                                TABLE_NAMENUM_ROWSLAST_ANALYZEDSAMPLE_SIZEGLOBAL_STATSUSER_STATS
                                XE_TRANSACTION1726068502-May-2014 11:25:45 PM17260685YESNO
                                LOAD_TABLE_MESSAGE_LOG850190413-Aug-2014 10:17:51 PM8501904YESNO

                                 

                                 

                                Is this command good enough to refresh the STATS:

                                 

                                dbms_stats.gather_table_stats(ownname=>'XEYE', tabname=>'XE_TRANSACTION', cascade=> true);  

                                 

                                 

                                Regards

                                • 13. Re: Is "PLAN_TABLE is old version" the reason?
                                  Jonathan Lewis

                                  That command looks as if it will collect some stats on the xe_transaction table - but I explained why I thought the load message table might be the problem, and I was able to read your earlier post showing the dates of last stats collection so I knew that the stats on that table were only 2 weeks old while the stats on the xe_transaction table were much older.

                                   

                                  You might also notice that I asked a very simple question about the actual data volume and you have not addressed it, yet you expect me to continue helping you - why do you think that is reasonable ?

                                   

                                  Regards

                                  Jonathan Lewis

                                  • 14. Re: Is "PLAN_TABLE is old version" the reason?
                                    kastolom

                                    Execute this:

                                    select owner, object_type, object_name
                                      from all_objects
                                     where object_name like 'PLAN_TABLE%'
                                    

                                    make sure that only sys is the owner of the plan table other schemes has the SYNONYM word in the OBJECT_TYPE column, if not, then the plan_table might have transfered during the migration or whenever. You have to drop this table for all the schemes except the SYS schema.

                                    SQL> drop table someschema.plan_table;
                                    

                                    From now the explain plan will use the temporary PLAN_TABLE$ table via the public PLAN_TABLE synonym, do not drop these objects.