Re: Redos gone crazy--a job for audit?

2003-10-10 Thread Barbara Baker
Dan: Thanks for this -- I'll definitely tuck this away for future reference. Sadly, it's not going to help this time. I don't have a user generating redo, I have an application running amuck. The users (reporters) never log into the database. Some service (Solaris high availability service, I

Re: Redos gone crazy--a job for audit?

2003-10-10 Thread Daniel Fink
Barb, Even if you can't find the user, you can still find the session info and run a trace on the session. If it is consistent, you should be able to trace for a short amount of time and retrieve the statements that are generating redo. Then you can go back to the vendor and say This statement

Re: Redos gone crazy--a job for audit?

2003-10-10 Thread Boris Dali
Barbara, Shoot in the dark. Any chance last vendor upgrade introduced global temporary tables? --- Daniel Fink [EMAIL PROTECTED] wrote: Barb, Even if you can't find the user, you can still find the session info and run a trace on the session. If it is consistent, you should be able to

RE: Redos gone crazy--a job for audit?

2003-10-10 Thread Igor Neyman
Why? Did you have bad experiences with temp tables? I thought, using temp tables should reduce amount of redo. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Boris Dali Sent: Friday, October 10, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Barbara, Shoot in the

RE: Redos gone crazy--a job for audit?

2003-10-10 Thread Boris Dali
Igor, Try running the following test: create table t6 (i int) ; create global temporary table t7 (i int) on commit delete rows; select value from v$mystat m, v$statname s where m.statistic#=s.statistic# and s.name = 'redo size'; -- Note the value here insert into t6 select obj# from

RE: Redos gone crazy--a job for audit?

2003-10-10 Thread Jamadagni, Rajendra
I just repeated this test and found that for normal tables the redo size is 292848 for GTT the redo size is 1581888 (increase of 540%) Thank You Thank You Thank You Thank You Thank You Thank You Boris for mentioning this. Raj

RE: Redos gone crazy--a job for audit?

2003-10-10 Thread Igor Neyman
Boris, I used your script (well, almost: in your script you create temporary global table, but never use it, so, I modified it). And it shows redo size increase substantially lower (~7 times) in case of using temp table. But, I was running script on 8.1.5. When, running on 9.2 it appears, that

RE: Redos gone crazy--a job for audit?

2003-10-09 Thread Jamadagni, Rajendra
Title: RE: Redos gone crazy--a job for audit? log miner should give you what you want ... why not? On last friday something happened and in our database which usually averages about 100x100M archive logs, it started throwing 41 files between 2pm-3pm, 248 between 3pm-4pm, 95 between 4pm-5pm

Re: Redos gone crazy--a job for audit?

2003-10-09 Thread Mladen Gogala
Here is a query that will help you find the biggest consumer of redo blocks: select sid, value from (select s.sid,s.value from v$sesstat s, v$statname n where s.statistic#=n.statistic# and n.name='redo blocks written' order by value); However, 50MB redo

Re: Redos gone crazy--a job for audit?

2003-10-09 Thread Mladen Gogala
It should have been redo entries, not redo blocks written. The only guy writing redo blocks is LGWR, so the previous query would always give a hume amount for SID=4 and 0 for everybody else. On Thu, 2003-10-09 at 13:09, Barbara Baker wrote: Hi, list. Ya, I'm still alive and kickin'. We have

RE: Redos gone crazy--a job for audit?

2003-10-09 Thread Rich Gesler
Just a quick thought, are any tablespaces left in Hot Backup Mode? Rich -Original Message- Barbara Baker Sent: Thursday, October 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird

RE: Redos gone crazy--a job for audit?

2003-10-09 Thread Khedr, Waleed
Do you have the database in backup mode? Waleed -Original Message- Sent: Thursday, October 09, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Hi, list. Ya, I'm still alive and kickin'. We have this small database that's running a weird vendor application. (We get all the

RE: Redos gone crazy--a job for audit?

2003-10-09 Thread Barbara Baker
Well, that was an excellent idea. But sadly, that's not it. (We actually don't use hot backups, but I checked just in case someone mucked with it. No dice.) Thanks. Barb --- Khedr, Waleed [EMAIL PROTECTED] wrote: Do you have the database in backup mode? Waleed -Original Message-

Re: Redos gone crazy--a job for audit?

2003-10-09 Thread Daniel Fink
Barb, Every time I have run into this situation, I have used the following approach and it has always worked. I've never validated it in all cases, so take it with a grain of salt. Redo is generated by block changes. Find the session that is generating the most # of block changes

RE: Redos gone crazy--a job for audit?

2003-10-09 Thread Khedr, Waleed
Sample the top sessions from v$sesstat for statname 'redo size' (statistic# 115 in my database) Then joining to v$sql should give you the sql that generates that redo. Waleed -Original Message- Sent: Thursday, October 09, 2003 2:54 PM To: Multiple recipients of list ORACLE-L Well,

Re: Redos gone crazy--a job for audit?

2003-10-09 Thread M Rafiq
You can track the sql which is doing DML while redo being generated. Look at v$sqlarea or v$sql. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 09 Oct 2003 09:09:24 -0800 Hi, list. Ya, I'm still alive and kickin'. We have this