6 Replies Latest reply: May 27, 2013 8:24 AM by amin_adatia RSS

    cache lock inserts into table with partitions

    amin_adatia
      11.2.0.3.0 (Exadata X3)
      I have a table with partitions on partition_date and data_type with 32 subpartitions on hash(Identifier).

      -- Update --

      There is a BLOB column in the table and no segment storage exists before I do the insert.
      There is a local index on partition_date and data_type;
      --

      I am trying to run multiple jobs which do

      insert into <partitioned table>
      select * from <queue_table>

      I have different queue_tables per partition_date and each insert only works with one date data

      I am getting row cache lock and library cache lock events and some jobs failed with

      ORA-04021: timeout occurred while waiting to lock object

      Do I have to insert <partitioned_table> partition (partition_name which is composite of all the subpartitions) ?

      Edited by: amin_adatia on May 23, 2013 10:45 AM
        • 1. Re: cache lock inserts into table with partitions
          sb92075
          amin_adatia wrote:
          11.2.0.3.0 (Exadata X3)
          I have a table with partitions on partition_date and data_type with 32 subpartitions on hash(Identifier)

          I am trying to run multiple jobs which do

          insert into <partitioned table>
          select * from <queue_table>

          I have different queue_tables per partition_date and each insert only works with one date data

          I am getting row cache lock and library cache lock events and some jobs failed with

          ORA-04021: timeout occurred while waiting to lock object

          Do I have to insert <partitioned_table> partition (partition_name which is composite of all the subpartitions) ?
          04021, 00000, "timeout occurred while waiting to lock object %s%s%s%s%s"
          // *Cause:  While waiting to lock a library object, a timeout occurred.
          // *Action: Retry the operation later.

          http://docs.oracle.com/cd/E11882_01/server.112/e16638/glossary.htm#PFGRF95044

          Library Cache has NO relationship to any table object
          • 2. Re: cache lock inserts into table with partitions
            sulimo
            Does table have any non indexed FK?
            • 3. Re: cache lock inserts into table with partitions
              amin_adatia
              There is a local index on partition_date and data_type and there is BLOB column in the table

              Digging into the process via SQL*Developer, I saw pop by an sql
              "select fragobj#, indfragobj#, ts#, file# .. from lobfrag$ where parentobj# = :1 order by frad#

              there was another for deferred segments I think but went by too fast to be sure
              • 4. Re: cache lock inserts into table with partitions
                amin_adatia
                The issue almost went away after adding a sort on the select from the <queue_table>

                insert into <partition_table>
                select from the <queue_table>
                order by
                data_type
                ,ORA_HASH(<column>,31,0)+1)

                Now I get inserts happening within 15 minutes as opposed to about 45 minutes **and** I get not lock errors!
                • 5. Re: cache lock inserts into table with partitions
                  jjk
                  amin_adatia wrote:
                  The issue almost went away after adding a sort on the select from the <queue_table>
                  what made you determine to add a sort ?
                  • 6. Re: cache lock inserts into table with partitions
                    amin_adatia
                    Some of the SQL that was going by (monitoring the session in SQL Developer) seemed to indicate that there was something going on where Oracle had to determine which subpartition to use. So I figured that if the data came in sorted by whatever the ORA_HASH value was then there would be less " locking" while Oracle determines the subpartition.

                    Why Oracle needs to lock the table/partitions in order to find the subpartition I have no idea; looks like some SQL Server database code developer got into the act! :)