Re: db cache size in oracle 9ir2

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1069614570.595998_at_yasure...
> Howard J. Rogers wrote:
>
> > "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> > news:1069549594.549005_at_yasure...
> >
> >>Howard J. Rogers wrote:
> >>
> >>
> >>>Because it doesn't store any real user data, and therefore has a
> >
> > completely
> >
> >>>different function from the data files and the redo logs?
> >>>
> >>>Regards
> >>>HJR
> >>
> >>Not a good enough reason ... or even, IMO, a valid reason. It stores
> >>only one bit of information not contained in the catalog tables: The
SCN.
> >
> >
> > You need to go back and check some more, Daniel.
>
> Always happy to oblige.
>
> > The control file stores the bit that tells us whether the database is in
> > archivelog mode or not.
>
> Ok so it is in one of the v_$ views. It could just as easily be in the
> data dictionary.

You really do need to look a little closer. Where do you think that view gets its information from? It reads it directly out of the control file... as do an awful lot of v$'s. So the comment "ok it's in the v$ views" is really rather meaningless... views are just stored select statements. The actual information has to come from somewhere else. In the case of the archivelog flag visible in v$database, it's coming right out of the control file.

Modyfying that flag requires the alteration of some bits. No redo is generated by such a modification; no rollback is generated; no buffers are read into the buffer cache. Would you rather that such things as putting a database into archivelog mode caused all the complexity of operation that goes on whenever ordinary table blocks are modified? Because if you transfer the information out of the control file into a regular data file, that's exactly what will happen.

Alter database archivelog; will start having to scan the buffer cache LRU list, transfer pointers found onto the dirty list (or checkpoint queue if you're being formal about it), consult a rollback/undo header block to reserve a rollback/undo block, generate some redo, place the rollback/undo, and then modify the bit. Presumably there'd have to be an auto-commit, so we'd then have to generate an SCN for the modification, write that into the log buffer, kick LGWR into action, and wait for the flush to the redo logs.

Quite a palaver.

We'd also have to make that transaction cause DBWR to flush the information out of the buffer cache to disk, because at the moment it's read by a lot of background processes which would still expect to find their stuff in a physical file and not in the buffer cache (either that, or you'd have to re-code those processes to now be able to have access to the buffer cache). CKPT, for example, has to read the control file to determine which files are offline or read only so that it doesn't try and update their header blocks with the new SCN... but CKPT has no rights to look in the buffer cache.

The whole thing's a big no-no.

>
> It contains the bits that describe whether a data file is online,
> offline, read-only or read-write.
>
> But isn't that also in the data dictionary tables?
>
> SELECT file_name, status
> FROM dba_data_Files;

This one I haven't checked, but dba_data_files is a view. Views are built on top of tables... or, as with the v$ views, pull their information direct out of the control file. So I suspect that dba_data_files is just looking, ultimately, at the control file.

>
> It contains the flag which indicates which is the current redo log,
> because that's not in the data dictionary either.
>
> Ok so it is in one of the v_$ views. It could just as easily be in the
> data dictionary.

You do know what a v$ view actually does, don't you?

> It contains the Database ID, which is unique to each
> > dtaabase. It contains the creation date for the database. If you use
RMAN,
> > it stores the repository information for RMAN.
>
> Once again in the data dictionary too is it not?

No. RMAN repository information can be copied or synchronised to a catalog database, at which point the repository information is stored within regular tables, upon which a bunch of RC_ views are built, and at that point I suppose you could say it was in the data dictionary. But a catalog is optional. And even with a catalog, that same information is still stored in the control file in the first instance.

>And RMAN will look
> anywhere the code bangers at Oracle tell it to look.

The control file is quite a special place in which to store such information, because the storage space is expandable *and reusable* (ie, controlfile_record_keep_time). It therefore behaves rather differently from anything else in the database, with the possible exception of rollback/undo segments.

>
> > There's lots it stores which you won't find in the data dictionary.
> >
> > Regards
> > HJR
>
> Thanks Howard but I don't buy it. I think the control file information
> should be stored in the data dictionary. From my experience ... I've
> lost a lot more control files over the years than system tablespaces.
> It is still, to me, complexity without value.

Functionally, it's quite different from a data file.

And I haven't even started to worry about how you go about protecting all this information from failure. There is no provision in Oracle to multiplex data files, but there is provision for multiplexing the control file, and thus protecting the controlfile from loss is a lot easier than it is a data file.

But putting it simply, it makes no sense, functionally or logically, to store the physical and logical description of a database within that database itself. It's the same as with the RMAN catalog: functionally, yes, you can put a catalog within the target it's used to back up. But it would be utter madness to do so, since a failure would knock out both the target itself and the thing you need to recover the target. Likewise, the control file is needed (for example) to establish the point to which all recoveries should aim to finish (non-RMAN ones as well as RMAN ones). Put that inside the thing which actually needs to be recovered before you can read it, and you're asking for an unrecoverable database.

Regards
HJR Received on Sun Nov 23 2003 - 14:00:31 CST