| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: oracle9i performance on Windows Server
"Paul Brewer" <nothing_at_nowhere.com> wrote in message news:<40e1d369$1_3_at_mk-nntp-1.news.uk.worldonline.com>...
> "Paul Drake" <bdbafh_at_gmail.com> wrote in message
> news:910046b4.0406261804.18d4db9e_at_posting.google.com...
> > Sybrand Bakker <sybrandb_at_hccnet.nl> wrote in message
> news:<8ljrd0hd126prmh6kqgcpjgagca0qgqv5o_at_4ax.com>...
> > > On 26 Jun 2004 10:59:01 -0700, premmehrotra_at_hotmail.com (Prem K
> > > Mehrotra) wrote:
> > >
> > > >My managemnt wants to use Windows servers instead of UNIX servers to
> > > >save costs. LINUX is not an option because my data center does not
> support
> > > >it. We are looking into buying HP's ProLiant G2 BL20p model of servers.
> It has
> > > >2 CPU's. We will get maximum memory of 8GB.
> > > >
> > > >We may get 1 or 2 internal drives. My database is not that big
> ~10GB.
> > > >However, 100 of users will be concurrently accessing it. Also, it will
> > > >be used for OLTP as well as for reporting.
> > > >
> > > >I have not worked with Windows server earlier. Are there benchmarks
> avaiable
> > > >for WIindows server vs HP UNIx servers. I have nothing against using
> Windows
> > > >Servers, just want to make sure performnace will not be an issue.
> > > >
> > > >
> > > >Thank you.
> > > >
> > > >Prem
> > >
> > >
> > > Google is your friend as usual. Recently (IIRC Howard Rogers)
> > > conducted a comparative study between Linux and Windoze.
> > > Needless to say Linux was much faster.
> > >
> > > Note also you need Windows Advanced Server to make use of any memory
> > > beyond 4 G at all, and Dead Connection Detection will not work, and
> > > prespawned servers will also not work.
> > > You may be in for some unpleasant suprises.
> > > By design the Windows O/S has limited scalability unlike Unix.
> > >
> > > Please make sure you have your management sign a letter you aren't
> > > responsible for any performance issues, which result from the platform
> > > swap.
> >
> > "Dead Connection Detection will not work"
> > Sybrand speaketh the truth here.
> >
> > nothing like a user that is blocked but is holding locks hitting
> > <ctrl>+<alt>+<del>, logging back into the app, and repeating what they
> > just did, slamming right into the locks held by their now zombied
> > session.
> >
> > logjam.
> >
> > be prepared to do some database babysitting, and rolling a routine to
> > (ora)kill zombies.
> >
> > this is without a doubt the number one PITA running on win32.
> >
> > -bdbafh
>
> Wow. I'm astonished. I must have been leading a life of luxury.
>
> Recent anecdote:
>
> We run Oracle 817 and 912 on hpux and Solaris.
> An anxious lead developer rang me on Friday evening. "I'm sorry, Paul; I
> submitted a stupid SQL select against Prod. I'm worried it won't finish, and
> will mess up batch jobs/shutdown/backups".
> "Don't worry, Greg", quoth I. "This is Oracle; you'll be OK. Just
> ctrl-alt-delete, kill your TOAD session, go home and enjoy your weekend. The
> database will clean up". And of course it did, as it has every time since at
> least 1997.
>
> Do I now have to start worrying again in this scenario if the instance is
> running on Winsucks?
>
> Regards,
> Paul
Paul,
this should handle it, with the addition of a .bat wrapper. You'd probably want to augment it to also clobber sessions that are not blocking, but are holding and zombified also. the last_call_et here is 5 minutes, adjust to taste.
SELECT 'host orakill '||i.instance_name||' '||p.SPID kill_cmd,
s.username||' '||s.osuser||' '||s.machine, sysdate log_time FROM v$process p, v$session s, v$instance i, dba_locks l
WHERE p.addr = s.paddr AND s.sid=l.session_id AND l.blocking_others='Blocking' AND s.last_call_et>300
here is the rest of it, not really battle tested yet, whipped up on the laptop on the train ride in this morning:
USE AT YOUR OWN RISK and TEST THIS IN A TESTING ENVIRONMENT
create table system.killed_blockers_log (
spooling_audsid number, spooling_rownum number, kill_cmd varchar2(100), username varchar2(30), osuser varchar2(30), machine varchar2(64), ROW_WAIT_OBJ# NUMBER, ROW_WAIT_FILE# NUMBER, ROW_WAIT_BLOCK# NUMBER, ROW_WAIT_ROW# NUMBER, log_time date, SESSION_ID NUMBER, LOCK_TYPE VARCHAR2(26), MODE_HELD VARCHAR2(40), MODE_REQUESTED VARCHAR2(40), LOCK_ID1 VARCHAR2(40), LOCK_ID2 VARCHAR2(40), LAST_CONVERT NUMBER)
var rownum_to_kill number;
var exec_kill_cmd varchar2(100);
insert into system.killed_blockers_log
SELECT sys_context('USERENV','SESSIONID') spooling_audsid, rownum
spooling_rownum,
'host orakill '||i.instance_name||' '||p.SPID kill_cmd,
s.username, s.osuser, s.machine,
s.row_wait_obj#, s.row_wait_file#, s.row_wait_block#,
s.row_wait_row#,
sysdate log_time, l.session_id, l.lock_type, l.mode_held,
l.mode_requested, l.lock_id1, l.lock_id2, l.last_convert
from v$process p, v$session s, v$instance i, dba_locks l
where p.addr = s.paddr and s.sid=l.session_id and l.blocking_others='Blocking'
select spooling_rownum, username, osuser, machine, lock_type,
mode_held
from system.killed_blockers_log
where sys_context('USERENV','SESSIONID')=spooling_audsid
order by spooling_rownum
/
prompt enter the number of the session to kill accept &rownum_to_kill
select kill_cmd into :exec_kill_cmd
from system.killed_blockers_log
where spooling_audsid=sys_context('USERENV','SESSIONID')
and spooling_rownum=&rownum_to_kill
/
host &exec_kill_cmd
prompt &exec_kill_cmd
prompt there you go, run it again if you're feeiling like a real
Bastard DBA.
pause
exit
Received on Wed Jun 30 2004 - 00:56:58 CDT
 |
 |