Oracle performance & tuning Quiz

1.This quiz consists of 35 questions .It is recommended to complete the test in 60 min
2. It consists both single choice and more than one choice questions to test your knowledge
3. You can press the submit button at the end of your test and get the final score
4.You will also get the know the correct choice after you finished the test.

Question 1 of 35

1. When tables are stored in locally managed tablespaces, where is extent allocation nformation stored?

Within the locally managed tablespace

Data dictionary

Temporary tablespace

Memory

Question 1 of 35

Question 2 of 35

2. What are two benefits of storing each table and index partition in a separate tablespace?

You can backup and recover each partition independently.

You can add and delete columns to partitions independently without affecting all the partitions.

You can control the mapping of partitions to disk drives, which is important for balancing I/ O LOAD.

You can add and delete column constraints to partitions independently without affecting all the partitions.

You can change a column data type in each partition independently without affecting all the other partitions.

Question 2 of 35

Question 3 of 35

3. Which three statements are true regarding the effect of frequent check pointing on your database?

Can slow down a commit.

Can affect instance recovery time.

Can cause the CKPT process to hang

Can be influenced by redo log file size.

Can case unexpected waits during redo log switches.

Question 3 of 35

Question 4 of 35

4. Which two views would you query to monitor sessions relates statistics?

V$SESSTAT

V$SESSION_EVENT

V$SESSION_STATS

V$SESSION_STATUS

V$WAITS_PER_SESSION

Question 4 of 35

Question 5 of 35

5. What is the main reason to create a reverse key index on a column?

The column contains many different values.

The column is mainly used for value range scans.

The column is populates using sequential numbers.

The column implements an inverted list attribute.

Question 5 of 35

Question 6 of 35

6. How are deadlocks resolved within Oracle?

The DBA must terminate the waiting session.

The DBA must terminate the blocking session.

Oracle detect deadlocks automatically and rolls back the statement which detects the deadlock.

TopSessions monitors long running transactions and terminates any session which holds a lock longer than the limit specified by LOCK_ THRESHOLD.

Question 6 of 35

Question 7 of 35

7. Why do these steps eliminate row migration?
Step 1: Run ANALYSE TABLE .. LIST CHAINED ROWS command
Step 2: Copy the rows to another table
Step 3: Delete the rows from the original table
Step 4: Insert the rows from step 2 back into the original table

Migration only occurs during an UPDATE operation.

The migrated rows are removed with the DELETE command.

Migration is automatically removed with the ANALYZE command.

Question 7 of 35

Question 8 of 35

8. When a deadlock occurs, what should be used to diagnose the problem and determine how to prevent the deadlock from occurring again?

Performance manager.

ORA 00060 error message.

The resulting trace file.

Question 8 of 35

Question 9 of 35

9. What is a potential reason for a snapshot too old error message?

You did not refresh your snapshots in time.

Your online redo log files are not big enough to snap your largest transactions.

Are rollback segment extent sizes are too large.

An ITL entry in a data block has been reused.

Question 9 of 35

Question 10 of 35

10. The optimizer rewrites a query so that the query can access a materialized view instead of the base tables. Although query rewrite activity is transparent to the applications, there are certain prerequisites that need to be satisfied for the optimizer to rewrite queries.
Which statement correctly describe one of the prerequisites?

OPTIMIZER_ MODE must be set with cost- based optimization.

ENABLE QUERY REWRITE must be specified in the parameter file.

A user who owns the materialized view must also own PLAN_ TABLE.

. A user must be granted QUERY REWRITE system privilege to enable materialized views in any schema.

QUERY_ REWRITE_ ENABLED= TRUE must be included in the option clause when a materialized view is created.

Question 10 of 35

Question 11 of 35

11. In an index- organized table, what type of segment is used to store row data that exceeds the indexes PCTTHRESHOLD?

DATA segment.

INDEX segment.

CHAIN segment.

EXCESS segment

OVERFLOW segment.

Question 11 of 35

Question 12 of 35

12. What are the two main benefits of index- organized tables?

More concurrency.

Faster full table scans.

Fast primary key- bases access.

Less contention on the segment header.

No duplication of primary key values storage.

Question 12 of 35

Question 13 of 35

13. What are two main advantages of using bitmap indexes? ( Choose two)

Bitmap indexes use less storage space.

Bitmap indexes offer maximum concurrency.

Bitmap indexes are easy to maintain when you issue DML statements.

Bitmap segments are updates upon COMMIT, at the end of the transaction.

Bitmap indexes work very fast with multiple predicates that are combined with AND, OR, and NOT operators.

Question 13 of 35

Question 14 of 35

14. What should be your main redo log buffer turning goal?

Avoid space waits situations in the redo log buffer.

Make the redo log buffer at least as large as the buffer cache.

Mirror redo log files and store group members on different disks.

Make sure that the LGWR process is faster than the database write processes.

Question 14 of 35

Question 15 of 35

15. Which statement could require a sort?

SELECT DISTINCT dept_ id FROM dept:

UPDATE dept SET salary= salary* 1.1 WHERE id =1342;

SELECT dept_id, name FROM dept WHERE dept_id= 16467;

SELECT dept_id, name FROM dept WHERE dept_id BETWEEN 7722 and 7100;

Question 15 of 35

Question 16 of 35

16. Which two views can be used to detect lock contention?

V$LOCK

V$LOCKED_ OBJECT

V$LOCK_ CONTENTION

V$LOCK_WAITING

Question 16 of 35

Question 17 of 35

17. What is the main reason for a row overflow area when creating index- organized tables?

To avoid row chaining and migration.

To speed up full table scans and fast full index scans.

To improve performance when the index- organized table is clustered.

To keep the B- Tree structure densely clustered to allow more rows per leaf block.

Question 17 of 35

Question 18 of 35

18. Which statement is valid regarding index clusters?

Index clusters can only be used for tables with low cardinality columns.

Index clusters are generally well suited for tables that have many full table scans.

Normal B- Tree indexes do not store null key values, whereas cluster indexes store null keys.

A cluster index always takes up much more storage space than a normal index for the same set of key values.

Question 18 of 35

Question 19 of 35

19. To provide more free lists for a number of your database segments, what is one of your
options?

Modify them with the INSERT_ _ FREELIST command.

Drop and re- create them with the required FREELIST value.

Change the default storage parameter of the tablespace( s) where they are stored.

Modify the FREELIST_ LIMIT parameter in your installation file and restart the instance.

Question 19 of 35

Question 20 of 35

20. If a willing- to- wait latch request is satisfied on the first attempt, which statistic gets incremented?

GETS

SLEEPS

MISSED

IMMEDIATE_ GETS

Question 20 of 35

Question 21 of 35

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

Shared pool latch

Library cache latch.

Cache buffers chains latch.

Cache buffers LRU chain latch.

Question 21 of 35

Question 22 of 35

22. What is a latch?.

A mechanism used by the Oracle optimizer to run user queries in parallel.

A mechanism to prevent data files that make up the database from auto- extending.

A low- level serialization mechanism to protect shared data structures in the system global area ( SGA)

A mechanism used by the background process PMON for cleaning up the caches and freeing up resources the user processes were using.

Question 22 of 35

Question 23 of 35

23. In a dictionary- managed tablespace, the SMON background process periodically coalesces neighboring free extents when which condition is true?

When there is more than one data file in the tablespace.

When the value of PCTINCREASE for the tablespace is zero.

When the value of PCTINCREASE for the tablespace is NOT zero.

When the value of INITIAL_ EXTENT for the tablespace is greater than 1 MB

Question 23 of 35

Question 24 of 35

24. When a checkpoint occurs, CKPT does the below

Writes the dirty buffers to the data files.

Cleans up temporary segments that are no longer in use.

Writes the redo entries form the log bugger to redo log files.

Updates the control files to record the details of the checkpoint.

Question 24 of 35

Question 25 of 35

25. Which dynamic view is most useful for determining the current number of blocks allocated to a buffer pool?

V$BUFFER_ POOL

V$SESS_ IO

V$SYSSTAT

V$CACHE

Question 25 of 35

Question 26 of 35

26. Which statement about locally managed tablespaces is true?

Only the SYSTEM tablespace can be locally managed

Locally managed tablespaces are necessary for parallel queries.

Free space information is contained within the locally managed tablespace.

Free space and allocation of extents in locally managed tablespaces is tracked only in the data dictionary.

Question 26 of 35

Question 27 of 35

27. Which tablespace is used as the temporary tablespace if TEMPORARY TABLESPACE is not specified for a user?

TEMP

DATA

UNDO

SYSTEM

Question 27 of 35

Question 28 of 35

28. What are free lists used to identify?

Free extents in a tablespace.

Blocks available for inserts.

Blocks beyond the high water mark in a segment.

Segments belonging to a Parallel Server instance.

Question 28 of 35

Question 29 of 35

29. You have a table with a 10 millions rows. You want to build an index on a column in the table that 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?

B- Tree Index.

Bitmap Index.

Compresses Indexes

Reverse key indexes

Question 29 of 35

Question 30 of 35

30. With reference to Oracle data storage structures, a cluster is defined as?

A group of table that each have more then 2 low cardinality columns.

A data structure where a group of one or more tables have their own dedicated tablespace

A group of one or more tables which resides in a tablespace that is striped across multiple disks.

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 30 of 35

Question 31 of 35

31. What does this statement do?
ANALYZE INDEX index_ name VALIDITY STRUCTURE;

It places information into the INDEX_STATS view and allows for the monitoring of space used by an index.

It provides information in the INDEX_ HISTOGRAM view to indicate whether an index is invalid or valid.

It provides information in the DBA_INDEXES view for the COST BASED Optimizer when choosing an execution plan.

None of the above

Question 31 of 35

Question 32 of 35

32. What should you confirm before changing the CURSOR_SPACE_FOR_TIME parameter in your initialization file to TRUE?

The TIMED_ STATISTICS parameter is set to TRUE.

The hit percentage in the buffer cache is at least 95%.

The OPEN_ CURSOR parameter is set to at least twice the default value.

The value in the RELOADS column of V$ LIBRARYCACHE is consistently zero.

Question 32 of 35

Question 33 of 35

33. 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)

DELETE FROM EDU_COM_MASTERS;

INSERT INTO EDU_COM_MASTERS VALUES ('JOHN', 1600);

CREATE TABLE EDU AS SELECT * FROM EDU_COM_MASTERS;

CREATE INDEX EDU_ALL_PK ON EDU_COM_MASTERS ( EMPLOYEE_ ID);

UPDATE EDU_COM_MASTERS SET FIRST_ NAME = 'JOHN' WHERE EDU_ ID = 1600;

Question 33 of 35

Question 34 of 35

34. Which three statements about rebuilding indexes are true?

The ALTER INDEX REBUILD command is used to change the storage characteristics of an index.

Using the ALTER INDEX REBUILD is usually faster than dropping and recreating an index because it uses the fast full scan feature.

Oracle allows for the creation of an index or re- creation of an existing index while allowing concurrent operations on the base table.

When building an index, the NOLOGGING and UNRECOVERABLE keywords can be used concurrently to reduce the time it takes to rebuild.

Question 34 of 35

Question 35 of 35

35. Which statement about the amount of undo generated is true?

The amount is the same for any DML operation.

Deletes are inexpensive, because only the ROWID must be stored.

Inserts are inexpensive, because only the ROWID must be stored.

Updates are inexpensive, because only the new column value must be stored.

Question 35 of 35

I hope you like this Oracle performance & tuning Quiz and it helps in your examination preparation. Best of Luck for the Oracle certification Examination
Related Articles
https://en.wikipedia.org/wiki/Oracle_Database
Oracle Architecture MCQ :This is a Oracle Architecture MCQ to check your understanding on Oracle database Architecture concepts like Oracle background processes, memory elements
Oracle sql online test :Check out this Oracle sql online test for interview preparation & certification. This will help in checking your understanding on Oracle SQL
Oracle DBA quiz : this is a oracle dba quiz to check your understanding on DBA concepts.This will help in Passing oracle certification examination and the interview also
oracle quiz : This is a online Oracle Quiz to check your understanding on Oracle stuff like Truncate,delete, drop ,constraints, select query,distinct and many more

Related Posts

Leave a Comment Cancel Reply