This discussion is archived
6 Replies Latest reply: May 27, 2013 8:24 AM by amin_adatia RSS

cache lock inserts into table with partitions

amin_adatia Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Does table have any non indexed FK?
  • 3. Re: cache lock inserts into table with partitions
    amin_adatia Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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! :)

Legend

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