Re: Performance tuning

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:418763f3$0$21989$afc38c87_at_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.

No it won't, and I apologise for forgetting what dbms_stats will and won't do. It is past midnight here, however, if that's any excuse. Analyze...compute statistics is the way to go. And that means you still have to do it on a table-by-table basis.

Anyway, inspired by your question, I got busy writing a paper I'd always been meaning to write:

http://www.dizwell.com/html/row_migration.html

Make of it what you will.

Regards
HJR Received on Tue Nov 02 2004 - 07:35:32 CST