Question:

[Oracle] DBA’s (Performance Tuning)

1. A tablespace has a table with 300 extents in it. Is this bad? Why or why not?
2. How do you set up tablespaces during an Oracle installation?
3. You use multiple fragments in the SYSTEM tablespace. What should you check first?
4. What are the indications that you need to increase or decrease the shared_pool size parameter?
5. What are the general guidelines for sizing DB_BLOCK_SIZE and DB_FILE_MULTIBLOCK_READ_COUNT for an application that does many table scans?
6. What is the fastest query method of a table in the RULE based optimizer?
7. Explain the use of TKPROF. What OS parameter should be set to get full TKPROFF output?
8. When looking at V$SYSSTAT you see that sorts (disk) is high. Is that bad or good? If bad – how do you correct it?
9. When should you increase copy latches? What parameter controls copy latches?
10. Where can you get a list of all initialization parameters for your system? How about if they are the default settings or have been changed?
11. Describe hit ratios as pertains to the database buffers. What is the difference between an instantaneous and cumulative hit ratio? Which one should you use for tuning?
12. Discuss row chaining. How does it happen? How do you correct it?
13. When looking at the estat events report you see that you are getting buffer busy waits. Is this bad/ How can you find what is causing it?
14. If you see contention for library caches how can you fix it?
15. If you see statistics that deal with UNDO, what are they really talking about?
16. If a tablespace has a default PCTINCREASE of 0, what will it cause (in relation to SMON).
17. If a tablespace shows excessive fragmentation, what are the methods to de-fragment the tablespace?
18. How can you tell if the tablespace has excessive fragmentation?
19. You see the following on your status report: redo log space requests – 23, redo log space wait time – 0. Is this something to worry about? What is the redo log wait time is high? How can you fix this?
20. What can cause a high value for recursive calls? How can this be fixed?
21. If you see a pin hit ratio of less than 0.8 in the estat library report – is this a problem? If so, how do you resolve it?
22. If you see a high value for reloads in estat library cache report, is this a matter for concern?
23. You look at the DBA_ROLLBACK_SEGS and see that there is a large number of shrinks and they are relatively small in size. Is this a problem? How can this be fixed?
24. You look at the DBA_ROLLBACK_SEGS and see a large number of wraps. Is this a problem?
25. You have a room to grow extents by 20%. Is there a problem? Should you take any action?
26. You see multiple extents in the temporary tablespace. Is this good or bad?

Answers:

1. Multiple extents are not bad. However, if you also have chained rows, this can hurt performance.
2. You should always use OFA. For the best results SYSTEM, ROLLBACK, UNDO, TEMPORARY, INDEX and DATA segments should be separated.
3. Ensure that users don’t have SYSTEM tablespace as their default and or temporary tablespace by checking DBA_USERS.
4. Poor data dictionary or library cache hit ratios or getting ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters are the same.
5. OS almost always reads in 64K chunks. The two should have a product of = 64K, a multiple of 64K, or the value for read size from your OS.
6. Fetch by ROWID.
7. The TKPROF is a tuning tool used to determine the execution time for SQL statements. Use it first by setting TIMED_STATISTICS parameter to TRUE and then setting the entire instance SQL_TRACE to on or just for the session with an ALTER SESSION command. Once that is done you run TKPROF and generate a readable report with an explain plan.
8. If you get excessive disk sorts this is bad. This means you need to tune the sorts area parameters in the init file – SORT_AREA_SIZe is the major one.
9. When you get excessive contention of the copy latches as shown by the redo copy latch hit ratio you can increase copy latches via the init parameter log simultenious_copies to twice the number of CPUs on your system.
10. You can look in the init.ora file or the V$PARAMETER view.
11. A hit ratio is measurement of how many times the database was able to read the value from the buffers, instead of disk. A value of 80%-90% is good. If you simply take the ratio of existing parameters, they will be applicable to since the instance started. If you do a comparison of readings based on some 2 arbitrary time spans, this is the instantaneous ratio for that time span (more valuable)
12. The row chaining happens when a variable length value is updated and the new value is longer than the old value and will not fit into remaining block space. This results in row chaining to another block. You can correct this by setting appropriate values for the table storage clause (PCTFREE). This can be corrected be exporting and importing the table.
13. Buffer busy wait can indicate contention in redo rollback or data blocks. You need to check the V$WAITSTAT to see what areas are causing the problem. The value of count tells you where the problem is, class tells you with what.
14. Increase the size of the shared pool.
15. Rollback segments associated with structures.
16. The SMON will not automatically coalesce its free space fragments.
17. In Oracle 7.0 and 7.2 use the ‘ALTER SESSION SET EVENTS IMMEDIATE TRACE NAME COALESCE LEVEL ts#’ command is the easiest way to defragment the space. The ts# is in the ts$ table owned by SYS. In version 7.3 alter tablespace <> coalesce;  If the free space is not contiguous, export, drop and import the tablespace.
18. If a select against DBA_FREE_SPACE shows that the count of tablespace’s extents is greater than the count of its datafiles, then it is fragmented.
19. Since the wait time is zero – no. If the wait time is high it might indicate a need for more or larger redo logs.
20. The high value of recursive calls is caused by improper usage of cursors, extensive dynamic space management actions, and excessive statements re-parses. You need to determine cause and correct it by eitherrelinking applications to hold cursors or use proper space management techniques (proper storage and sizing) to ensure repeated queries are placed in the packages for proper use.
21. This indicates that shared pool size is too small. Increase the size of shared pool.
22. You should strive for zero reloads if possible. If you see excessive reloads – increase the size of shared pool.
23. The large number of small shrinks indicates the need to increase the size of extents of rollback segments. Ideally, you should have no shrinks or small number of large shrinks. To alleviate this just increase the size of extents and adjust optimal accordingly.
24. A large number of wraps indicates that rollback segment extent size is too small. Increase the size of your extents. You can look at an average transaction size in the same view.
25. No, it is not a problem. You have 40 extents showing and an average of 40 users. Since there is plenty of room to grow there is no problem.
26. As long as they are all the same size it is not a problem.