NEW QUESTION 1
You have been asked to assess if using column store compression (previously known as hybrid columnar compression or HCC) would help improve the performance of queries on some large tables.
Which three aspects should you consider before you choose this compression method? (Choose three.)

• A. Check whether direct path load operations are used to insert rows in the table.
• B. Check whether the table is frequently queried using full table scans as column store compression only minimizes I/O during full table scans.
• C. Check whether the table is frequently updated because it will have overhead for insert and update operations.
• D. Check whether the table has LOB columns as it will minimize I/O for the queries.
• E. Check whether the table blocks are sparsely populated as this will defragment the blocks.

Answer: ABD

NEW QUESTION 2
In which three situations does DB time always increase? (Choose three.)

• 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

Answer: ABC

Explanation: Reference: http://www.oracle.com/technetwork/oem/db-mgmt/s317294-db-perf-tuning-with- db-time-181631.pdf (page 21)

NEW QUESTION 3
You define the warning threshold for the tablespace usage metric for the USERS tablespace to be 60% and the critical threshold to be 80%.
Which two sources should you check for the alert information when either the warning or the critical threshold is exceeded? (Choose two.)

• A. the alert log
• B. Oracle Enterprise Manager Cloud Control
• C. DBA_ALERT_HISTORY
• D. DBA_OUTSTANDING_ALERTS
• E. DBA_ACTIVE_SESSION_HISTORY
• F. DBA_THRESHOLDS

Answer: AF

NEW QUESTION 4
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.

Answer: BCD

NEW QUESTION 5
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 an 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.

Answer: ACE

NEW QUESTION 6
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

Answer: A

NEW QUESTION 7
Examine the partial AWR report taken for a time period of 60 minutes:

Which two inferences can you draw from this report? (Choose two.)

• A. The database user calls are issuing frequent explicit commits.
• B. The CPUs are busy executing server processes and background processes for a considerable amount of CPU time.
• C. The database user calls are spending most of their time in I/O for single block reads.
• D. The database user calls are spending most of their time waiting for sessions that are in more important consumer groups.

Answer: BC

NEW QUESTION 8
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.

Answer: ABEF

NEW QUESTION 9
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 an SQL profile
• F. creating a KEEP cache

Answer: ABF

NEW QUESTION 10
You are administering a database that supports an OLTP workload. The CURSOR_SHARING parameter is set to EXACT for the instance. The performance of queries issued by one of the modules has degraded. The queries executed by the module are almost identical in syntax. To investigate, you analyze the latest AWR report and find a large number of latch:shared pool wait events and also a high percentage of the hard parse elapsed time.
Which two can be reasons for this? (Choose two.)

• A. The I/O performance is slow.
• B. Bind variables are not used for similar queries, causing hard parses.
• C. Repeated access to a small number of blocks.
• D. Excessive time is spent on finding cached cursors in the library cache.
• E. The CURSOR_SHARING parameter is set to EXACT, which does not allow similar queries to share a cursor.

Answer: BC

NEW QUESTION 11
Examine an extract from a PGA Memory Advisory for your database:

Which two inferences are correct? (Choose two.)

• A. Automatic management of PGA memory is disabled.
• B. The current PGA size requires the use of a temporary tablespace for sorting operations.
• C. The current PGA size is sufficient and does not require the memory manager to allocate more memory.
• D. PGA size should be increased at least four times its current size for significant improvement in performance and disk space management.

Answer: BD

NEW QUESTION 12
You recently joined a new team administering a database.
You notice that full table scans are performing poorly compared with full table scans on the databases you administered in a previous job.
You decide that performance problems are caused by a misconfiguration of factors affecting full table scans.
Which three factors should you investigate to determine the cause of the poorly performing Full Table Scans (FTS)? (Choose three.)

• A. value of DB_FILE_MULTIBLOCK_READ_COUNT
• B. storing query results in the result cache
• C. setting of the DISK_ASYNC_IO parameter to TRUE
• D. setting of the OPTIMIZER_MODE parameter to ALL_ROWS
• E. use of parallel queries
• F. block size of the tablespaces in which the tables being scanned are stored
• G. value of the OPTIMIZER_DYNAMIC_SAMPLING parameter

Answer: ABC

NEW QUESTION 13
Your database supports a mixed workload. In an application, multiple complex queries with functions and expressions are executing. You want to analyze the queries that are currently cached in the library cache to receive recommendations about the usage of indexes and materialized views.
What should you do to achieve this? (Choose the best answer.)

• A. Create an STS for the queries cached in the library cache and submit it as an input to SQL Tuning Advisor.
• B. Create an STS for the queries cached in the library cache and submit it as an input to SQL Access Advisor.
• C. Capture the workload in an STS and submit to SQL Tuning Advisor for recommendations.
• D. Create an STS for the queries cached in the library cache and submit it as an input to SQL Performance Analyzer.

Answer: D

NEW QUESTION 14
To investigate the slow response time of queries on the TRANS table, you gathered the table statistics and executed the query:

The table is stored in a tablespace that has Automatic Segment Space Management (ASSM) enabled. The tablespace is created with a standard block size of 8192 bytes.
Which three can be reasons for the slow response time of the queries? (Choose three.)

• A. Row size is too large to fit into a single block during insert operations.
• B. Row moves from one data block to another data block because the row grows too large to fit in the original block.
• C. The table is subject to frequent insert, update, and delete DML activity leading to sparsely populated blocks.
• D. The value of PCTUSED is set to a value lower than the default, causing row changing.
• E. The value of PCTFREE is set to a value lower than the default, causing row chaining.

Answer: ABC

NEW QUESTION 15
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.

Answer: CD

NEW QUESTION 16
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.

Answer: D

NEW QUESTION 17
In your database, the locally managed tablespace, USERS, has the default space usage alert set to 85% for the warning level and 97% for the critical level.
Which two statements are true? (Choose two.)

• A. Alerts are recorded in both Oracle Enterprise Manager Cloud Control and DBA_OUTSTANDING_ALERTS only when the critical threshold is exceeded.
• B. Alert settings for the warning and critical levels must be disabled before taking the USERS tablespace offline.
• C. Alerts that are triggered are automatically recorded in DBA_ALERT_HISTORY after they are cleared.
• D. Alerts are triggered when the space usage reaches the warning level, again when it reaches the critical level, and yet again when the space usage falls below the critical level.

Answer: BC

NEW QUESTION 18
Which three statements are true about using Real-Time Database Operations? (Choose three.)

• A. The STATISTICS_LEVEL initialization parameter must be set to ALL to enable automatic SQL monitoring for all long-running queries.
• B. The CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter must be set to DIAGNOSTIC+TUNUNG to use Real-Time Database Operations.
• C. The STATISTICS_LEVEL initialization parameter can be set to TYPICAL or ALL to enable Real-Time Database Operations.
• D. Real-Time Database Operations can be enabled only at the system level.
• E. Real-Time Database Operations can be created by using the DBMS_MONITOR or DBMS_SESSION packages.
• F. Database operation monitoring starts automatically when a database operation consumes at least five seconds of the CPU or I/O time in a single execution.

Answer: BCF