iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

 

PERFORMANCE QUESTIONS

We are against multiple choice questions. 

Online Oracle Training

Oracle Performance Tuning Fundamental Exam Questions

Intro to Oracle Database Architecture

Q:   What are the Oracle Architectural components?

Q:   What are the Oracle Memory Components?

Q:   What is the Server Parameter File?

Q:   What is the Parameter File?

Q:   How do you use the init.ora file?

Q:   What is the System Global Area (SGA)?

Q:   What is the Shared Pool in SGA?

Q:   What is the Buffer Cache in SGA?

Q:   What does the Buffer Cache hold in SGA?

Q:   What are the differences between the Library Cache and Dictionary Cache?

Q:   What is the Redo Log Buffer in SGA?

Q:   Describe the Large Pool component in SGA.

Q:   Describe the Multi-threaded Server process.

Q:   What are PGA and UGA?

Q:   Describe the log writer background process (LGWR).

Q:   How often does LGWR write user’s entries to the Online Redo Log Buffer files?

Q:   Describe the Checkpoint process.

Q:   How do you automatically force the Oracle to perform a checkpoint?

Q:   What is the Recovery Process?

Q:   What is the Lock Background Process?

Q:   How does the Archive Process work?

Q:   How do you configure your database to do an automatic archiving?

Q:   What is the System Monitor Process?

Q:   Describe the Program Monitor Process Job.

Q:   What are the differences between the SPFILE and PFILE startup?

Q:   What is the controlfile?

Q:   How do you backup your database controlfiles?

Q:   What does a controlfile contain?

Q:   Describe the password file.

Q:  How do you create a password file?

Q:   Describe the Online Redo Log file.

 

Why tuning and what is Granule unit

Q:   How do you perform tuning on your database?

Q:   What is a Granule Unit?

Q:   How does a granule unit work in an increasing or decreasing the database memory?

Q:   When a client complain about his/her application performance,  what are the list of steps you go through?

Q:   If the size of your SGA is greater than 128M, what is the size of your database granule unit?

Q:   If the size of your SGA is less than 128M, what is the size of your database granule unit?

Q:   What is the minimum number of granules allocated to the buffer cache, and the shared pool?

 

Sizing the Shared Pool

Q:   How do you change a size of the shared pool?

Q:   How do you keep an object in the Shared Pool memory?

Q:   How do you remove an object from the Shared Pool memory?

Q:   How do you calculate the Dictionary Cache Hit ratio value?

Q:   What are the Major components of the Shared Pool Memory?

Q:   What is the User Global Area (UGA)?

Q:   When does the Oracle database use UGA?

Q:   What does the SHARED_POOL_RESERVED_SIZE parameter?

Q:   What does the DBMS_SHARED_POOL package?

 

Sizing the Buffer Cache

Q:   What does the buffer cache contain in the Oracle SGA memory?

Q:   How do you change the size of buffer cache in the SGA memory?

Q:   What is the Dynamic Buffer Cache Advisory parameter?

Q:   What is the Least Recently Used (LRU) list in the buffer cache memory?

Q:   What is a Dirty Buffer in the Buffer cache memory?

Q:   How do you perform tuning on the Buffer Cache Memory?

Q:   How do you check a SGA memory size?

Q:   How do you use the V$PARAMETER view?

Q:   How do you check the Buffer cache, Shared Pool, Redo Log buffer, and JAVA Pool sizes?

Q:   How do you decrease a shared pool memory size?

Q:   How do you increase a buffer cache memory size?

Q:   What does the SHOW PARAMETER command?

Q:   How many lists are the buffers organized in the buffer cache?

Q:   Describe the DB_CACHE_ADVICE parameter.

Q:   Describe the Buffer Cache Advisory method.

Q:   How do you measure the buffer cache hit ratio?

Q:   How do you create a cache table?

Q:   How do you calculate a hit ratio for multiple pools?

Q:   How do you display a hit ratio for the KEEP buffer pool?

Q:   How do you cache an object into the buffer pools using hint in a SQL statement?

Q:   What is a FREELIST?

Q:   How do you diagnose the FREELIST contentions in the buffer cache?

Q:   How do you use the DEFAULT pool?

Q:   How do you use the KEEP pool?

Q:   When do you use the RECYCLE pool?

Q:   What is the V$SYSSTAT view?

Q:   What is the V$BUFFER_POOL view?

Q:   What is the V$BUFFER_POOL_STATISTICS dictionary view?

Q:   What is a hint in the SQL statement?

Q:   How do you drop a table?

Q:   Describe the session logical reads, physical reads direct, and physical reads direct (lob), and physical reads in the V$SYSSTAT view?

Q:   What is an acceptable range for a buffer cache hit ratio?

Q:   Try to cache the department table by using a hint in a SQL statement.

Q:   What does the following SQL statement?

SQL> SELECT s.segment_name, s.segment_type,

               s.FREELISTs, w.wait_time,
               w.seconds_in_wait, w.state
               FROM dba_segments s, v$session_wait w
               WHERE w.event='buffer busy waits'
               AND w.p1=s.header_file
               AND w.p2=s.header_block
/

 

Monitor and size the Redo Log buffer

Q:   How do you monitor the redo log buffer memory size?

Q:   How do you re-size the redo log buffer memory size?

Q:   How do you monitor the redo allocation entries ratio?

Q:   How do you monitor a waiting session in the redo log buffer?

Q:   How do you monitor your online full redo log file?

Q:   Describe the V$SESSION_WAIT view?

Q:   Describe the redo log entries.

Q:   What is the redo log files used in the Oracle database?

Q:   How often does the redo log buffer flush in to the Online redo log files?

Q:   When do you have to increase a size of a redo log buffer?

Q:   What is an acceptable range for the redo log buffer entries ratio?

Q:   How do you monitor a waiting session to obtain a log buffer space?

Q:    What should you do if the log buffer space waits exist in the Red Log buffer memory?

Q:   How do you monitor if a server is waiting for the next redo log file?

 

Database I/O problem

Q:   How do you reduce a database I/O problem?

Q:   How do you monitor a database I/O problem?

Q:   How do you monitor the checkpoint process activities of a database?

Q:   How do you tune the checkpoint process activities?

Q:   How do you use the V$FILESTAT view?

Q:   How do you use the V$SYSSTAT view?

Q:   How do you use the V$SYSTEM_EVENT view?

Q:   How do you use the DBA_DATA_FILES view?

Q:   How do you set the UNDO_MANAGEMENT parameter?

Q:   What does the UNDO_MANAGEMENT parameter?

Q:   Why and how do you distribute your tablespaces on different disks?

Q:   Describe RAID.

Q:   What does the SCOPE=spfile mean in the ALTER SYSTEM SET statement?

Q:   How do you avoid I/O contention in an Oracle database?

Q:   What does the following SQL statement?

SQL> SELECT file_name, phyrds, phywrts
           FROM v$filestat a, dba_data_files b
           WHERE a.file# = b.file_id

Q:   What does the UNDO_MANAGEMENT=AUTO parameter mean?

Q:   What does the following SQL statement?

 

SQL> SELECT name, value
           FROM v$sysstat
           WHERE name IN ('table scans (short tables)',
           'table scans (long tables)')
/

 

Optimize Sort Operations

Q:   How do you optimize a sort operation in the Oracle SGA memory?

Q:   How do you monitor a sort operation?

Q:   How do you use the V$SYSSTAT view to check a sort usage in the SGA memory?

Q:   Describe the ‘sorts (disk)’ value in the V$SYSSTAT view.

Q:   Describe the ‘sorts (memory)’ value in the V$SYSSTAT view.

Q:   How do you calculate the sort ratio value in the SGA sort area?

Q:   How do you optimize the SORT_AREA_SIZE memory?

Q:   what does the following SQL statement?

SQL> SELECT 100*(a.value-b.value)/(a.value) AS "Sort Ratio"
           FROM v$sysstat a, v$sysstat b
           WHERE a.name = 'sorts (memory)'
           AND b.name ='sorts (disk)'
/

Q:   What is an acceptable range for the sort ratio in the SGA sort area?

Q:   When should you consider increasing your SORT_AREA_SIZE parameter?

Q:   When should you consider increasing your PGA_AGGREGATE_TARGET parameter?

 

Diagnosing Contention for Latches

Q:   Describe a latch in the SGA memory.

Q:   What does a latch protect?

Q:   How do you diagnose contention for latches?

Q:   How do you view the shared pool memory size?

Q:   How do you view the library cache request latches hit ratio value?

Q:   How do you view the redo allocation latch ratio value?

Q:   How do you view the redo copy latch wait ratio value?

Q:   How many types of latch request does Oracle have?

Q:   Describe the V$LATCH dictionary view.

Q:   Describe the V$LATCHEHOLDER view.

Q:   Describe the V$LATCHNAME view.

Q:   Calculate the shared pool latch ratio value?

Q:   Calculate the copy wait ratio value?

Q:   What does a process do when a latch is willing to wait for a request and does not get a latch?

Q:   What does a process do when a latch is not willing to wait for a request and does not get a latch?

Q:   What does the following SQL statement?

SQL> SELECT name, (1-(misses/gets))*100

             AS "Ratio", sleeps
             FROM v$latch
             WHERE name in ('library cache', 'shared pool')
/

Q:  What is an acceptable shared pool latch hit ratio?

Q:   What action do you need to perform if the value of the following SQL statement is more than 1.

SQL> SELECT h.pid, n.name, (l.misses/l.gets)*100 wait_ratio
           FROM v$latchholder h, v$latchname n, v$latch l
           WHERE h.laddr = l.addr
           AND l.latch# = n.latch#
           AND n.name in ('redo allocation', 'redo copy')
/

 

Tuning rollback or undo segment

Q:   How do you tune the UNDO segments?

Q:   Describe the UNDO_MANAGEMENT parameter.

Q:   Describe the UNDO_RETENTION parameter.

Q:   Describe the UNDO_TABLESPACE parameter.

Q:   Describe the V$UNDOSTAT view.

Q:   Describe the V$ROLLNAME view.

Q:   Describe the V$ROLLSTAT view.

Q:   How do you monitor the DB_BLOCK_GETS, and CONSISTENT GETS parameters?

Q:   When do you monitor the SHRINKS and AVESHRINS columns in the V$ROLLSTAT dictionary view?

Q:   How do you calculate an estimate of undo spaces to meet the undo retention requirement for 15 minutes?

Q:   How do you get a list of UNDO segments?

Q:   What does the WRITES column indicate in the V$ROLLSTAT dictionary view?

Q:   When do you get the following undo segment error message?

ORA-01555: snapshot too old.

Q:   What is an optimal size when you use an undo segments manually?

Q:   What does the following SQL statement?

SQL> SELECT name, value 
           FROM v$sysstat
           WHERE name in ('db block gets','consistent gets');

 

Monitoring and Detecting Lock Contention

Q:   What is a lock contention in the Oracle database?

Q:   How do you monitor and detect a lock contention?

Q:   How do you lock a table in the exclusive mode?

Q:   How do you lock a table in the shared mode?

Q:   How do you kill a session?

Q:   Describe the different types of user locks.

Q:   What does the following SQL statement?

SQL> SELECT o.owner, o.object_name, o.object_type, l.type
           FROM dba_objects o, v$lock l
           WHERE o.object_id = l.id1
           AND o.owner = 'ISELF'
/

Q:   How do you monitor a lock acquired by a transaction in the Oracle database?

 

Optimizing a SQL statement

Q:   How do you optimize a SQL statement?

Q:   How do you identify that a SQL statement is not optimized?

Q:   Describe the EXPLAIN PLAN statement.

Q:   How do you create the PLAN_TABLE table?

Q:   Describe the use of the SET STATEMENT_ID clause.

Q:   Describe the following operation in PLAN_TABLE.

TABLE ACCESS FULL

TABLE ACCESS BY INDEX

INDEX UNIQUE SCAN

NESTED LOOPS

MERGE JOIN

FILTER

SORT AGGREGATE

Q:   What does the following SQL statement?

SQL> EXPLAIN PLAN

           SET STATEMENT_ID='MY_FIRST_TEST'

            INTO plan_table FOR

            SELECT last_name, trade_date,

                            sum(shares_owned*current_price) portfolio_value

            FROM customers, portfolio, stocks s

                     WHERE id = customer_id and stock_symbol = symbol

                     AND trade_date = (SELECT max(trade_date) FROM stocks

                                                      WHERE symbol = s.symbol)

           GROUP BY last_name, trade_date

/

Q:   What does the following SQL statement?

SQL> SELECT id, parent_id,

            lpad(' ', 2*(level-1)) || operation || ' ' ||

            options || ' ' || object_name || ' ' ||

            decode (id, 0, 'Cost = ' || position) "Query_Plan"

            FROM plan_table

            START WITH id = 0 and STATEMENT_ID = 'MY_FIRST_TEST'

            CONNECT BY PRIOR ID = PARENT_ID

                         AND STATEMENT_ID = 'MY_FIRST_TEST'

/

Q:   How do you read the following PLAN_TABLE output?

ID  PARENT_ID Query_Plan

--- ---------- ----------------------------------------------

  0            SELECT STATEMENT   Cost =

  1          0   SORT GROUP BY

  2          1     FILTER  

  3          2       NESTED LOOPS 

  4          3         MERGE JOIN

  5          4           SORT JOIN

  6          5             TABLE ACCESS FULL STOCKS

  7          4           SORT JOIN

  8          7             TABLE ACCESS FULL PORTFOLIO

  9          3         TABLE ACCESS BY INDEX ROWID CUSTOMERS

10          9           INDEX UNIQUE SCAN SYS_C003126

11          2       SORT AGGREGATE

12         11         TABLE ACCESS FULL STOCKS

   

Diagnostic and Tuning Tools

Q:   Describe the STATSPACK utility.

Q:   How do you install the STATSPACK utility?

Q:   Describe the SPCREATE script.

Q:   How do you run the SPCREATE script?

Q:   Describe the PERFSTAT user.

Q:   How do you create the PERFSTAT user?

Q:   How do you clean the STATSPACK tables?

Q:   How do you produce a performance report using the STATSPACK utility?

Q:   How do you perform a snapshot in the STATSPACK utility?

Q:   Why and how do you set the TIMED_STATISTICS parameter?

Q:   Describe the V$FIXED_TABLE view.

Q:   Describe the following scripts:

SPCREATE.SQL

SPTRUNC.SQL

SPREPORT.SQL

 

How to read STATSPACK report

Q:   What is a reasonable snap shots interval for the STATSPACK utility?

Q:   What does it mean if an output be represented by #######?

Q:   What does the Instance Workload Information section contain in the STATSPACK report output?

Q:   What does the Instance Cache Information section contain in the STATSPACK report utility?

Q:   What does the Load Profile Information section contain in the STATSPACK report utility?

Q:   What does the Instance Efficiency Ratios section contain in the STATSPACK report utility?

Q:   What does the Foreground and Background Wait Events section contain in the STATSPACK report utility?

Q:   What does the Buffer Pool and Buffer Wait Statistics section contain in the STATSPACK report utility?

Q:   What does the PGA Memory Statistics section contain in the STATSPACK report utility?

Q:   What does the ‘Rollback Segment Stats/Storage/Summary for DB’ section contain in the STATSPACK report utility?

Q:   What does the Latch Activity section contain in the STATSPACK report utility?

Q:   What does the Latch Sleep Breakdown and Miss Sources section contain in the STATSPACK report utility?

Q:   What does the Library Cache Statistics section contain in the STATSPACK report utility?

Q:   What does the SGA Memory Summary section contain in the STATSPACK report utility?

Q:   What does the SGA Memory Detail section contain in the STATSPACK report utility?

Q:   What does the INIT.ora Parameter Summary section contain in the STATSPACK report utility? Answers

 

Google
 
Web web site