02:08
jobsindia
Oracle Interview Questions
Multipleextents in and of themselves aren?t bad. However if you also have chained rowsthis can hurt performance.
2.How do you set uptablespaces during an Oracle installation?Youshould always attempt to use the Oracle Flexible Architecture standard oranother partitioning scheme to ensure proper separation of SYSTEM, ROLLBACK,REDO LOG, DATA, TEMPORARY and INDEX segments.
3.You see multiple fragmentsin the SYSTEM tablespace, what should you check first?
Ensurethat users don?t have the SYSTEM tablespace as their TEMPORARY or DEFAULTtablespace assignment by checking the DBA_USERS view.
4.What are some indicationsthat you need to increase the SHARED_POOL_SIZE parameter?
Poordata dictionary or library cache hit ratios, getting error ORA-04031. Anotherindication is steadily decreasing performance with all other tuning parametersthe same.
5.What is the generalguideline for sizing db_block_size and db_multi_block_read for an applicationthat does many full table scans?
Oraclealmost always reads in 64k chunks. The two should have a product equal to 64 ora multiple of 64.
6.What is the fastest querymethod for a table
Fetchby rowid
7.Explain the use of TKPROF?What initialization parameter should be turned on to get full TKPROF output?
Thetkprof tool is a tuning tool used to determine cpu and execution times for SQLstatements. You use it by first setting timed_statistics to true in theinitialization file and then turning on tracing for either the entire databasevia 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 fileand then look at the output from the tkprof tool. This can also be used togenerate explain plan output.
8.When looking at v$sysstatyou see that sorts (disk) is high. Is this bad or good? If bad -How do youcorrect it?
Ifyou get excessive disk sorts this is bad. This indicates you need to tune thesort area parameters in the initialization files. The major sort are parameteris the SORT_AREA_SIZe parameter.
9.When should you increasecopy latches? What parameters control copy latches
Whenyou get excessive contention for the copy latches as shown by the "redocopy" latch hit ratio. You can increase copy latches via theinitialization parameter LOG_SIMULTANEOUS_COPIES to twice the number of CPUs onyour system.
10.Where can you get a listof all initialization parameters for your instance? How about an indication ifthey are default settings or have been changed
Youcan look in the init.ora file for an indication of manually set parameters. Forall parameters, their value and whether or not the current value is the defaultvalue, look in the v$parameter view.
11.Describe hit ratio as itpertains to the database buffers. What is the difference between instantaneousand cumulative hit ratio and which should be used for tuning
Thehit ratio is a measure of how many times the database was able to read a valuefrom the buffers verses how many times it had to re-read a data value from thedisks. A value greater than 80-90% is good, less could indicate problems. Ifyou simply take the ratio of existing parameters this will be a cumulativevalue since the database started. If you do a comparison between pairs ofreadings based on some arbitrary time span, this is the instantaneous ratio forthat time span. Generally speaking an instantaneous reading gives more valuabledata since it will tell you what your instance is doing for the time it wasgenerated over.
12.Discuss row chaining, howdoes it happen? How can you reduce it? How do you correct it
Rowchaining occurs when a VARCHAR2 value is updated and the length of the newvalue is longer than the old value and won?t fit in the remaining block space.This results in the row chaining to another block. It can be reduced by settingthe storage parameters on the table to appropriate values. It can be correctedby export and import of the effected table.
Posted in: Oracle
0 comments:
Post a Comment