Oracle performance tuning examination few questions are present below.
Question 1.When tables are stored in locally managed tablespaces, where is extent allocation nformation stored?
A. Within the locally managed tablespace
B. Data dictionary
C. Temporary tablespace
Question 2.What are two benefits of storing each table and index partition in a separate tablespace?
A. You can backup and recover each partition independently.
B. You can add and delete columns to partitions independently without affecting all the partitions.
C. You can control the mapping of partitions to disk drives, which is important for balancing I/ O LOAD.
D. You can add and delete column constraints to partitions independently without affecting all the partitions.
E. You can change a column data type in each partition independently without affecting all the other partitions.
Answer: A, C.
Question 3.Which three statements are true regarding the effect of frequent checkpointing on your database?
A. Can slow down a commit.
B. Can affect instance recovery time.
C. Can cause the CKPT process to hang.
D. Can be influenced by redo log file size.
E. Can case unexpected waits during redo log switches.
Answer: B, D, E.
Question 4.Which two views would you query to monitor sessions relates statistics?
Answer: A, B.
Question 5.You have a 512- byte OS block size. You want to cause a checkpoint event to occur every time 10K of data has been written from the Redo Log Buffer to the online redo log.
Which initialization parameter setting will achieve this?
A. LOG_ BUFFER= 10240000
B. LOG_ CHECKPOINT_ BYTES= 10
C. LOG_ CHECKPOINT_ TIMEOUT= 10
D. LOG_ CHECKPOINT_ INTERVAL= 20
E. LOG_ CHECKPOINT_ INTERVAL= 10
F. LOG_ CHECKPOINT_ TIMEOUT= 5120
Question 6.In the CREATE TABLE syntax for an Index Organized Table, what is the purpose of the INCLUDING clause?
A. It specifies the name of the primary key column in the index organized table.
B. It specifies at which column to break a row into twp pieces when a row length exceeds the size set aside in TCTTHRESHOLD.
C. It specifies what percentage of the entire data block to hold open in order to store the row data associated with a primary key value.
D. It specifies the tablespace where the second half f the row data will be stored when the row¡¯s length exceeds the size set aside in PCTTHRESHOLD.
Question 7:.What is the main reason to create a reverse key index on a column?
A. The column contains many different values.
B. The column is mainly used for value range scans.
C. The column is populates using sequential numbers.
D. The column implements an inverted list attribute.
Question 8.How are deadlocks resolved within Oracle?
A. The DBA must terminate the waiting session.
B. The DBA must terminate the blocking session.
C. Oracle detect deadlocks automatically and rolls back the statement which detects the deadlock.
D. TopSessions monitors long running transactions and terminates any session which holds a lock longer than the limit specified by LOCK_ THRESHOLD.
Question 9.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
A. Migration only occurs during an UPDATE operation.
B. The migrated rows are removed with the DELETE command.
C. Migration is automatically removed with the ANALYZE command.
Question 10.When a deadlock occurs, what should be used to diagnose the problem and determine how to prevent the deadlock from occurring again?
A. Performance manager.
B. ORA 00060 error message.
C. The resulting trace file.
Question 11.What is a potential reason for a snapshoot too old error message?
A. You did not refresh your snapshots in time.
B. Your online redo log files are not big enough to snap your largest transactions.
C. Are rollback segment extent sizes are too large.
D. An ITL entry in a data block has been reused.
Question 12.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?
A. OPTIMIZER_ MODE must be set with cost- based optimization.
B. ENABLE QUERY REWRITE must be specified in the parameter file.
C. A use who owns the materialized view must also own PLAN_ TABLE.
D. A user must be granted QUERY REWRITE system privilege to enable materialized views in any schema.
E. QUERY_ REWRITE_ ENABLED= TRUE must be included in the option clause when a materialized view is created.
Question 13.In an index- organized table, what type of segment is used to store row data that exceeds the indexes PCTTHRESHOLD?
A. DATA segment.
B. INDEX segment.
C. CHAIN segment.
D. EXCESS segment
E. OVERFLOW segment.
Question 14 What are the two main benefits of index- organized tables?
A. More concurrency.
B. Faster full table scans.
C. Fast primary key- bases access.
D. Less contention on the segment header.
E. No duplication of primary key values storage.
Answer: C, E.
Question 15 What are two main advantages of using bitmap indexes? ( Choose two)
A. Bitmap indexes use less storage space.
B. Bitmap indexes offer maximum concurrency.
C. Bitmap indexes are easy to maintain when you issue DML statements.
D. Bitmap segments are updates upon COMMIT, at the end of the transaction.
E. Bitmap indexes work very fast with multiple predicates that are combined with AND, OR, and NOT operators.
Answer: A, E.
Question 16 What should be your main redo log buffer turning goal?
A. Avoid space waits situations in the redo log buffer.
B. Make the redo log buffer at least as large as the buffer cache.
C. Mirror redo log files and store group members on different disks.
D. Make sure that the LGWR process is faster than the database write processes.
Question 17 .Which statement could require a sort?
A. SELECT DISTINCT dept_ id FROM dept:
B. UPDATE dept SET salary= salary* 1.1 WHERE id =1342;
C. SELECT dept_id, name FROM dept WHERE dept_id= 16467;
D. SELECT dept_id, name FROM dept WHERE dept_id BETWEEN 7722 and 7100;
Question 18 .Which two views can be used to detect lock contention?
B. V$LOCKED_ OBJECT
C. V$LOCK_ CONTENTION
Answer: A, B.
Question 19 .What is the main reason for a row overflow area when creating index- organized tables?
A. To avoid row chaining and migration.
B. To speed up full table scans and fast full index scans.
C. To improve performance when the index- organized table is clustered.
D. To keep the B- Tree structure densely clustered to allow more rows per leaf block.
Question 20 Which statement is valid regarding index clusters?
A. Index clusters can only be used for tables with low cardinality columns.
B. Index clusters are generally well suited for tables that have many full table scans.
C. Normal B- Tree indexes do not store null key values, whereas cluster indexes store null keys.
D. A cluster index always takes up much more storage space than a normal index for the same set of key values.