1 2 Previous Next 19 Replies Latest reply on Aug 3, 2017 2:28 PM by Jonathan Lewis

    Oracle range partitions drop operation (with update indexes clause) is slow

    raulk89

      Hi

       

      Oracle linux 7.3

      11.2.0.4.0

       

      In one of my databases, there is a range partitioned table for logs, so that each month has its own partition.

      Recently gathered statistics, and table has 126 million rows, each partition has about 3-4 million rows.

      Table has 6 indexes (one is composite index, with 2 columns).

       

      So, when I drop these partitions I am using this statement. FYI, I have tried using parallel clause also, but I would say, that this does not make any difference. Each partition drop takes still up to 4 hours (3 to 4 hours most of the time).

      ALTER TABLE <OWNER>.<TABLE_NAME> DROP PARTITION <PARTITION_NAME> UPDATE INDEXES [PARALLEL N];
      

       

      Btw, how do I know, do I have global indexes or local indexes on that partitioned table..?

      Indexes are created like this, so basically does not specify local or global keyword, just wondering which one of these is default..?

      CREATE INDEX <OWNER>.<index_name> ON <owner>.<table_name> (<column_name>);
      

       

      Or could the problem be that my indexes are too fragmented, is it even possible this slows this operation down..?

       

      Regards

      Raul

        • 1. Re: Oracle range partitions drop operation (with update indexes clause) is slow
          JohnWatson2
          orclz> create table parts (c1 number) partition by hash (c1) partitions 4;
          
          
          Table created.
          
          
          orclz> create index i1 on parts(c1);
          
          
          Index created.
          
          
          orclz> select index_name,partitioned from user_indexes where table_name='PARTS';
          
          
          INDEX_NAME                     PAR
          ------------------------------ ---
          I1                             NO
          
          
          orclz>
          

           

           

          Your indexes are by default not partitioned. ie, they are global.

          1 person found this helpful
          • 2. Re: Oracle range partitions drop operation (with update indexes clause) is slow
            Martin Preiss

            without global indexes the drop of a partition should be a fast operation since it requires only an update of internal metadata: since it takes a lot of time it is very likely that global indexes are involved. I think you can remove "fragmentation" from the list of exlanations for this behaviour.

            • 3. Re: Oracle range partitions drop operation (with update indexes clause) is slow
              Hemant K Chitale

              The fact that your indexes are Global (which is the default because you don't specify LOCAL in the CREATE INDEX) means that Oracle has to update each of the 6 indexes by removing the reference to each of the rows in the table partitions being dropped.  It is these updates that take time.  As you are in 11g, if you don't specify UPDATE INDEXES, the indexes would become UNUSABLE  (12g has deferred index maintenance).  That is why it becomes necessary for you to issue the UPDATE [GLOBAL] INDEXES which takes a long time.

               

              Had the indexes been Local, Oracle could have simply dropped the corresponding index partitions (as a DDL) corresponding to the table partitions.

               

              Hemant K Chitale

              1 person found this helpful
              • 4. Re: Oracle range partitions drop operation (with update indexes clause) is slow
                AndrewSayer

                It doesn't sound as if you put much thought into the partitioning strategy if you didn't consider global / local indexes. I recommend you go through them and figure out what they should be.

                 

                Of course it will take time to update index entries for 4 million rows, and you'll probably find it's not doing a lot of good to your index if you are inserting into the table at the same time with indexed values similar to what you're removing.

                 

                If you were to upgrade to 12c then you'll find the operation is much quicker due to defferred maintenance, it will still happen just not when you execute the statement. https://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm

                 

                First thing would be to review your indexes, I would bet that some would love to be local.

                • 5. Re: Oracle range partitions drop operation (with update indexes clause) is slow
                  Hemant K Chitale

                  Before you jump to recreating the indexes as LOCAL(ly partitioned), review the application and usage by users / queries. Some of them may weill need Global Indexes.  Those would degrade if you switch to LOCAL indexes.

                   

                  Hemant K Chitale

                  • 6. Re: Oracle range partitions drop operation (with update indexes clause) is slow
                    raulk89

                    Andrew Sayer wrote:

                     

                    It doesn't sound as if you put much thought into the partitioning strategy if you didn't consider global / local indexes. I recommend you go through them and figure out what they should be.

                     

                    ....

                     

                    Yeah, actually I haven't put any thought of it, because they (global indexes on partitioned table) already were like this when I started as a dba, less than a year ago..

                    So I am just wondering if there is something to be done here..

                     

                    Just wondering, for example if I create local indexes, I suppose that each index (for each of the partitions) has unique name (text + some numbers at the end of sth sth..?

                     

                    Regards

                    Raul

                    • 7. Re: Oracle range partitions drop operation (with update indexes clause) is slow
                      raulk89

                      Application only inserts rows (logs) into this table, nothing more.

                      Few users daily query the table, for example to check what some application users have done in certain timeframe or something like that.

                       

                      But still, may "index rebuild online" would make any difference at all regarding to partions drop..?

                       

                      Regards

                      Raul

                      • 8. Re: Oracle range partitions drop operation (with update indexes clause) is slow
                        Martin Preiss

                        when you use partitioning Oracle takes care of the administrative details: you create a local index not for each partition but for the table and Oracle internally builds the corresponding index segments for the partitions. So the index has just one (global) name, but when you take a look at the corresponding segments you will see one index segment per table segment and each index segment has an internal name.

                         

                        Or to rephrase it a little bit: basically a partitioned table is similar to the thing that was there before partitioning had been introduced: the partition view - i.e. a bunch of tables of the same structure and a UNION ALL view on top of them. Each table has its one indexes but the user has just to query one object and has not to think about the details. Partitioning is much more sophisticated than that, but basically it is the same idea.

                        1 person found this helpful
                        • 9. Re: Oracle range partitions drop operation (with update indexes clause) is slow
                          Hemant K Chitale

                          Dropping the indexes and then recreating them as LOCAL would certainly make your DROP Partition zipping fast !

                          But :

                          1.  You need to assess the impact on existing usage of the application and database

                          2.  It will take time to create the indexes

                          3.  If any of the indexes are unique and you need to maintain them, the partition key should be a subset of the index column(s)

                          4. You need to plan for the downtime to rebuild the indexes

                          5. TEST TEST TEST your scripts and the impact of the LOCAL indexes on your application and database before you do anything in Production

                           

                          Hemant K Chitale

                          • 10. Re: Oracle range partitions drop operation (with update indexes clause) is slow
                            DeepC

                            raulk89 wrote:

                             

                            Application only inserts rows (logs) into this table, nothing more.

                            Few users daily query the table, for example to check what some application users have done in certain timeframe or something like that.

                             

                            But still, may "index rebuild online" would make any difference at all regarding to partions drop..?

                             

                            Regards

                            Raul

                            You can drop partition and then rebuild indexes online - but there will be an execution gap between drop and rebuild - in 12c you can rebuild indexes later by Asynchronous index maintenance https://docs.oracle.com/database/121/VLDBG/GUID-087B87A6-959A-40C6-82AF-36E401FD089B.htm#VLDBG14107.

                            Global index on partition table is a overhead- so, I think first you should check the definition of these indexes and if any/all indexes having partition key as a column then you can convert all these indexes as local by excluding the partition key.

                            • 11. Re: Oracle range partitions drop operation (with update indexes clause) is slow
                              Jonathan Lewis

                              If you are licensed to use the AWR and can identify the session that did one of the partition drops then query dba_hist_active_sess_history for the session that the drop to get a summary of where (roughly) the time was spent.

                               

                              select  event, count(*)

                              from     dba_hist_active_sess_history

                              where dbid = (select d.dbid from v$database d where rownum = 1)

                              and instance_number = (select i.instance_number from v$instance i where rownum = 1)

                              and snap_id = {some suitable value)

                              and session_id = {xxx}

                              and session_serial# = {xxx}

                              group by

                                   event

                              order by count(*)

                              ;

                               

                              Alternatively, if you can run a drop now then query v$active_session_history for the session.

                              If you can run a drop you could otherwise enable SQL trace including wait states for the session.

                               

                              Possible time figures might be:

                              a) time spent waiting for an exclusive lock on the partition before the drop TM enqueue mode 6

                              b) time waiting for flushing of any dirty index blocks - enq: RO - fast object reuse, enq: CR - block range reuse ckpt

                              c) db file sequential read waits as the indexes are read for updates

                              d) time waiting for log file syncs

                               

                              Global indexes subject to the activity you describe CAN be subject to severe degradation in "the past", so that the index maintence of dropping an old partition does a very large number of single block reads and updates - possibly as much as half of each index. It is worth checking if your indexes are in a very bad state.   You could let us know:

                              a) list of columns in each index with avg_col_len for each column

                              b) number of leaf blocks in each index

                              c) database block size

                               

                              We may be able to give you an idea of whether or not your indexes are VERY much larger than typical.

                               

                              Regards

                              Jonathan Lewis

                              • 12. Re: Oracle range partitions drop operation (with update indexes clause) is slow
                                raulk89

                                Hi, thanks, I managed to get this from dba_indexes view. At the moment it looks like this. Actually there are now 106 million rows, because I dropped a few partitions for past 2 days.

                                Block size 8kb

                                 

                                By the way, if I am not licenced for AWR, shouldn't I run any query against dba_hist_active_sess_history view..? I thought that I shouldn't use special AWR functions only and queryng from view is actually ok.

                                 

                                Regards

                                Raul

                                • 13. Re: Oracle range partitions drop operation (with update indexes clause) is slow
                                  Jonathan Lewis

                                  If you're not licensed for the diagnostic pack you can't query any of the data - dynamic views or data stored in the repository). So you're going to have to rely on Statspack, SQL Trace, v$session_event, v$sesstat, v$session_time_model (and system equivalents) to tell you what's going on with this truncate.

                                   

                                  I asked for the columns and average column lengths for the indexes, and the block size; without that information it's not possible to get an idea of the "reasonable" block size for your indexes. However, assuming an 8KB block size, you've got one index of 3M blocks for a table of 106M rows, which is 35 index entries per block - that's highly suggestive of a degenerate index because even at 50% empty space that would give you an average index entry of about 115 bytes (including overheads) which is a little unusual unless it has lots of columns or indexes a large varchar2().

                                   

                                  The index with 40 distinct values looks like is a complete waste of space - but maybe that's been created to avoid foreign key locking, in which case I'd still want to check if it's necessary.

                                   

                                  Regards

                                  Jonathan Lewis

                                  1 person found this helpful
                                  • 14. Re: Oracle range partitions drop operation (with update indexes clause) is slow
                                    raulk89

                                    Sory, my bad, here are the results you requested (if there are some more columns you would like to investigate, then please let me know):

                                     

                                    select c.table_name, c.column_name, c.data_type, c.data_length, c.num_distinct, c.density, c.num_nulls, c.num_buckets, c.char_col_decl_length, c.avg_col_len, c.char_length
                                        , i.index_name, i.blevel, i.leaf_blocks, i.distinct_keys, i.avg_leaf_blocks_per_key, i.avg_data_blocks_per_key, i.num_rows
                                    from dba_tab_columns c
                                    inner join dba_ind_columns ic on c.column_name = ic.column_name
                                        and c.table_name = ic.table_name
                                    inner join dba_indexes i on ic.index_name = i.index_name
                                        and c.owner = ic.table_owner
                                    where c.owner = 'AETADMIN' and c.table_name = 'LOGI' 
                                    order by c.table_name, i.index_name, c.column_name 
                                    
                                    
                                    

                                     

                                    And yes, this index with 40 distinct values seems like a complete waste..

                                     

                                    Regards

                                    Raul

                                    1 2 Previous Next