|
Oracle Performance Tuning Fundamental Exam Questions
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.
|
"All
human actions have one or more of these seven causes: chance, nature,
compulsion, habit, reason, passion, and desire.”
Aristotle
|
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?
|
"Dignity
consists not in possessing honors, but in the consciousness that we
deserve them.”
Aristotle
|
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?
|
"Education
is the best provision for the journey to old age.”
Aristotle
|
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
/
|
"In
the arena of human life the honours and rewards fall to those who show
their good qualities.”
Aristotle
|
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?
|
"It
is in justice that the ordering of society is centered.”
Aristotle
|
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)')
/
|
“It is the mark of an educated mind to be able to
entertain a thought without accepting it.”
Aristotle
|
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?
|
“Man perfected by society is the best of all animals; he
is the most terrible of all when he lives without law, and without
justice.”
Aristotle
|
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')
/
|
“Pleasure in the job puts perfection in the work.”
Aristotle
|
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');
|
“Poverty is the parent of revolution and crime.”
Aristotle
|
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?
|
"To
give a satisfactory decision as to the truth it is necessary to be rather
an arbitrator than a party to the dispute.”
Aristotle
|
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'
< |