Take a look at what is "read-only" in your system. If you can, then move it to a read-only tablespace that then does not need to be backed up as frequently

Tom

Give the link to SERVER reference
manual

Hope you had a great vacation.

I am trying to load a table with data from 7 different tables. When I run the Select statement (with the 7 table join) in SQLPLUS then I get he results within 7-10 mins. But when I use a cursor (with this Select statement) to insert into the table then the process becomes very slow. In 7 hours it has inserted only 300,000 records ( out of 30 million that it has to). The DBA tells that we have huge SGA (20 GB – 64 bit) and enough sort area . I just ran a query on V$SESSION_WAIT for my SID and goth following results

ADWP: SQL> select * from v$session_wait where sid = 50;

       SID       SEQ#
---------- ----------
EVENT
----------------------------------------------------------------
P1TEXT                                                                   P1
---------------------------------------------------------------- ----------
P1RAW
----------------
P2TEXT                                                                   P2
---------------------------------------------------------------- ----------
P2RAW
----------------
P3TEXT                                                                   P3
---------------------------------------------------------------- ----------
P3RAW             WAIT_TIME SECONDS_IN_WAIT STATE
---------------- ---------- --------------- -------------------
        50      13889
direct path read
file number                                                              27
000000000000001B
first dba                                                            104766
000000000001993E
block cnt                                                                 1
0000000000000001         -1              15 WAITED SHORT TIME


My question is what does this mean and how to get over it.

Thanks
 

Thanks for the response. I am going to run Tkprof. What I want to know is ---" what do the waits as they show up in the V$session_wait query Mean"

I am reading the about the locks but found the file that we have waits on was the REDO LOG. I have heard about NOLOGGING. Can we make the table nologging and then maybe the process will be faster

Hi Tom,

I did the following test on my development environment. I connected as the same user id in 2 different sessions.

Session 1
=========
UPDATE a
set col1 = 'Holding'
where col2 = 10

Session 2
=========
SELECT SID FROM V$SESSION WHERE AUDSID = USERENV('sessionid')

SID
===
299

UPDATE a
set col1 = 'Waiting'
where col2 = 10


I went back again to session 1 and did the query few times :

select seq#,sid,event,seconds_in_wait,state
from v$session_wait
where sid = 299 (which is the session id of waiting session
that I obtained as shown above)

To my surprise, I keep seeing records in the table with increasing seq# and a blank sid each time for the same wait event.

Output from First run of the query
==================================
795 null ENQUEUE 3 WAITING

Second run of the query (after a few seconds)
=============================================
800 null ENQUEUE 0 WAITING

1. ENQUEUE is occuring (because of session 1 holding the table). Is that correct?
2. Why SECONDS_IN_WAIT shows 3 in the first case and shows 0 in the second case with different seq#. My understanding of v$session_wait is that this will show only one entry and a STATE of WAITING with a event of ENQUEUE until the lock is released. Is that understanding not correct? If so, please explain
3. If this is the case, how can we use this view to track down waits and how do we figure out the latest wait information from this view for each wait event.

Tom,

1. The column you had mentioned in V$SESSION will give a total wait. Does this mean it will give a total wait of the session for all events that the session is currently waiting? This is because the definition of V$SESSION_WAIT is that the wait event time shows the wait times for sessions that are currently waiting of the last waited time.
2. If 1 is true, then how to find wait time for individual wait events from V$SESSION_WAIT.
3. I have read some materials on wait interface showing waits for a given session at a specific point in time. If this keeps changing, how are they able to take a snapshot of waits based on V$SESSION_WAIT. Also, pls. clarify that these documents suggest that you need to take wait event snapshots between 2 time intervals. In such a case should we use V$SESSION_EVENT view or V$SESSION_WAIT view to get the starting and ending snapshots.
4. Pls provide me info/script on how each wait events for a specific session are determined and listed and the correct method of using the wait interfaces. ie when to use v$system_event, v$session_event, v$session_wait etc

Hi,

You mentioned in response to the first question:

"Wait until a buffer becomes available. This event happens because a buffer is
either being read into the buffer cache by another session (and the session is
waiting for that read to complete) or the buffer is the buffer cache, but in a
incompatible mode (that is, some other session is changing the buffer)."

But why if the current session is a select and it needs a CR version of the block it can allocate onother block on the same chain right ? then why does it have to wait for the other session to complete reading into the block.

Hi,

I could have added this to my previous review. but anyway..

Is buffer lock handled in a queue like enqueue and not like latches.
If yes is there any data structues which exposes this queue.
I am asking this since I was looking at finding the blockers and waiters for buffer busy waits just like the utllockt.sql script for enqueues

so is there a way to find out which session is currently locking a particular block.
I am asking this because
I have a problem right now where smon is waiting for a block in c_obj# cluster from today morning. So that means any query against dba views based on tables in c_obj# cluester which does a full scan just hangs with buffer busy wait on that particular block.

SQL> SQL> SELECT PRS_ID, APP_ID, MIN(SIGNON_TS), MAX(SIGNON_TS), COUNT(1)                 
FROM GCA_DS_APP_SIGNON                                                           
WHERE FAILED_SIGNON_IND = '0'                                                    
GROUP BY PRS_ID,APP_ID 

      SID       SEQ# EVENT                                                                    P1 P1RAW            P1TEXT                                                            WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- --------------- -------------------
       141        745 db file scattered read                                                  211 00000000000000D3 file#                                                                     7               0 WAITED KNOWN TIME


SQL> SELECT /*+ index(GCA_DS_APP_SIGNON) */ PRS_ID, APP_ID, MIN(SIGNON_TS), MAX(SIGNON_TS), COUNT(1)                 
FROM GCA_DS_APP_SIGNON                                                           
WHERE FAILED_SIGNON_IND = '0'                                                    
GROUP BY PRS_ID,APP_ID


       SID       SEQ# EVENT                                                                    P1 P1RAW            P1TEXT                                                            WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- --------------- -------------------
       141       5335 db file sequential read                                                 209 00000000000000D1 file#                                                                     0               0 WAITING



Please prvide updates 

Great thread. I have been experiencing a blocker-blockee situation in Production with the following info from v$lock:

  1   select sid,type,lmode,request,block
  2     from backup.vlock_9Nov04
  3*   where sid in (25,609)
SQL> /

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
        25 TX          6          0          1
        25 TM          3          0          0
        25 TM          3          0          0
        25 TM          3          0          0
        25 TM          3          0          0
        25 TM          3          0          0
       609 TX          6          0          0
       609 TM          3          0          0
       609 TM          3          0          0
       609 TM          3          0          0
       609 TM          3          0          0

       SID TY      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ----------
       609 TM          3          0          0
       609 TM          3          0          0
       609 TX          0          4          0

14 rows selected.

So SID 25 is blocking SID 609, its waiting for a shared TX enqueue. 

- The SQL for SID 25 will update one row in a table. SID 609 has accessed that table and has moved on to other things, but I'm 100% sure it hasn't touched the row SID 25 is trying to update (its not a row lock anyway)
- INITRANS for the table is 2, MAXTRANS is 255 and there's no FREELIST clause in the table script.
- The table is partitioned, has a MV built on it, and contains about 10 million rows.
- I have also taken backups of v$session and v$session_wait.

Please suggest how I can troubleshoot further?  

HI TOM,

MY V$WAITSTAT HAS BEEN SHOWING SEGMENT HEADER 2.
BUT MY V$SESSION_WAIT NOT SHOWING ANY BUFFER BUSY WAITS.

A QUERY TO IDENTIFY THE SEGMENTS AND SEGMENT TYPES CONTENDED IN BUFFER CACHE SHOWN IN DOCS LIKE THIS

SELECT segment_owner, segment_name
FROM DBA_EXTENTS
WHERE file_id = <&p1>
AND <&p2> BETWEEN block_id AND block_id + blocks - 1;

V$SESSION_WAIT GIVING MY SESSION INFO ONLY.BUT HOW TO IDENTIFY P1 ,P2 AND P3 FOR HIGHEST RESOUCE USING SESSIONS.

THANKS IN ADVANCE

hi tom.
sorry for capslock on.

How to find the contended segments in db cache?
could you give me step by step procedure.
MY db having 100 sessions all the time.if i logged in as sysdba ,then how to find events and values (p1,p2,p3) of other sessions.(v$session_wait shows events for that session itself)

thanks ,

Confirmation of the blocker-blockee... 

  1  select (select substr(program,1,12) from backup.vsession_9nov04 where sid=a.sid) blocker,
  2         a.sid,
  3         'is blocking',
  4         (select substr(program,1,12) from backup.vsession_9nov04 where sid=b.sid) blockee,
  5         b.sid
  6    from backup.vlock_9Nov04 a, backup.vlock_9Nov04 b
  7   where a.block=1
  8     and b.request>0
  9     and a.id1=b.id1
 10*    and a.id2=b.id2
SQL> /

BLOCKER            SID 'ISBLOCKING BLOCKEE            SID
------------ --------- ----------- ------------ ---------
ServConsole1        25 is blocking ServResponse       609


Here's the app logic, I have tried to make it short!
The app is a telecom billing system.
ServConsole1 generates flat files that are sent to a switch mediation software. Its primary input is from a table CC_ServiceProvisionOrder, in which requests are inserted by other components of the billing system. 

At the time of the block, SID 25 - ServConsole1 - had just completed processing for ~ 10,000 requests. The processing logic is:
- push unprocessed requests into a scratch table: insert into SO_ServiceProvisionOrder select * from CC_ServiceProvisionOrder where servconsoleid=1 and status=0 
- indicate the above requests are being processed: update CC_ServiceProvisionOrder set status=1 where requestid in (...)
- commit
- issue selects against the db to get required data
- write data to flat files
- delete from SO_ServiceProvisionOrder; commit
- Poll for new requests in CC_ServiceProvisionOrder.

ServResponse - SID 609 - polls for responses from the mediation software (flat files again) and updates the status in CC_ServiceProvisionOrder. It was stuck executing the following update:
update cc_serviceprovisionorder
   set status            = :ph4,
       provisiondatetime = :ph5,
       failurereasoncode = :ph6
 where requestid = :ph7 

RequestId is the primary key for CC_ServiceProvisionOrder.

Here's a dump of v$lock, v$session_wait, v$session for the two SIDs. I can post / email them as insert statements if that helps.

How can I find what session 609 is _really_ waiting for?

::::::::::::::::: v$lock ::::::::::::::::: 
  1    select * from backup.vlock_9Nov04 
  2     where sid in (25,609);

ADDR     KADDR          SID TY       ID1       ID2     LMODE   REQUEST     CTIME     BLOCK
-------- -------- --------- -- --------- --------- --------- --------- --------- ---------
01AC14A8 01AC1574        25 TX    131120     81763         6         0       749         1
D28CFDE4 D28CFDF8        25 TM      3791         0         3         0       749         0
D28CE23C D28CE250        25 TM      3956         0         3         0       749         0
D28CBE9C D28CBEB0        25 TM   1836236         0         3         0       749         0
D28CBADC D28CBAF0        25 TM     13731         0         3         0       749         0
D28CB884 D28CB898        25 TM      3792         0         3         0       749         0
01AC14A8 01AC1574       609 TX   1310740     79817         6         0       565         0
D28CE764 D28CE778       609 TM      3792         0         3         0       565         0
D28CE50C D28CE520       609 TM      3956         0         3         0       565         0
D28CE494 D28CE4A8       609 TM      3790         0         3         0       565         0
D28CD60C D28CD620       609 TM   1836236         0         3         0       565         0
D28CD3B4 D28CD3C8       609 TM      3793         0         3         0       565         0
D28CC4B4 D28CC4C8       609 TM      3789         0         3         0       565         0
D6DBEE54 D6DBEE64       609 TX    131120     81763         0         4       565         0

14 rows selected.

::::::::::::::::: v$session_wait ::::::::::::::::: 

select sid, seq#, substr(event,1,12) event, 
       substr(p1text,1,10) p1text, p1, p1raw, 
       substr(p2text,1,10) p2text, p2, p2raw, 
       substr(p3text,1,10) p3text, p3, p3raw, 
       wait_time w1, seconds_in_wait w2, state
  from backup.vsesstionwait_9Nov04 
 where sid in (25,609);

SID    SEQ#    EVENT        P1TEXT        P1        P1RAW        P2TEXT    P2    P2RAW        P3TEXT    P3    P3RAW        W1    W2    STATE
609    8556    enqueue        name|mode    1415053316    54580004    id1    131120    00020030    id2    81763    00013F63    0    660    WAITING
25    1969    SQL*Net mess    driver id    1413697536    54435000    #bytes    1    00000001        0    00        0    0    WAITING


::::::::::::::::: v$session ::::::::::::::::: 

select saddr, sid, serial#, audsid, paddr, 
       user#, substr(username,1,10) username, command, ownerid, 
       taddr, lockwait, status, server, 
       schema#, substr(schemaname,1,10) schemaname, 
       substr(osuser,1,5) osuser, process, substr(machine,1,10) machine, substr(terminal,1,10) terminal, program, type, 
       sql_address, sql_hash_value, prev_sql_addr, prev_hash_value, 
       substr(module,1,10) module, module_hash, substr(action,1,10) action, action_hash, substr(client_info,1,10) client_info, fixed_table_sequence, 
       row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, 
       logon_time, last_call_et, 
       pdml_enabled, failover_type, failover_method, failed_over, 
       resource_consumer_group, pdml_status, pddl_status, pq_status
  from backup.vsession_9Nov04 
 where sid in (25,609);
 
SADDR    SID    SERIAL#    AUDSID    PADDR    USER#    USERNAME    COMMAND    OWNERID    TADDR    LOCKWAIT    STATUS    SERVER    SCHEMA#    SCHEMANAME    OSUSER    PROCESS    MACHINE    TERMINAL    PROGRAM    TYPE    SQL_ADDRESS    SQL_HASH_VALUE    PREV_SQL_ADDR    PREV_HASH_VALUE    MODULE    MODULE_HASH    ACTION    ACTION_HASH    CLIENT_INFO    FIXED_TABLE_SEQUENCE    ROW_WAIT_OBJ#    ROW_WAIT_FILE#    ROW_WAIT_BLOCK#    ROW_WAIT_ROW#    LOGON_TIME    LAST_CALL_ET    PDML_ENABLED    FAILOVER_TYPE    FAILOVER_METHOD    FAILED_OVER    RESOURCE_CONSUMER_GROUP    PDML_STATUS    PDDL_STATUS    PQ_STATUS
D6B4DCC0    25    25358    6328072    D6AB81F4    77    UNIPROD    0    2147483644    D29B9F24    null    INACTIVE    DEDICATED    77    UNIPROD    uat    27513    PROCRATE    null    ServConsole1@PROCRATE (TNS V1-V3)    USER    00    0    CDC21E18    2585836462    null    0    null    0    null    2756024    -1    0    0    0    11/5/04 10:11:39 AM    0    NO    NONE    NONE    NO    DEFAULT_CONSUMER_GROUP    DISABLED    ENABLED    ENABLED
D6C81320    609    34468    6330449    D6ABA10C    77    UNIPROD    6    2147483644    D29BBE80    D6DBEE64    ACTIVE    DEDICATED    77    UNIPROD    uat    7994    PROCRATE    pts/13    ServResponse@PROCRATE (TNS V1-V3)    USER    CEF6AD54    75272065    00    0    null    0    null    0    null    2755724    -1    0    0    0    11/7/04 9:40:43 PM    573    NO    NONE    NONE    NO    DEFAULT_CONSUMER_GROUP    DISABLED    ENABLED    ENABLED

 

pg 119 of your book: "ID1 colm for a TM lock is the object id of the DML-locked object". Guess I needed to turn that page, the answer was right there!

So, the objects are
for SID 609 (blockee)
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
SO_PREREPLYDETAILS 3789
SO_PREREPLYNETSTATUS 3790
SO_REPLYDETAILS 3792
SO_REPLYNETSTATUS 3793
CC_SERVICEPROVISIONORDER 3956
CC_SERVICEPROVISIONORDER CC_SERVICEPROVISIONORDER0411 1836236
as expected. CC_ServiceProvOrder is partitioned, and 0411 is the partition for Nov 2004. Fine.

for SID 25 (blocker)
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID
SO_PREREQUESTDETAILS 3791
SO_REPLYDETAILS 3792
CC_SERVICEPROVISIONORDER 3956
===> MLOG$_CC_SERVICEPROVISIONO 13731
CC_SERVICEPROVISIONORDER CC_SERVICEPROVISIONORDER0411 1836236

Could it be that the MV refresh is causing the session to hang? If so, how can I determine this for sure?

Thanks so much Tom.

The MV is on CC_ServiceProvisionOrder.

This blocking lock came up after a process inserted some 10,000 rows into the table, in one bulk operation. The MV refreshes every hour. My guess is that the "other process" - servresponse - was blocked for the duration of the MV refresh.

How do I go about "proving" the "guess". the MV was created by my customer's DBA and he (DBA) says there is no way to determine how long a snapshot refresh took.

Never say never but I'm 99.99% certain that it cannot be both processes trying to update the same row. This table is a queueing mechanism, servconsole1 "creates" a request and servresponse "updates" the status of a processed request. It typically takes 3-5 minutes for a request to be "processed", and these processes have a poll interval of one minute.

Hi Tom,

I am currently reading the Oracle Press book on Oracle tuning using Oracle Wait Interface authored by Richmond Shee, Kirtikumar Deshpande and Gopalakrishnan. In this book, the authors have given a sample but an incomplete stored procedure explaining how to use the v$session_wait view to get wait event data at specific frequencies. However, after reading the discussion above I understand that for certain wait events (such as "enqueue" in your eg. above), the SECONDS_IN_WAIT column could be 0 when the wait timed-out and the process start waiting again. If that is the case, how can V$SESSION_WAIT be used to collect wait event data for specific periods of time.
1. Is my understanding correct.
2. If so, how can they use V$SESSION_WAIT to gather wait-event data. Should they not use V$SESSION_EVENT as indicated by you?

Please advise

Hi Tom,

I need your help in understanding the three columns in V$SESSION_WAIT. I tried to gather information from Oracle manuals and other performance tuning books and I am not clear as how these work together. I need the following info :

1. What does SECONDS_IN_WAIT and WAIT_TIME represent and when each of them is relavant. ie which column to look for based on different STATE column values
2. I remember reading in one of your threads that SECONDS_IN_WAIT will be reset to zero each time the process times out and waits again. Is my understanding correct. If so, how can this view be used to take delta timings. In such a case we should use V$SESSION_EVENT. Right?
3. How to interpret the values in two columns.
An example would be very helpful for me and I would appreciate if you could illustrate the significance of these two columns by an example. I am trying to spot out some performance problems in my development database and trying to use these views but the results I look after querying these views are not clear to me.

Thanks

it is not cumulative like that over waits.

it only works for a long blocking wait.  


lets say you were blocked on a lock, for example:

ops$tkyte@ORA9IR2> create table t ( x int );
                                                                                           
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1 );
                                                                                           
1 row created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> update t set x = 1;
 
1 row updated.


<b>do that in one session, in another:</b>

ops$tkyte@ORA9IR2> select sid from v$mystat where rownum = 1;
 
       SID
----------
         9
 
ops$tkyte@ORA9IR2> update t set x = x;
 
<b>that'll block, in the first session issue:</b>

ops$tkyte@ORA9IR2> select event, wait_time, seconds_in_wait from v$session_wait
  2  where sid = 9;
 
EVENT                                                             WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT
---------------
enqueue                                                                   0
             25

....
<b>
seconds in wait will increase. In 3 second increments (that is the timeout for an enqueue).

Now, in that session -- commit, and rerun the wait query...</b>


ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> select event, wait_time, seconds_in_wait from v$session_wait
  2  where sid = 9;
 
EVENT                                                             WAIT_TIME
---------------------------------------------------------------- ----------
SECONDS_IN_WAIT
---------------
SQL*Net message from client                                               0
              3
 

<b>we are in the next wait event (the enqueue is gone...)</b>

it is basically for just the current wait.  it is not cumulative.


You want to trace an application?  use the 10046 level 12 trace and tkprof.... it'll all be there statement by statement.
 

Tom,

I read somewhere about the description of V$SESSION_WAIT. The description states that "V$SESSION_WAIT contains one row for each oracle process. It lists the columns in that table and subsequently mentions that SID is the oracle process".
1. Does that mean that this view contains only 1 row for each connected session? ie. does that mean for each connected session, there can be only 1 row in this table at any point of time.

Please clarify

Tom,

I read somewhere about the description of V$SESSION_WAIT. The description states that "V$SESSION_WAIT contains one row for each oracle process. It lists the columns in that table and subsequently mentions that SID is the oracle process".
1. Does that mean that this view contains only 1 row for each connected session? ie. does that mean for each connected session, there can be only 1 row in this table at any point of time.

Please clarify

Hopefully this isn't too far off topic - it does reference v$session_wait, but certainly it's not really a review... however since in the years I have been viewing asktom on an almost daily basis I have never seen anything other than "Sorry I have a large backlog now", and since I think it's possible that these queries might be useful, I'm going to post anyway.

Perhaps naively I think that the queries below will reveal useful things about the specified session, producing a sort of resource profile useful for saying things like - "well, your session didn't wait much on enqueues" or "sheez look at all the time you spent on 'global cache cr request'". It's not a substitute for 10046, and I imagine it's subject to various types of numeric strangeness, still I wonder what you think of this type of query for checking running sessions.

It appears that v$session_event (9.2.0.4) doesn't include figures for the current wait, so if the session has been waiting for 3 hours on sqlnet message from client, those 3 hours aren't present in v$session_event. Or so it appears - when I include both v$session_wait and v$session_event I get more believeable figures for unaccounted_for time.

select :sid, username, osuser,
cpu_time cpu_seconds,
time_waited wait_seconds,
elapsed elapsed_seconds,
elapsed - time_waited - cpu_time
unaccounted_for_time
from
( select username, osuser, logon_time,
( sysdate - logon_time ) * 24 * 60 * 60 elapsed
from v$session where sid = :sid
) el,
( select sum(time_waited) time_waited
from
( select time_waited/100 time_waited
from v$session_event where sid = :sid
union all
select seconds_in_wait
from v$session_wait
where sid = :sid and wait_time = 0
)
) t,
( select value / 100 cpu_time
from v$sesstat t, v$statname n
where n.name = 'CPU used by this session'
and n.statistic# = t.statistic#
and sid = :sid
) c
/

select sid, event, sum(time_waited) waited_seconds,
sum(total_waits) num_waits, sum(average_wait) avg_wait,
sum(total_timeouts) num_timeouts,
to_char(100 * ratio_to_report(sum(time_waited)) over (),'999.999') pct_contrib
from
( select 'e' whence, sid, event, total_waits, average_wait/100 average_wait, total_timeouts, time_waited / 100 time_waited
from v$session_event where sid = :sid
union all
select 'w', sid, event, 1, seconds_in_wait, 0, seconds_in_wait
from v$session_wait where sid = :sid and wait_time = 0
union all
select 'c', sid, name, 1, value/100, 0, value /100
from v$sesstat s, v$statname n
where s.sid = :sid and s.statistic# = n.statistic# and n.name = 'CPU used by this session'
) group by sid, event order by sum(time_waited)
/

Ultimately I would like to include unaccounted for time
in the profile, just haven't had the time yet. Any comments would be appreciated.

While updating one of the application tables with high number of concurrency(update) we started seeing TX wait on I_OBJ# and C_OBJ# (object id 2 and 3) as below.

select * from gv$lock where request !=0;
   INST_ID      SID TY        ID1        ID2      LMODE    REQUEST
CTIME      BLOCK
---------- -------- -- ---------- ---------- ---------- ----------
---------- ----------
         1       50 TX    2424846          2          0          6
  519          0
         1      116 TX    7733248          2          0          6
    4          0
         1       24 TX    7667720          3          0          6
   16          0
         1      110 TX    7798790          3          0          6
    5          0

The above lock count increases when concurrency increases.

SQL> select  id2,count(*) from gv$lock where lmode=6 and id2 in (2,3)
group by id2;

       ID2   COUNT(*)
---------- ----------
         2        132
         3        132

132, is it a limit to acquire number of TX locks on I_OBJ# and C_OBJ#?

Thanks in advance.
Sami  

Hi,

Afaik, with TX6 lock and id2 <> 0, id1/id2 give transaction id and not object id; and when id2=0, this is id1 (not id2) that gives the object id.
Your first query shows a standard pattern of 4 sessions waiting to update 4 rows hold by 4 other transactions (but maybe something else).

You second query does not filter on lock type, so it is hard to say if it refers to TX lock or other ones.

Regards
Michel

doh, didn't even really look at that part - you are correct.  I got hung up on their comments about the cluster and cluster index.

<quote src=Expert Oracle: Database Architecture>

First, let’s start a transaction (if you don’t have a copy of the DEPT table, simply make one using a CREATE TABLE AS SELECT ):

ops$tkyte@ORA10G> update dept set deptno = deptno+10;
4 rows updated.

Now, let’s look at the state of the system at this point. This example assumes a single-user system; otherwise, you may see many rows in V$TRANSACTION. Even in a single-user system, do not be surprised to see more than one row in V$TRANSACTION, as many of the background Oracle processes may be performing a transaction as well.

ops$tkyte@ORA10G> select username,
  2         v$lock.sid,
  3         trunc(id1/power(2,16)) rbs,
  4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
  5         id2 seq,
  6         lmode,
  7         request
  8  from v$lock, v$session
  9  where v$lock.type = 'TX'
 10    and v$lock.sid = v$session.sid
 11    and v$session.username = USER;
 
USERNAME   SID RBS SLOT    SEQ LMODE REQUEST
--------- ---- --- ---- ------ ----- -------
OPS$TKYTE  145   4   12  16582     6       0
 
ops$tkyte@ORA10G> select XIDUSN, XIDSLOT, XIDSQN
  2    from v$transaction;
 
    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         4         12      16582

The interesting points to note here are as follows:
    *    The LMODE is 6 in the V$LOCK table and the request is 0. If you refer to the definition of the V$LOCK table in Oracle Server Reference manual, you will find that LMODE=6 is an exclusive lock. A value of 0 in the request means you are not making a request; you have the lock.
    *    There is only one row in this table. This V$LOCK table is more of a queuing table than a lock table. Many people expect there would be four rows in V$LOCK since we have four rows locked. What you must remember, however, is that Oracle does not store a master list of every row locked anywhere. To find out if a row is locked, we must go to that row.
    *    I took the ID1 and ID2 columns and performed some manipulation on them. Oracle needed to save three 16-bit numbers, but only had two columns in order to do it. So, the first column ID1 holds two of these numbers. By dividing by 2^16 with trunc(id1/power(2,16)) rbs, and by masking out the high bits with bitand(id1,to_number('ffff','xxxx'))+0 slot, I am able to get back the two numbers that are hiding in that one number.
    *    The RBS, SLOT, and SEQ values match the V$TRANSACTION information. This is my transaction ID.

Now we’ll start another session using the same username, update some rows in EMP, and then try to update DEPT:

ops$tkyte@ORA10G> update emp set ename = upper(ename);
14 rows updated.
 
ops$tkyte@ORA10G> update dept set deptno = deptno-10;

We’re now blocked in this session. If we run the V$ queries again, we see the following:

ops$tkyte@ORA10G> select username,
  2         v$lock.sid,
  3         trunc(id1/power(2,16)) rbs,
  4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
  5         id2 seq,
  6         lmode,
  7         request
  8  from v$lock, v$session
  9  where v$lock.type = 'TX'
 10    and v$lock.sid = v$session.sid
 11    and v$session.username = USER;
 
USERNAME   SID RBS SLOT    SEQ LMODE REQUEST
--------- ---- --- ---- ------ ----- -------
OPS$TKYTE  144   4   12  16582     0       6
OPS$TKYTE  144   5   34   1759     6       0
OPS$TKYTE  145   4   12  16582     6       0
 
ops$tkyte@ORA10G> select XIDUSN, XIDSLOT, XIDSQN
  2    from v$transaction;
 
    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         5         34       1759
         4         12      16582

What we see here is that a new transaction has begun, with a transaction ID of (5,34,1759). Our new session, SID=144, has two rows in V$LOCK this time. One row represents the locks that it owns (where LMODE=6). It also has a row in there that shows a REQUEST with a value of 6. This is a request for an exclusive lock. The interesting thing to note here is that the RBS/SLOT/SEQ values of this request row are the transaction ID of the holder of the lock. The transaction with SID=145 is blocking the transaction with SID=144. We can see this more explicitly simply by doing a self-join of V$LOCK:

ops$tkyte@ORA10G> select
  2        (select username from v$session where sid=a.sid) blocker,
  3         a.sid,
  4        ' is blocking ',
  5         (select username from v$session where sid=b.sid) blockee,
  6             b.sid
  7    from v$lock a, v$lock b
  8   where a.block = 1
  9     and b.request > 0
 10     and a.id1 = b.id1
 11     and a.id2 = b.id2;
 
BLOCKER    SID 'ISBLOCKING'  BLOCKEE    SID
--------- ---- ------------- --------- ----
OPS$TKYTE  145  is blocking  OPS$TKYTE  144

Now, if we commit our original transaction, SID=145, and rerun our lock query, we find that the request row has gone:

ops$tkyte@ORA10G> select username,
  2         v$lock.sid,
  3         trunc(id1/power(2,16)) rbs,
  4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
  5         id2 seq,
  6         lmode,
  7         request
  8  from v$lock, v$session
  9  where v$lock.type = 'TX'
 10    and v$lock.sid = v$session.sid
 11    and v$session.username = USER;
 
USERNAME   SID RBS SLOT    SEQ LMODE REQUEST
--------- ---- --- ---- ------ ----- -------
OPS$TKYTE  144   5   34   1759     6       0
 
ops$tkyte@ORA10G> select XIDUSN, XIDSLOT, XIDSQN
  2    from v$transaction;
 
    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         5         34       1759

The request row disappeared the instant the other session gave up its lock. That request row was the queuing mechanism. The database is able to wake up the blocked sessions the instant the transaction is completed. There are infinitely more “pretty” displays with various GUI tools, but in a pinch, having knowledge of the tables you need to look at is very useful.
</quote> 

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Dec 27 13:40:03 2005 
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> show parameter undo
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     36000
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS1
SQL> 


-------------------------------------------------
$ cat run_all_w_sleep.sh
#!/bin/ksh
CNT=1
while [ $CNT -lt 51 ]
do
   echo "running $CNT"
   run_once_w_sleep.sh &
   let CNT=CNT+1
done
------------------------------------------------- 

$ cat run_once_w_sleep.sh
#!/bin/ksh
sqlplus -s /nolog <<!
set echo off
set feedback off
CONNECT rep1/rep1
begin
/*
The below FOR LOOP cursor picks up one PRIMARY KEY randomly. This key will be used to update profileduser table
*/
for c0rec in (
select profileduserid from (
select rownum rid,userid,profileduserid
             from profileduser
                    where userid like 'PMT_DBA_%'
                    and rownum <= round(dbms_random.value*100000)
)
where rid > round(dbms_random.value*100000)
  and profileduserid not in (select substr(what,9) from log_table)
and rownum <=1
   )
   loop
update profileduser set last_successful_login =sysdate where profileduserid=c0rec.profileduserid;
 insert_log(c0rec.profileduserid); --AUTONOMOUS STORED PROCEDURE TO LOG SESSION INFO
end loop;
dbms_lock.sleep(10000);
end;
/
rollback;
!
 

-------------------------------------------------

$ cat proc_insert_log.sql
CREATE OR REPLACE PROCEDURE insert_log(p_profileduserid profileduser.profileduserid%TYPE) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  INSERT INTO log_table VALUES (sysdate,'Updated '||p_profileduserid, (select sid from V$session where audsid = userenv('sessionid')), (select serial# from V$session where audsid = userenv('sessionid')));
  COMMIT;
END;
/
-------------------------------------------------


$ run_all_w_sleep.sh                   
running 1
Connected.
running 2
Connected.

...

running 49
Connected.
running 50
Connected.

While these 50 uncommited transactions are waiting in 50 separate sessions, I did run the following query.
 
SQL>  @?/rdbms/admin/utllockt.sql
WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD      LOCK_ID1          LOCK_ID2
----------------- ----------------- -------------- -------------- ----------------- -----------------
394               None
   277            Transaction       Exclusive      Exclusive      7864354           9
 
 
Table dropped.
 
SQL> set pages 200 lines 130
SQL> select * from gV$lock where sid in (394,277);
 
   INST_ID ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
         1 0000000397605900 0000000397605928        277 TM       6878          0          3          0        400          2
         1 00000003931CF6F8 00000003931CF718        277 TX    7864354          9          0          6        400          0
         1 00000003977E2E98 00000003977E3010        394 TX    7864354          9          6          0        400          1
         1 0000000397605D98 0000000397605DC0        394 TM       3877          0          3          0        400          2
         1 0000000397605B40 0000000397605B68        394 TM       3885          0          3          0        400          2
         1 00000003976056C0 00000003976056E8        394 TM       6878          0          3          0        401          2
 
6 rows selected.
 
SQL> 


  1* select * from gv$session_wait where sid in (394,277)
SQL> /
 
   INST_ID        SID       SEQ# EVENT
---------- ---------- ---------- ----------------------------------------------------------------
P1TEXT                                                                   P1 P1RAW
---------------------------------------------------------------- ---------- ----------------
P2TEXT                                                                   P2 P2RAW
---------------------------------------------------------------- ---------- ----------------
P3TEXT                                                                   P3 P3RAW             WAIT_TIME SECONDS_IN_WAIT STATE
---------------------------------------------------------------- ---------- ---------------- ---------- --------------- -------------------
         1        277       2167 enqueue
name|mode                                                        1415053318 0000000054580006
id1                                                                 7864354 0000000000780022
id2                                                                       9 0000000000000009          0            1075 WAITING
 
         1        394         48 PL/SQL lock timer
duration                                                            1000000 00000000000F4240
                                                                          0 00
                                                                          0 00                        0            1075 WAITING
 
 


SQL> select sid,sql_hash_value,taddr,status from v$session where sid in (394,277);
 
       SID SQL_HASH_VALUE TADDR            STATUS
---------- -------------- ---------------- --------
       277      123561006 00000003977E2840 ACTIVE     <=== I don't see this TADDR in v$transaction table.
       394     1049750310 00000003977E2E98 ACTIVE

SQL> select sql_text from V$sql where hash_value='123561006';
 
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
UPDATE PROFILEDUSER SET LAST_SUCCESSFUL_LOGIN =SYSDATE WHERE PROFILEDUSERID=:B1
 
SQL>  select sql_text from V$sql where hash_value='1049750310';
 
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
begin for c0rec in ( select profileduserid from ( select rownum rid,userid,profileduserid              from profileduser
 where userid like 'PMT_DBA_%'                     and rownum <= round(dbms_random.value*100000) ) where rid > round(dbms_random.value*10000
0)   and profileduserid not in (select substr(what,9) from log_table) and rownum <=1    )    loop update profileduser set last_successful_lo
gin =sysdate where profileduserid=c0rec.profileduserid;  insert_log(c0rec.profileduserid); end loop; dbms_lock.sleep(10000); end;
 
 

 
SQL> select addr,xidusn,XIDSLOT,XIDSQN from v$transaction where addr in (select taddr from v$session where sid in (394,277));
 
ADDR                 XIDUSN    XIDSLOT     XIDSQN
---------------- ---------- ---------- ----------
00000003977E2E98        120         34          9
 
 

Even after so much investigation(going thru documents and forums) I could not figure out why SID 277 is being blocked by SID 394?

Are both sessions trying to use the same UNDO Segment and Slot? 

Dear Tom,

I don't see any "row lock waits". Also I am making sure that duplicate value are not picked up by the cursor (though I use random). My "autonomous procedure" records the KEY value and at the end of the test I confirmed that no duplicate keys in the log_table.

Looks like I am facing the issue mentioned below.

https://asktom.oracle.com/Misc/oramag/on-the-explicit-size-and-complex.html

Under "enqueue" wait section:
[[
The third and most likely issue is when multiple users are updating the same block. If there are no free ITL slots, a block-level lock could occur. You can easily avoid this scenario by increasing the initrans and/or maxtrans to allow multiple ITL slots and/or by increasing the pctfree on the table.
]]

Unfortunately we are not in a position to easily change INITTRANS/MAXTRANS/PCTFREE which requires object rebuild.

SQL> select event,count(*) from v$session_wait group by event;
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
PL/SQL lock timer                                                       171
SQL*Net message to client                                                 1
enqueue                                                                  22
gcs remote message                                                        2
ges remote message                                                        1
jobq slave wait                                                           3
pmon timer                                                                1
rdbms ipc message                                                         9
smon timer                                                                1
wakeup time manager                                                       1
 
10 rows selected.
 
SQL> l
  1   select     /*+ ordered */
  2      a.sid blocker_sid,
  3      a.username blocker_username,
  4      a.serial#,
  5      a.logon_time,
  6      b.type,
  7      b.lmode mode_held,
  8      b.ctime time_held,
  9      c.sid waiter_sid,
 10      c.request request_mode,
 11      c.ctime time_waited
 12  from    v$lock b,v$enqueue_lock c,v$session a
 13  where    a.sid=b.sid
 14  and    b.id1=c.id1(+)
 15  and    b.id2=c.id2(+)
 16  and    c.type(+)='TX'
 17  and     b.type='TX'
 18* and    b.block=1
SQL> /
 
BLOCKER_SID    TY  MODE_HELD  TIME_HELD WAITER_SID REQUEST_MODE TIME_WAITED
-----------    -- ---------- ---------- ---------- ------------ -----------
        406    TX          6        296         76            4         241
        117    TX          6        330         83            4         244
        534    TX          6        334         91            4         244
        155    TX          6        329        106            4         241
        148    TX          6        330        108            4         241
        148    TX          6        330        121            4         241
        493    TX          6        340        132            4         241
        330    TX          6        307        136            4         244
        431    TX          6        347        141            4         244
        166    TX          6        301        143            4         241
        330    TX          6        307        152            4         241
         44    TX          6        323        153            4         244
        533    TX          6        301        154            4         241
        454    TX          6        305        157            4         241
        433    TX          6        336        159            4         241
        524    TX          6        334        160            4         241
        454    TX          6        305        177            4         241
        107    TX          6        329        383            4         241
        511    TX          6        340        399            4         241
        529    TX          6        301        405            4         241
        406    TX          6        296        423            4         241
        456    TX          6        331        455            4         244
 
22 rows selected.
 
SQL>  

1. What is this meaning of wait_time = -1 in v$session_wait ?
2. Is the time from this column is in sec?

Thanks

Tom,

Database version : 8.1.7.4 

I had a oracle session in the database doing INSERT waited on "SQL*Net more data from client wait" for ever as per v$session_wait details shown below :

SQL> select * from v$session_wait where sid=11 ;

       SID       SEQ# EVENT                                                            P1TEXT                                                                   P1 P1RAW
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ----------------
P2TEXT                                                                   P2 P2RAW            P3TEXT                                                                   P3 P3RAW             WAIT_TIME
---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ----------
SECONDS_IN_WAIT STATE
--------------- -------------------
        11      53065 SQL*Net more data from client                                    driver id                                                        1952673792 0000000074637000
#bytes                                                                26485 0000000000006775                                                                           0 00                       -2
           2914 WAITED UNKNOWN TIME


When i checked the sql details, the session was hanging on the following query and i was not able to find out the actual INSERT statement this session as executing.

Hanging on the following recursive query :

select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey, i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#, nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0), nvl(i.spare1,i.intcols),i.spare4,spare2,decode(i.pctthres$,null,null, mod(trunc(i.pctthres$/256),256)) from ind$ i, (select enabled, min(cols) unicols, min(to_number(bitand(defer,1))) deferrable#, min(to_number(bitand(defer,4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.bo#=:1


Questions:

What this query does and How to find the actual INSERT statement the session executing.

Thanks for any insights.

Tom,

<< can you show us that the session was actually "active"

Yes, the session was ACTIVE. I have captured the session screenshot (taken via DBArtisan) in the word document.

<< how did you determine "hanging"

Sesssion status was in ACTIVE status over 30 minutes until i kill the session. So thought that this session hung up.

Sometimes we see the actual INSERT itself showing up for sessions waiting on "sqlnet more data from client" but quite few times it just shows up the "query retrieving constraint information you just mentioned (thanks for the details!) not the acutal INSERT". We usually just kill the session waiting on "sqlnet more data from client" as we were told that 8.1.7.4 JDBC driver has issue with it.

Inserting table has 1000 columns.

greetings Thomas,

and thanks like always,
Can you please advice and explain the below, oracle version 9.2.0.6:

select
(select username from v$session where sid=a.sid) blocker, (select schemaname from v$session where sid=a.sid) schemaname, (select program from v$session where sid=a.sid) program, (select type from v$session where sid=a.sid) type,
a.sid,
' is blocking ',
(select username from v$session where sid=b.sid) blockee, (select schemaname from v$session where sid=b.sid) schemaname, (select program from v$session where sid=b.sid) program, (select type from v$session where sid=b.sid) type,
b.sid
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2

BLOCKER PROGRAM TYPE SID ISBLOCKING' BLOCKEE PROGRAM_1 TYPE_1 SID
------------------------------------------------------------------------------------------
SYS oracle@xxxxx (CKPT) BACKGROUND 4 is blocking ODS_VAS oracle@xxxxx USER 63
SYS oracle@xxxxx (CKPT) BACKGROUND 4 is blocking ODS_VAS sqlplus@xxxx USER 145
SYS oracle@xxxxx (QMN0) USER 8 is blocking RUNREP null USER 82
SYS oracle@xxxxx (QMN0) USER 8 is blocking RUNREP_ null USER 89
SYS oracle@xxxxx (QMN0) USER 8 is blocking SYS null USER 97
DWH JDBC Thin Client USER 77 is blocking ODS_CSM sqlplus.exe USER 23
DWH JDBC Thin Client USER 77 is blocking ODS_EPA null USER 47
RUNREP_ null USER 89 is blocking SYS oracle@xxxxx USER 7
SYS null USER 97 is blocking SYS null USER 20
SYS null USER 97 is blocking SYS null USER 22
SYS null USER 97 is blocking SYS null USER 41
SYS null USER 97 is blocking SYS null USER 42
SYS null USER 97 is blocking SYS null USER 44
SYS null USER 97 is blocking SYS null USER 54
SYS null USER 97 is blocking SYS null USER 58
SYS null USER 97 is blocking SYS null USER 62
SYS null USER 97 is blocking SYS null USER 65
SYS null USER 97 is blocking SYS null USER 66
SYS null USER 97 is blocking SYS null USER 70
SYS null USER 97 is blocking SYS null USER 71
SYS null USER 97 is blocking SYS null USER 72
SYS null USER 97 is blocking SYS null USER 76
SYS null USER 97 is blocking SYS null USER 81
SYS null USER 97 is blocking SYS null USER 84
SYS null USER 97 is blocking SYS null USER 91
SYS null USER 97 is blocking SYS null USER 93
SYS null USER 97 is blocking SYS null USER 95
SYS null USER 97 is blocking SYS null USER 100
SYS null USER 97 is blocking SYS null USER 101
SYS null USER 97 is blocking SYS null USER 104
SYS null USER 97 is blocking SYS null USER 112
SYS null USER 97 is blocking SYS null USER 120
SYS null USER 97 is blocking SYS null USER 124
SYS null USER 97 is blocking SYS null USER 125
SYS null USER 97 is blocking SYS null USER 126
SYS null USER 97 is blocking SYS null USER 129
SYS null USER 97 is blocking SYS null USER 134
SYS null USER 97 is blocking SYS null USER 140
SYS null USER 97 is blocking SYS null USER 146
SYS null USER 97 is blocking SYS null USER 147
SYS null USER 97 is blocking SYS null USER 149
SYS null USER 97 is blocking SYS null USER 152
SYS null USER 97 is blocking SYS null USER 156
SYS null USER 97 is blocking SYS null USER 164

Hi Tom,

Is there a way to find the table names associated with the TX entries in v$lock without looking at the associated TM row. I know how to find the table name assoaciated with TM.

Thanks

Tom:
I am running insert query which is retrieving data from sybase using Oracle transparent gateway and inserting into oracle table. I see the insert is just hanging and v$session_wait shows event='db file scattered'.

At this point, i do not know, where the issue is ?
whether local database (oracle) or remote (sybase).

how to confirm this ?

 
SQL> @qry_sesswait
SQL> set lines 132 pages 999
SQL> column event format a30
SQL> 
SQL> select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID1;

       SID EVENT                                SEQ#         P1         P2         P3  WAIT_TIME
---------- ------------------------------ ---------- ---------- ---------- ---------- ----------
        17 db file scattered read               1005         19      46729         15         -2

SQL> select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID1;

       SID EVENT                                SEQ#         P1         P2         P3  WAIT_TIME
---------- ------------------------------ ---------- ---------- ---------- ---------- ----------
        17 db file scattered read               1005         19      46729         15         -2

SQL> select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID1;

       SID EVENT                                SEQ#         P1         P2         P3  WAIT_TIME
---------- ------------------------------ ---------- ---------- ---------- ---------- ----------
        17 db file scattered read               1005         19      46729         15         -2

SQL> l
  1  select * from v$session_event where event='db file scattered read' and total_Waits > 0
  2* and sid=17 order by 3,2
SQL> /

       SID EVENT                          TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT   MAX_WAIT
---------- ------------------------------ ----------- -------------- ----------- ------------ ----------
        17 db file scattered read                 909              0           0            0          0

SQL> select * from v$session_longops where time_remaining > 0 ;

no rows selected


SQL:
====
INSERT 
INTO PRODUCER_MASTER(
   REF_GROUP_CD,
   PRODUCER_CD,
   PRODUCER_GROUP_CD,
   PRODUCER_NAME,
   PRODUCER_FRST_NAME,
   PRODUCER_MID_NAME,
   PRDR_TITLE_CD,
   PRDR_CLASS_CD,
   REMARKS_TXT_ID,
   PRDR_AGREE_DATE,
   PRDR_AGR_CANC_DATE,
   PRDR_STATUS_CD,
   CORP_EQUIV_CD,
   PRDR_TYPE_CD,
   PREV_PRODUCER_NO,
   EXEC_RESP_NAME,
   ADDRESS_ID,
   PHONE_ID,
   NET_GROSS_CD,
   PRDR_BRANCH_CD,
   CREDIT_TERM_CNT,
   INDIVIDUAL_ID_NO,
   SURPLUS_LIC_NO,
   PRODUCER_XFER_CD,
   PRODUCER_XFER_DT,
   BANK_ACCT_NO,
   BANK_CD,
   PRDR_BIRTH_DT,
   LANGUAGE_ID,
   PRDR_SHORT_NAME,
   PRDR_TAX_TRD_1_ID,
   PRDR_TAX_TRD_2_ID,
   BANK_ACCT_NAME,
   PRDR_BRANCH_SUB_CD,
   CODE_EFF_DATE,
   CODE_EXP_DATE,
   PRDR_CONTACT_ID,
   SOURCE_SYSTEM_ID,
   USERID_CD,
   PRDR_GENDER_CD,
   PRDR_BIODATA_ID,
   PRDR_WTX_ID,
   TIMESTAMP,
   ALT_PRDR_LAST_NAME,
   ALT_PRDR_FRST_NAME,
   ALT_PRDR_MID_NAME,
   ALT_PRDR_SHRT_NAME,
   PRDR_LAST_NAME_ID,
   PRDR_FIRST_NAME_ID,
   EXTRN_USR_OFF_CD,
   AGREEMENT_NO,
   INDIVDL_OR_ORG_CD,
   BANK_LOCATION_NM,
   BUS_REG_NO,
   PRDR_REGION_CD,
   PRDR_WS_BRANCH_CD,
   PRDR_WS_REGION_CD,
   WR_REGION_CD,
   WR_BRANCH_CD 
)
SELECT "ref_group_cd",
   "producer_cd",
   "producer_group_cd",
   "producer_name",
   "producer_frst_name",
   "producer_mid_name",
   "prdr_title_cd",
   "prdr_class_cd",
   "remarks_txt_id",
   "prdr_agree_date",
   "prdr_agr_canc_date",
   "prdr_status_cd",
   "corp_equiv_cd",
   "prdr_type_cd",
   "prev_producer_no",
   "exec_resp_name",
   "address_id",
   "phone_id",
   "net_gross_cd",
   "prdr_branch_cd",
   "credit_term_cnt",
   "individual_id_no",
   "surplus_lic_no",
   "producer_xfer_cd",
   "producer_xfer_dt",
   "bank_acct_no",
   "bank_cd",
   "prdr_birth_dt",
   "language_id",
   "prdr_short_name",
   "prdr_tax_trd_1_id",
   "prdr_tax_trd_2_id",
   "bank_acct_name",
   "prdr_branch_sub_cd",
   "code_eff_date",
   "code_exp_date",
   "prdr_contact_id",
   "source_system_id",
   "userid_cd",
   "prdr_gender_cd",
   "prdr_biodata_id",
   "prdr_wtx_id",
   "timestamp",
   "alt_prdr_last_name",
   "alt_prdr_frst_name",
   "alt_prdr_mid_name",
   "alt_prdr_shrt_name",
   "prdr_last_name_id",
   "prdr_first_name_id",
   "extrn_usr_off_cd",
   "agreement_no",
   "indivdl_or_org_cd",
   "bank_location_nm",
   "bus_reg_no",
   "PRDR_REGION_CD",
   "PRDR_WS_BRANCH_CD",
   "PRDR_WS_REGION_CD",
   :b1,
   :b2
FROM TPRDUCER 
WHERE "producer_cd" = :b3 
AND "PRDR_REGION_CD" = :b4 
AND "prdr_branch_cd" = :b5

What does 'first dba' mean in the P2TEXT?