Oracle 1z1-064 Exam : Oracle Database 12c: Performance Management and Tuning

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 hit ratio may indicate that repeated scanning of the same large table or index is being performed.
• 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 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.
• E. A high value indicates that the buffer cache is adequately sized for the current workload.

Your database supports multiple applications. The applications run on the middle tier and use connection pooling for connecting to the database.
You notice that the sessions created by the applications are competing for resources. You want to statistically measure the workload and set priorities.
What action must you perform to achieve this? (Choose the best answer.)

• A. Create services for the applications and set the relative priority of services within an instance by mapping the services directly to consumer groups.
• B. Create services for the applications and set a relative priority by assigning them to application users and using the DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE procedure to trace the services.
• C. Create services for the applications and set a relative priority by assigning them to application users and using the DBMS_MONITOR.SESSION_TRACE_ENABLE procedure to trace the services.
• D. Create services for the applications and set a relative priority by assigning them to application users.

Examine the Time Model Statistics section of an AWR report:

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

• A. The available CPU resources were not utilized to their maximum capacity.
• B. New child cursors were created because of new bind values or usage of literal values as well as different bind types or sizes.
• C. The DB CPU time was not spent exclusively for processing SQL statements.
• D. A large number of connected user sessions were idle.
• E. All sequence numbers used during this AWR time interval were cached.

You want to generate statistics for new objects added to an OLTP application without affecting old statistics and the sessions that currently use them.
Which two tasks would you perform to test the new statistics? (Choose two.)

• A. Use the DBMS_STATS.PUBLISH_PENDING_STATS procedure to make pending statistics the current statistics.
• B. Set the PUBLISH statistics preference to FALSE and then gather statistics.
• C. Set the STALE_PERCENT statistics preference to zero and then gather statistics.
• D. Set the NO_INVALIDATE statistics preference to FALSE and gather statistics without affecting old statistics.
• E. Set the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to TRUE for the session.