I have couple of questions about the Oracle performance tuning..One of our customer has sent us the report on performance.
I could not understand some of things that he has said.
1.db_file_multiblock_read_count*db_block_size = number of blocks read during the full table scan which should be max CHUNK SIZE, usually 64K.
Where can I find max CHUNK SIZE ?
Is this true for all queries doing full tablescan ?
Is this value should be lower or higher to check for performance ?
Is their any way that I can find which queries doing full table
scan ? At glance report ...
2. Increase the number of db_writer_processes. If the no. or data block waits exceeds a threshold value undo statistics are for rollback segment blocks. If the number of waits is greater than 0 then there is contention for the rollback segment.
What does all this mean ?
3. Redo log buffer:
If too many waits for space in redo log buffer cache occur, then increase the log_buffer parm in init.ora file. Log_buffer must be multiple of operation system blocksize.
Waits are usually caused by 1. log_buffer being too small 2. Frequent check pointing. 3. Frequent log switching.
The redo buffer allocation retries should be near to 0.
redo entries should be less than 1 %.
redo log space requests indicates active log file is full and server is waiting for space for redo log entries(log switch).
Is this all true ?
Can I just update v$parameter for all these ?
4. What does v$system_event table shows ? What should be the values in there ?
5. What is enqueue_resources mean ?
6. What's the query for general database activity statistics ?
Is there any document that explains all these ?
Thanks a lot