Oracle Performance Tuning Flashcards
What do you mean by row chaining?
Row chaining occurs when a row is too large to fit into one data block. This will cause Oracle to store the data for the row in a chain or blocks reserved for that segment.
Explain the advantages of using a view.
The view helps provide security, presents data in a different perspective, and stores complex queues.
Who is responsible for updating the indexes?
Oracle will automatically maintain and use indexes. When any change is made in the table, Oracle will automatically distribute it into the relevant indexes.
Why is an index used?
Used to increase performance of retrieval and slows down the performance of insertion.
How do you prevent users form connecting to a database during a maintenance period?
Open the DB in restricted mode. In this mode, only users with the restricted session privilege can connect to the database. By default, only DBAs have this privilege.
A user is complaining the DB is slow. How would you find the performance issue?
- Join your query v$session with v$wait to check the events that are waiting for something and try to find out the object locks.
- Run SQL Tuning Advisor
- Run TOP command in Linux to check CPU usage.
- Run VMSTAT, SAR, PRSTAT to get more info on CPU, memory usage, and possible blocking.
- Check trace file using tkprof.
What is the difference between DB file sequential read and DB file scattered read?
- File sequential read operation reads data into contiguous memory. It’s also used for rebuilding the control file and reading datafile headers.
- File scattered read will read multiple data blocks and scatter them into different discontinuous buffers in the SGA.
What is row migration?
Row migration occurs when there is insufficient free space in the block for an updated row. That row is moved to a different block and Oracle will leave a pointer (rowid) in the original block. This can result in fragmentation of the block/table/database.
