![[Oracle] Entrevistas de Emprego para DBA’s (Performance Tuning) – Respostas](https://certificacaobd.com.br/wp-content/uploads/2012/03/oracle_dest.jpg)
Seguem as respostas da Entrevistas de Emprego para DBA’s (Performance Tuning):
- Multiple extents are not bad. However, if you also have chained rows, this can hurt performance.
- You should always use OFA. For the best results SYSTEM, ROLLBACK, UNDO, TEMPORARY, INDEX and DATA segments should be separated.
- Ensure that users don’t have SYSTEM tablespace as their default and or temporary tablespace by checking DBA_USERS.
- 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.
- 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.
- Fetch by ROWID.
- 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.
- 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.
- 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.
- You can look in the init.ora file or the V$PARAMETER view.
- 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)
- 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.
- 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.
- Increase the size of the shared pool.
- Rollback segments associated with structures.
- The SMON will not automatically coalesce its free space fragments.
- 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.
- 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.
- Since the wait time is zero – no. If the wait time is high it might indicate a need for more or larger redo logs.
- 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.
- This indicates that shared pool size is too small. Increase the size of shared pool.
- You should strive for zero reloads if possible. If you see excessive reloads – increase the size of shared pool.
- 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.
- 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.
- 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.
- As long as they are all the same size it is not a problem.
Para ler as todas as Entrevistas de Emprego, vá ao Menu principal e clique em Quero ser um DBA!
