1Z0-064 Online Practice Questions and Answers

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

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? (Choose the best answer.)

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.

Examine the output of the query executed to diagnose the reason for performance degradation of queries:

Which three factors will you investigate further to identify the cause of the performance degradation? (Choose three.)

A. Check the number of disk sorts.

B. Check for the causes of the full table scans.

C. Check the number of chained or migrated rows.

D. Check the indexes on the tables used in queries for clustering factor.

E. Check the size of the temporary tablespace for sorting operations.

Which three methods can you use to create a pre-change SQL trial to capture performance data by using SQL Performance Analyzer? (Choose three.)

A. executing SQL statements in a SQL Tuning Set (STS) on a test database by using database links to the production database.

B. generating only execution plans on a test database without actually running SQL statements.

C. generating an execution plan and statistics for selective SQL statements captured in an STS

D. loading performance data and execution plans from an STS.

E. generating both execution plans and statistics for each SQL statement in an STS by actually running the SQL statements on a test database.

Your database supports a DSS workload. In an application, a few complex queries that contain multiple functions and expressions are using materialized views. You notice that some queries are performing poorly because they are not benefiting from query rewrites.

Which three actions would you take to improve the performance of queries? (Choose three.)

A. Create a SQL Tuning Set (STS) and submit as input to the SQL Access Advisor to generate recommendations about query rewrite and fast refresh for materialized views.

B. Use the DBMS_MVIEW.EXPLAIN_REWRITE procedure to analyze why a query failed to rewrite.

C. Create an STS and submit as input to the SQL Performance Analyzer to get recommendations about improving the performance of queries.

D. Use the DBMS_ADVISOR.TUNE_MVIEW procedure to get recommendations about rewriting materialized views.

E. Use the DBMS_ADVISOR.QUICK_TUNE procedure to analyze queries based on the usage of query rewrite with materialized views.

You are administering a database that supports an OLTP workload. CURSOR_SHARING is set to EXACT for the instance. An application is frequently executing almost identical queries that vary in literal values in the WHERE clause, causing a large number of hard parses to occur.

Which four statements would be true if you use bind variables for these queries? (Choose four.)

A. Mutex contention in the library cache will be reduced.

B. The optimizer will use one parent cursor and one child cursor for each SQL statement with different literal values.

C. Hard parses will be reduced for the queries.

D. The optimizer will use bind peeking and subsequent execution of the queries will always generate the same plans irrespective of the cardinality.

E. The optimizer will generate the same plan for all bind values if no histograms exist on the columns used in the WHERE clause of these queries.

F. The optimizer will use bind peeking and use the literal value to determine the execution plan for these queries.

Users complain about increased response time for queries in your production database that supports an OLTP workload. On investigation, you notice a large number of db file scattered read, latch: cache buffers lru chain, and latch: cache buffers chains wait events:

Identify three possible reasons for the increased response time. (Choose three.)

A. too many sort operations being performed

B. repeated simultaneous access to a block or small number of blocks

C. the shared pool is inadequately sized

D. queries not using indexes and performing full table scans

E. queries repeatedly fetching blocks that are not in the database buffer cache

F. cursors are closed explicitly after each execution

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.

You are administering a database that supports a mixed workload. You upgrade your database from Oracle Database 11g to 12c and after the upgrade, users complain about degraded performance of some queries. The SQL plan baselines imported from the previous version are present for the queries and are loaded to the SQL Management Base as accepted plans. On further investigation, you find that better plans are generated but not used by the optimizer.

Examine the parameters set for the instance: Which three tasks would you perform to improve the performance of these queries? (Choose three.)

A. Gather statistics for the objects used in the queries.

B. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve new plans and fix the plans for the statements.

C. Create a SQL Tuning Set (STS) and run it through the SQL Access Advisor to generate recommendations.

D. Create an STS and run it through the SQL Tuning Advisor to generate recommendations.

E. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE.

F. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to alter the accepted plans as fixed plans.

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

Which two statements are true about the interpretation of Buffer Cache Hit Ratio in the Instance Efficiency Percentages section of an AWR report? (Choose two.)

A. A high value indicates that the buffer cache is adequately sized for the current workload.

B. Poor hit ratios indicate that a large number of indexed lookups or small table scans are being performed.

C. A low hit ratio does not necessarily imply that increasing the size of the buffer cache will improve performance.

D. A high hit ratio may indicate that repeated scanning of the same large table or index is being performed.

E. A low hit ratio indicates that a KEEP buffer pool should be configured based on the size of the largest object accessed in the buffer cache.

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

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.

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

Queries on the SALES table frequently use the PROD_ID and COST_ID columns together in the WHERE clause. Examine the command:

Which three statements are true about executing this command?

A. It creates extended statistics for the column group (PROD_ID and CUST_ID).

B. It improves the cardinality estimates when both the PROD_ID and CUST_ID columns are used in the WHERE clause.

C. It creates a virtual column for the column group (PROD_ID and CUST_ID).

D. It improves the selectivity estimates of a single predicate in the WHERE clause.

E. It gathers individual column statistics for the PROD_ID and CUST_ID columns.