I have 1-1/2 years experience as a DBA and I have an interview next week. What are the top performance questions I should be aware of?
20. What can cause a high value for recursive calls? How can this be fixed?
Expected answer: A high value for recursive calls is cause by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses. You need to determine the cause and correct it By either relinking applications to hold cursors, use proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse.
21. If you see a pin hit ratio of less than 0.8 in the estat library cache report is this a problem? If so, how do you fix it?
Expected answer: This indicates that the shared pool may be too small. Increase the shared pool size.
22. If you see the value for reloads is high in the estat library cache report is this a matter for concern?
Expected answer: Yes, you should strive for zero reloads if possible. If you see excessive reloads then increase the size of the shared pool.
23. You look at the dba_rollback_segs view and see that there is a large number of shrinks and they are of relatively small size, is this a problem? How can it be fixed if it is a problem?
Expected answer: A large number of small shrinks indicates a need to increase the size of the rollback segment extents. Ideally you should have no shrinks or a small number of large shrinks. To fix this just increase the size of the extents and adjust optimal accordingly.
24. You look at the dba_rollback_segs view and see that you have a large number of wraps is this a problem?
Expected answer: A large number of wraps indicates that your extent size for your rollback segments are probably too small. Increase the size of your extents to reduce the number of wraps. You can look at the average transaction size in the same view to get the information on transaction size.
25. In a system with an average of 40 concurrent users you get the following from a query on rollback extents:
ROLLBACK CUR EXTENTS
You have room for each to grow by 20 more extents each. Is there a problem? Should you take any action?
Expected answer: No there is not a problem. You have 40 extents showing and an average of 40 concurrent users. Since there is plenty of room to grow no action is needed.
26. You see multiple extents in the temporary tablespace. Is this a problem?
Expected answer: As long as they are all the same size this isn't a problem. In fact, it can even improve performance since Oracle won't have to create a new extent when a user needs one.
Dig Deeper on Oracle database performance problems and tuning
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.