Oracle Oracle Database 1Z0-064 Questions & Answers

• Question 51:

  Which two statements are true about Active Session History (ASH)? (Choose two.)

  A. The Data Sample size available in an ASH report is dynamic and, at any given moment, is directly related to the amount of work being performed.

  B. ASH contains sampled data from all sessions that are connected to a database instance at any given moment.

  C. ASH samples data from V$SESSION every second.

  D. An ASH report can be used to identify the service that may be the cause of a transient performance problem.

  Correct Answer: CD

• Question 52:

  You have been asked to use table compression for two large tables. Given are the details of the tables:

  The TRANS_DET table:

  The table is used by an OLTP application.

  High volume insert and update operations are performed on the table.

  The table is frequently queried using index range scans.

  The TRANS_HISTORY table:

  The table is used by a DSS application.

  High volume bulk loads are performed on the table.

  The table is used to store archival data on which large table full-table scans (FTS) are performed.

  Which row store compression would you recommend for these tables with minimal overhead on

  performance? (Choose the best answer.)

  A. basic table compression for both the tables

  B. advanced row compression for both the tables

  C. basic table compression for the TRANS_HISTORY table and advanced row compression for the TRANS_DET table

  D. basic table compression for the TRANS_DET table and advanced row compression for the TRANS_HISTORY table

  E. warehouse compression for the TRANS_DET table and archive compression for the TRANS_HISTORY table

  Correct Answer: A

• Question 53:

  Which two statements are true about viewing the details of Real-Time Database Operations? (Choose two.)

  A. In V$SQL_MONITOR monitoring, statistics are cumulative over several executions of the SQL statement that is being monitored in a session.

  B. SQL Developer can be used to view running database operations.

  C. Oracle Enterprise Manager Database Express can be used to view running database operations.

  D. When the SQL statement that is being monitored is executing, V$SQL_MONITOR is refreshed once every minute. After the execution ends, the monitoring information in V$SQL_MONITOR is deleted immediately.

  E. Oracle Enterprise Manager Cloud Control can be used to view running database operations.

  Correct Answer: AC

• Question 54:

  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

  Correct Answer: AD

• Question 55:

  Your database supports a DSS system.

  Examine the parameter values configured in your database:

  sga_max_size = 480M

  sga_target = 480M

  pga_aggregate_target = 160M

  The CUSTOMERS table contains 8,000 rows. The CUST_ID column is the primary key and the

  COUNTRY_ID column contains only three possible values: 1111, 2222, and 3333.

  You execute the commands:

  SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(`SH',`CUSTOMERS');

  PL/SQL procedure successfully completed.

  SQL> CREATE INDEX COUNTRY_IDX ON CUSTOMERS (COUNTRY_ID);

  Index created.

  You then perform several INSERT, UPDATE, and DELETE operations, significantly altering the data in the

  table.

  View the Exhibit to examine the query and its execution plan.

  

  Which three options would improve the performance of the query? (Choose three.)

  A. creating a bitmap index on the COUNTRY_ID column

  B. regathering statistics on the CUSTOMERS table

  C. creating a histogram on the COUNTRY_ID column

  D. increasing the size of the PGA

  E. creating a SQL profile

  F. creating a KEEP cache

  Correct Answer: ABF

• Question 56:

  Examine the command to change a parameter value from the default to 50:

  SQL> ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ = 50;

  What is the effect of changing the value of the parameter? (Choose the best answer.)

  A. It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced.

  B. It influences the optimizer to use bitmap indexes as the estimated cost of conversion from bitmap to rowid is reduced.

  C. It influences the optimizer to always use fast full index scans as the estimated cost of using an index is reduced.

  D. It influences the optimizer to use indexes instead of full table scans as the estimated cost of using an index is reduced.

  Correct Answer: D

• Question 57:

  You are administering a database that supports an OLTP workload. An application performs a large number of small transactions. Users complain about increased response times for transactions. On investigation, you find that the cache hit ratio is 69%. Examine a partial output from V$SYSTEM_EVENT: Which four can be possible reasons for the increased response time? (Choose four.)

  

  A. The database buffer cache is inadequately sized.

  B. DBWR is not writing the dirty buffers fast enough.

  C. A large number of blocks are fetched from disks frequently.

  D. Several full table scans are performed by transactions.

  E. Blocks are aging out of the buffer cache frequently.

  F. Many sessions are waiting for buffers that are currently being read into the buffer cache by other sessions.

  Correct Answer: BCEF

• Question 58:

  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.

  Correct Answer: CD

• Question 59:

  Examine the parameters set for a database instance:

  

  An application performs a large number of identical queries on small lookup tables very frequently. Users complain about the slow response time of queries on these tables. On investigation, you notice that buffers are getting aged out of the buffer cache. To mitigate the issue, you increase the value of the SGA_MAX_SIZE and SGA_TARGET parameters, but after some time, you notice the same issue again.

  Which two would you recommend as long-term solutions for this issue? (Choose two.)

  A. increasing the size of the database buffer cache

  B. configuring Automatic Memory Management

  C. configuring the KEEP buffer pool and altering tables to use the KEEP pool

  D. pinning the cursors of the queries in the library cache

  E. configuring the result cache for the instance

  Correct Answer: AB

• Question 60:

  Which three statements are true about the interpretation of an execution plan? (Choose three.)

  A. The cost of the entire plan is indicated by the line with Id 0 and always includes both I/O and CPU resources.

  B. The ROWS column indicates the cardinality of each operation and is always calculated by dividing the total number of rows in the table by the number of distinct values in the column used in the WHERE clause predicate.

  C. A TABLE ACCESS FULL in the Operation column for a query with a WHERE clause occurs only if no index exists for the filter column.

  D. An INDEX UNIQUE SCAN in the Operation column always implies that only one row will be returned.

  E. A HASH JOIN in the Operation column always implies that two tables are joined by using an equijoin.

  Correct Answer: ABC