Interview Questions.

Q1. How Do You Increase The Db Cache?

Regulate desk emp cache

Q2. Which Init Parameter You Have To Set To Bypass Parsing?

Cursor_sharing=pressure

Q3. If A Tablespace Has A Default Pct Increase Of Zero What Will This Cause (in Relationship To The Smon Process)?

The SMON manner gained’t automatically coalesce its free space fragments.

Q4. 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?

A massive range of small shrinks shows a want to increase the size of the rollback segment extents. Ideally you should haven't any shrinks or a small number of big shrinks. To fix this just boom the size of the extents and regulate premiere thus.

Q5. How Do You Collect Statistics Of A Table?

Analyze table emp compute information or examine table emp estimate data.

Q6. How Many Types Of Hits Are There And What Are They?

There are two kinds of hits, they're

Buffer hit

library hit

Q7. What Are The Types Of Wait Events?

Cpu time

direct course study

Q8. Which Optimizer Is The Best One?

CBO

Q9. You See Multiple Extents In The Temporary Tablespace. Is This A Problem?

As lengthy as they're all the identical size this isn’t a problem. In fact, it can even enhance overall performance considering the fact that Oracle received’t should create a new extent while a person wishes one.

Q10. How Do You Get The Info About Fts?

The usage of v$sysstat

Q11. What Are The Types Of Tunings?

CPU Tuning

Memory Tuning

IO Tuning

Application Tuning

Database Tuning

Q12. What Is The General Guideline For Sizing Db_block_size And Db_multi_block_read For An Application That Does Many Full Table Sc?

Oracle almost always reads in 64k chunks. The two should have a product equal to sixty four or a a couple of of sixty four.

Q13. Where Do You Get The Info Of Library Cache?

V$librarycache

Q14. If A Tablespace Shows Excessive Fragmentation What Are Some Methods To Defragment The Tablespace? (7.1,7.2 And 7.Three Only)

In Oracle 7.0 to 7.2 The use of the ‘modify consultation set events ‘immediate trace call coalesce stage ts#’;’ command is the very best manner to defragment contiguous loose space fragmentation. The ts# parameter corresponds to the ts# fee located within the ts$ SYS table. In version 7.3 the ‘regulate tablespace coalesce;’ is satisfactory. If loose area isn’t contiguous then export, drop and import of the tablespace contents can be the best manner to reclaim non-contiguous unfastened area.

Q15. You See Multiple Fragments In The System Tablespace, What Should You Check First?

Ensure that customers don’t have the SYSTEM tablespace as their TEMPORARY or DEFAULT tablespace task through checking the DBA_USERS view.

Q16. What Is Performance Tuning?

Making most appropriate use of gadget the use of present resources referred to as performance tuning.

Q17. What Is An Optimizer?

Optimizer is a mechanism as a way to make the execution plan of an square announcement.

Q18. How Do You See The Statistics Of A Table?

Pick num_rows,blocks,empty_blocks from dba_tables where tab_name=’emp’

Q19. If You See Contention For Library Caches How Can You Fix It?

Increase the scale of the shared pool.

Q20. What Is The Fastest Query Method For A Table?

Fetch via rowid.

Q21. How Do You Get The Index Of A Table And On Which Column The Index Is?

Dba_indexes and dba_ind_columns

Q22. How Do You Collect Statistics Of A User/schema?

Exec dbms_stats.Gather_schema_stats(scott)

Q23. When Looking At The Estate Events Report You See That You Are Getting Busy Buffer Waits. Is This Bad? How Can You Find What Is Causing It?

Buffer busy waits can also indicate contention in redo, rollback or facts blocks. You want to check the v$waitstat view to see what regions are causing the trouble. The cost of the “remember” column tells wherein the hassle is, the “class” column tells you with what. UNDO is rollback segments, DATA is data base buffers.

Q24. A Table Space Has A Table With 30 Extents In It. Is This Bad? Why Or Why Not?

Multiple extents in and of themselves aren’t bad. However if you also have chained rows this will harm overall performance.

Q25. How Do You See The Trace Files?

The usage of tkprof usage: tkprof allllle.Trc llkld.Txt

Q26. What Are Some Indications That You Need To Increase The Shared_pool_size Parameter?

Poor records dictionary or library cache hit ratios, getting blunders ORA-0403@Another indication is gradually lowering overall performance with all other tuning parameters the same.

Q27. What Is A Execution Plan?

Its a road map how sq. Is being executed by means of oracle db..

Q28. What Are Chained Rows?

These are rows, it sp in more than one blocks.

Q29. What Mainly Database Tuning Contains?

Hit Ratios

Wait Events

Q30. How Do You Delete Statistics Of An Object?

Analyze desk emp delete statistics.

Q31. Explain The Use Of Tkprof? What Initialization Parameter Should Be Turned On To Get Full Tkprof Output?

The tkprof device is a tuning device used to decide cpu and execution instances for SQL statements. You use it through first placing timed_statistics to actual inside the initialization file after which turning on tracing for either the entire database via the sql_trace parameter or for the consultation the usage of the ALTER SESSION command. Once the hint record is generated you run the tkprof tool towards the trace record and then look at the output from the tkprof tool. This also can be used to generate provide an explanation for plan output.

Q32. How Can You Tell If A Tablespace Has Excessive Fragmentation?

If a select towards the dba_free_space table suggests that the count of a tablespaces extents is extra than the matter of its information documents, then it's miles fragmented.

Q33. In A System With An Average Of forty Concurrent Users You Get The Following From A Query On Rollback Extents:

rollback Cur Extents

r01 11

r02 eight

r03 12

r04 9

device 4

yo

No there is not a problem. You have forty extents showing and a mean of 40 concurrent customers. Since there may be lots of room to develop no action is wanted.

Q34. What Are The Types Of Optimizers?

RBO(Rule Based Optimizer)

CBO(Cost Based Optimizer)

Q35. When Should You Increase Copy Latches? What Parameters Control Copy Latches?

When you get immoderate rivalry for the replica latches as shown by way of the “redo reproduction” latch hit ratio. You can boom replica latches via the initialization parameter LOG_SIMULTANEOUS_COPIES to two times the range of CPUs to your gadget.

Q36. How Do You Flush The Shared Pool?

Regulate system flush shared_pool

Q37. How Do You Know Which Session Is Running Long Jobs?

With the aid of going v$session_longops

Q38. You See The Following On A Status Report:

redo Log Space Requests 23

redo Log Space Wait Time 0

is This Something To Worry About? What If Redo Log Space Wait Time Is High? How Can You F

Since wait time is 0, no. If wait time became excessive it might suggest a want for more or large redo logs.

Q39. Describe Hit Ratio As It Pertains To The Database Buffers. What Is The Difference Between Instantaneous And Cumulative Hit Ratio; Which Should Be Used For Tuning?

Hit ratio is a measure of how usually the database become capable of read a fee from the buffers verses how many times it had to re-examine a records value from the disks. A value extra than eighty-ninety% is right, less ought to suggest problems. If you take the ratio of existing parameters this will be a cumulative value for the reason that database started. If you do a assessment between pairs of readings based totally on a few arbitrary time span, that is the immediately ratio for that point span. Generally speakme a right away studying gives greater precious statistics considering it's going to let you know what your example is doing for the time it become generated over.

Q40. If You See The Value For Reloads Is High In The Estate Library Cache Report Is This A Matter For Concern?

Yes, you ought to attempt for 0 reloads if feasible. If you notice excessive reloads then boom the size of the shared pool.

Q41. Discuss Row Chaining, How Does It Happen? How Can You Reduce It? How Do You Correct It?

Row chaining occurs whilst a VARCHAR2 cost is up to date and the period of the new cost is longer than the vintage value and received’t in shape inside the last block area. This outcomes inside the row chaining to some other block. It can be reduced with the aid of setting the garage parameters on the table to appropriate values. It may be corrected by export and import of the effected table.

Q42. If You See A Pin Hit Ratio Of Less Than 0.Eight In The Estate Library Cache Report Is This A Problem? If So, How Do You Fix It?

This indicates that the shared pool can be too small. Increase the shared pool size.

Q43. What Are The Pre Requsited To Make Use Of Optimizer?

Set the optimizer mode

Collect the data of an object

Q44. Where Can You Get A List Of All Initialization Parameters For Your Instance? How About An Indication If They Are Default Settings Or Have Been Changed?

You can look inside the init.Ora file for an illustration of manually set parameters. For all parameters, their price and whether or not or now not the modern-day fee is the default fee, look in the v$parameter view.

Q45. What Is The Diff Between Compute And Estimate?

If you operate compute, The FTS will show up, in case you use estimate just 10% of the desk could be study

Q46. You Look At The Dba_rollback_segs View And See That You Have A Large Number Of Wraps Is This A Problem?

A large wide variety of wraps suggests that your extent length to your rollback segments are likely too small. Increase the scale of your extents to lessen the wide variety of wraps. You can examine the average traction size within the identical view to get the information on traction size.

Q47. What Can Cause A High Value For Recursive Calls? How Can This Be Fixed?

A high price for recursive calls is purpose via incorrect cursor utilization, immoderate dynamic space control moves, and or immoderate statement re-parses. You need to determine the cause and accurate it By either relinking applications to keep cursors, use proper area control strategies (proper storage and sizing) or make sure repeat queries are positioned in packages for correct reuse.

Q48. How Do You Get The Information Of Specific Session?

V$mystat

Q49. When Looking At V$sysstat You See That Sorts (disk) Is High. Is This Bad Or Good? If Bad, How Do You Correct It?

If you get excessive disk sorts that is terrible. This suggests you want to tune the type region parameters within the initialization documents. The main kind are parameter is the SORT_AREA_SIZe parameter.

Q50. Data Dictionary Follows Which Optimizer Mode?

RBO.