OCA OCP Oracle Certification Exam Braindumps

1 You executed the following SQL statement to change the goal of the cost-based optimizer (CBO): What will be the result of this statement?
A.The goal of the CBO for the current session is set to best throughput.
B.The optimizer uses a cost-based approach for all SQL statements in the session   provided the statistics are available.
C.It will result in an error because the goal of the CBO cannot be changed at the session level.
D.The goal of the CBO for the current session is set to find the best plan for fast delivery of all the rows while using a mix of cost and heuristics.
ANSWER: A

2. Which two statements are true regarding the statistics used by the optimizer to   generate an optimal plan? (Choose two.)
A.The statistics collected through a histogram are not useful for better selectivity estimates of unevenly distributed data.
B.Dynamic sampling is preferred when the cost of collecting the sample statistics outweighs the advantage of a better execution plan.
C.The maximum and minimum values collected by the column statistics leads to an optimal plan if there is an even distribution of data between those two values.
D.The statistics collected through a histogram are useful if the data distribution is skewed between minimum and maximum values.
ANSWER: CD

3. Notice the following factors about a table: Which is the least time-consuming method that you would use to deallocate the unused space?
A.follow the EXPORT, DROP, and IMPORT method
B.use the DBMS_SPACE.FREE_BLOCKS procedure
C.follow the EXPORT, TRUNCATE, and IMPORT method
D.use MOVE with the ALTER TABLE command to move the table
E.use DEALLOCATE UNUSED with the ALTER TABLE command
ANSWER: D

4. In your database, you executed the following command to monitor the ind1 index: After this, the table is updated by a large transaction. Now, you want to check the index statistics. What would you do to update the INDEX_STATS view with the latest statistics?
A.analyze the index with the ESTIMATE STATISTICS option
B.analyze the index with the COMPUTE STATISTICS option
C.analyze the index with the VALIDATE STRUCTURE option
D.gather the table statistics first, and then analyze the index with any one of the options
ANSWER: C

5. Which statement correctly describes the purpose of the REFRESH COMPLETE option when used with materialized view?
A.truncates the materialized view and repopulates it with the data from the base table in the query
B.populates the materialized view with the data from the base tables that has changed since the last refresh
C.populates the materialized view with the data from the base tables after each commit to any of the base tables
D.truncates the materialized view and populates it with the data from the base tables that has changed since the last refresh
ANSWER: A