Oracle slots... what are they?

[dklloyd]

I have a process that is not moving any.. In the v$seesion_wait the process is waiting during a direct load read, first dba.
I have gathered the following from the oradoc site but what are the slots they are referring to and how can I identify the shortage on which tablespace or table?
Hope you can help.. thanks in advance....
ORADOC content....
direct path read
During Direct Path operations the data is asynchronously read from the database files. At some stage the session needs to make sure that all outstanding asynchronous I/O have been completed to disk. This can also happen if during a direct read no more slots are available to store outstanding load requests (a load request could consist of multiple I/Os).

Wait Time: 10 seconds. The session will be posted by the completing asynchronous I/O. It will never wait the entire 10 seconds. The session waits in a tight loop until all outstanding I/Os have completed.

Parameters:

descriptor address
This is a pointer to the I/O context of outstanding direct I/Os on which the session is currently waiting.

first dba
The dba of the oldest I/O in the context referenced by the descriptor address.

block cnt
Number of valid buffers in the context referenced by the descriptor address.

V$seesion_wait contents...
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
23 55233 direct path read file number 15 0000000F first dba 4777 000012A9 block cnt 1 1 0 7336 WAITING

 

[karluk]

My guess is that this refers to the so-called ITL (interested transaction list) slots in the Oracle block header. Those are the slots controlled by the initrans and maxtrans parameters. Oracle needs to keep track of which transactions are active within the block, and if it runs out of room to record this information, a transaction may need to wait until another finishes.

 

[kindus]

This event is waiting for some initial data block which is aparently locked. You can find the object which this event is wating using
select owner, segment_name, segment_type
from dba_extents where file_id = 15
and 4777 between block_id and (block_id + blocks);
hope it helps !!

 

[dklloyd]

Thanks for the responses on this problem. It's a bank holiday here and I wasn't sure if anyone would respond so quickly.
I ran the query suggested which returns:
sys, 13.2, temporary.
I can't see any lock_collisions or blocking... can your see what may be happening from this?

thanks again

dklloyd

 

[dklloyd]

FIXED!!!
great thanks to both of you. Indeed from investigating a bit further and using some of the SYBEX Performance SQL the temporary sort space was preventing the process to run any further. I increased the sort_area_size and the process has run through successfully for the 1st time.
Some of the SQL used was as follows:

to view the size of sort segment:
select tablespace_name, current_users, max_sort_blocks
from v$sort_segment;

To see who's using how much sort space:
select user, tablespace, blocks
from v$sort_usage
order by blocks;

From the above it was obvious that the process we were running was using all the above the amount specified in the init.ora parameter and needed more as was shown in the SQL provided by Kindus.