01. Which two result in the latest fragmentation within segments and the least external fragmentation within tablespaces?
a) automatic segment space managed segments
b) locally managed tablespaces with uniform extent size
c) freelist managed segments with one freelist
d) dictionary managed tablespace
e) locally managed tablespaces that were converted from dictionary managed tablespaces
f) freelistmanaged segments with multiplefreelist
02. Which two situations can lead to sparsely populated index blocks?
a) Data is frequently inserted using direct path load into a table with an index.
b) Indexed columns in a table are frequently updated.
c) Values in an indexed column are inserted using monotonically incrementing sequences.
d) Bulk delete operations are performed on a table with indexes.
e) Online table move operations are performed frequently on a table with indexes.
03. Which two are prerequisites for running the I/O calibration tool?
a) The database must be inMOUNTstate.
b) The database should be opened in restricted mode.
c) For determining latency time, theSTATISTICS_LEVELparameter must be set toTYPICALorALL.
d) The disks to be tested must be configured to use asynchronous I/O for data files.
e) The database instance must be started using anSPFILE.
04. In which three situations can dynamic statistics be used?
a) when the sampling time is a small fraction of the total time for a query
b) when an execution plan is suboptimal because of complex predicates
c) when extended statistics are not available for SQL statements that use complex predicates
d) when a query is on a partitioned table with a global index
e) when index statistics are missing on a column that is used in SQL statements with highly selective filters
05. Which two statements are true about gathering statistics?
a) If an application has only SQL statements with bind variables, it is better to drop existing histograms, disable creation of histograms, and allow the optimizer to select the best execution plans.
b) If end users query newly inserted data, it is possible to get a suboptimal execution plan even if the automatic statistics gathering job is enabled.
c) If concurrent statistics gathering is done by using parallel execution, the Resource Manager should be used for efficient resource management.
d) For each session that is accessing a global temporary table, the optimizer uses only the shared statistics.
06. In which three situations doesDB timealways increase?
a) when the host is CPU bound for foreground processes
b) when I/O wait time increases for foreground processes
c) when more connections are made to a database instance
d) when CPU consumption by background processes increases
e) when wait time for data to be sent over a network increases
07. Which two statements are true about ADDM or Real-Time ADDM?
a) ADDM can be run manually by selecting any range of AWR snapshots available within the AWR retention period, provided they do not cover a time period when the instances were restarted.
b) ADDM runs in Partial mode to analyze any hung database issues.
c) Real-Time ADDM can proactively detect and diagnose transient performance issues that last for a few seconds.
d) Real-Time ADDM is automatically invoked by ADDM at the end of every hour.
08. Which two statements are true aboutDB timeinV$SYS_TIME_MODEL?
a) DB timecannot exceed the total elapsed time (walk clock time) since the database instance started.
b) DB timecannot exceed the maximum number ofconcurrentsessions multiplied by the actual elapsed time for each session.
c) DB timeincludes the time spent on client processes and background processes.
d) ReducingDBtimeallows a database instance to support more user requests by using the same resources.
e) DB timeis always greater than or equal to theDB CPUtime.
09. Identify two effects of theDB_FILE_MULTIBLOCK_READ_COUNTparameter on the optimizer.
a) Decreasing the value ofDB_FILE_MULTIBLOCK_READ_COUNTfrom the default increases the cost of index probes for DSS workloads.
b) full table scan can become cheaper than index scans if the database instance has a high enoughDB_FILE MULTIBLOCK_READ_COUNTfor both OLTP and DSS workloads.
c) Increasing the value ofDB_FILE_MULTIBLOCK_READ_COUNTwithin OS limits lowers the costingof an index probe that is done inconjunctionwith a nested loop for OLTP workloads.
d) In DSS workloads where full table scans may run in parallel and bypass the buffer cache, decreasing the value ofDB_FILE_MULTIBLOCK_READ_COUNTfrom the default increases the cost of full table scans.
e) Increasing the value ofDB_FILE_MULTIBLOCK_READ_COUNTwithin OS limits lowers the cost of full table scans and can result in the optimizer choosing a full table scan over an index scan for both OLTP and DSS workloads.
10. For your database some users complain about not being able to execute transactions. Upon investigation, you find that the problem is caused by some users performing long- running transactions that consume huge amounts of space in the UNDO tablespace.
You want to control the usage of the UNDO tablespace only for these user sessions. How would you avoid the issue from repeating in future?
a) Create a profile for the users with the LOGICAL_READS_PER_SESSION and LOGICAL_READS_PER_CALL limits defined.
b) Create external roles to restrict the usage of the UNDO tablespace and assign them to the users.
c) Set the threshold for UNDO tablespace usage for the users.
d) Implement a Database Resource Manager plan by mapping the users to a resource consumer group with limits defined for UNDO tablespace usage.