Re: Oracle White Papers: architecture compared to other RDBMS

in article Q5pw7.42551$jE3.4977792_at_news6-win.server.ntlworld.com, Kalten at cyberkalt_at_ntlworld.com wrote on 10/8/01 2:44 PM:

> Hi all,
>
> Hope someone out here can help. I am looking for some white papers with
> regards to Oracles database architecture for comparing with other RDBMSs.

Wow - A product manager's dream come true !! Anyhow, there are simply far too many. I suggest you start with OTN (Oracle Technology Network), especially the section at
http://otn.oracle.com/products/oracle9i/content.html

Look at the white papers on this page, and peruse some of the others in the Related Topics section on the right hand side. If you then have questions, I'm sure the newsgroup will be happy to answer ;-)

Note that you will have to register for OTN, but it is free.

> I need papers that highlight Oracle's strengths (and hopefully weaknesses
> too). For example, is it a single or multi-threaded architecture (I
> understand it is multi-threaded as queries can be parallel-processed).

Hmm - parallelism isn't related to threaded-ness in Oracle. In Oracle, multi-threaded refers to an architecture that allows multiple users to have their requests satisfied by a shared backend server process - as opposed to a dedicated server architecture (also supported in Oracle), where each connected user gets their own dedicated background process. The actual engine itself also consists of a number of other 'components', which are individual processes on some OS's (such as Unix), or are threads of a single process on some OS's (NT). Parallelism is a completely seperate topic - basically the ability for the database to take a single operation, and perform small parts of it in parallel. In Oracle, parallelism is independent of which server architecture is being used. The confusion comes from the Informix world, where parallelism does depend on the number of engine 'threads' that are configured.  

> As I understand it, there is the 10% rule in Oracle in that if the query
> retrieves more than 10% of records in the database, it will bypass any index
> and go straight to the tablespace to save having to search index > record >
> index > record etc.

Oracle's CBO will bypass indexes if a table scan is more efficient. However, the calculation that is used to determine when a scan is more efficient is much more complex than just '>10% of the records'. Note that all good databases will also do this - not just Oracle.  

> I understand that unlike SQL Server which, after locking so many records
> within a table, raises the lock from row level to page/table level which may
> result in unnecessary locks on the records.

Lock escalation basically occurs because some databases manage locks in memory. As concurrency rises, more locks need to be managed, and eventually there isn't enough physical memory to store row level locks - so some databases will attempt to combine a number of row level locks into page level locks. This can cause problems - deadlocks etc. Oracle does two things differently - firstly, it doesn't use read locks, so there are less locks to manage, and secondly, it sort of stores the locks on disk - so as data is paged in and out of the cache, the corresponding locks are also paged. It's actually much more complicated than this, but basically, Oracle cannot run out of memory for lock management, so doesn't need to escalate locks. Many people who use Oracle solely use Oracle for this reason - no read locks, and non-escalating row level locking.

> There are also other issues that I would be interested in reading about. I
> understand that Oracle uses something called shared-drive architecture
> whereas DB2 uses shared-nothing. Each has its merits and weaknesses. Can
> anyone point me in the direction of some white papers that explain all?

Note that this is for clustered databases only - and their is a great deal of information on the web about the pro's and cons of each approach.  

> I appreciate any help (as I am not an Oracle Guru. :P).
>
> Thx,
>
> Kalten
> p.s. Please send a reply to my email address.
Received on Mon Oct 08 2001 - 22:12:41 CDT