NEW QUESTION 1
Which two situations can lead to sparsely populated index blocks? (Choose two.)

• A. Data is frequently inserted using direct path load into a table with an index.
• B. Indexed columns in a table are frequently updated.
• C. Values in an indexed column are inserted using monotonically incrementing sequences.
• D. Bulk delete operations are performed on a table with indexes.
• E. Online table move operations are performed frequently on a table with indexes.

Answer: BD

NEW QUESTION 2
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 3
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 4
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 5
Which two statements are true about ADDM? (Choose two.)

• A. It analyzes the performance of a database instance based on the time period covered by the most recent AWR snapshot, and generates recommendations based on hard-coded criteria.
• B. It can analyze performance issues that occurred in past events provided they fall within the AWR retention period.
• C. ADDM resource utilization and cost of analysis depends on the actual load on the database and the number of performance problems analyzed.
• D. It first identifies the performance symptoms, and then refines them to reach the root cause with the singular aim of reducing the DB CPU metric.
• E. It documents only those components and wait classes that are significantly impacting the performance of the database.

Answer: AB

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

Answer: ABC

NEW QUESTION 7
You want to capture the performance of your database during the last ten days of the first quarter of the current financial year, so that you can compare this performance against the remaining quarter ends of the current financial year.
Which method should you use? (Choose the best answer.)

• A. Create a static baseline that can be used with AWR compare reports.
• B. Create a new moving window baseline and enable adaptive thresholds for relevant metrics.
• C. Use a repeating baseline template to create and drop baselines based on a repeating time schedule and set adaptive thresholds at a high significance level.
• D. Use fixed baseline templates to create a new moving window baseline and set relevant warning alerts that are computed as a percentage multiple of the maximum value observed for the data in the moving window baseline.

Answer: D

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

Answer: A

Explanation: Reference: http://www.dba-oracle.com/oracle_tips_cost_adj.htm

NEW QUESTION 9
Examine the parameters set for a database instance:

The database supports a mixed workload. Users complain about the increased response time of a few DSS queries. During investigation, you execute the query:

Based on the output, which two are possible ways to improve the performance of the queries? (Choose two.)

• A. Enable temporary undo.
• B. Enable Automatic Memory Management.
• C. Increase the number of DBWn processes.
• D. Enable Automatic Shared Memory Management.
• E. Increase the value of the SGA_TARGET parameter.
• F. Increase the value of the PGA_AGGREGATE_TARGET parameter.

Answer: CE

NEW QUESTION 10
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 11
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 12
Examine the parameters:

Your database supports a mixed workload and users have dedicated server connections. Users complain about the increased response time of a few queries that are performing large sort operations. On investigation, you notice an increase in the number of multipass work area executions and high number of direct path write wait events.
Which two actions could improve the performance? (Choose two.)

• A. increasing the value of the SORT_AREA_SIZE parameter
• B. increasing the value of the PGA_AGGREGATE_TARGET parameter
• C. enabling Automatic Memory Management for the instance
• D. increasing the size of the default temporary tablespace
• E. using parallel hint in queries performing large sort operations
• F. enabling Automatic Shared Memory Management for the instance

Answer: AF

NEW QUESTION 13
You observe that queries are performing poorly on the SALES_RECORDS table in your database. On investigation, you find that at the end of each day the contents of the SALES_RECORDS table are moved to the SALES_HISTORY table. The delete operations cause the table to be sparsely populated.
The SALES_RECORDS table is created in a tablespace using Automatic Segment Space Management (ASSM) and row movement is enabled. The table must be accessible 24x7.
Which two tasks would you recommend to improve the performance? (Choose two.)

• A. Perform EXPORT, DROP, and IMPORT operations on the SALES_RECORDS table.
• B. Shrink the SALES_RECORDS table by using the ALTER TABLE…SHRINK SPACE command.
• C. Move the SALES_RECORDS table to a different location by using the ALTER TABLE…MOVE command.
• D. Deallocate the space in the SALES_RECORDS table by using the ALTER TABLE…DEALLOCATE UNUSED command.
• E. Move the SALES_RECORDS table to a tablespace by using manual segment space management.
• F. Reorganize the SALES_RECORDS table online by using the DBMS_REDEFINITION package.

Answer: BD

NEW QUESTION 14
You plan to upgrade your production database from Oracle Database 11g to 12c and also to introduce new objects to the database. You also want to upgrade the hardware. You have already created a test system with the upgrades to be made to the production database. As part of the testing, you want to:
✑ analyze and compare the overall database workload with concurrency and transaction characteristics
✑ find SQL statements that might get regressed because of the upgrade
✑ analyze execution plans for SQL statements for which performance might get regressed
✑ analyze the impact of new schema objects on database performance
Which two tools would you recommend to achieve the objective? (Choose two.)

• A. Database Replay
• B. SQL Tuning Advisor
• C. SQL Access Advisor
• D. Automatic Database Diagnostic Monitor (ADDM) compare periods report
• E. SQL Performance Analyzer
• F. Automatic Workload Repository (AWR) compare periods report

Answer: BE

NEW QUESTION 15
You are administering a database that supports a mixed workload. The CURSOR_SHARING parameter is set to the default value. While analyzing the latest Automatic Workload Repository (AWR) report, you find a large number of cursor: pin S wait on X, cursor: pin X wait on S, and library cache mutex waits in the Top 10 foreground events section. Examine the Instance Efficiency Percentages section in the AWR report:

Which three statements are true in this scenario? (Choose three.)

• A. Sessions are waiting for mutexes in share mode on cursors but other sessions areholding the mutexes in exclusive mode.
• B. The CPU is spending more time in finding cursors in the library cache.
• C. Cursors are not getting shared, resulting in a large number of hard parses.
• D. Sessions are waiting for mutexes in exclusive mode on cursors but other sessions are holding the mutexes in share mode.
• E. The buffers required by queries are not found in the buffer cache, thereby increasing expensive disk I/O.

Answer: BDE

NEW QUESTION 16
Examine the query and its output:

Which two statements are true? (Choose two.)

• A. Session 2832 had to wait 2029 seconds for a message to arrive because of a network bottleneck.
• B. Session 4208 is either idle or experiencing poor response time due to a network or resource bottleneck on the client process.
• C. Session 3346 is in wait state because it wants to lock a row in a block in which other sessions have already locked rows, and there is no free ITL slot available in this block.
• D. Session 3346 is in wait state because either it is waiting to update a row that is locked by another session or another session is trying to insert the same key value in a UNIQUE index.
• E. Session 4208 is definitely idle and should be killed to free network resources.

Answer: AD