Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance tuning

Re: Performance tuning

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Tue, 2 Nov 2004 21:40:06 +1100
Message-ID: <418763f3$0$21989$afc38c87@news.optusnet.com.au>

"manny" <msmajhail_at_gmail.com> wrote in message news:b7d514bd.0411012305.84926ca_at_posting.google.com...
> 1/ INITRANS is a parameter that indicates the concurrency level of
> transactions when working inside a db block. Where is this parameter
> stored?
> When and how can this be set/changed?

It's an attribute of the table, so you can see it in DBA_TABLES. It is set when you create a table, and it can be changed at any time with an alter table X pctfree Y command.

> 2/ I can find out if there are any chained rows in my database using
> the "select name, value from v$sysstat where 'table fetch by continued
> row';
> If the number is not zero then there are rows chained. What I do not
> know is that which table or tables have this problem?
> I am supposed to use ANALYZE TABLE tablename LIST CHAINED ROWS; to
> find out. But if I have 100 tables,
> I do not want to run this command one by one against all my 100
> tables. How do I figure out where my chained rows are?

Analyze table X compute statistics. Look in DBA_TABLES at the CHAIN_CNT column.

Analayze...compute is fairly old technology now, though. You are strongly advised in 9i and above to execute dbms_stats.gather_XXX_stats, where XXX can be 'table' or 'schema' or 'database' -so with one command, 'exec dbms_stats.gather_database_stats', you can collect statistics for all tables in the database in one go. That will still populate the CHAIN_CNT column.

The analyze...list chained rows command you mention is not actually the way to *detect* chained or migrated rows, it is how you go about fixing them up.

And be sure you are aware of the difference between a chained row, which cannot be fixed, and a migrated row, which can. Both get detected and counted in the same CHAIN_CNT column.

> 3/ Data cache Read Efficiency (RE) is defined as percentage of data
> reads satisfied by the cache.
> If N is total number of reads issued, M is the number of reads that
> were done against the disk, then N-M is the number that were satisfied
> by information in the cache.
> Therefore, RE=(N-M)/100%. How do I get these stats? Is there a view
> that contains this information? Do I have to some type of analysis to
> get them?

Read up on the Buffer Cache Hit Ratio. It is complete garbage as a tuning statistic, and you'd be better off doing a pottery class for all the difference it will make to the speed with which your database runs.

> 4/ There is also a similar question on Procedure Cache efficiency. How
> do I get the RE for procedure cache.

I imagine you are talking about the Library Cache hit ratio, which is visible as a statistic in V$LIBRARY_CACHE.

That's a lot of quite vague questions on a disparate set of topics. That's fine, and we all have to start somewhere. But do yourself a favour: start reading around the subject. Read the Oracle Concepts guide if you haven't already done so. Then start reading trusted authors on the topic. The 'Links' page at www.dizwell.com gives you my ideas of which sites are interesting/useful/trustworthy.

Regards
HJR Received on Tue Nov 02 2004 - 04:40:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US