Oracle Interview-4

1. A tablespace has a table with the extents in it. Is this bad? Why or is not.

Multiple extents in and of themselves aren?t bad. However if you also has chained rows this can hurt performance.

2. How does the set up tablespaces during an Oracle installation?

You should all attempt to use the Oracle flexible Architecture standard or another partitioning scheme to ensure proper Separation of SYSTEM, ROLLBACK, REDO LOG, DATA, temporary and INDEX segments.

3. Multiple fragments in the SYSTEM tablespace, what should do you check first?

Ensure that users don?t has the SYSTEM tablespace as their temporary or DEFAULT tablespace assignment by checking the DBA _users view.

4. What is some indications that is need to increase the shared_pool_size parameter?

Poor data dictionary or library cache hit ratios, getting error ORA-04031. Another indication is steadily decreasing performance with all other tuning parameters the same.

5. What's the general guideline-sizing db_block_size and Db_multi_block_read for a application that does many full T Able scans?

Oracle almost always reads in 64k chunks. The should has a product equal to 64 or a multiple of.

6. What's the fastest query method for a table

Fetch by rowID

7. Explain the use of TKPROF? What initialization parameter should is turned on to get full TKPROF output?

The Tkprof tool is a tuning tool used-determine CPU and execution times for SQL statements. You use it by first setting Timed_statistics to true in the initialization file and then turning on tracing for either the Entire database via the Sql_trace parameter or for the session using the ALTER session command. Once The trace file is generated you run the Tkprof tool against the trace file and then look at the output from the Tkpro F tool. This can also is used to generate explain plan output.

8. When the looking at V$sysstat-sorts (disk) is high. Is this bad or good? If bad-how Do you correct it?

If you get the excessive disk sorts this is the bad. This indicates your need to tune the sort area parameters in the initialization files. The major sort is parameter is the sort_area_size parameter.

9. When should increase copy latches? What parameters Control copy latches

When you get excessive contention for the copy latches as shown by the ' Redo copy ' latch hit ratio. You can increase copy latches via the initialization parameter log_simultaneous_copies to twice the number of CPUs on your System.

Where can you get a list of all initialization parameters for your instance? How on an indication if they is default settings or has been changed

You can look in the Init.ora file for an indication of manually set parameters. For any parameters, their value and whether or not, the current value is the default value, look in the V$parameter view.

Describe hits ratio as it pertains to the database buffers. What's the difference between instantaneous and cumulative hit ratio and which should being used for tuning

The hit ratio are a measure of how many times the database were able to read a value from the buffers verses how many times It had to re-read a data value from the disks. A value greater than 80-90% is good, less could indicate problems. If you simply take the ratio of existing parameters this would be a cumulative value since the database started. If You do a comparison between pairs of readings based on some arbitrary time span, this is the instantaneous ratio for th At time span. Generally speaking an instantaneous reading gives more valuable data since it'll tell you what your instance is doing fo R the time it is generated over.

Discuss row chaining, how does it happen? How can I reduce it? How does you correct it

Row chaining occurs when a VARCHAR2 value was updated and the length of the new value is longer than the old value and won? T fit in the remaining block space. This results the row chaining to another block. It can be reduced by setting the storage parameters on the table to appropriate values. It can corrected by export and import of the effected table.

From:http://www.orafaq.com/wiki/interview_questions

Oracle Interview-4