Oracle Interview Questions Page 4

1.When looking at the estatevents report you see that you are getting busy buffer waits. Is this bad? Howcan you find what is causing it

Bufferbusy waits could indicate contention in redo, rollback or data blocks. You needto check the v$waitstat view to see what areas are causing the problem. Thevalue of the "count" column tells where the problem is, the"class" column tells you with what. UNDO is rollback segments, DATAis data base buffers.

2.If you see contention forlibrary caches how can you fix it

Increasethe size of the shared pool.

3.If you see statistics thatdeal with "undo" what are they really talking about

Rollbacksegments and associated structures.

4.If a tablespace has adefault pctincrease of zero what will this cause (in relationship to the smonprocess)

TheSMON process won?t automatically coalesce its free space fragments.

5.If a tablespace showsexcessive fragmentation what are some methods to defragment the tablespace?(7.1,7.2 and 7.3 only)

InOracle 7.0 to 7.2 The use of the 'alter session set events 'immediate tracename coalesce level ts#';? command is the easiest way to defragment contiguousfree space fragmentation. The ts# parameter corresponds to the ts# value foundin the ts$ SYS table. In version 7.3 the ?alter tablespace coalesce;? is best.If the free space isn?t contiguous then export, drop and import of thetablespace contents may be the only way to reclaim non-contiguous free space.

6.How can you tell if atablespace has excessive fragmentation

Ifa select against the dba_free_space table shows that the count of a tablespacesextents is greater than the count of its data files, then it is fragmented.

7.You see the following on astatus report:

redolog space requests 23

redolog space wait time 0

Isthis something to worry about? What if redo log space wait time is high? Howcan you fix this

Sincethe wait time is zero, no. If the wait time was high it might indicate a needfor more or larger redo logs.

8.What can cause a highvalue for recursive calls? How can this be fixed

Ahigh value for recursive calls is cause by improper cursor usage, excessivedynamic space management actions, and or excessive statement re-parses. Youneed to determine the cause and correct it By either relinking applications tohold cursors, use proper space management techniques (proper storage andsizing) or ensure repeat queries are placed in packages for proper reuse.

9.If you see a pin hit ratioof less than 0.8 in the estat library cache report is this a problem? If so,how do you fix it

Thisindicate that the shared pool may be too small. Increase the shared pool size.

10.If you see the value forreloads is high in the estat library cache report is this a matter for concern

Yes,you should strive for zero reloads if possible. If you see excessive reloadsthen increase the size of the shared pool.

11.You look at thedba_rollback_segs view and see that there is a large number of shrinks and theyare of relatively small size, is this a problem? How can it be fixed if it is aproblem

Alarge number of small shrinks indicates a need to increase the size of therollback segment extents. Ideally you should have no shrinks or a small numberof large shrinks. To fix this just increase the size of the extents and adjustoptimal accordingly.

12.You look at thedba_rollback_segs view and see that you have a large number of wraps is this aproblem

Alarge number of wraps indicates that your extent size for your rollbacksegments are probably too small. Increase the size of your extents to reducethe number of wraps. You can look at the average transaction size in the sameview to get the information on transaction size.

Posted in: Oracle