This content has been marked as final. Show 6 replies
amin_adatia wrote:04021, 00000, "timeout occurred while waiting to lock object %s%s%s%s%s"
18.104.22.168.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) ?
// *Cause: While waiting to lock a library object, a timeout occurred.
// *Action: Retry the operation later.
Library Cache has NO relationship to any table object
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
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! :)