Home » RDBMS Server » Server Administration » Archive Files filling up
Archive Files filling up [message #297249] Wed, 30 January 2008 16:42 Go to next message
deweye
Messages: 2
Registered: January 2008
Junior Member
I don't have any queries running, and when I look at the sessions that are running, I don't see any named ones except the one I am using to run my Enterprise Manager Console.

So why would my archive logs be filling up to the tune of about 15 per minute? Each one is 42MB.

Thanks in advance,
Randy
Re: Archive Files filling up [message #297250 is a reply to message #297249] Wed, 30 January 2008 17:06 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Queries do not generate REDO.
DML generates REDO.
If you want to learn what is being changed use LOGMINER.

or

SQL> select count(*) from v$open_cursor where sql_text like 'INSERT%';

  COUNT(*)
----------
        13

& do the same for UPDATE and DELETE

[Updated on: Wed, 30 January 2008 17:09] by Moderator

Report message to a moderator

Re: Archive Files filling up [message #297281 is a reply to message #297249] Thu, 31 January 2008 00:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you have jobs.
If you are in 10g Oracle makes many things like gathering data and activity statistics.

Regards
Michel
Re: Archive Files filling up [message #297284 is a reply to message #297249] Thu, 31 January 2008 00:46 Go to previous messageGo to next message
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
Member
Oracle does a lot of private DML operations on dictionary objects while you are just doing plain selects. Thus a plain select statement from user perspective also generates Redo. This can be verified by logminer. Most of EM operations are select only and they generates a good amount of Redo. [if you are not using em for making changes].

Regards

Manjit Kumar[mkbhati]

[Updated on: Thu, 31 January 2008 00:48]

Report message to a moderator

Re: Archive Files filling up [message #297454 is a reply to message #297249] Thu, 31 January 2008 14:20 Go to previous messageGo to next message
deweye
Messages: 2
Registered: January 2008
Junior Member
Thank you guys very much. You guys are all very helpful. I checked the logs and actually found some disk full issues that led to some odd hung jobs which explained my log files.

Now the question is, "Is there a way to tell the database not to do squat when I am just doing Selects?" Kind of like the performance benefits of the 'with (NOLOCK)' in SQL Server?

I have been a long time user and even a DBA with ORACLE, but I must confess that the rate of change and enhancements with ORACLE has my head spinning and makes it difficult to be up on all it has to offer.

Currently, I have a very real problem of trying to combine very large datasets and get the performance I would expect. I would like to use a Materialized View but the one I tried to create would never actually finish running and it was so large that it pushed my disk storage envelope over the edge. Hence, I have created a regular view, but the performance is not what I would hope for. It is decent on small result sets, but terrible when trying to aggregate large time periods of data and completely unusable with any ORDER BY clause.

Any wild or generic suggestions would be very helpful.

Thanks again,
Randy
Re: Archive Files filling up [message #297455 is a reply to message #297454] Thu, 31 January 2008 14:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there a way to tell the database not to do squat when I am just doing Selects?" Kind of like the performance benefits of the 'with (NOLOCK)' in SQL Server?

Select does not lock anything in Oracle.

For performances question read How to Identify Performance Problem and Bottleneck and the provided links, above all Database Performance Tuning Guide (Part IV Optimizing SQL Statements).

Regards
Michel
Re: Archive Files filling up [message #297482 is a reply to message #297249] Thu, 31 January 2008 23:33 Go to previous messageGo to next message
mkbhati
Messages: 93
Registered: February 2007
Location: Mumbai
Member
Quote:
I have been a long time user and even a DBA with ORACLE, but I must confess that the rate of change and enhancements with ORACLE has my head spinning and makes it difficult to be up on all it has to offer.


If you have a situation with your data than there is always some kind of solution or feature available from Oracle. The advancements in few recent releases of oracle have made it first choice among databases. In fact Oracle has become more complex than modern operating systems. You may call it any thing but I call it Beauty of Oracle. This beauty keeps we DBAs employed & paid.

Oracle has already absorbed storage technologies into Oracle server by giving us ASM which has been successfully accepted by oracle users. Do not be surprised if in next few releases oracle gives you an OS kernel built into Oracle server....? thus giving you a true platform independent RDBMS.

All though they [Oracle] claim that they can teach oracle to any one within 2 days [2days DBA Guides & 5 Days Oracle university courses]. But actual reality is that after spending 10 years on oracle one still find himself struggling with oracle when you are thrown with a never seen error.

The resultant of all above can be summed in one sentence that Oracle gives you Bricks & Tools only and you as an architect have to assemble a complex building for your self. Your choice, what & how you want to built.

By the way do not get dishearten, have a look on VLDB features, hope you will find there what you are looking for.

And thanks for sharing your opinion.

Regards
Manjit Kumar [mkbhati]

[Updated on: Thu, 31 January 2008 23:58] by Moderator

Report message to a moderator

Re: Archive Files filling up [message #299971 is a reply to message #297249] Wed, 13 February 2008 21:56 Go to previous messageGo to next message
suhasbhide
Messages: 9
Registered: February 2008
Junior Member
Hi Michel...

You mentioned "Select does not lock anything in Oracle". But, I have observed some of my developers doing test using stmts like
"Select * * * for update" will put a lock on table. Maybe others can check and explain.
Re: Archive Files filling up [message #300011 is a reply to message #297249] Thu, 14 February 2008 00:31 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:
This beauty keeps we DBAs employed & paid.

but this beauty also has automated so many things that i doubt except PT,what other role could a DBA have in the near future?
And what about aspiring new DBAs? Sad
Re: Archive Files filling up [message #300025 is a reply to message #299971] Thu, 14 February 2008 01:01 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Select for update" is not select, it is like "update" from the locking point of view.

Regards
Michel
Previous Topic: Low open file descriptor
Next Topic: i want to insert row in table t1
Goto Forum:
  


Current Time: Wed Dec 07 07:09:06 CST 2016

Total time taken to generate the page: 0.32897 seconds