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

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 chaining.

E. The value of PCTFREE is set to a value lower than the default, causing row chaining.

In your database, supporting an OLTP workload, the SALES table is range-partitioned on the SALES_DATE column. A new partition is created every quarter and is frequently used for updates and queries. You recently noticed degraded performance of queries on a new partition due to stale statistics. A local partitioned index exists on the SALES_DATE column.

What would you recommend to improve performance?

A. dropping the local partitioned index and creating a global partitioned index on the SALES_DATE column

B. setting the INCREMENTAL preference to TRUE and the NO_INVALIDATE preference to TRUE for the partitioned table while gathering statistics

C. setting the INCREMENTAL preference to TRUE and the CASCADE preference to FALSE for the partitioned table while gathering statistics

D. setting the INCREMENTAL preference to TRUE and the GRANULARITY preference to AUTO for the partitioned table while gathering statistics

Examine the Load Profile and partial Top 10 Foreground Events by Total Wait Time sections from an AWR report.

Which two areas should you examine next to identify possible bottlenecks?

A. the application code because of CPU-intensive activities

B. the application code because user calls are performing several queries that require sorting

C. the "SQL ordered by Gets" section of the AWR report to check for excessive logical I/O

D. the "SQL ordered by Reads" section of the AWR report to check for excessive physical reads