Home » SQL & PL/SQL » SQL & PL/SQL » Major issues with Merge - please help!!
Major issues with Merge - please help!! [message #275344] Fri, 19 October 2007 10:12 Go to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
ok, I am dying here Sad I have a process that performs a merge between two databases. There are 8 tables total. 4 tables do note have anything uniqly identifying a row, so I am forced to do a delete and then an insert on these. Then another 4 tables I am doing a merge. All this happens in a loop in one transaction.
This process is run via a dbms_job that is scheduled to run every hour. Our users can also manually run this process whenever they want to. I dont know what is causing it, but ALL these 8 tables get locked and cannot be accessed. This happens ONLY when the job is run via dbms_job. If we shut down the database and then re-start and kill the dbms_job and only run the process manually, all is fine.


My questons:

1.
I dont have a rollback in the procss if things go south. SHould I? Maybe something goes wrong with one of the merge statements and things just hang?
2.
Can there be a problem if dbms_job runs this process and the users run it at the exact same time?

any suggestions will be very much appreciated!!!!!!!!!

[Updated on: Fri, 19 October 2007 10:16]

Report message to a moderator

Re: Major issues with Merge - please help!! [message #275349 is a reply to message #275344] Fri, 19 October 2007 10:20 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
SELECT DECODE(request,0,'Holder: ','Waiter: ') request, ss.sid sid, serial#, id1, id2, lmode, vl.type type
FROM V$LOCK vl, v$session ss
WHERE (id1, id2, vl.type) IN (SELECT id1, id2, type FROM V$LOCK
                              WHERE request>0)
AND ss.sid = vl.sid
ORDER BY id1, request;

If in fact you have a locking disorder, this query will identify the culprit & the victim(s).

Needless to say this query needs to be run while the lock up is occurring.

[Updated on: Fri, 19 October 2007 10:21] by Moderator

Report message to a moderator

Re: Major issues with Merge - please help!! [message #275352 is a reply to message #275349] Fri, 19 October 2007 10:33 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Right, this identifies the rows that are locked. I am trying to figure out why they get locked Sad thank you though, its a good select to have Smile
Re: Major issues with Merge - please help!! [message #275354 is a reply to message #275344] Fri, 19 October 2007 10:47 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Right, this identifies the rows that are locked.
NO, it identifies the session which holds the lock(s).
When you know the session & session ID (SID), you can find the SQL causing the lock.
Once you know the SQL causing the lock, then hopefully you can recode to fix the problem.

Ready, Fire, AIM!
Re: Major issues with Merge - please help!! [message #275355 is a reply to message #275354] Fri, 19 October 2007 10:50 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Oh, ok. So if I saw in the database that all 8 tables are locked, I can identify an exact sql that is causing this lock? Do you know how to identify this sql via session and session id? Thank you!
Re: Major issues with Merge - please help!! [message #275359 is a reply to message #275344] Fri, 19 October 2007 11:28 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Are the tablespaces involve LMT or dictionary managed tablespaces?
Re: Major issues with Merge - please help!! [message #275360 is a reply to message #275359] Fri, 19 October 2007 11:39 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Local
Re: Major issues with Merge - please help!! [message #275366 is a reply to message #275344] Fri, 19 October 2007 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You may not have an actual "LOCK" problem; per se.
You may be encountering Enqueue Waits due to INITRANS value being at default value of 1 for tables.
IIRC, my previously posted SQL should identify such a condition.
While you are waiting for your Production system to tie itself into knots, you could be practicing identify Locks & Enqueue Waits on your development system.
You'll need at least four different windows open to same DB.
Start an UPDATE in Window1, try to UPDATE same row in Window2, run my SQL from Window3 & then track down the culprit in Window4.
Re: Major issues with Merge - please help!! [message #275369 is a reply to message #275366] Fri, 19 October 2007 12:12 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
The table was locked (or maybe its enqueue waits) for a day until we restarted out database. Is such long time possible for enqueue waits? Also, which column in the select that you so kindly provided would I see that this is, indeed, an equeue problem. Thank you!

p.s. also, how do i identify sql if i have SID? I am definatelly exploring this possibility. Thank you so much!
Re: Major issues with Merge - please help!! [message #275370 is a reply to message #275366] Fri, 19 October 2007 12:18 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
sorry, what do i do in window 4?
Re: Major issues with Merge - please help!! [message #275371 is a reply to message #275344] Fri, 19 October 2007 12:20 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
> Is such long time possible for enqueue waits?
Yes, Oracle is VERY patient & will wait forever.
Enqueue Waits can occur when multiple tables have DML operations against them in the same transaction.

>I have a process that performs a merge between two databases.
Between two databases (SIDs) or between two schemas?
Across SQL*Net (yes or no)?
Re: Major issues with Merge - please help!! [message #275372 is a reply to message #275371] Fri, 19 October 2007 12:24 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
Enqueue Waits can occur when multiple tables have DML operations against them in the same transaction.

That is exactly what i have 8 tables have DML operations in the same transactions..I have two different Oracle 9i databases (not schmeas) - hence 2 different SIDs. I am assuming across SQL*Net
Re: Major issues with Merge - please help!! [message #275373 is a reply to message #275344] Fri, 19 October 2007 12:34 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Enqueue Waits can occur when multiple tables have DML operations against them in the same transaction.
However the Enqueue Waits occur only when multiple sessions are attempting DML against the same table(s)
Re: Major issues with Merge - please help!! [message #276050 is a reply to message #275344] Tue, 23 October 2007 15:03 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
please provide some additional information:

1) are the 8 tables being updated on one instance or are you updating some tables on instance A and others on Instance B?

2) attach a file with the code.

3) does your job enforce any kind of "serialized job" symantics? For example: is possible for dbms_job to start the job, and then your user to start the job again so that you have two copies of the job running. This may not be your problem but you may wish to add some kind of lock management to the job to prevent multiple copies of the job running at the same time. Normally DBMS_JOB acquires a job lock on the job so that it knows not to start the job again if the last invocation has not finished. But since you have multiple ways to start the job, this won't help you. Who knows, maybe the users keep hitting "run job" over and over to make it go faster.

4) also, what exactly do you mean by "LOCKED TABLES"?

Kevin
Re: Major issues with Merge - please help!! [message #276059 is a reply to message #275344] Tue, 23 October 2007 16:04 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
1) are the 8 tables being updated on one instance or are you updating some tables on instance A and others on Instance B?

same instance, but different processes. the tables can be populated by the dmbs_job process that kicks of a pl/sql procedure that actually does the "populating". This dbms_job runs every hour. Then users can also manually run this pl/sql procedure and populate this 8 tables.

2) attach a file with the code.

dmbs_job script or pl/sql procedure?

3) does your job enforce any kind of "serialized job" symantics? For example: is possible for dbms_job to start the job, and then your user to start the job again so that you have two copies of the job running. This may not be your problem but you may wish to add some kind of lock management to the job to prevent multiple copies of the job running at the same time. Normally DBMS_JOB acquires a job lock on the job so that it knows not to start the job again if the last invocation has not finished. But since you have multiple ways to start the job, this won't help you. Who knows, maybe the users keep hitting "run job" over and over to make it go faster.

HOW? Smile I did it as follows:

SELECT
dj.what
FROM dba_jobs dj , dba_jobs_running djr
WHERE dj.job = djr.job
AND dj.WHAT LIKE 'myProcess%';

-- if this returns something, then job does not run.

Its not working, of course, because it checks all sessions including mine. How do I check sessions OTHER then mine?

4) also, what exactly do you mean by "LOCKED TABLES"?

checked v$lock ...and some other views..we had to shut down the database

Re: Major issues with Merge - please help!! [message #276063 is a reply to message #276059] Tue, 23 October 2007 16:13 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
2) please post both. They can't be that big.

3) how about using something like DBMS_LOCK.ALLOCATE_UNIQUE(...

http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_lock.htm

http://www.adp-gmbh.ch/ora/plsql/sync_sessions.html

4) from a different sessions can you:

a) describe the table(s)
b) select from the table(s)
c1) insert into the table(s)
c2) delete
c3) update


1) I was trying to get more information on where tables live:

a) do all tables being updated live in the scheam? the same instance?
b) what is the breakdown of where tables live and where processes operating from do there selects and other dml.

Kevin
Re: Major issues with Merge - please help!! [message #276068 is a reply to message #276063] Tue, 23 October 2007 17:35 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
1.
This is how the process is scheduled:

CREATE OR REPLACE PROCEDURE schedulePWSprocess AS

jobnumber NUMBER;

BEGIN


dbms_job.submit(JOB => jobnumber,
WHAT=>'executeCostProcess;',
NEXT_DATE=>sysdate, -- start right now
INTERVAL=>'sysdate+1/24'); -- run every hour
commit;
END;
/

----------

CREATE OR REPLACE PROCEDURE executeCostProcess AS

errnum NUMBER;
errmsg VARCHAR2(20);

BEGIN

cost_processing.merge_data(errnum, errmsg);

END;
/

---------
cost_processing.merge_data is a big package that merges data in 8 tables between two different databases among other things...It is doing so in one big transaction with one big commit in the end. If things go south then we rollback in the end and insert into error table...


2.
Can I from my session check if any other session runs my job?

3.

a) do all tables being updated live in the scheam? the same instance?

Yes, all tables live in the same place. same instance.

b) what is the breakdown of where tables live and where processes operating from do there selects and other dml.

Tables from where we get the data live in remote database and tables where we put the data via merge live in our local database. The process (pl/sql package) does the merge between the two. DBMS_JOB kickes off a procedure in this package that does the merge. It is scheduled to run every hour. But users can also run in manually...
Re: Major issues with Merge - please help!! [message #276069 is a reply to message #276068] Tue, 23 October 2007 17:44 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
thanks

2) if you use dbms_application_info in your plsql code cost_processing.merge_data, then you can look at the v$session view to see who is running the job (very cool) (assuming you can view v$session, if not ask your dba).

3) again, I suggest you do something with dbms_lock.allocation_unique, to ensure that the dbms_job invocations and user invocations can not collide.

1) off hand I do not see a problem. You have described a basic etl process. I write something similar to this about once every three months (different clients). If the two suggestions above provide no releaf, then post the code as a attachment and I can make suggestions (if you want to listen to them).

I would ask anonther question. I this a new process, or a production process that used to work well but is now giving you trouble? If the former, then there may be a coding issue and something we can resolve readily, of the later, then possibly and change was made, or there is a bug to deal with, or other known Oracle issue, hard to say.

Kevin
Re: Major issues with Merge - please help!! [message #276070 is a reply to message #276069] Tue, 23 October 2007 17:51 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
2) if you use dbms_application_info in your plsql code cost_processing.merge_data, then you can look at the v$session view to see who is running the job (very cool) (assuming you can view v$session, if not ask your dba).

cool, i will look into it.

3) again, I suggest you do something with dbms_lock.allocation_unique, to ensure that the dbms_job invocations and user invocations can not collide.

i will also look into this

1) off hand I do not see a problem. You have described a basic etl process. I write something similar to this about once every three months (different clients). If the two suggestions above provide no releaf, then post the code as a attachment and I can make suggestions (if you want to listen to them).

I am attaching the code to this message

I would ask anonther question. I this a new process, or a production process that used to work well but is now giving you trouble? If the former, then there may be a coding issue and something we can resolve readily, of the later, then possibly and change was made, or there is a bug to deal with, or other known Oracle issue, hard to say.

Looks like it was working and now its not. It might be that new data is bad, or maybe this is the first time the jobs colided or who knows...Or maybe it wasnt merging right to begin with..

[Updated on: Tue, 23 October 2007 19:13]

Report message to a moderator

Re: Major issues with Merge - please help!! [message #276071 is a reply to message #276069] Tue, 23 October 2007 18:03 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
can u elaborate on this one?

if you use dbms_application_info in your plsql code cost_processing.merge_data, then you can look at the v$session view to see who is running the job (very cool) (assuming you can view v$session, if not ask your dba).

I have access to v$session and I use the following select to figure out if my job is running (sorry, i had to change some object names in my previous post):

SELECT
dj.what
FROM dba_jobs dj , dba_jobs_running djr
WHERE dj.job = djr.job
AND dj.WHAT LIKE 'executePWSprocessing%';

how do i add v$session to that to make sure if it is NOT my session that is running this job?

Re: Major issues with Merge - please help!! [message #276072 is a reply to message #276071] Tue, 23 October 2007 18:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
sure

SQL> desc dbms_application_info
PROCEDURE READ_CLIENT_INFO
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_INFO                    VARCHAR2                OUT
PROCEDURE READ_MODULE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MODULE_NAME                    VARCHAR2                OUT
 ACTION_NAME                    VARCHAR2                OUT
PROCEDURE SET_ACTION
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ACTION_NAME                    VARCHAR2                IN
PROCEDURE SET_CLIENT_INFO
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_INFO                    VARCHAR2                IN
PROCEDURE SET_MODULE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MODULE_NAME                    VARCHAR2                IN
 ACTION_NAME                    VARCHAR2                IN
PROCEDURE SET_SESSION_LONGOPS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RINDEX                         BINARY_INTEGER          IN/OUT
 SLNO                           BINARY_INTEGER          IN/OUT
 OP_NAME                        VARCHAR2                IN     DEFAULT
 TARGET                         BINARY_INTEGER          IN     DEFAULT
 CONTEXT                        BINARY_INTEGER          IN     DEFAULT
 SOFAR                          NUMBER                  IN     DEFAULT
 TOTALWORK                      NUMBER                  IN     DEFAULT
 TARGET_DESC                    VARCHAR2                IN     DEFAULT
 UNITS                          VARCHAR2                IN     DEFAULT

SQL> 


this package allows you to set the columns
(module, action, client_info) in v$session for the session issues the call.

Try this:

exec dbms_application_info.set_client_info('my test');

select sid,serial#,audsid,username,client_info
from v$session
where client_info is not null
/


you should see something looks like this:

SQL> exec dbms_application_info.set_client_info('my test');

PL/SQL procedure successfully completed.

SQL> select sid,serial#,audsid,username,client_info
  2  from v$session
  3  where client_info is not null
  4  /

       SID    SERIAL#     AUDSID USERNAME                       CLIENT_INFO
---------- ---------- ---------- ------------------------------ ---------------------
       242         62     192688 KM21378                        my test

1 row selected.

SQL>


If you put a similar set of calls in your code, then evertime it executes, it will set the values. If a user runs the job, you can see their username, pcid, or whatever you set, if the dbms_job does it, then you will likely see something other than a user's username, maybe null.


Kevin
Re: Major issues with Merge - please help!! [message #276073 is a reply to message #276072] Tue, 23 October 2007 18:20 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
ok, thank u, i am trying it out
Re: Major issues with Merge - please help!! [message #276075 is a reply to message #276070] Tue, 23 October 2007 19:27 Go to previous messageGo to next message
lotusdeva
Messages: 198
Registered: March 2005
Senior Member
do you have an example of how this can be done in the code:

again, I suggest you do something with dbms_lock.allocation_unique, to ensure that the dbms_job invocations and user invocations can not collide.
Re: Major issues with Merge - please help!! [message #276266 is a reply to message #276075] Wed, 24 October 2007 10:21 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
nah, you can read manuals for it. Do a web search, there are plenty of examples.

Kevin
Previous Topic: about USERENV('SESSIONID')
Next Topic: Deadlock during DELETE operation
Goto Forum:
  


Current Time: Sat Dec 03 05:44:28 CST 2016

Total time taken to generate the page: 0.19641 seconds