Performance Monitoring
Stefan Wetter
I'm pretty new to this theme. My task is, at the moment, to improve the
performance of a database of one of our customers.
I monitored the database-activity with the help of some tools and find
pretty much DISK-IO (read). I think that should be lower. To the same
time, the buffers are only filled half.
I think i should find out, wich tables cause the physical reads (, to
examine which process does need them to optimize the clients if necessary.
If not, i would change the storage clauses of that tables to use another
part of the buffers.)
So, my question at this time is: how can i find out which tables (or
maybe indexes) cause the disk-io?
Thank you very much in advance!
Stefan
Jerome Vitalis
> So, my question at this time is: how can i find out which tables (or
> maybe indexes) cause the disk-io?
What is your Oracle version?
If <10g, your best choice is probably to install and use statspack.
If >=10g and if your customer has paid for the tuning pack, you can use
ADDM instead. Without the tuning pack license, use statspack.
astalavista
"Stefan Wetter" <swe...@arcor.de> a écrit dans le message de news:
46fa1a86$0$4518$9b4e...@newsspool3.arcor-online.net...
see V$SEGMENT_STATISTICS
joel garry
1. Read Concepts manual.
2. Understand that most performance issues come from application
issues. For example, if some silly SQL reads an entire table to get a
few rows, you wil likely have a lot of unnecessary I/O that won't fill
up the SGA.
3. Read the Performance manual.
4. Understand the optimizer. It can only use the information it is
given. If the statistics it uses are wrong, non-existent, or skewed
in a manner the optimizer doesn't know about, it can choose a silly
plan for accessing the data. Sometimes a full table scan is not
silly.
5. Understand what plans are and how to use them to understand 4.
6. Understand what statspack (or other tools such as Jerome
mentioned) can tell you.
7. Understand when, how and why to use tracing.
8. Understand what waits are and how to evaluate them.
9. Read and work through books and articles by Jonathan Lewis, Tom
Kyte, and Cary Milsap. (And I still like Lawson's book for the basic
how-to-dba implicit in your question, though it is out of date now).
10. Understand why rules of thumb can be a bad idea for improving the
database of customers.
11. Understand that tools based strictly on Oracle can be a bit
misleading from a systems standpoint, and systems tools can be
misleading from Oracle's viewpoint. Simply knowing you have a lot of
reads does not mean anything is wrong, after all, what is a database
going to be used for? A proper tuning methodology will figure out
what critical bottlenecks are, and what to do about them.
12. Understand the basics. For example, if you have sequential write-
intensive archive writing interfering with random reads and writes for
undo and everything else, thrashing a SAN cache, you probably have a
configuration problem. If you have multiple users accessing data, you
need to understand how Oracle handles the issues involved.
13. Create clear metrics for performance improvement.
14. Read Concepts manual.
15. Iterate.
Welcome to the group! See http://www.dbaoracle.net/readme-cdos.htm
jg
--
@home.com is bogus.
http://shop.lego.com/product/Default.aspx?p=10179&LangId=2057
hpuxrac
I recommend doing almost nothing else first except purchase and read
Cary Millsap's book "Optimizing Oracle Performance".
You have invented your own way of trying to fix things. Cary has a
well documented and repeatable methodology based on an deep
understanding.
Jan Krueger
I will print this and put it at my office wall in a frame.
Never read good advise for performance problems in such a condensed way
before.
Jan
Jerome Vitalis
Well said Joel!
But isn't there a typo in number 2?
joel garry
What might that be? (It's hard to be more specific than what I wrote,
given parallel and pga and LRU chains and what-all. But if I didn't
say something correctly, let us know what you think, it may be
enlightening for everyone. Examing V$BH for various configurations
and table access can be informative, sometimes even surprising. The
explanations Doug Burns and Jonathan Lewis, among others, have put out
are very useful.).
The list was just off the top of my head. Sometimes that can be a
great way to abstract a large subject, sometimes that can miss some
biggies. The exact ordering could perhaps use some work, if framing
is involved :-)
I can't disagree with hpuxrac's post, in any event. There's a
difference between picking a good methodology and sticking to it,
versus what one should do overall, but not an exclusive difference. A
newbie should do both.
jg
--
@home.com is bogus.
Stefan Wetter
things. My problem is, that i am no dba. I am software developer with
some basic oracle-dba-knowledges. Now, one of our customers has a
problem with his oracle database. And because i set it up und keep it
running i am his contact if there are problems. And the Problem is, that
the database is slow. Or, I should better say: is getting slow.
And i do understand that having data in memory is much faster than
having data to be red from disk. And i do see, that we have much
physical read and empty buffers. So, can someone please give me an
advice how i can tell my server to have more data in memory?
And i see one more thing: There are about 15% unindexed queries. How can
i find out which queries were maid that are unindexed?
Thank you very much in advance!!
Stefan
Jan Krueger
Hi Stefan,
read Cary Milsap, Optimizing Oracle for Performance.
My approach would be to start with the business transaction or GUI
screen, where your customer experiences the biggest problem and can
generate the largest benefit by getting this faster.
Once you tuned this, step ahead to the second one.
Jan
Jerome Vitalis
> On Sep 28, 9:53 am, Jerome Vitalis
> <vitalismanN05...@gmail.com.invalid> wrote:
>> On Wed, 26 Sep 2007 13:54:08 -0700, joel garry wrote:
>>
>> Well said Joel!
>> But isn't there a typo in number 2?
>
> What might that be? (It's hard to be more specific than what I wrote,
> given parallel and pga and LRU chains and what-all. But if I didn't
> say something correctly, let us know what you think, it may be
> enlightening for everyone.
Shouldn't point 2 read as follows?
"[...] For example, if some silly SQL reads an entire table to get a
few rows, you wil likely have a lot of unnecessary I/O that *will* fill
up the SGA."
joel garry
wrote:
No.
For example, I ran this:
SQL> select count(*), orig_document_number from ic_movements
2 group by orig_document_number;
COUNT(*) ORIG_DOCUM
---------- ----------
19079104
5043841
I purposefully picked that field knowing that it is not in an index.
Then, in another session as /, I ran this (this is out of the
performance manual, google v$bh jonathan lewis for better
information):
COLUMN object_name FORMAT a40
COLUMN number_of_blocks FORMAT 999,999,999,999
SELECT o.object_name, COUNT(1) number_of_blocks
FROM DBA_OBJECTS o, V$BH bh
WHERE o.object_id = bh.objd
AND o.owner != 'SYS'
GROUP BY o.object_name
ORDER BY count(1);
OBJECT_NAME NUMBER_OF_BLOCKS
---------------------------------------- ----------------
[snip]
IC_MOVEMENTS_2 22
[snip]
IC_MOVEMENTS_1 28
IC_MOVEMENTS 41
[snip]
We can guess 24M rows can't fit in 41 8K blocks. The explain plan
also shows a full table scan (can't paste it in since I'm viewing it
in OEM).
The reason this doesn't fill up the SGA is a full table scan reads the
blocks onto the LRU end of the buffer cache in chunks of block_size *
db_file_multiblock_read_count, IIRC. The general idea is you won't be
rereading those blocks soon, no reason to keep them around. If you
_are_ re-reading them (and it isn't a small table), you obviously have
a problem. Less obvious is bad programming doing things like too many
commits, forcing too many re-reads. That sort of thing will load up
the SGA, because it is doing too many reads and _not_ doing full table
scans. That's also the sort of thing where people start fiddling with
magic init parameters or throw hardware at a problem when they
shouldn't.
See http://www.evdbt.com/SearchIntelligenceCBO.doc for a nice
explanation of the reads.
Parallel and direct load operations will bypass the SGA.
jg
--
@home.com is bogus.
"..and meanwhile, as if it wasn't already obvious enough, can I please
remind would-be members here that anything even vaguely resembling a
freebie, anonymous email account will render your application rejected
in an instant. That also covers 'vanity domains': home-brew websites
with home-brew email addresses. If you abuse your membership
privileges here, I want an ISP and/or an employer I can complain to,
not yourself in your self-appointed "web administrator" role!" - some
dork of an admin of a vanity domain who highly overestimates the
effect his complaints will have. I find this very humorous since I
discovered hotmail and sbcglobal reject my ISP's domain, but not my
own, coming through my ISP.
Jerome Vitalis
> On Oct 5, 4:08 am, Jerome Vitalis <vitalismanN0S...@gmail.com.invalid>
>> "[...] For example, if some silly SQL reads an entire table to get a
>> few rows, you wil likely have a lot of unnecessary I/O that *will* fill
>> up the SGA."
>
> No.
>
> For example, I ran this:
Ah, OK then! That was about FTS.
In fact, I had completely misunderstood the purpose of your sentence. I
thought you wanted to say that if some program happens to read many more
rows than necessary, it would result in "useless" blocks entering the
buffer cache.
After re-reading your sentence, especially the "entire table" part, it
now seems more obvious to me that you were referring to FTS. My bad.
Thanks for your answer, Joel.
joel garry
wrote:
No problem, you could probably tell I was leading you on, since it is
so easy to think Oracle does simple-minded things (which I inferred
the OP was thinking). Astalavista's answer was exactly what the OP
asked, and perfectly correct, but it implicitly confirmed
misconceptions in my opinion.
There's also lots of stuff on metalink and OTN to see, though some of
the older things on metalink may be somewhat misleading, and tend not
to refer to third party techniques which may be better. Here's an
interesting link:
https://metalink.oracle.com/metalink/plsql/f?p=130:10:3146610117385679612::::ALLTEXT,ANYTEXT,EXCTEXT,NONTEXT,KNOWLEDGE,PRODUCT_ID,PLATFORM_ID,SS,ST,NUMHITS,FILTCRIT,FILTCRITVAL:performance%20monitoring,,,,TRUE,,,0,DR,100,2,ST.Server.Performance.Database
Also see the technical information under
http://www.oracle.com/technology/deploy/performance/index.html I
haven't tried Note:271064.1, but it sounds interesting.
Note that the "Separating Tables and Indexes" may be wrong or
misleading in Note:148342.1. There may be correlated performance
improvements simply by the distribution across devices, but the table
and index separation is not why. There may be table and index
accesses by different users at the same time, but that is a different
issue. Also see http://sysdba.wordpress.com/2006/09/26/interview-with-tom-kyte/
.
jg
--
@home.com is bogus.
"I used to be a stoner until I saw Reverend Horton Heat" - Bumper
sticker