Here are some more questions on Oracle database performance tuning examination
Question 1 What are two main benefits of index- organized tables?
A. More concurrency.
B. Faster full table scans.
C. Fast primary key- based access.
D. Less contention on the segment header.
E. Less storage is required because there is no duplication of primary key values.
Answer: C, E.
Question 2. To provide more free lists for a number of your database segments, what is one of your
A. Modify them with the INSERT_ _ FREELIST command.
B. Drop and re- create them with the required FREELIST value.
C. Change the default storage parameter of the tablespace( s) where they are stored.
D. Modify the FREELIST_ LIMIT parameter in your installation file and restart the instance.
Question 3. If a willing- to- wait latch request is satisfied on the first attempt, which statistic gets
D. IMMEDIATE_ GETS
E. IMMEDIATE_ GETS
F. IMMEDIATE_ GETS
Question 4. You have a table with a 10 millions rows. You want to build an index on a column in the tablethat has a low cardinality. The table is part of a Decision Support System.Your goal is to build an index that would be efficient for queries using AND/ OR predicates.
Which type of index would be most suitable?
A. B- Tree Index.
B. Bitmap Index.
C. Compresses Indexes
D. Reverse key indexes
Question 5. With reference to Oracle data storage structures, a cluster is defined as?
A. A group of table that each have more then 2 low cardinality columns.
B. A data structure where a group of one or more tables have their own dedicated tablespaces.
C. A group of one or more tables which resides in a tablespace that is striped across multiple disks.
D. A group of one or more tables that share the same data blocks because they share common columns and are often used together in join queries.
Question 6. Summing the system statistics, ( db blocks gets + consistent gets) gives the total number of requests. What is the other system, statistic required to calculate the buffer cache hit ratio?
A. Physical reads.
B. Session logical gotten.
C. Table scan blocks gotten.
D. DBWR buffers scanned.
Question 7. When tables are stored in locally managed tablespaces, where is extent allocation information stored?
B. Data dictionary.
C. Temporary tablespace.
D. Corresponding tablespace itself.
Question 8 What does this statement do?
ANALYZE INDEX index_ name VALIDITY STRUCTURE;
A. It places information into the INDEX_STATS view and allows for the monitoring of space used by an index.
B. It provides information in the INDEX_ HISTOGRAM view to indicate whether an index is invalid or valid.
C. It provides information in the DBA_INDEXES view for the COST BASED Optimizer when choosing an execution plan.
D. None of the above
Question 9. What are free lists used to identify?
A. Free extents in a tablespace.
B. Blocks available for inserts.
C. Blocks beyond the high water mark in a segment.
D. Segments belonging to a Parallel Server instance.
Question 10. Which two statements about row migration are true?
A. Row migration is caused by a PCTREE value set too low.
B. Row migration can be resolved using the ANALYZE command.
C. Row migration can be reduced by choosing a larger block size.
D. Row migration means that row pieces are stored in different blocks.
E. Queries that use an index to select migrated rows perform additional I/ O.
Answer: A, B.
Question 11. What should you confirm before changing the CURSOR_SPACE_FOR_TIME parameter in your initialization file to TRUE?
A. The TIMED_ STATISTICS parameter is set to TRUE.
B. The hit percentage in the buffer cache is at least 95%.
C. The OPEN_ CURSOR parameter is set to at least twice the default value.
D. The value in the RELOADS column of V$ LIBRARYCACHE is consistently zero.
Question 12. To control fragmentation of your shared pool space, when is the best time for you to pin objects with the DBMS_SHARED_POOL package?
A. When the sum of values in the SHARABLE_ MEMORY column of the V$DB_OBJECT_ CACHE view exceeds the value of the SHARED_ POOL_ SIZE initialization parameter.
B. After an object has been used for the first time.
C. When the V$ LIBRARYCACHE view contains higher values in the RELOADS column than in the PINS column.
D. Immediately after instance startup.
Question 13. Which tablespace is used as the temporary tablespace if TEMPORARY TABLESPACE is not specified for a user?
Question 14. Which statement about locally managed tablespaces is true?
A. Only the SYSTEM tablespace can be locally managed.
B. Locally managed tablespaces are necessary for parallel queries.
C. Free space information is contained within the locally managed tablespace.
D. Free space and allocation of extents in locally managed tablespaces is tracked only in the data dictionary.
Question 15.Which dynamic view is most useful for determining the current number of blocks allocated to a buffer pool?
A. V$BUFFER_ POOL
B. V$SESS_ IO
Question 16. When a checkpoint occurs, CKPT does the below
A. Writes the dirty buffers to the data files.
B. Cleans up temporary segments that are no longer in use.
C. Writes the redo entries form the log bugger to redo log files.
D. Updates the control files to record the details of the checkpoint.
Question 17. The EDU_COM_MASTERS table was created with the NOLOGGING attribute and has these columns:
FIRST_ NAME_ VARCHAR2;
EDU_ ID NUMBER;
Which two SQL statements can use NOLOGGING mode? ( Choose two)
A. DELETE FROM EDU_COM_MASTERS;
B. INSERT INTO EDU_COM_MASTERS VALUES (‘JOHN’, 1600);
C. CREATE TABLE EDU AS SELECT * FROM EDU_COM_MASTERS;
D. CREATE INDEX EDU_ALL_PK ON EDU_COM_MASTERS ( EMPLOYEE_ ID);
E. UPDATE EDU_COM_MASTERS SET FIRST_ NAME = ‘JOHN’ WHERE EDU_ ID = 1600;
Answer: C, D.
Question 18.In a dictionary- managed tablespace, the SMON background process periodically coalesces neighboring free extents when which condition is true?
A. When there is more than one data file in the tablespace.
B. When the value of PCTINCREASE for the tablespace is zero.
C. When the value of PCTINCREASE for the tablespace is NOT zero.
D. When the value of INITIAL_ EXTENT for the tablespace is greater than 1 MB.
Question 19.What is a latch?.
A. A mechanism used by the Oracle optimizer to run user queries in parallel.
B. A mechanism to prevent data files that make up the database from auto- extending.
C. A low- level serialization mechanism to protect shared data structures in the system global area ( SGA)
D. A mechanism used by the background process PMON for cleaning up the caches and freeing up resources the user processes were using.
Question 20. Which latch would be required when dirty blocks are written to the disk or when a server process is searching for blocks to write to?
A. Shared pool latch
B. Library cache latch.
C. Cache buffers chains latch.
D. Cache buffers LRU chain latch.