Oracle Myths
Niall Litchfield
Store your segments in one extent for optimum performance.
Space in an index is never reused.
Full table scans are always bad: use the index.
Seperate tables and indexes for performance reasons.
You should always backup the online redo logs to avoid data loss.
Buffer cache hit ratio should be as high as possible preferably greater than
90%.(from the java tool we use developed in house)
Library Cache hit ratio should be greater than 99% if it isn't increase the
size of the shared pool.(ditto!)
Smallest table should be the driving table for hash joins.
PCTIncrease should be as small as possible but non-zero to minimize
tablespace fragmentation.1% is a good value (from my OCP course notes though
not necessarily given by the tutor!)
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
George Barbour
Always set global_name=FALSE in init<SID>.ora
As if nobody will ever ask for Distributed transactions, Advanced queuing,
replication etc.
George Barbour.
"Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message
news:3ce21b71$0$8510$ed9e...@reading.news.pipex.net...
Jeff Traigle
would appreciate more info about. The corporate DBAs in my company wrote a
script that checks some basic performance metrics. Two of the items that it
checks are buffer cache hit ratio and library cache hit ratio.
On 5/15/02 4:25 AM, in article
3ce21b71$0$8510$ed9e...@reading.news.pipex.net, "Niall Litchfield"
<n-litc...@audit-commission.gov.uk> wrote:
> Suggested list to be added to, deleted from etc
>
> Buffer cache hit ratio should be as high as possible preferably greater than
> 90%.(from the java tool we use developed in house)
>
> Library Cache hit ratio should be greater than 99% if it isn't increase the
> size of the shared pool.(ditto!)
--
Jeff Traigle
tra...@si.umich.edu
http://www-personal.si.umich.edu/~traigle/
Fraser McCallum
If you check your buffer cache hit ratio in the traditional way the cache
misses can include the direct reads which no amount of buffer cache tuning
can eliminate. Off the top of my head temporary tablespaces are an example
of something that does direct reads. I know that Steve Adams has scripts
that take all of this into account somewhere on his web site
www.ixora.com.au as well as probably some more in-depth details.
Increasing the shared pool size in response to a bad library hit ratio will
create more memory in which the database will create longer free lists and
thus ultimately increasing the time taken to scan these lists and making
your hit ratio worse. However after you restart the database when their is
still lots of free chunks things will appear to have been made better, thus
many DBA's fall into a cycle of continuos enlargement.
Kind Regards,
Fraser McCallum
MVP Oracle Administration
www.brainbench.com
"Jeff Traigle" <tra...@si.umich.edu> wrote in message
news:B907C95A.139FB%tra...@si.umich.edu...
Niall Litchfield
news:n0tE8.10994$b5.3...@newsfeeds.bigpond.com...
> Jeff,
>
> If you check your buffer cache hit ratio in the traditional way the cache
> misses can include the direct reads which no amount of buffer cache tuning
> can eliminate. Off the top of my head temporary tablespaces are an example
> of something that does direct reads. I know that Steve Adams has scripts
> that take all of this into account somewhere on his web site
> www.ixora.com.au as well as probably some more in-depth details.
In addition the hit ratio is just that a ratio it hides the values you
should be measuring. If you have 2 statements one which does 10 io's to get
the results 3 of which are physical and a second which does 10000 ios 1 of
which is physical the first query will always perform better despite having
a "terrible" hit ratio of 70%.
In addition the last document I have seen from oracle suggests that 80% is a
'good' hit ratio this is lower than often quoted.
>
> Increasing the shared pool size in response to a bad library hit ratio
will
> create more memory in which the database will create longer free lists and
> thus ultimately increasing the time taken to scan these lists and making
> your hit ratio worse. However after you restart the database when their is
> still lots of free chunks things will appear to have been made better,
thus
> many DBA's fall into a cycle of continuos enlargement.
I actually had in mind the more subtle problem of web based apps which
always seem to use literals. increasing the memory to store these statements
is an utterly pointless exercise since they'll never get reused. what you
should be dioing is eliminating the poor sql queries.
NOTE I am also not saying that these measures are completely useless - just
that one needs to understand what is happening in your system and that
ratios (especially) don't tell you everything you need to know.
Joe E O
I have been doing Oracle Database Administration for about two years
(transitioning from Sybase and MS SQL) and have found that these items are
more prevalent and pervasive than myth - they have become truth and accepted
as "best practices" by IT management and many DBAs.
I have had my competence question on the "Store Segments in one Extent"
issue - I wasn't spending time sizing each object to fit in one extent. I am
currently interviewing and "failed" the technical part of an interview
because my responses relating to the "high buffer cache hit ratios are
always better" and "set Pct increase to 1% to avoid tablespace
fragmentation" and the "separate tables and indexes for better performance
reasons"
Very frustrating...
Joe O'Brien
"Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message
news:3ce21b71$0$8510$ed9e...@reading.news.pipex.net...
Daniel Morgan
I can understand this and no doubt others here can too. I have had more than a
few interviews where I found myself correcting the information that the
interviewers believed to be true. The only saving grace, as I teach Oracle, is
that they perceive me as knowing more than they do. Without that ... I am sure
I'd have been excused and never contacted again.
Daniel Morgan
Daniel Morgan
Thanks. I am going to get on the first one and see what I can determine.
Daniel Morgan
Alan
reasons." is not true?
Daniel Morgan
> Can someone explain why "Seperate tables and indexes for performance
> reasons." is not true?
To avoid your being pummeled ... let me suggest that you look in the
Google archives. This one was painfully beaten into submission a few
weeks ago. And as I score it ... the experts convinced the skeptics.
Daniel Morgan
Connor McDonald
A simple PL/SQL program to obtain any desired hit ration for a system
can be downloaded from my site...
SQL> exec choose_hit_ratio(99.3)
and will be churn away happily until your hit ratio is 99.3
Magic!
--
==============================
Connor McDonald
"Some days you're the pigeon, some days you're the statue..."
Chris Fournier
Store your segments in one extent for optimum performance.
Seperate tables and indexes for performance reasons.
Buffer cache hit ratio should be as high as possible preferably greater than
90%.(from the java tool we use developed in house)
Thanks!
"Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message
news:3ce21b71$0$8510$ed9e...@reading.news.pipex.net...
Pablo Sanchez
"Daniel Morgan" <dmo...@exesolutions.com> wrote in message
news:3CE2A78A...@exesolutions.com...
Wow, I missed that thread ... anyway, I separate the two for one and
only one reason:
Different I/O characteristics between the two types of data.
I find that typically there's some locality of data in 'data' versus
'indexes' which is why I love DB_FILE_MULTIBLOCK_READ. Sure we might
do an index scan but typically not.
--
Pablo Sanchez, High-Performance Database Engineering
mailto:pa...@hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts
Nuno Souto
>
One more I've been hitting very often of late:
- You cannot create declarative RI across schemas. Oracle doesn't let
you do it.
(could it be these people have never heard of the REFERENCES grant?)
- Defining RI in application code is safer and less maintenance
intensive than doing it in the databse.
Sometimes I wonder...
Cheers
Nuno
Nuno Souto
Oh yeah, almost forgot:
- Put the most selective column first in a concatenated index.
(this one AFAIK was never true, but somehow it stuck)
Cheers
Nuno
Gerald B. Rosenberg
litch...@audit-commission.gov.uk says...
> Suggested list to be added to, deleted from etc
>
>
Good list, but I for one would would find the list more helpful if you
could add the appropriate counterpoint statement for each "myth."
Thanks,
Gerald
Niall Litchfield
news:3CE2A7...@yahoo.com...
> SQL> exec choose_hit_ratio(99.3)
>
> and will be churn away happily until your hit ratio is 99.3
Hmm have to get hit ratios into my performance targets!
Niall Litchfield
news:ue5fnl8...@corp.supernews.com...
> I have questions about a few of these which I though were correct:
>
> Store your segments in one extent for optimum performance.
This is guaranteed (in a dictionary managed tablespace) to cause
fragmentation. Segments that are constantly grabbing a new extent simply
have the wrong extent size but io against a (say) 2gb table will perform
indistinguishably regardless of wether the segment has 1 or 100 extents.
More to the point with the advent of 8i I consider (though I know others
disagree) that all tablespaces should be locally managed (with the
exception of SYSTEM which can't be) with uniform extent allocation.
>
> Seperate tables and indexes for performance reasons.
see the huge thread on this earlier at http://shrinkalink.com/201
I think my summary of this is that in general seperating data and indexes
should be done for
1) management reasons only.
>
> Buffer cache hit ratio should be as high as possible preferably greater
than
> 90%.(from the java tool we use developed in house)
the goal should be to achieve you results in the minimum number of ios - a
hit ratio hides the volume of work being done. mean time connor's piece of
code referred to above will let you pick any hit ratio you may be required
to meet, this in itself demonstrates the foolishness of relying upon a
single, simple metric to diagnose good performance.
HTH
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************>
> Thanks!
>
>
> "Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message
> news:3ce21b71$0$8510$ed9e...@reading.news.pipex.net...
> > Suggested list to be added to, deleted from etc
> >
> > Store your segments in one extent for optimum performance.
> > Space in an index is never reused.
> > Full table scans are always bad: use the index.
> > Seperate tables and indexes for performance reasons
Niall Litchfield
news:dd5cc559.02051...@posting.google.com...
Interesting the same thing is coming up frequently in
microsoft.public.sqlserver.programming.
I think this is probably a database myth. To be fair to developers there is
a DBA myth which says that you should only validate data in the database you
don't need to do it in the application.
Knut Talman
> To be fair to developers there is
> a DBA myth which says that you should only validate data in the database you
> don't need to do it in the application.
And there are applications which validate nothing in the database, use no
referential integrity etc. and do everything in the application layer (e.g.
Oracle Applications).
Other applications I've seen do everything in the database, they have loads of
triggers, make massive use of DBMS_JOB and totaly rely on database mechanisms.
What do you suggest developers when they ask you how to design their
application?
Regards,
Knut
Howard J. Rogers
column'
Regards
HJR
"Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message
news:3ce21b71$0$8510$ed9e...@reading.news.pipex.net...
Niall Litchfield
jonathan lewis posted a while back that the optimum strategy from a data
security point of view was to be 'a real bastard to your users' i.e.
validate at the data and just pass the error stack back.
My view would be that you should validate seperately (but consistently!) at
both layers. the app layer to ensure an effective user experience,the data
layer to protect the data .
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************"Knut Talman"
<knut....@mytoys.de> wrote in message news:3CE3731A...@mytoys.de...
Nuno Souto
(and I quote):
> I think its open to argument.
Not wrong...
>
> jonathan lewis posted a while back that the optimum strategy from a data
> security point of view was to be 'a real bastard to your users' i.e.
> validate at the data and just pass the error stack back.
That is the "defensive mode". Works. Lots of work, too.
>
> My view would be that you should validate seperately (but consistently!) at
> both layers. the app layer to ensure an effective user experience,the data
> layer to protect the data .
Yup, same here. I'd take it one step further. Data-specific validations
(type, overflow of column capacity, null/not null and so on) I'd validate
on the database.
Things like "a purchase order cannot be passed without approval if its
value is > $1000" belong in the application. Note that validation of
this requires a relationship between two attributes, possibly derived:
the approval and the calculated total. If the application uses a table to
get the "$1000", fine. But it is a business specific rule. It may be
better located in the appserver code.
Often the separation line is hazy. Particularly when dealing with J2EE
designers: for them everything has to be "decoupled" from anything that
is not Java. And "coupled" with EJBeans. Bottom line: they simply shift
the maintenance needs around and make them totally coupled to the app
server. A total mistake, for which we have to thank Sun and their
"illuminated" designers...
--
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Nuno Souto
(and I quote):
> >
> > - You cannot create declarative RI across schemas. Oracle doesn't let
> > you do it.
> > (could it be these people have never heard of the REFERENCES grant?)
> microsoft.public.sqlserver.programming.
Yeah, but what p*ss*s me off is that I got this one from people that are
supposed to be Oracle "experts". Or so they claim. No matter what I
said, they continued to claim that Oracle does NOT support cross schema
RI. Totally got me off my rocker, darn thing has been there since V7!
Knut Talman
> Often the separation line is hazy. Particularly when dealing with J2EE
> designers: for them everything has to be "decoupled" from anything that
> is not Java. And "coupled" with EJBeans. Bottom line: they simply shift
> the maintenance needs around and make them totally coupled to the app
> server. A total mistake, for which we have to thank Sun and their
> "illuminated" designers...
And what about referential integrity? As I said earlier, some apps even don't
use database mechanisms to ensure referential integrity.
Me, myself and I always always use it. Even w/o the application I have a way to
ensure or check referential integrity.
Regards,
Knut
tojo
Creating a database in Oracle is complex, because you have to use
tablespaces.
(I hate that one)
-- Tom
In article <3ce21b71$0$8510$ed9e...@reading.news.pipex.net>, n-
litch...@audit-commission.gov.uk says...
Pablo Sanchez
I believe that this is critical. The 'closer' you can keep the RI and
business rules in the DB, the better you're off. Otherwise what
happens when application Y comes along developed by yet another group?
As Niall points out, to ensure an optimal user experiece, it's
important to copy some of these checks to the front-end. This
minimizes the chatter between the front-end and the back-end.
Daniel Morgan
Oracle experts, like experts in so many other fields, are self-annointed. I
am considered one by many people and yet I cringe when I look around me at
people (such as many in this group) that know far more than I do.
The problem here is that the only reference point is the OCP ... and I
wouldn't even hire someone because of it.
Daniel Morgan
Daniel Morgan
But that begs the question as to whether the counterpoint statement has
been verified or is just, perhaps, going to show up on another myth list
in two years. I am working right now to set an up an environment where I
hope, this weekend, to put one of them to the test on a Solaris server.
Daniel Morgan
Alan
the Oracle experts don't know what they're talking about, or that half the
Oracle experts know what they are talking about. The other half aren't
talking. Anyway, I posted the question to Metalink, so we'll see what the
consensus of the Oracle cube-dwellers is. Too bad there's no SQL standard
related to this problem- if there was we could count on a definitive answer,
or at least one from Joe Celko.
Best to extract tongue from cheek now, lest I get myself into trouble...
"Daniel Morgan" <dmo...@exesolutions.com> wrote in message
news:3CE288A2...@exesolutions.com...
Nuno Souto
>
> The problem here is that the only reference point is the OCP ... and I
> wouldn't even hire someone because of it.
Never fear: Oracle has just "invented" the OCM. All these problems
will now be a thing of the past.
How do you become one? You take an exam like the OCP one.
Wait a minute, wasn't that the problem to start with? Oh, never
mind...
<why does the competition bother, Oracle
themselves are doing a much better job
of shooting themselves in the foot...>
Cheers
Nuno
Niall Litchfield
news:ac1304$m75sv$1...@ID-114862.news.dfncis.de...
> Well, I read through the archived messages, and discovered that either
half
> the Oracle experts don't know what they're talking about, or that half the
> Oracle experts know what they are talking about. The other half aren't
> talking. Anyway, I posted the question to Metalink, so we'll see what the
> consensus of the Oracle cube-dwellers is. Too bad there's no SQL standard
> related to this problem- if there was we could count on a definitive
answer,
> or at least one from Joe Celko.
Of course you can ignore this if I don't know what I am talking about! but
Jonathan's book has the following lesson drawn from the Cautionary Tale
which I consider to be some of the best advice to be found in any Oracle
book anywhere.
1. You only know what you have discovered so far
2. What worked last time might not work next time
3. Different isn't necessarily wrong.
At least some of the rules I've referred to above as Myths have or had some
basis in fact. Others of them notably the buffer cache myth have some merit
but don't actually say what they are widely considered to say. I'm
considering working some of these (say the top 5) into a presentation, but
it will be interesting to see what comes out of your tar and Mark's offer to
raise with the devs. (plus I'm hoping to have 9i2 to play with soon
bandwidth permitting)
Pascal Byrne
> tablespace fragmentation.1% is a good value (from my OCP course notes though
> not necessarily given by the tutor!)
I've seen this quoted in a recent paper by Michael R. Ault (author of
"Oracle8i Administration and Management"). The reason given was that
SMON will not coalesce free space if PCTINCREASE is zero. Is this
definitly a myth?
-Pascal
Alan
following:
The only reliable benchmark is how it runs on your system.
Anyway, I posted the tablespace question on Metalink, and here is the
repsonse:
"Queries are not serial in that one does not read all the relevant index
block and then start retrieving rows. But, it's not full concurrent either,
in that it reads 1 index block at a time then we fetch the relevant data
blocks. i.e. read one index block, retrieve the relevant rows, repeat, etc.
The only true parallelism would be parallel query.
Even without concurrency in the strictest sense it is still beneficial to
put the indexes and data on different disks. The above info relates to one
query. There is nothing to stop multiple queries against the same rows and
indexes from processing concurrently.
So yes, you can get performance benefit by splitting the data and indexes
onto different disks."
"Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message
news:3ce4be26$0$230$ed9e...@reading.news.pipex.net...
Ed Stevens
I can't say for sure, but my bet is that this falls into the catagory of "not a
myth, but so what?" It's my understanding that with proper tablespace
definition and management, tablespace fragmentation is itself a non-issue.
Whether my understanding of that "fact" is correct or not, the real point I'm
tryng to make is that in some cases, the basic "statement of truth" ( in this
case it is "SMON will not coalesce free space if PCTINCREASE is zero.") may
still be true, but other features/functions make it a non-issue. Kind of like
the old story of the housewife who always cut an inch off the end of a ham
before putting it in the baking pan.
--
Ed Stevens
(Opinions expressed do not necessarily represent those of my employer.)
Nuno Souto
quote):
>
Yupper.
> Even without concurrency in the strictest sense it is still beneficial to
> put the indexes and data on different disks. The above info relates to one
> query. There is nothing to stop multiple queries against the same rows and
> indexes from processing concurrently.
>
> So yes, you can get performance benefit by splitting the data and indexes
> onto different disks."
>
call me a dreamer, but if you put tables and indexes in same tablespace
and spread that tablespace over a number of devices you get EXACTLY the
same result as above.
;-D
Gregory N. Mirsky
I've been trying to follow this thread and quite a few topics have
been brought up. Can you post an updated list of the "Oracle Myth's"?
So far I've learned quite a bit of how some of our DBA's have been
mis-informed or just Bull-S#$@! their way through.
Great thread topic!
Greg
"Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message news:<3ce21b71$0$8510$ed9e...@reading.news.pipex.net>...
> Suggested list to be added to, deleted from etc
>
> Store your segments in one extent for optimum performance.
> Space in an index is never reused.
> Full table scans are always bad: use the index.
> Seperate tables and indexes for performance reasons.
> You should always backup the online redo logs to avoid data loss.
> Buffer cache hit ratio should be as high as possible preferably greater than
> 90%.(from the java tool we use developed in house)
> Library Cache hit ratio should be greater than 99% if it isn't increase the
> size of the shared pool.(ditto!)
> Smallest table should be the driving table for hash joins.
> PCTIncrease should be as small as possible but non-zero to minimize
> tablespace fragmentation.1% is a good value (from my OCP course notes though
> not necessarily given by the tutor!)
>
Pablo Sanchez
"Nuno Souto" <nso...@optushome.com.au.nospam> wrote in message
news:3ce510a9$0$15144$afc3...@news.optusnet.com.au...
>
> > Even without concurrency in the strictest sense it is still
beneficial to
> > put the indexes and data on different disks. The above info
relates to one
> > query. There is nothing to stop multiple queries against the same
rows and
> > indexes from processing concurrently.
> >
> > So yes, you can get performance benefit by splitting the data and
indexes
> > onto different disks."
> >
>
>
> call me a dreamer, but if you put tables and indexes in same
tablespace
> and spread that tablespace over a number of devices you get EXACTLY
the
> same result as above.
You need to look down at the disk heads. If we're issuing zillions of
single I/O's, that's a considerably different profile than zillions of
I/O's that have some multi_block influence.
Daniel Morgan
Thanks. But I feel like I am trying to walk across quick sand.
Has Oracle support bought into the myth or is it still "generally speaking" good
advice?
I am not convinced that striping across multiple disks is the same thing as
placing them physically onto separate disks. The former doesn't necessarily
balance the I/O. It may just give the illusion by obscuring the location.
Daniel Morgan
Daniel Morgan
My best guess would be that it was valid back before 8i and before LMT.
Then I always created my tablespaces with PCTINCREASE 1 and my tables with PCTINCREASE 0.
But why oh why oh why does Oracle, itself, continue to create the system tablespace with the defaults it does? Is it due to optimization
or just a reluctance to change it?
Daniel Morgan
Alan
data and index are on different physical drives, is it
index read, then: data read, then: index read...
OR is it
index read, then: data and index read, then: data and index read...
in quasi-pseudo-faux-graphical form:
R R R R Index Disk
R R R R Data Disk
Or like this:
R R R R
X R R R
I'll report back when I get the answer.
"Nuno Souto" <nso...@optushome.com.au.nospam> wrote in message
news:3ce510a9$0$15144$afc3...@news.optusnet.com.au...
Michael Brown
Mirsky) wrote:
My 2 cents worth:
Buffer cache hit ratio: go to www.hotsos.com and get the paper "Why
99%+ Database Buffer Cache Hit Ratio is NOT OK." This paper
demonstrates that extremely high hit ratios are usually a result of
poorly tuned code.
PCTIncrease should be non-zero. If PCTIncrease is anything other than
0 for both the tablespace and the objects in the tablespace,
fragmentation occurs. Since the number of extents has no bearing on
performance (yes this is arguable if the number is in the tens of
thousands), the best utilization of the space in a tablespace is if
every extent is the same size. This allows any free extent in the
tablespace to be used when a new extent is needed. There is no reason
to coalesce since the only fragmentation is on datafile boundaries (an
extent must be in a single datafile).
Pablo Sanchez
"Michael Brown" <mbr...@japhi.com> wrote in message
news:laraeu4j49vmdqgj4...@4ax.com...
I found the following doc very instructional:
http://technet.oracle.com/deploy/availability/pdf/defrag.pdf
Howard J. Rogers
> I have Jonathan's book, and supoprt his rules comlpetely. I also add the
> following:
>
> The only reliable benchmark is how it runs on your system.
>
> Anyway, I posted the tablespace question on Metalink, and here is the
> repsonse:
>
> "Queries are not serial in that one does not read all the relevant index
> block and then start retrieving rows. But, it's not full concurrent
either,
> in that it reads 1 index block at a time then we fetch the relevant data
> blocks. i.e. read one index block, retrieve the relevant rows, repeat,
etc.
> The only true parallelism would be parallel query.
>
> Even without concurrency in the strictest sense it is still beneficial to
> put the indexes and data on different disks. The above info relates to one
> query. There is nothing to stop multiple queries against the same rows and
> indexes from processing concurrently.
There is also nothing to stop simultaneous queries against two tables. On
this basis, all tables should be housed on a separate disk!
>
> So yes, you can get performance benefit by splitting the data and indexes
> onto different disks."
>
So now you go back to Metalink and say "prove it". Preferably with some
statistics. They won't be able to.
Regards
HJR
Daniel Morgan
I absolutely disagree. For the tablespace ... perhaps back with dictionary managed tablespaces and before 8i. For the objects in the
tablespace? What? (registering complete surprise) Where did this come from?
Daniel Morgan
Pablo Sanchez
"Howard J. Rogers" <d...@hjrdba.com> wrote in message
news:ac41ca$r87$1...@lust.ihug.co.nz...
>
> > > So yes, you can get performance benefit by splitting the data
and indexes
> > onto different disks."
> >
>
> So now you go back to Metalink and say "prove it". Preferably with
some
> statistics. They won't be able to.
I think the burden of proof is on folks who don't believe it's
important.
As of this writing, the seventh place TPC-C result is an HP 9000
Superdome Enterprise Server running Oracle 9i. Reviewing the FDR, on
page 98, we see Appendix E - Disk Storage. Notice that no table
segments are co-mingled on the same tablespace as any index/cluster
tablespaces:
http://www.tpc.org/results/FDR/TPCC/tpcc_hp_sd_750_fdr.pdf
[1410KB]
Jim Kennedy
are a bunch of segments that start at explicit hard coded block locations.
Why do I believe that to be true? If you look in sql.bsq you will see some
unusual create table statements. They have some undocumented parameters.
(which I suggest NO ONE use) Also when we had an Oracle consultant come out
to use the DUL (Data Unloader Tool - and I was involved in the aftermath of
this database reconstruction, not one I knew about until someone from
another department asked me to recover their unbacked up, nonarchivelog mode
database) on a database of ours. It can read the actual data files with out
the Oracle binaries right from the raw files. It does not even have to run
on the same OS that the files were created on. (eg Oracle NT data files can
be DUL extracted on a Sun box without Oracle present) It needs some
configuration files to tell it which file is the system tablespace and the
other database files, but it reads the schema etc right out of the system
datafile. So I suspect that the system tablespace has not been changed due
to this and other reasons.
I could be wrong.
Jim
"Daniel Morgan" <dmo...@exesolutions.com> wrote in message
news:3CE52C02...@exesolutions.com...
Connor McDonald
SYSTEM will be a LMT (by default) in 9i.2
hth
connor
--
==============================
Connor McDonald
"Some days you're the pigeon, some days you're the statue..."
Connor McDonald
I like to think of it like this...
a) Take a wooden house
b) Smash it to pieces with a hammer (ie its now "fragmented")
c) Get some nails
d) Use the hammer to put the house back to together ("defragment")
Voila! We can now proclaim that a hammer is an effective defragmentation
device.
pctincrease is the same. You set to 1 (which causes fragmentation more
often than now), and then claim that setting it to 1 will solve the very
problem that they've just caused.
koert54
- sql.bsq does not contain any unusual create table statements - there are
however a lot of tables that
take part in clustered tables
- dul can extract the data from the system tablespace based on object ID -
which is part of the block header - for example
obj$ is always objectid 18 in Oracle 8.0 & 8.1, tab$ is the first table in
clustertable with id 2, etc
- there's nothing magic about dul unload data coming from datafiles from
another platform, if you know the platform's
characteristics (little/big endian) and the Oracle specifics on that
platform (structure of dba etc)
https://sourceforge.net/projects/jdul/
"Jim Kennedy" <kennedy...@attbi.com> wrote in message
news:m7kF8.47092$UV4.7228@rwcrnsc54...
Pete Sharman
>
>- System does not have hard coded block locations ...
>- sql.bsq does not contain any unusual create table statements - there are
>however a lot of tables that
>take part in clustered tables
>- dul can extract the data from the system tablespace based on object ID -
>which is part of the block header - for example
>obj$ is always objectid 18 in Oracle 8.0 & 8.1, tab$ is the first table in
>clustertable with id 2, etc
>- there's nothing magic about dul unload data coming from datafiles from
>another platform, if you know the platform's
>characteristics (little/big endian) and the Oracle specifics on that
>platform (structure of dba etc)
>
>https://sourceforge.net/projects/jdul/
And just a reminder to make things 100% clear - DUL is a tool that MUST be used
by Oracle only. It's completely unsupported outside this environment. It's a
tool that was written to get as much information as we could from a corrupt
database in much the same way as OS vendors have tools to try to recover corrupt
disks. When an Oracle person uses DUL, you are almost certain to still end up
with a logically corrupt database i.e. not all transactions can be recovered.
You will need to manually re-enter data, but at least you'll have SOME of your
corrupted database recovered.
Once again, DO NOT use DUL without Oracle Support doing it for you.
HTH. Additions and corrections welcome.
Pete
SELECT standard_disclaimer, witty_remark FROM company_requirements;
Nuno Souto
> > call me a dreamer, but if you put tables and indexes in same
> tablespace
> > and spread that tablespace over a number of devices you get EXACTLY
> the
> > same result as above.
>
> You need to look down at the disk heads.
Last thing in my list of priorities, Pablo! :-D
> If we're issuing zillions of
> single I/O's, that's a considerably different profile than zillions of
> I/O's that have some multi_block influence.
> --
It doesn't matter. At all. Think about it. An I/O is an I/O is an I/O.
A disk doesn't care less if the block just read is a table block or an
index block. Totally irrelevant. It's a block, period. The only thing
that may be relevant, remotely, is if it is contiguous to the previous
read or not.
Unless you have a VERY specific and VERY narrow scope application, there
is simply no need to separate table I/O from index I/O at the database
level through tablespaces. It doesn't make any sense unless you are
prepared to do all your I/O control through tablespaces and assign one
table per tablespace. This was done in the past with DB2 and other
databases. No need for that nowadays.
With a modern system, what you do is bunch it up in a single logical unit
and spread it over as many devices as you can, using striping or a LVM.
Guaranteed to give you as good as or better performance than you doing
the work by hand with tablespace separation.
Of course, other considerations enter here. It is safer to separate
tables from indexes, because you may want to re-build indexes or reload a
table or do other maintenance that that would cause uneven distribution
if you had everything in the same tablespace. THAT is the reason for
separating.
But most definitely NOT performance. Not with a modern system with an
LVM and the ability to stripe devices and controllers.
The point is not that separating tables from indexes into individual
tablespaces is no good. The point is that it need not be done if the
only issue is performance.
Pete Sharman
>
almighty great snip
>
>SYSTEM will be a LMT (by default) in 9i.2
>
>hth
>connor
>--
>==============================
>Connor McDonald
>
>http://www.oracledba.co.uk
>
>"Some days you're the pigeon, some days you're the statue..."
Waddya mean "will be"? It is! ;)
Thomas Kyte
you might even find you get BETTER ;)
the goal -- even IO distribution.
puttting indexes on disk1 and data on disk2 -- you may well find your index
(much more cacheable in general then the data) results in very very little
physical IO to disk 1. Disk 2 on the other hand it getting beat up.
stripe disk1 and disk2 and you'll get even io in this simple case.
>
>;-D
>
>--
>Cheers
>Nuno Souto
>nso...@optushome.com.au.nospam
--
Thomas Kyte (tk...@oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp
Nuno Souto
> >
> >call me a dreamer, but if you put tables and indexes in same tablespace
> >and spread that tablespace over a number of devices you get EXACTLY the
> >same result as above.
>
> you might even find you get BETTER ;)
>
> the goal -- even IO distribution.
>
> puttting indexes on disk1 and data on disk2 -- you may well find your index
> (much more cacheable in general then the data) results in very very little
> physical IO to disk 1. Disk 2 on the other hand it getting beat up.
>
> stripe disk1 and disk2 and you'll get even io in this simple case.
>
Exactly! Thanks for explaining it much better than I can. That is
usually the problem that surfaces next when people do the "table/index
shuffle". Much better to just stripe the darn thing to start with. Even
distribution of I/O is the name of the game, not the logical partitioning
of the same.
koert54
>
There's no other way ! It seems to me Oracle is very protective about this
tool ... they won't let you
anywhere near DUL ! :-)
"Pete Sharman" <peter....@oracle.com> wrote in message
news:ac5n8...@drn.newsguy.com...
Nuno Souto
I quote):
> > Once again, DO NOT use DUL without Oracle Support doing it for you.
> >
>
> There's no other way ! It seems to me Oracle is very protective about this
> tool ... they won't let you
> anywhere near DUL ! :-)
>
Hehehe! You CAN write your own, you know that don't you?
koert54
That's what I'm doing right now - hoping to finish it in a couple of
months - currently I can dump tables and clustered tables containing char,
varchar, long, number & dates ... I just need to implement chained &
migrated rows and finish up the CLI ...
"Nuno Souto" <nso...@optushome.com.au.nospam> wrote in message
news:3ce66be4$0$15144$afc3...@news.optusnet.com.au...
Jim Kennedy
NOT something that Oracle even lends to a customer. The process took more
than one day and before he left for the evening he removed the tool from my
disk drive. That was standard operating procedure. I expected it and it
wasn't a problem for me. I just thought it was pretty neat that it could
read multiple files from different OS's and that the program was rather
small (on NT it was under 300K and Solaris a little bigger - Now if MS had
written it it would have needed a DVD... ;-) ). We were able to recover a
limited amount of data. This was not a limitation of the tool rather the
non-technical person who upgraded the database according to the vendor's
instructions dropped all the original data so there was nothing to recover.
Ahh, I found what I meant. You are correct sql.bsq has standard create
statements in it. I had once looked at the contents of the Bootstrap$ table
and it had some extra statements in the create statements in its rows create
table storage clause -> objno 17 extents(file 1 block 59) for example. So
does the block header have the object ID and Oracle just scans the start of
the system tablespace until it finds objectid 1 etc. so it can find the data
dictionary so it can work?
Jim
"Pete Sharman" <peter....@oracle.com> wrote in message
news:ac5n8...@drn.newsguy.com...
Jim Kennedy
Oracle charges. :-)
Jim
"koert54" <koe...@nospam.com> wrote in message
news:eouF8.83609$Ze.1...@afrodite.telenet-ops.be...
koert54
> does the block header have the object ID and Oracle just scans the start
of
> the system tablespace until it finds objectid 1 etc. so it can find the
data
> dictionary so it can work?
That's how I do it - I see no other way ...
I first create a block map for each distinct object id that I find - then if
I want to
unload obj$, I iterate over the blocks containig that specific objectid and
decode the rowdictionary-> rows -> columns
It's a bit more trickier for tab$ & col$ (c_obj#) and user$ (c_user#) -
these are part of clustered
tables and you need to iterate the clustered table's blocks and extract the
correct table data... and
a block from c_obj# can contain up to 15 tables if I'm not mistaken ... this
also means that, although tab$
and col$ have different objectid's they refer to the same clustered table
c_obj# by means of the data objectid
(see second column of obj$)
"Jim Kennedy" <kennedy...@attbi.com> wrote in message
news:XbvF8.17296$Bw6...@rwcrnsc51.ops.asp.att.net...
koert54
price of it... me and another DBA started fooling around
about 'how cool it would be to write our own'- so it became kind of a bet
and a challenge to prove we could do it - had some problems in the beginning
but I'm almost there ...
Anyway - I will not be selling it but make it available to the community
(with no garantuees of course) - otherwise I'll have to support it ... it's
just an
exercise to fill my DUL evenings ...
The first version will be for Oracle 8.0 & 8i on Wintel, followed by Linux
and IBM AIX ...
"Jim Kennedy" <kennedy...@attbi.com> wrote in message
news:TdvF8.49697$UV4.8357@rwcrnsc54...
Pete Sharman
>
>> Once again, DO NOT use DUL without Oracle Support doing it for you.
>>
>
>There's no other way ! It seems to me Oracle is very protective about this
>tool ... they won't let you
>anywhere near DUL ! :-)
Nope, and for damn good reasons. I've heard horror stories of people who
haven't used it the right way. I sure wouldn't want to support the results in
any other way than enforcing it as strictly as we do! :)
koert54
> haven't used it the right way. I sure wouldn't want to support the
results in
> any other way than enforcing it as strictly as we do! :)
hmm from what I have seen it's pretty easy to extract data with DUL - the
result will be same
no matter who's in front of the keyboard - the only difficult thing about
DUL is setting the
platform specific parameters. And DUL only does reads - so it's not like
it'll damage the DB
even more ... BBED on the other hand is shipped with Oracle on the NT
platform and *will* let
you change the blocks directly, being able to corrupt data quite easely
without deep knowledge of
oracle block structures ... I can't imagine what those horror stories would
be, I can only imagine
the relieve of the IT departement recovering whatever data they can get.
btw Pete - you working for Oracle and all - why did Oracle ship BBED with
Oracle on NT and didn't
bother to document it - they even password protected it ... is this a *bug*
and it shouldn't be there in
the first place ? :-)
"Pete Sharman" <peter....@oracle.com> wrote in message
news:ac61l...@drn.newsguy.com...
Jonathan Lewis
Well - I'm off the web for a week, and look at the
size of thread that comes up !
A question I posed at the EOUG conference yesterday
was this:
"When an industry-recognised expert stands on this
stage and tells you something that contradicts what
you heard yesterday from an industry-recognised
expert, who are you going to believe ?"
I hope the answer is "the one who tells you WHY
they make a claim, demonstrates a proof of the
claim, and shows you how to repeat the demonstration
for yourself".
Take the comment below from Metalink. It is no longer
100% true. There is a new nested-loop optimisation that
works as follows -
Get first row from table 1
get related rowids by range scan of index on table2
Get next row from table 1
get related rowids by range scan of index on table2
... repeat as necessary
Sort all acquired rowids into order
For each rowid in turn, visit table 2.
This means that all the I/O on index 2 occurs
before any of the I/O on table 2 - which introduces
another bias into the strategy for table/index/stripe
allocation.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Alan wrote in message ...
Sybrand Bakker
I think the strange thing here is Howard Rogers site got shut down by
Oracle, and what you are doing is clearly an attempt at reverse engineering.
This is usually prohibited by your license, and it looks like you are
clearly violating it.
For me the surprising thing is Oracle didn't take any legal action against
you, which I would recommend them doing
I would also imagine when you make your 'tool' available, you are not going
to assume any legal responsibilities for using it.
This alone would be a sufficient reason to strongly discourage anyone to use
your 'tool' at all. After all, if you hire Oracle to rescue your database
and Oracle screws your database, your legal position is completely
different.
I am also annoyed you are treating Pete Sharman this way.
Is Oracle an Open Source product? Clearly it isn't and they have the full
right not to disclose this information.
You, however, are hacking and reverse engineering, which is clearly illegal,
and not in the interest of the user community.
I would just love to see Oracle stops your reverse engineering work. This
would mean a legal move, definitely more justified than shutting Howard J
Rogers site down.
Regards
--
Sybrand Bakker
Senior Oracle DBA
to reply remove '-verwijderdit' from my e-mail address
Pete Sharman
>
>> Nope, and for damn good reasons. I've heard horror stories of people who
>> haven't used it the right way. I sure wouldn't want to support the
>results in
>> any other way than enforcing it as strictly as we do! :)
>
>hmm from what I have seen it's pretty easy to extract data with DUL - the
>result will be same
>no matter who's in front of the keyboard - the only difficult thing about
>DUL is setting the
>platform specific parameters. And DUL only does reads - so it's not like
>it'll damage the DB
>even more ... BBED on the other hand is shipped with Oracle on the NT
>platform and *will* let
>you change the blocks directly, being able to corrupt data quite easely
>without deep knowledge of
>oracle block structures ... I can't imagine what those horror stories would
>be, I can only imagine
>the relieve of the IT departement recovering whatever data they can get.
Well, if you had an idiot in front on the keyboard ... ;)
>
>btw Pete - you working for Oracle and all - why did Oracle ship BBED with
>Oracle on NT and didn't
>bother to document it - they even password protected it ... is this a *bug*
>and it shouldn't be there in
>the first place ? :-)
Yes, I work for Oracle - how did you guess? ;) Unfortunately, I don't work in
Development and I suspect only the creator of that utility can explain the
reasoning behind it - I certainly can't. Of course, you could always log it as
a bug if you like :)
koert54
"Sybrand Bakker" <pos...@sybrandb.demon.nl> wrote in message
news:uedi3on...@corp.supernews.com...
1. I'm not disrespecting Pete at all - I'm just wandering about BBED (which
I would really appreciate getting some more info on)...
I think Pete is a great guy on this newsgroup - and him working for Oracle
makes it even better !
2. there's not much to reverse engineer if you want to write a dataunloading
tool - - everything is basically documented on the
internet - it's the puzzling to get pieces together that's difficult ...
I'll even post some of the materials I used :
http://oraperf.sourceforge.net/refs/all_dumps.html
http://www.dbakorea.pe.kr/article/db_block.html
several metalink docs on decoding dba, dates and numbers
3. if Oracle says it's not ok - then it's not ok - and that's that ... I'm
just writing this tool because it's a challenge - it's much more fun
than writing yet another performance monitoring tool ... I've seen tools
that generate export dump files, tools that extract data from dump files
etc...
That's basically the same ... but hey - who knows ... maybe I'll never
release it and just write an in-depth paper about it
4. > I would also imagine when you make your 'tool' available, you are not
going
> to assume any legal responsibilities for using it.
> This alone would be a sufficient reason to strongly discourage anyone to
use
> your 'tool' at all.
You're absolutely correct - actually - people should never need to use a
tool like this ... they should have good backup & recovery strategies ...
but
then again - we're all human ...
5. > and not in the interest of the user community.
Let's see how interested someone would be, coming on a site where there is
no real DBA, they purchased a license through their application provider
that went broke,
the db crashed with no valid backup and Oracle refuses to provide support
even if you're willing to pay ... however - in the 6 years that I'm working
as a DBA
consultant this happened to me only once - so maybe you're right that a tool
like that is low in demand.
Sybrand, you basically have some real valid points here - and it's good that
you remind me of them because at first I wanted to open source the tool,
then I thought ...
hmm maybe I'll just release the binaries ... now I'm thinking about not
releasing it until I'm certain that I actually can - if not ... though luck
... this is not my day job !
Mark Townsend
peter....@oracle.com wrote on 5/18/02 3:10 PM:
> Yes, I work for Oracle - how did you guess? ;) Unfortunately, I don't work
> in
> Development and I suspect only the creator of that utility can explain the
> reasoning behind it - I certainly can't. Of course, you could always log it
> as
> a bug if you like :)
It's shipped so that if somebody from Oracle needs to use it remotely, it's
already on the machine.
Nuno Souto
>
> I think the strange thing here is Howard Rogers site got shut down by
> Oracle, and what you are doing is clearly an attempt at reverse engineering.
Relax, Sy. It isn't. Oracle's own doco describes the contents of blocks
in detail and the contents of the dictionary itself are available for
anyone to see. Many books in the market describe how to dump blocks
and analyze them. There is no reverse engineering here whatsoever, just
integration of knowledge.
> I would also imagine when you make your 'tool' available, you are not going
> to assume any legal responsibilities for using it.
> This alone would be a sufficient reason to strongly discourage anyone to use
> your 'tool' at all. After all, if you hire Oracle to rescue your database
> and Oracle screws your database, your legal position is completely
> different.
This is a good point. For anyone writing tools (of any kind!) this is
always a concern. Better to keep a tool like that for oneself. And use
it only when absolutely needed. After all, it's simply not fair that
Oracle charges 4 or 5 grand a day for use of a tool but a DBA outside of
the "sphere of illuminati" cannot get a job at $200/day because he/she is
"too expensive", TCO, yadda-yadda. It's only natural that people may look
for better deals when what comes from Oracle is grossly over-priced.
> You, however, are hacking and reverse engineering, which is clearly illegal,
> and not in the interest of the user community.
Hacking is not illegal, Sy. Not at all. Or else we have to chase up
Steve Adams, Jonathan, Tom Kyte, yourself, Howard, me, etcetc. Do you
think that is even feasible? RE is illegal, but that's not what is
taking place here. In fact if anything, what Steve and Jonathan do can
be more readily classified as RE than this. Yet is isn't, and for good
reason.
> I would just love to see Oracle stops your reverse engineering work. This
> would mean a legal move, definitely more justified than shutting Howard J
> Rogers site down.
Hmmm, don't think they would have a leg to stand on. Either they make
this sort of info available like they do in the manuals and wear this
sort of hacker activity, or they clam up and incur the ire of anyone with
more than a passing interest in point-and-click (which is what they did
before V7 and got into trouble for). So, moot point, really.
Now, anyone that writes a tool like this and makes it:
1- update data.
2- commercially available
is truly inviting trouble. That is for sure.
Pablo Sanchez
"Nuno Souto" <nso...@optushome.com.au.nospam> wrote in message
> In article <3ce51c7a$1_...@news.teranews.com>, you said (and I
quote):
> > > call me a dreamer, but if you put tables and indexes in same
> > tablespace
> > > and spread that tablespace over a number of devices you get
EXACTLY
> > the
> > > same result as above.
> >
> > You need to look down at the disk heads.
>
> Last thing in my list of priorities, Pablo! :-D
>
> > If we're issuing zillions of
> > single I/O's, that's a considerably different profile than
zillions of
> > I/O's that have some multi_block influence.
> > --
>
> It doesn't matter. At all. Think about it. An I/O is an I/O is an
I/O.
Actually, it does matter in that if the disk head is at one end of the
platter and an index I/O sends it to the other end, that can be
significant.
Now, Thomas points out the obvious (which I failed to do! <g>) that
given b-trees, you're typically getting two levels of your b-tree
cached. If you have a table with many many rows, you might see a
b-tree with three levels and given random access to the table, you're
going to have to issue one physical I/O to snarf the leaf.
Pablo Sanchez
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote in message
news:1021745576.11435....@news.demon.co.uk...
>
> Well - I'm off the web for a week, and look at the
> size of thread that comes up !
>
> A question I posed at the EOUG conference yesterday
> was this:
>
> "When an industry-recognised expert stands on this
> stage and tells you something that contradicts what
> you heard yesterday from an industry-recognised
> expert, who are you going to believe ?"
>
> I hope the answer is "the one who tells you WHY
> they make a claim, demonstrates a proof of the
> claim, and shows you how to repeat the demonstration
> for yourself".
I agree. One of the premises that I have is to try and (micro?)
benchmark as much as possible. I may write a small little looping
program to prove/disprove. My other favorite thing to do (mentioned
and probably burried somewhere) is to par/strace/truss to an Oracle
process and 'see' what it's doing from a system call perspective. Of
course I do that on a development environment. I encourage all folks
to do this, very cool to see a read()/write() call. <g>
Nuno Souto
> >
> > It doesn't matter. At all. Think about it. An I/O is an I/O is an
> I/O.
>
> Actually, it does matter in that if the disk head is at one end of the
> platter and an index I/O sends it to the other end, that can be
> significant.
And a table I/O will never send the disk head to the other end? Index or
table is immaterial. What is important is the I/O itself. The less of
those in a single disk, the better. The more you spread the load across
multiple disks, the faster overall I/O will be. If each of them is table
or index is not relevant.
At database and tablespace level, you have no control whatsoever as to
the positioning of the disk heads. Those are determined by the
proportion of the disk platters that is allocated to your data file and
the mix of use of the disk drive.
If you stripe a large datafile across many drives, you will be using a
very small portion of the platters in EACH drive. Which will reduce your
issue with disk heads moving from one end of the platter to the other in
any individual disk.
Again, this has everything to do with the LVM and the striping of your
disks and absolutely nothing to do with whatever you do tablespace-wise
in the database.
This reminds me of a design a couple of years ago of a partitioning
scheme for a very large table (nearly 0.5Tb). The data was keyed by
date. But the PK (and only index) was a surrogate key. Many times a day,
all data for that day had to be scanned. Once a week, all data for THAT
week had to be scanned multiple times. Once a month, all data for that
month had to be scanned multiple times. Data was kept for three months.
Two partitioning schemes were tried. One concentrated on spreading the
I/O load at tablespace level by hashing the key into ten partitions and
keeping every single consecutive row in one of these 10 partitions. Each
partition of course stored in a single tablespace. Great performance in
inserting rows: spread all over the tablespaces.
Absolutely LOUSY for scanning the daily, weekly and monthly stuff: the
ENTIRE table's 10 partitions had to be full scanned for this simple
process. Horrendous load in the system and NO WAY we could run any of
these scans concurrently with daily processing.
Second partitioning scheme: forget the hashing, one partition per day.
Every day, a new partition is created in a STRIPED disk set, the previous
day's partition is moved to a normal set. This takes care of the daily
load of inserts. Now, all the daily scans happen in a single partition
on a striped set. No need to full scan the ENTIRE table. Major
improvement in performance right there. Weekly? Try 7 partitions, all
of them not the current day one. No contention for doing a weekly scan
while the daily processing is going on. Monthly? Try 30 partitions out
of 91. Still one third of the previous (daily) load.
No guesses as to which partitioning proved easier to use and maintain, as
well as having better overall performance. By a country mile.
Andrew Mobbs
>
>I think the burden of proof is on folks who don't believe it's
>important.
>
>As of this writing, the seventh place TPC-C result is an HP 9000
>Superdome Enterprise Server running Oracle 9i. Reviewing the FDR, on
>page 98, we see Appendix E - Disk Storage. Notice that no table
>segments are co-mingled on the same tablespace as any index/cluster
>tablespaces:
Thank you for providing the rope to hang yourself by :-)
If you look at Table 5.2 of that document, you'll see that "Each
logical volume is striped 74-way across all the arrays."
_All_ the disks were being used for both tables and indexes.
The fact they were logically separated into different tablespaces is a
management issue. I use the same sort of configuration at benchmarks.
--
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/
Nuno Souto
quote):
>
> logical volume is striped 74-way across all the arrays."
>
Bang! There goes the disk head overhead out the window! :-)
And I'll bet the partition that is being striped is the outer one in the
platters...
Pablo Sanchez
"Andrew Mobbs" <and...@chiark.greenend.org.uk> wrote in message
news:QpC*mZ...@news.chiark.greenend.org.uk...
> Pablo Sanchez <pa...@dev.null> wrote:
> >
> >I think the burden of proof is on folks who don't believe it's
> >important.
> >
> >As of this writing, the seventh place TPC-C result is an HP 9000
> >Superdome Enterprise Server running Oracle 9i. Reviewing the FDR,
on
> >page 98, we see Appendix E - Disk Storage. Notice that no table
> >segments are co-mingled on the same tablespace as any index/cluster
> >tablespaces:
>
> Thank you for providing the rope to hang yourself by :-)
>
> If you look at Table 5.2 of that document, you'll see that "Each
> logical volume is striped 74-way across all the arrays."
My bad. This is most excellent data that I missed. Thank you for
pointing it out.
Given that RAID with cache performs drastically different than a
regular disk ...
Daniel Morgan
> > > tryng to make is that in some cases, the basic "statement of truth" ( in
> this
> > > case it is "SMON will not coalesce free space if PCTINCREASE is zero.")
> may
> > > still be true, but other features/functions make it a non-issue. Kind
> of like
> > > the old story of the housewife who always cut an inch off the end of a
> ham
> > > before putting it in the baking pan.
> > > --
> > > Ed Stevens
> > > (Opinions expressed do not necessarily represent those of my employer.)
> >
> > My best guess would be that it was valid back before 8i and before LMT.
> >
> > Then I always created my tablespaces with PCTINCREASE 1 and my tables with
> PCTINCREASE 0.
> >
> > But why oh why oh why does Oracle, itself, continue to create the system
> tablespace with the defaults it does? Is it due to optimization
> > or just a reluctance to change it?
> >
> > Daniel Morgan
> >
Thanks.
I am enjoying a good thread without a flame war. Without doubt many will
benefit. Hopefully my students among them.
Daniel Morgan
Daniel Morgan
> Pascal Byrne wrote:
> >
> > "Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message news:<3ce21b71$0$8510$ed9e...@reading.news.pipex.net>...
> > > PCTIncrease should be as small as possible but non-zero to minimize
> > > tablespace fragmentation.1% is a good value (from my OCP course notes though
> > > not necessarily given by the tutor!)
> >
> > I've seen this quoted in a recent paper by Michael R. Ault (author of
> > "Oracle8i Administration and Management"). The reason given was that
> > SMON will not coalesce free space if PCTINCREASE is zero. Is this
> > definitly a myth?
> >
> > -Pascal
>
> I like to think of it like this...
>
> a) Take a wooden house
> b) Smash it to pieces with a hammer (ie its now "fragmented")
> c) Get some nails
> d) Use the hammer to put the house back to together ("defragment")
>
> Voila! We can now proclaim that a hammer is an effective defragmentation
> device.
>
> pctincrease is the same. You set to 1 (which causes fragmentation more
> often than now), and then claim that setting it to 1 will solve the very
> problem that they've just caused.
>
> hth
> connor
> --
> ==============================
> Connor McDonald
>
> http://www.oracledba.co.uk
>
> "Some days you're the pigeon, some days you're the statue..."
The original point of setting pctincrease in the tablespace to 1 was not that it would ever be used. Just to force the clean-up. The
reason to use 1, rather than 50, was that if a mistake did take place it wouldn't make as big a mess.
Daniel Morgan
Daniel Morgan
> I *think* that what Michael was trying to say was the same as you. I.E. that
> the tablespace should have a non-zero % increase and segments should have a
> zero pctincrease. If one can manage to enforce this I guess this is OK,
> however I've never understood why people who advocate this always seem to
> advocate 1% as the non-zero figure. If one can enforce it it doesn't matter
> what the non-zero figure is so why not just take the defaults. If one can't
> enforce it (like in the real world) then it doesn't matter what the non-zero
> figure is you'll always get fragmentation.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "Daniel Morgan" <dmo...@exesolutions.com> wrote in message
> news:3CE58A31...@exesolutions.com...
> > Michael Brown wrote:
> >
> > > On 17 May 2002 07:46:19 -0700, gmi...@optonline.net (Gregory N.
> > > Mirsky) wrote:
> > >
> > > >Niall,
> > > >
> > > >I've been trying to follow this thread and quite a few topics have
> > > >been brought up. Can you post an updated list of the "Oracle Myth's"?
> > > >So far I've learned quite a bit of how some of our DBA's have been
> > > >mis-informed or just Bull-S#$@! their way through.
> > > >
> > > >Great thread topic!
> > > >
> > > >Greg
> > > >
> > > >
> > > >"Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in
> message news:<3ce21b71$0$8510$ed9e...@reading.news.pipex.net>...
> > > >> Suggested list to be added to, deleted from etc
> > > >>
> > > >> Store your segments in one extent for optimum performance.
> > > >> Space in an index is never reused.
> > > >> Full table scans are always bad: use the index.
> > > >> Seperate tables and indexes for performance reasons.
> > > >> You should always backup the online redo logs to avoid data loss.
> > > >> Buffer cache hit ratio should be as high as possible preferably
> greater than
> > > >> 90%.(from the java tool we use developed in house)
> > > >> Library Cache hit ratio should be greater than 99% if it isn't
> increase the
> > > >> size of the shared pool.(ditto!)
> > > >> Smallest table should be the driving table for hash joins.
> > > >> PCTIncrease should be as small as possible but non-zero to minimize
> > > >> tablespace fragmentation.1% is a good value (from my OCP course notes
> though
> > > >> not necessarily given by the tutor!)
> > > >>
> > > >> --
> > > >> Niall Litchfield
> > > >> Oracle DBA
> > > >> Audit Commission UK
> > > >> *****************************************
> > > >> Please include version and platform
> > > >> and SQL where applicable
> > > >> It makes life easier and increases the
> > > >> likelihood of a good answer
> > > >>
> > > >> ******************************************
> > > My 2 cents worth:
> > > Buffer cache hit ratio: go to www.hotsos.com and get the paper "Why
> > > 99%+ Database Buffer Cache Hit Ratio is NOT OK." This paper
> > > demonstrates that extremely high hit ratios are usually a result of
> > > poorly tuned code.
> > >
> > > PCTIncrease should be non-zero. If PCTIncrease is anything other than
> > > 0 for both the tablespace and the objects in the tablespace,
> > > fragmentation occurs. Since the number of extents has no bearing on
> > > performance (yes this is arguable if the number is in the tens of
> > > thousands), the best utilization of the space in a tablespace is if
> > > every extent is the same size. This allows any free extent in the
> > > tablespace to be used when a new extent is needed. There is no reason
> > > to coalesce since the only fragmentation is on datafile boundaries (an
> > > extent must be in a single datafile).
> >
> > I absolutely disagree. For the tablespace ... perhaps back with dictionary
> managed tablespaces and before 8i. For the objects in the
> > tablespace? What? (registering complete surprise) Where did this come
> from?
> >
> > Daniel Morgan
> >
Because mistakes do happen. And if it takes a few months to find a table that
slipped by better 1% than 50%. At least that was the thinking.
Daniel Morgan
Jonathan Lewis
Using 1% is the probably one of the worst
possible values, as it allows very irritating
slippage.
Back in the days of 6.0.33, I used to insist
that 0 was the ONLY sensible value, but eventually
I worked out after a few years that there was actually
a good alternative, which was 100%.
Of course, as soon as MINIMUM EXTENT became
available, it really ceased to matter, beyond the fact
that any value other than 0 would make smon do
some pointless work.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Daniel Morgan wrote in message <3CE918F8...@exesolutions.com>...
Daniel Morgan
I agree. I was just trying to explain the logic behind the choice of 1% (or
illogic as the case may be).
Daniel Morgan
Daniel Morgan
with reference to disk size.
And this reminded me of another common bit of "wisdom?" with respect to Oracle I
thought I would throw into the mythology pot.
For many years I was told that Oracle likes lots of small disks. That the ideal
Oracle system was 100 2GB drives rather than 10 20GB drives. I haven't heard
this repeated in awhile. It was only about 5 years ago I remember a SysAdmin
whining that he could no longer get 4GB drives and would have to move Oracle to
9GB or 18GB drives.
Any commments? Myth or wisdom?
Daniel Morgan
Niall Litchfield
I haven't thought this through yet - let alone tested it but ISTM that there
is wisdom in this. A tablespace striped (and mirrored) across 100 spindles
would I imagine perform better than one striped across ten. My only comment
would be that I rather suspect a law of diminishing returns to be in action
here as well.
Niall
who has also whinged about the lack of 4gb drives but had in mind
(particularly) 50MB redo logs.
Sybrand Bakker
'It depends'
Regards
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Daniel Morgan
I think you have just performed the equivalent of what other posters do when they say
"I was trying something and I got some error what should I do?" ;-)
Daniel Morgan
Pablo Sanchez
Having more actuators will always be a Good Thing[tm], however, as I
was recently corrected! <g>, with RAID and their monster caches (like
EMC's) and that changes things a bit.
Looking at the HP TPC-C with Oracle 9i, what they did was create
monster stripes with RAID 1 as the back-end. I didn't bother checking
how much cache were on the arrays but my dollar would say they were
stacked.
Given JBOD, I'd want drives as small as possible ... the analogy I use
is:
Is it quicker to load/unload a bus via one door? Or to load the same
set of people into two seater cars? <g>
Daniel Morgan
On the face of it I can't disagree. But then I couldn't disagree initially
with a number of things pointed out as myths.
Has anyone actually put this to the test and gathered the numbers ... or
is this unsubstantiated theory?
The quest continues.
Daniel Morgan
Jonathan Lewis
I don't think this is one where you can produce
a definitive 'hard' proof; you can only posit a
logical argument. Viz (and I'm sure you already
say this to your students, Daniel):
1) I can always invent a system that requires
a given number of real physical I/O requests per
second, irrespective of the size of any available
cache.
2) Real hardware devices can only support
around 100 random I/O requests per second.
3) If I want to 10,000 I/Os per second then
I need a minimum of 100 devices.
Given the free choice between 50 x 20GB drives
and 100 x 9 GB drives, I'd need to have the larger
number of smaller drives. On the other hand,
given the choice between 100 x 20 GB drives
and 100 x 9 GB drives, I'd take the larger and
be very careful about how the spare 11GB per
drive was used.
QED
In real life, of course, the calculations are rarely
made (until it's too late), and the scale of operation
is rarely so extreme that throwing loads of cash
at a cache fails to make the difference.
--
Jonathan Lewis
http://www.jlcomp.demon.co.uk
Author of:
Practical Oracle 8i: Building Efficient Databases
Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html
Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Daniel Morgan wrote in message <3CE987D0...@exesolutions.com>...
Jonathan Bliss
> Is it quicker to load/unload a bus via one door? Or to load the same
> set of people into two seater cars? <g>
On a single track one way street (no overtaking) with a set of single
pick up/drop off points so each car has to pull up sequentially,
probably the bus.
As Sybrand says 'It depends' on everything else.
Jonathan
Nuno Souto
said (and I quote):
> snip...
> In real life, of course, the calculations are rarely
> made (until it's too late), and the scale of operation
> is rarely so extreme that throwing loads of cash
> at a cache fails to make the difference.
>
Hehehe! Very good point!!!
Carl Kayser
most RDBMS systems and is not Oracle-specific. My $ would be on cars.
"Pablo Sanchez" <pa...@dev.null> wrote in message
news:3ce979f1$1...@news.teranews.com...
>
Daniel Morgan
Thanks,
Daniel Morgan
Connor McDonald
adding complexity the whole scenario is that you get a 9G drive and it
was made by vendor x, has rotation y, tranfer time z, et al, and the 18G
drive comes from vendor p, rotation q, and you can only have 'b' 9G
drives on the controller which runs at 'a' Hz, whereas the 18G drives
run on the new 'c' controller with 'd' Hz.. blah blah blah
.. all of which is moot, when the budget department purchase a single
800G drive because it was cheaper :-)
Cheers
D.Y.
> Alan wrote:
>
> > Can someone explain why "Seperate tables and indexes for performance
> > reasons." is not true?
>
> To avoid your being pummeled ... let me suggest that you look in the
> Google archives. This one was painfully beaten into submission a few
> weeks ago. And as I score it ... the experts convinced the skeptics.
>
> Daniel Morgan
I am not sure everyone(except for a few including myself) are thinking
along the same lines now. Just ran into an old article,
http://www.ixora.com.au/newsletter/2000_10.htm#same
It explained similar issues much more eloquently than I could have.
D.Y.
> "Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message news:<3ce21b71$0$8510$ed9e...@reading.news.pipex.net>...
> > Suggested list to be added to, deleted from etc
> >
>
>
>
> - You cannot create declarative RI across schemas. Oracle doesn't let
> you do it.
> (could it be these people have never heard of the REFERENCES grant?)
>
The first time I heard this.
> - Defining RI in application code is safer and less maintenance
> intensive than doing it in the databse.
>
A while ago some people did this for portability reasons when, for
example, RI was available in Oracle but not Sybase.
>
> Sometimes I wonder...
> Cheers
> Nuno
Daniel Morgan
800GB? I'll bet on request they would also come on down and set it up as RAID 5
with 1MB stripes too.
Daniel Morgan
D.Y.
> "Niall Litchfield" <n-litc...@audit-commission.gov.uk> wrote in message news:<3ce21b71$0$8510$ed9e...@reading.news.pipex.net>...
> > Suggested list to be added to, deleted from etc
>
>
> - Put the most selective column first in a concatenated index.
> (this one AFAIK was never true, but somehow it stuck)
>
I found myself on the opposite side again :-)
There is one reason to do this: the most selective column is more likely
to be part of the criteria of many queries. Therefore having the most
selective column first will likely allow you to serve many queries with
one index. As for performance, compressed index with the least selective
column first may be better. So do what's best for your application.
>
> Cheers
> Nuno
Niall Litchfield
news:f369a0eb.02052...@posting.google.com...
where the query uses more than one column Oracle is bright enough to select
the index even if the columns are in the 'wrong' order (the output below I
think shows this). As you say indexes built in this way are also cadidates
for key compression.
Where you have one column used in many queries you should be considering
indexing that column alone.
SQL> select count(distinct client) from agltransact;
COUNT(DISTINCTCLIENT)
---------------------
3
SQL> select count(distinct cur_amount) from agltransact;
COUNT(DISTINCTCUR_AMOUNT)
-------------------------
436018
SQL> CREATE INDEX MOSTSEL ON AGLTRANSACT(CUR_AMOUNT,CLIENT);
Index created.
SQL> analyze table agltransact estimate statistics;
Table analyzed.
SQL> set autotrace on explain statistics;
SQL> select rowid,cur_amount,status
2 from agltransact
3 where client = 'DE'
4 and cur_amount = 293.75;
ROWID CUR_AMOUNT S
------------------ ---------- -
AAAGbaAALAAACwQAAh 293.75
<snip>
AAAGbaAAOAAADf6AAh 293.75
50 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=366 Card=369 Bytes=5
166)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'AGLTRANSACT' (Cost=366 C
ard=369 Bytes=5166)
2 1 INDEX (RANGE SCAN) OF 'MOSTSEL' (NON-UNIQUE) (Cost=3 Car
d=369)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
3434 bytes sent via SQL*Net to client
740 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed
SQL> drop index mostsel;
Index dropped.
SQL> create index leastsel on agltransact(client,cur_amount);
Index created.
SQL> select rowid,cur_amount,status
2 from agltransact
3 where client = 'DE'
4 and cur_amount = 293.75;
ROWID CUR_AMOUNT S
------------------ ---------- -
AAAGbaAALAAACwQAAh 293.75
<snip>
AAAGbaAAOAAADf6AAh 293.75
50 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=369 Bytes=516
6)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'AGLTRANSACT' (Cost=2 Car
d=369 Bytes=5166)
2 1 INDEX (RANGE SCAN) OF 'LEASTSEL' (NON-UNIQUE) (Cost=1 Ca
rd=369)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
3434 bytes sent via SQL*Net to client
740 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
50 rows processed
Nuno Souto
(and I quote):
>
> to be part of the criteria of many queries. Therefore having the most
> selective column first will likely allow you to serve many queries with
> one index. As for performance, compressed index with the least selective
> column first may be better. So do what's best for your application.
I'm curious. Why do you assume that the most selective column is more
likely to be part of the criteria of many queries? What says this is so?
I have yet to find one single instance where a very selective column
coming first in a concatenated index makes ANY difference to data
processing.
If the majority of your access is by this single column, then why use a
concatenation? It's just added overhead. Create a single index on that
column, period.
If not, then you MUST use the correct sequence in the index columns (less
to more selective) or you simply will NOT use the index at all.
Ie, putting the most selective column first stops the concatenated index
from being used for ANY queries where this column isn't specified. Which
completely defeats the purpose of having that column together with
another in a concatenation.
Also, I've found that most heavy volume processing (batch and reporting)
is done on groups or aggregations. Which mean that the group or
aggregation key MUST be first in a concatenation with ANY other keys or
you will simply not be able to use the index at all.
Nuno Souto
(and I quote):
Interesting. WHich version of Oracle is this? I tried this in V7, V8.0
and V8.1 and it doesn't pick up the index at all in any of these,
provided there is a reasonable number of rows in the table with similar
distributions. Also, does it still pick up the index if you do NOT
select the rowid? That is kept in the index and would be enough for the
optimizer to bias itself, no?
--
Cheers
Nuno Souto
nso...@optushome.com.au.nospam