Oracle 1Z0-064 Online Practice Questions and Exam Preparation

Oracle 1Z0-064 Online Questions & Answers

• Question 1:

  Examine the query and its output:

  

  How would you investigate the cause of three entries of identical SQL statements by using dynamic performance views?

  A. Query V$SQLAREA to find out if the SQL statement is bind aware.
  B. Query V$SQLAREA to find out if multiple sessions executed this SQL statement, which resulted in multiple entries in V$SQL.
  C. Query V$SQL to find out if any invalidations or reloads occurred for this SQL statement.
  D. Query V$SQL_SHARED_CURSOR to identify the reason that a child cursor cannot be shared.
  D. Query V$SQL_SHARED_CURSOR to identify the reason that a child cursor cannot be shared.

• Question 2:

  Which two statements are true about gathering statistics? (Choose two.)

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

• Question 3:

  Examine the query and its output:

  

  Which two inferences can be definitely derived from this output? (Choose two.)

  A. The db file scattered read event has occurred 104 times in this session for file# 12.
  B. The session has completed performing a full table scan.
  C. The SQL statements in this session are performing excessive disk reads.
  D. The multiblock factor is 8 for this I/O but it could vary for the other I/O events.
  A. The db file scattered read event has occurred 104 times in this session for file# 12.
  C. The SQL statements in this session are performing excessive disk reads.

• Question 4:

  Your database supports an OLTP workload during the day and batch processing at night. You want to monitor performance metrics to detect any degradation of performance in both types of workloads over a time period of 30 days. Examine this list of possible steps:

  1.

  Create a fixed baseline.

  2.

  Create a baseline template.

  3.

  Create a new moving window baseline.

  4.

  Increase the retention period default value to 30 days.

  5.

  Increase the size of the existing moving window baseline to 30 days.

  6.

  Create warning and critical alerts for the relevant metrics.

  7.

  Enable adaptive thresholds to detect the workload patterns and specify a high-significance-level threshold type.

  8.

  Enable adaptive thresholds to detect the workload patterns and set different threshold values as a percentage of the maximum value. Which option represents the required steps in the correct order? (Choose the best answer.)

  A. 5, 7
  B. 2, 4, 3
  C. 3, 4, 8
  D. 4, 5, 7
  E. 5, 1, 6, 8
  E. 5, 1, 6, 8

• Question 5:

  For which three problem categories does Automatic Database Diagnostic Monitor (ADDM) provide analysis and recommendations by default? (Choose three.)

  A. for network stack-related bandwidth contention
  B. for concurrency issues because of buffer busy problems
  C. for high-load PL/SQL execution and compilation, and high-load Java usage
  D. for application-level lock contention.
  B. for concurrency issues because of buffer busy problems
  C. for high-load PL/SQL execution and compilation, and high-load Java usage
  D. for application-level lock contention.

• Question 6:

  In which three situations can dynamic statistics be used? (Choose three.)

  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
  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

• Question 7:

  You are administrating a database on a symmetric multiprocessing (SMP) machines. The database supports a mixed workload. You notice a large number of free buffer waits and buffer busy waits that are affecting the performance of the database. To mitigate the issue, you increase the buffer cache size, but after some time, you notice the same wait events with a high number.

  Examine the parameters set for the instance: Which two actions would you recommend to resolve the issue?

  

  A. decreasing the idle time resource limit in the profile assigned to users
  B. enabling asynchronous I/O for the file system
  C. increasing the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter
  D. configuring the KEEP buffer pool
  E. increasing the number of DBWn processes
  A. decreasing the idle time resource limit in the profile assigned to users
  C. increasing the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter

• Question 8:

  In which three scenarios can you use ADDM to get recommendations?

  A. to determine the SQL statements that are performing poorly before you launch the SQL Tuning Advisor to tune them
  B. to determine the segments on which waits are occurring when the database has a high number of buffer busy waits
  C. to determine whether SQL profiling needs to be done to tune SQL statements transparently without requiring any change to the application code
  D. to determine if the SGA_TARGET parameter value needs to be changed when your database is configured for Automatic Shared Memory Management and additional physical memory is added to the database server
  E. to analyze problems in an unresponsive database, which requires a database restart
  A. to determine the SQL statements that are performing poorly before you launch the SQL Tuning Advisor to tune them
  C. to determine whether SQL profiling needs to be done to tune SQL statements transparently without requiring any change to the application code
  E. to analyze problems in an unresponsive database, which requires a database restart

• Question 9:

  In the CUSTOMERS table, the values in the CUST_STATE column are dependent on the values in the COUNTRY_ID column. You want to make the optimizer aware of this dependency when these columns are used together in WHERE clause predicates that contain equalities or in-lists.

  Which two methods achieve this? (Choose two.)

  A. gathering statistics on the CUSTOMERS table and its dependent objects, and then locking the statistics
  B. using SQL plan directives to generate an optimal plan
  C. setting the dynamic statistics level to 4 and setting the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to true
  D. creating column group statistics, regathering statistics, and ensuring that histograms exist on both these columns
  A. gathering statistics on the CUSTOMERS table and its dependent objects, and then locking the statistics
  D. creating column group statistics, regathering statistics, and ensuring that histograms exist on both these columns

• Question 10:

  Your database supports a mixed workload. The ERP application creates short sessions and performs small, random I/Os; the REPORTING application executes long-running DSS queries.

  You want to set a priority for the workload generated by the ERP application and optimize resource usage for them.

  Which three objectives can be achieved by the Resource Manager? (Choose three.)

  A. limiting the amount of time that a session is idle and blocking other sessions of the ERP application
  B. limiting the amount of undo generated by operations performed by sessions created by the ERP application
  C. creating two resource plans with resource limits defined for the workload generated by the applications and automatically changing resource plans based on the workload
  D. allocating a lower percentage of CPU to sessions used by the REPORTING application than to those used by the ERP application
  E. limiting the physical I/O performed by the sessions or users of the ERP application that are connected to the database
  B. limiting the amount of undo generated by operations performed by sessions created by the ERP application
  D. allocating a lower percentage of CPU to sessions used by the REPORTING application than to those used by the ERP application
  E. limiting the physical I/O performed by the sessions or users of the ERP application that are connected to the database