São Paulo, 26-May-2010,
This complex procedure was tested and you'll be able to accomplish the steps in 2 hours approximately. LOL.
- Step one
Before start the installation edit the following file
"WhereUveExtractedDaOraFiles"\database\install\oraparam.ini
And find this
...
[Certified Versions]
#You can customise error message shown for failure, provide value for CERTIFIED_VERSION_FAILURE_MESSAGE
Windows=5.0,5.1,5.2,6.0
...
Append 6.1 to the end of the line.
That works for 10g and 11g, I havent tried for 9i but Im pretty sure It should be the same.
- Step two
Get back to that gossip, soccer, torrent, gaga or whatever website you were.
Enjoy, Rod.
sexta-feira, 26 de março de 2010
Outer Join using case
São Paulo, 26-May-2010,
I was workin on table integrity issues, u know, when data from different systems have to be consolidaded in a sort of bi report and they dont have a relationship between them.
The report was supposed to retreive customer information from those systems, cross it n present the results.
But what if one system tells u the customer did something and the other dont.
Well, lets have a look at this piece of code (if I can call sql as a real code :P):
create table system1(
key char(1), --dont ask me why char, ok?
status char(8),
descrpt char(6),
avail char(1));
create table system2(key char(1),
status char(8),
descrpt char(6),
avail char(1),
surrogate number); --have u watched that movie? crap.
insert into system1 values ('A', 'ACTIVE', 'PROD A', 'Y');
insert into system1 values ('B', 'ACTIVE', 'PROD B', 'N');
insert into system1 values ('C', 'INACTIVE', 'PROD C', 'Y');
insert into system2 values ('A','INACTIVE','PROD A','Y',1001);
insert into system2 values ('A','ACTIVE', 'PROD A','Y',1002);
insert into system2 values ('B','INACTIVE','PROD B','Y',2001);
insert into system2 values ('D','ACTIVE', 'PROD D','Y',4001);
select t2.surrogate, t1.key, t1.status, t1.key T1KEYNULL, t2.key T2KEYNULL, t1.key ' ' t2.key T1KEYT2KEYDIFF from t1 full outer join t2 on t1.key=t2.key
/
SELECT *
FROM (SELECT t2.surrogate,
t1.key,
t1.status,
CASE WHEN t1.key IS NULL THEN 1 ELSE 0 END AS t1keynull,
CASE WHEN t2.key IS NULL THEN 1 ELSE 0 END AS t2keynull,
CASE WHEN t1.key IS NOT NULL AND t2.key IS NOT NULL AND (t1.status <> t2.status) /* Status is mandatory */ THEN 1 ELSE 0 END AS t1keyt2keydiff
FROM t2
FULL OUTER JOIN t1 ON t1.key = t2.key)
WHERE t1keynull + t2keynull + t1keyt2keydiff > 0
/
I wont post the results from the selects above, Its up to you give it a try.
I tried to predict all possibilites using the case statement.
Hope it helps.
Rod.
I was workin on table integrity issues, u know, when data from different systems have to be consolidaded in a sort of bi report and they dont have a relationship between them.
The report was supposed to retreive customer information from those systems, cross it n present the results.
But what if one system tells u the customer did something and the other dont.
Well, lets have a look at this piece of code (if I can call sql as a real code :P):
create table system1(
key char(1), --dont ask me why char, ok?
status char(8),
descrpt char(6),
avail char(1));
create table system2(key char(1),
status char(8),
descrpt char(6),
avail char(1),
surrogate number); --have u watched that movie? crap.
insert into system1 values ('A', 'ACTIVE', 'PROD A', 'Y');
insert into system1 values ('B', 'ACTIVE', 'PROD B', 'N');
insert into system1 values ('C', 'INACTIVE', 'PROD C', 'Y');
insert into system2 values ('A','INACTIVE','PROD A','Y',1001);
insert into system2 values ('A','ACTIVE', 'PROD A','Y',1002);
insert into system2 values ('B','INACTIVE','PROD B','Y',2001);
insert into system2 values ('D','ACTIVE', 'PROD D','Y',4001);
select t2.surrogate, t1.key, t1.status, t1.key T1KEYNULL, t2.key T2KEYNULL, t1.key ' ' t2.key T1KEYT2KEYDIFF from t1 full outer join t2 on t1.key=t2.key
/
SELECT *
FROM (SELECT t2.surrogate,
t1.key,
t1.status,
CASE WHEN t1.key IS NULL THEN 1 ELSE 0 END AS t1keynull,
CASE WHEN t2.key IS NULL THEN 1 ELSE 0 END AS t2keynull,
CASE WHEN t1.key IS NOT NULL AND t2.key IS NOT NULL AND (t1.status <> t2.status) /* Status is mandatory */ THEN 1 ELSE 0 END AS t1keyt2keydiff
FROM t2
FULL OUTER JOIN t1 ON t1.key = t2.key)
WHERE t1keynull + t2keynull + t1keyt2keydiff > 0
/
I wont post the results from the selects above, Its up to you give it a try.
I tried to predict all possibilites using the case statement.
Hope it helps.
Rod.
segunda-feira, 6 de julho de 2009
procedure para pegar locks (imcompleta) :(
CREATE OR REPLACE procedure UNIMED.pr_monitora_locks as
v_conta number(10):=0;
v_guarda_sql varchar2(4000);
v_data date;
cursor clock is
select lpad(' ',3*(level-1)) waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from tmp_lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
rlock clock%rowtype;
cursor csess is
select PROCESS,username USERNAME,
sid,
serial#,
MACHINE MACHINE,
TERMINAL TERMINAL,
osuser OSUSER,
LOCKWAIT,
status,
program,
lpad(to_char(sql_hash_value),20,'0') SQL_HASH_VALUE,
sql_address,
module,action,client_info
from v$session
where username is not null
and username <> 'SYS'
and sid = to_number(rtrim(ltrim(rlock.waiting_session)));
rsess csess%rowtype;
cursor ctextosql is
select sql_text
from v$sqltext
where address = rsess.sql_address
order by piece;
rtextosql ctextosql%rowtype;
v_erro varchar2(200);
v_code number;
begin
insert into tmp_dba_locks_temp
select * from dba_locks;
commit;
insert into tmp_lock_holders
select w.session_id,
h.session_id,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
from tmp_dba_locks_temp w, tmp_dba_locks_temp h
where h.blocking_others = 'Blocking'
and h.mode_held != 'None'
and h.mode_held != 'Null'
and w.mode_requested != 'None'
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2;
commit;
insert into tmp_lock_holders
select holding_session, null, 'None', null, null, null, null
from tmp_lock_holders
minus
select waiting_session, null, 'None', null, null, null, null
from tmp_lock_holders;
commit;
v_data:=sysdate;
open clock;
loop
fetch clock into rlock;
exit when clock%notfound;
v_conta:=v_conta+1;
if v_conta = 6 then exit;
end if;
open csess;
fetch csess into rsess;
v_guarda_sql:=' ';
open ctextosql;
loop
fetch ctextosql into rtextosql;
exit when ctextosql%notfound;
if length(v_guarda_sql) >= 4000 then
null;
else
v_guarda_sql:=substr(v_guarda_sqlrtextosql.sql_text,1,4000);
end if;
end loop;
close ctextosql;
if csess%found then
insert into tmp_guarda_lock_holders
(data_registro,sequencia,
waiting_session,
--holding_session,
lock_type ,mode_held ,
mode_requested ,lock_id1 ,
lock_id2 ,sess_process ,
sess_username ,sess_sid ,
sess_serial# ,sess_machine ,
sess_terminal ,sess_osuser ,
sess_lockwait ,sess_status ,
sess_program ,sess_sqladdress,
sess_module ,sess_action ,
sess_client_info,sql_texto)
values(v_data,v_conta,
rlock.waiting_session,
--rlock.holding_session,
rlock.lock_type ,rlock.mode_held ,
rlock.mode_requested ,rlock.lock_id1 ,
rlock.lock_id2 ,
rsess.process ,
v_conta number(10):=0;
v_guarda_sql varchar2(4000);
v_data date;
cursor clock is
select lpad(' ',3*(level-1)) waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_id1,
lock_id2
from tmp_lock_holders
connect by prior waiting_session = holding_session
start with holding_session is null;
rlock clock%rowtype;
cursor csess is
select PROCESS,username USERNAME,
sid,
serial#,
MACHINE MACHINE,
TERMINAL TERMINAL,
osuser OSUSER,
LOCKWAIT,
status,
program,
lpad(to_char(sql_hash_value),20,'0') SQL_HASH_VALUE,
sql_address,
module,action,client_info
from v$session
where username is not null
and username <> 'SYS'
and sid = to_number(rtrim(ltrim(rlock.waiting_session)));
rsess csess%rowtype;
cursor ctextosql is
select sql_text
from v$sqltext
where address = rsess.sql_address
order by piece;
rtextosql ctextosql%rowtype;
v_erro varchar2(200);
v_code number;
begin
insert into tmp_dba_locks_temp
select * from dba_locks;
commit;
insert into tmp_lock_holders
select w.session_id,
h.session_id,
w.lock_type,
h.mode_held,
w.mode_requested,
w.lock_id1,
w.lock_id2
from tmp_dba_locks_temp w, tmp_dba_locks_temp h
where h.blocking_others = 'Blocking'
and h.mode_held != 'None'
and h.mode_held != 'Null'
and w.mode_requested != 'None'
and w.lock_type = h.lock_type
and w.lock_id1 = h.lock_id1
and w.lock_id2 = h.lock_id2;
commit;
insert into tmp_lock_holders
select holding_session, null, 'None', null, null, null, null
from tmp_lock_holders
minus
select waiting_session, null, 'None', null, null, null, null
from tmp_lock_holders;
commit;
v_data:=sysdate;
open clock;
loop
fetch clock into rlock;
exit when clock%notfound;
v_conta:=v_conta+1;
if v_conta = 6 then exit;
end if;
open csess;
fetch csess into rsess;
v_guarda_sql:=' ';
open ctextosql;
loop
fetch ctextosql into rtextosql;
exit when ctextosql%notfound;
if length(v_guarda_sql) >= 4000 then
null;
else
v_guarda_sql:=substr(v_guarda_sqlrtextosql.sql_text,1,4000);
end if;
end loop;
close ctextosql;
if csess%found then
insert into tmp_guarda_lock_holders
(data_registro,sequencia,
waiting_session,
--holding_session,
lock_type ,mode_held ,
mode_requested ,lock_id1 ,
lock_id2 ,sess_process ,
sess_username ,sess_sid ,
sess_serial# ,sess_machine ,
sess_terminal ,sess_osuser ,
sess_lockwait ,sess_status ,
sess_program ,sess_sqladdress,
sess_module ,sess_action ,
sess_client_info,sql_texto)
values(v_data,v_conta,
rlock.waiting_session,
--rlock.holding_session,
rlock.lock_type ,rlock.mode_held ,
rlock.mode_requested ,rlock.lock_id1 ,
rlock.lock_id2 ,
rsess.process ,
terça-feira, 23 de junho de 2009
# tip: Use utllockt.sql to find the offending user# locksql.sql
select ses.username'('ses.sid')' users, acc.owner owner,acc.object object, ses.lockwait, txt.sql_text sqltextfrom v$sqltext txt, v$access acc, v$session seswhere txt.address = ses.sql_addressand txt.hash_value = ses.sql_hash_valueand ses.sid = acc.sidand ses.lockwait is not null;
# If your database has many users logging on to the database using# the same user ID, you can obtain the operating system process ID # using the following script
# lockproc.sql
select ses.username'('ses.sid')' users, acc.owner owner,acc.object object, ses.lockwait, ses.osuser os_processfrom v$process prc, v$access acc, v$session seswhere prc.addr = ses.paddrand ses.sid = acc.sidand ses.lockwait is not null;
# To observe the blocking user sessions and the waiting user# sessions all in a single statement, you can run the following# script
# lockhldr.sql or blocker.sql
TTITLE 'User Blocking and Waiting for Other Users'Select distinct o.object_name, sh.username'('sh.sid')' 'Holder', sw.username'('sw.sid')' 'Waiter',decode(lh.lmode, 1, 'NULL', 2,'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') 'Lock Type'from all_objects o, v$session sw, v$lock lw, v$session sh, v$lock lhwhere lh.id1 = o.object_idand lh.id1 = lw.id1and sh.sid= lh.sidand sw.sid= lw.sidand sh.lockwait is nulland sw.lockwait is not nulland lh.type = 'TM'and lw.type = 'TM'/
# To obtain more information on the type of lock being held, you can # run the following script. The id1 column in the v$lock table is the# OBJECT_ID of the object being locked. The OBJECT_ID can be used to# search the DBA_OBJECTS table to determine the object and object´s# owner
# tranlock.sql or alllocks.sql
TTITLE 'Transactions Experiencing Lock Contention'Select decode(lmode, 2, 'Row-S(SS)',3, 'Row-X(SX)',4, 'Share',5, 'S/Row-X(SSX)',6, 'Exclusive','Other') Lock_Requested,id1, id2from v$lock where type='TX' and (id1, id2) in (select id1, id2 from v$lock l where id1=l.id1 and id2 = l.id2 and l.request > 0);
# To obtain all locks held by al users...
select s.username, l.type, o.object_name,decode(l1.lmode, 2, 'Row-S(SS)',3, 'Row-X(SX)',4, 'Share',5, 'S/Row-X(SSX)',6, 'Exclusive','Other') Mode_Heldfrom dba_objects o, v$session s, v$lock lwhere s.sid = l.sidand o.object_id= l.id1;
select ses.username'('ses.sid')' users, acc.owner owner,acc.object object, ses.lockwait, txt.sql_text sqltextfrom v$sqltext txt, v$access acc, v$session seswhere txt.address = ses.sql_addressand txt.hash_value = ses.sql_hash_valueand ses.sid = acc.sidand ses.lockwait is not null;
# If your database has many users logging on to the database using# the same user ID, you can obtain the operating system process ID # using the following script
# lockproc.sql
select ses.username'('ses.sid')' users, acc.owner owner,acc.object object, ses.lockwait, ses.osuser os_processfrom v$process prc, v$access acc, v$session seswhere prc.addr = ses.paddrand ses.sid = acc.sidand ses.lockwait is not null;
# To observe the blocking user sessions and the waiting user# sessions all in a single statement, you can run the following# script
# lockhldr.sql or blocker.sql
TTITLE 'User Blocking and Waiting for Other Users'Select distinct o.object_name, sh.username'('sh.sid')' 'Holder', sw.username'('sw.sid')' 'Waiter',decode(lh.lmode, 1, 'NULL', 2,'row share', 3, 'row exclusive', 4, 'share', 5, 'share row exclusive', 6, 'exclusive') 'Lock Type'from all_objects o, v$session sw, v$lock lw, v$session sh, v$lock lhwhere lh.id1 = o.object_idand lh.id1 = lw.id1and sh.sid= lh.sidand sw.sid= lw.sidand sh.lockwait is nulland sw.lockwait is not nulland lh.type = 'TM'and lw.type = 'TM'/
# To obtain more information on the type of lock being held, you can # run the following script. The id1 column in the v$lock table is the# OBJECT_ID of the object being locked. The OBJECT_ID can be used to# search the DBA_OBJECTS table to determine the object and object´s# owner
# tranlock.sql or alllocks.sql
TTITLE 'Transactions Experiencing Lock Contention'Select decode(lmode, 2, 'Row-S(SS)',3, 'Row-X(SX)',4, 'Share',5, 'S/Row-X(SSX)',6, 'Exclusive','Other') Lock_Requested,id1, id2from v$lock where type='TX' and (id1, id2) in (select id1, id2 from v$lock l where id1=l.id1 and id2 = l.id2 and l.request > 0);
# To obtain all locks held by al users...
select s.username, l.type, o.object_name,decode(l1.lmode, 2, 'Row-S(SS)',3, 'Row-X(SX)',4, 'Share',5, 'S/Row-X(SSX)',6, 'Exclusive','Other') Mode_Heldfrom dba_objects o, v$session s, v$lock lwhere s.sid = l.sidand o.object_id= l.id1;
How did we solve our “log file sync” ?
Posted by Navdeep Saini on April 24, 2008
It was one of those intriguing days when for almost 2-3 days users/developers/managers has been bugging me for “possible” slowness in our EPR instance. Ofcourse I checked everything: no long running queries eating up resources, no sudden spurt of sessions, nothing in AWR report to show off, nothing to show that there is slowness in the database !!!
I do not normaly loose sleep over performance issues, but it was irritating when apps support guys woke me up in middle of night for two consecutive nights. One batch job (conc. request) very critical to business, was running 5 hours behind schedule. Obvious place to look for was dbconsole “top activity” and tracing the conc. request. The trace file showed that request was just running crazy (tail -f of trace) and dbconsole did not show any particular activity which might be causing the slowness in the database. I tkprofed (sys=no, sort=exeela,prsela,fchela) the trace and found nothing wrong with the sqls that were reported. No wait events like “db file sequential/scattered read”, apart from few occurances of “log file sync” event. Looking more carefuly in the “top sessions” in dbconsole it occured to me that indeed “log file sync” was reported occasionally but only for few seconds.
Observing more keenly I also found that trace of the sessions (tail -f of trace) was freezing occasionaly for few second and showing “log file sync” after it freezes. So it seems “log file sync” is my area of concern, albeit it was very miniscule in the overall database scenario.
As per metalink standard Note:34592.1 on “log file sync”:
The log file sync wait may be broken down into the following components:
1. Wakeup LGWR if idle2. LGWR gathers the redo to be written and issue the I/O3. Time for the log write I/O to complete4. LGWR I/O post processing5. LGWR posting the foreground/user session that the write has completed6. Foreground/user session wakeup
Tuning advice based on log file sync component breakdown above:
Steps 2 and 3 are accumulated in the “redo write time” statistic. (i.e. as found under STATISICS section of Statspack and AWR)Step 3 is the “log file parallel write” wait event. (Note 34583.1:”log file parallel write” Reference Note )Steps 5 and 6 may become very significant as the system load increases. This is because even after the foreground has been posted it may take a some time for the OS to schedule it to run. May require monitoring from O/S level.
As mentioned earlier, my AWR report was not showing “redo write time” and “log file parallel write” to be that significant. As such Load on the machine was “normal” and there was nothing that could point me to a direction in which I could proceed.
The situation was becoming worse and batch job finally finished at 7:30 AM in the morning, 5:30 hours behind schedule. Blood hounds (business/apps support) were out after me and they were smelling blood. But I was not letting it go and turned to my internals knowledge. During my oracle days one of the thing I learned was that we can 10046 (sql trace) trace background sessions also. They will only show wait events in trace files as they normally do not fire any sqls. LGWR was my guy this time and I turned on the trace. VOILA !!! it was showing “log file parallel write” !!
However my hopes were dashed immiediately as it was normal for LGWR to post this event as it writes (IO) to redo logs. Again I was in dark and could not fathom what could be the reason for “log file sync”. One wild idea that was floating in my mind was that it could be that there are suddenly more commits in the sytem. Apps support guys purged almot 800 millions rows in workflow tables, as they fixed some issue with workflow becuase of which they were not able to purge WF tables for almost a year !!!. Could it be that there are now 1/2 the number of rows in WF tables which might have “oiled” the system and now it was “churing” out more !!! There is suddenly more commits in the database and causing the LGWR to lag behind…..
Keen observation is one common concept that many try to overlook and I was the one who was the victim of it that day. Looking at LGWR trace files again, I saw that elapsed time for “log file parallel write” was sometimes spiking. Simple unix sort on it showed that “elapsed time” was sometimes in secs where-as, as per oracle for 20k of redo it should not take more than 15 milliseconds. It was “log file parallel write” , a simple sutpid IO request on redo log files causing this delay. And since LGWR has to write to redo at every commit it was stalling the whole system !!!
Here is portion of lgwr trace showing “log file parallel write” spiking to sometime to 39 secs !!
WAIT #0: nam=’log file parallel write’ ela= 22324424 files=2 blocks=2 requests=2 obj#=-1 tim=13758521131245WAIT #0: nam=’rdbms ipc message’ ela= 6595 timeout=294 p2=0 p3=0 obj#=-1 tim=13758521138897WAIT #0: nam=’log file parallel write’ ela= 2343353 files=2 blocks=64 requests=4 obj#=-1 tim=13758521143128WAIT #0: nam=’rdbms ipc message’ ela= 97448 timeout=290 p2=0 p3=0 obj#=-1 tim=13758521262712WAIT #0: nam=’log file parallel write’ ela= 1294 files=2 blocks=80 requests=2 obj#=-1 tim=13758521264788WAIT #0: nam=’rdbms ipc message’ ela= 9538 timeout=280 p2=0 p3=0 obj#=-1 tim=13758521275843WAIT #0: nam=’log file parallel write’ ela= 909835 files=2 blocks=8 requests=2 obj#=-1 tim=13758521278533
Mount points for redo logs was targeted and I contacted my SAs for DMX system. They immiediately found that out of four fiber adaptors on database machine , which connects to storage array via SAN switch, one was performing little slower. They asked for some more time to investigate. I waited for almost 1 excrutiating hour and finally heard back from them. It was indeed the IO sub-system. One of the port on SAN switch was “dying” causing frequent IO queues. They swapped the port with another one and it fixed it !!!. So it was just one simple bad port on SAN switch which was giving frequent “heart burns” to the database.
Leason learned…on a very very busy system, esp. an OLTP like Oracle APPS, it is absolutely critical that write throughput on redo logs is maintained at every time. No RAID-5, just simple RAID-1, on faster fiber channels, san switches etc..
Posted by Navdeep Saini on April 24, 2008
It was one of those intriguing days when for almost 2-3 days users/developers/managers has been bugging me for “possible” slowness in our EPR instance. Ofcourse I checked everything: no long running queries eating up resources, no sudden spurt of sessions, nothing in AWR report to show off, nothing to show that there is slowness in the database !!!
I do not normaly loose sleep over performance issues, but it was irritating when apps support guys woke me up in middle of night for two consecutive nights. One batch job (conc. request) very critical to business, was running 5 hours behind schedule. Obvious place to look for was dbconsole “top activity” and tracing the conc. request. The trace file showed that request was just running crazy (tail -f of trace) and dbconsole did not show any particular activity which might be causing the slowness in the database. I tkprofed (sys=no, sort=exeela,prsela,fchela) the trace and found nothing wrong with the sqls that were reported. No wait events like “db file sequential/scattered read”, apart from few occurances of “log file sync” event. Looking more carefuly in the “top sessions” in dbconsole it occured to me that indeed “log file sync” was reported occasionally but only for few seconds.
Observing more keenly I also found that trace of the sessions (tail -f of trace) was freezing occasionaly for few second and showing “log file sync” after it freezes. So it seems “log file sync” is my area of concern, albeit it was very miniscule in the overall database scenario.
As per metalink standard Note:34592.1 on “log file sync”:
The log file sync wait may be broken down into the following components:
1. Wakeup LGWR if idle2. LGWR gathers the redo to be written and issue the I/O3. Time for the log write I/O to complete4. LGWR I/O post processing5. LGWR posting the foreground/user session that the write has completed6. Foreground/user session wakeup
Tuning advice based on log file sync component breakdown above:
Steps 2 and 3 are accumulated in the “redo write time” statistic. (i.e. as found under STATISICS section of Statspack and AWR)Step 3 is the “log file parallel write” wait event. (Note 34583.1:”log file parallel write” Reference Note )Steps 5 and 6 may become very significant as the system load increases. This is because even after the foreground has been posted it may take a some time for the OS to schedule it to run. May require monitoring from O/S level.
As mentioned earlier, my AWR report was not showing “redo write time” and “log file parallel write” to be that significant. As such Load on the machine was “normal” and there was nothing that could point me to a direction in which I could proceed.
The situation was becoming worse and batch job finally finished at 7:30 AM in the morning, 5:30 hours behind schedule. Blood hounds (business/apps support) were out after me and they were smelling blood. But I was not letting it go and turned to my internals knowledge. During my oracle days one of the thing I learned was that we can 10046 (sql trace) trace background sessions also. They will only show wait events in trace files as they normally do not fire any sqls. LGWR was my guy this time and I turned on the trace. VOILA !!! it was showing “log file parallel write” !!
However my hopes were dashed immiediately as it was normal for LGWR to post this event as it writes (IO) to redo logs. Again I was in dark and could not fathom what could be the reason for “log file sync”. One wild idea that was floating in my mind was that it could be that there are suddenly more commits in the sytem. Apps support guys purged almot 800 millions rows in workflow tables, as they fixed some issue with workflow becuase of which they were not able to purge WF tables for almost a year !!!. Could it be that there are now 1/2 the number of rows in WF tables which might have “oiled” the system and now it was “churing” out more !!! There is suddenly more commits in the database and causing the LGWR to lag behind…..
Keen observation is one common concept that many try to overlook and I was the one who was the victim of it that day. Looking at LGWR trace files again, I saw that elapsed time for “log file parallel write” was sometimes spiking. Simple unix sort on it showed that “elapsed time” was sometimes in secs where-as, as per oracle for 20k of redo it should not take more than 15 milliseconds. It was “log file parallel write” , a simple sutpid IO request on redo log files causing this delay. And since LGWR has to write to redo at every commit it was stalling the whole system !!!
Here is portion of lgwr trace showing “log file parallel write” spiking to sometime to 39 secs !!
WAIT #0: nam=’log file parallel write’ ela= 22324424 files=2 blocks=2 requests=2 obj#=-1 tim=13758521131245WAIT #0: nam=’rdbms ipc message’ ela= 6595 timeout=294 p2=0 p3=0 obj#=-1 tim=13758521138897WAIT #0: nam=’log file parallel write’ ela= 2343353 files=2 blocks=64 requests=4 obj#=-1 tim=13758521143128WAIT #0: nam=’rdbms ipc message’ ela= 97448 timeout=290 p2=0 p3=0 obj#=-1 tim=13758521262712WAIT #0: nam=’log file parallel write’ ela= 1294 files=2 blocks=80 requests=2 obj#=-1 tim=13758521264788WAIT #0: nam=’rdbms ipc message’ ela= 9538 timeout=280 p2=0 p3=0 obj#=-1 tim=13758521275843WAIT #0: nam=’log file parallel write’ ela= 909835 files=2 blocks=8 requests=2 obj#=-1 tim=13758521278533
Mount points for redo logs was targeted and I contacted my SAs for DMX system. They immiediately found that out of four fiber adaptors on database machine , which connects to storage array via SAN switch, one was performing little slower. They asked for some more time to investigate. I waited for almost 1 excrutiating hour and finally heard back from them. It was indeed the IO sub-system. One of the port on SAN switch was “dying” causing frequent IO queues. They swapped the port with another one and it fixed it !!!. So it was just one simple bad port on SAN switch which was giving frequent “heart burns” to the database.
Leason learned…on a very very busy system, esp. an OLTP like Oracle APPS, it is absolutely critical that write throughput on redo logs is maintained at every time. No RAID-5, just simple RAID-1, on faster fiber channels, san switches etc..
quinta-feira, 4 de junho de 2009
Procedure para tentar pegar os locks
procedure get_lock_log is
--create table lock_log_holder (
--pk number,
--sid number,
--sql_address raw(8),
--username varchar2(30),
--osuser varchar2(30),
--estacao varchar2(30),
--program varchar2(50),
--horario date,
--comando varchar2(4000)
--ds_usu VARCHAR2(30));
--create table lock_log_waiter (
--pk number,
--sid number,
--sid_holder number,
--sql_address raw(8),
--username varchar2(30),
--osuser varchar2(30),
--estacao varchar2(30),
--program varchar2(50),
--horario date,
--comando varchar2(4000)
--ds_usu VARCHAR2(30));
-- create sequence seq_lock_log_holder increment by 1;
-- create sequence seq_lock_log_waiter increment by 1;
v_sql_address varchar2(16);
v_username varchar2(30);
v_osuser varchar2(30);
v_estacao varchar2(30);
v_program varchar2(50);
v_sysdate varchar2(16);
v_sql_text varchar2(64);
v_ds_usu varchar2(30);
v_pk_holder number;
v_pk_waiter number;
begin
-- Who are the Lock Holders?
for holder in (select sid, id1, id2 from sys.v$lock where block = 1) loop
select to_char(sysdate, 'DD-MON-YY hh24:mi') into v_sysdate from dual;
-- Get the Holder´s SQL_ADDRESS
select a.sql_address, a.username, a.osuser, decode(substr(a.terminal,1,24),'Window',a.machine,a.machine), a.program, b.ds_usu
into v_sql_address, v_username, v_osuser, v_estacao, v_program, v_ds_usu
from sys.v$session a, vld.usuarios b
where a.sid=holder.sid
and a.username=b.nome_usu;
-- Record the Holder
insert into lock_log_holder
values(seq_lock_log_holder.nextval,
holder.sid,
v_sql_address,
v_username,
v_osuser,
v_estacao,
v_program,
to_date(v_sysdate,'DD-MON-YY hh:mi'),
' ',
v_ds_usu)
returning pk into v_pk_holder;
-- Then record the Holder´s SQL_TEXT
for text in (select sql_text from sys.v_$sqltext where address = v_sql_address order by piece) loop
update lock_log_holder set comando = comando || ' ' || text.sql_text where pk=v_pk_holder;
commit;
end loop;
commit;
v_sql_address := '';
v_username := '';
v_osuser := '';
v_estacao := '';
v_program := '';
v_sql_text := '';
v_ds_usu := '';
-- Get the Waiter´s SID and SQL_ADDRESS
for waiter in (select sid from sys.v$lock where id1=holder.id1 and id2=holder.id2 and block=0) loop
-- Get the Waiter´s SQL_ADDRESS
select a.sql_address, a.username, a.osuser, decode(substr(a.terminal,1,24),'Window',a.machine,a.machine), a.program, b.ds_usu
into v_sql_address, v_username, v_osuser, v_estacao, v_program, v_ds_usu
from sys.v$session a, vld.usuarios b
where a.sid=waiter.sid
and a.username=b.nome_usu;
-- Record the Waiter
insert into lock_log_waiter
values(seq_lock_log_waiter.nextval,
waiter.sid,
holder.sid,
v_sql_address,
v_username,
v_osuser,
v_estacao,
v_program,
to_date(v_sysdate,'DD-MON-YY hh:mi'),
' ',
v_ds_usu)
returning pk into v_pk_waiter;
--Then get the Waiter´s SQL_TEXT
for text in (select sql_text from sys.v_$sqltext where address = v_sql_address order by piece) loop
update lock_log_waiter set comando = comando || ' ' || text.sql_text where pk=v_pk_waiter;
commit;
end loop;
commit;
end loop;
v_sql_address := '';
v_username := '';
v_osuser := '';
v_estacao := '';
v_program := '';
v_sql_text := '';
v_ds_usu := '';
v_sysdate := '';
v_pk_holder := '';
v_pk_waiter := '';
end loop;
commit;
exception
when others then dbms_output.put_line(sqlerrm);
end;
na Cron
# Investigacao Locks Decat (NTUX)
0-59 8-17 * * 1-5 /ora817/ntux/get_lock_log_decat.sh
.sqls
/ora817/ntux>more sql.sql
set linesize 200
select optimizer_mode,first_load_time,executions,buffer_gets,disk_reads,sql_text from v$sqlarea where address='&addr'
/
/ora817/ntux>more sql.sql
set linesize 200
select optimizer_mode,first_load_time,executions,buffer_gets,disk_reads,sql_text from v$sqlarea where address='&addr'
/
/ora817/ntux>more allsql.sql
column sid format 999
set linesize 200
set pagesize 1000
column user_ora format a8
column conexao format a13
col program for a21 trunc
col sql_text for a70
select s.sid SID,
s.sql_address,
s.username USER_ORA,
s.program PROGRAM,
--s.status,
to_char(s.logon_time,'ddMonyy hh24:mi') CONEXAO,
executions,
sql_text
from V$SESSION s,
V$SQLAREA a
where a.hash_value = s.sql_hash_value
and a.address = s.sql_address
--and upper(a.sql_text) like '%COMMIT%'
and username is not null
and s.status = 'ACTIVE'
order by 5 desc;
/ora817/ntux>more lock2.sql
SELECT sid, DECODE( block, 0, 'NO', 'YES' ) BLOCKER,
DECODE( request, 0, 'NO', 'YES' ) WAITER
FROM v$lock
WHERE request > 0 OR block > 0 ORDER BY block DESC
/
/ora817/ntux>more prcs.sql
select s.sid, s.osuser, a.optimizer_mode,a.first_load_time,a.executions,a.buffer_gets,a.disk_reads,a.sql_text
from v$sqlarea a, v$session s, v$process p where a.ADDRESS=s.sql_address and s.paddr=p.addr and p.spid='&prcs';
/ora817/ntux>more sqltext.sql
SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SELECT --s.sid,
s.status "Status",
--p.spid "Process",
--s.schemaname "Schema Name",
--s.osuser "OS User",
--Substr(a.sql_text,1,120) "SQL Text",
s.program "Program",
a.sql_text
FROM v$session s,
--v$sqlarea a,
v$sqltext a,
v$process p
WHERE s.sql_hash_value = a.hash_value (+)
AND s.sql_address = a.address (+)
AND s.paddr = p.addr
--AND s.status = 'ACTIVE'
AND s.sid = '&sid'
order by s.sid, a.piece;
SET PAGESIZE 14
SET FEEDBACK ON
/ora817/ntux>more cpu.sql
set pages 200
set lines 132
col username for a16
col program for a32
col pid for a5
col sid for 99999
select p.spid PID,
s.sid,
s.username,
decode(nvl(p.background,0),1,bg.description,s.program) program,
ss.value/100 "CPU (segs)",
physical_reads "disk_io (Blks)"
from v$process p,
v$session s,
v$sesstat ss,
v$sess_io si,
v$bgprocess bg
where s.paddr=p.addr
and ss.sid=s.sid
and ss.statistic#=12
and si.sid=s.sid
and bg.paddr(+)=p.addr
order by 5 desc;
/ora817/ntux>more reads.sql
set lines 200
set pages 999
col sql_text for a75 word_wrapped
col username for a10
col executions for 99999999999
col loads for 99999999999
col DISK_READS for 99999999999
col ROWS_PROCESSED for 99999999999
col BUFFER_GETS for 99999999999
select c.sql_text,
username,
executions,
loads,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea a,
dba_users b,
v$sqltext c
where a.PARSING_USER_ID = b.user_id
and DISK_READS > 5000000
and b.username <> 'SYS'
and a.hash_value=c.hash_value
order by c.piece
/
/ora817/ntux>more sql_text.sql
select piece, sql_text from sys.v_$sqltext where address = upper('&address') order by piece;
/ora817/ntux>more sid.sql
select s.sid, p.spid, s.osuser, a.optimizer_mode,a.first_load_time,a.executions,a.buffer_gets,a.disk_reads,a.sql_text
from v$sqlarea a, v$session s, v$process p where a.ADDRESS=s.sql_address and s.paddr=p.addr and s.sid='&sid';
Assinar:
Postagens (Atom)
