You want to capture AWR data to monitor performance variation every Monday between 9:00 AM and

12:00 PM for three months and automatically remove the older AWR data every fortnight.

How would you achieve this? (Choose the best answer.)

A. Create AWR baselines.

B. Create SQL plan baselines.

C. Create repeating baseline templates.

D. Create database services and make sure that user connections use them to connect to the database instance.

E. Create a single baseline template.

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. All sequence numbers used during this AWR time interval were cached.

C. A large number of connected user sessions were idle.

D. New child cursors were created because of new bind values or usage of literal values as well as different bind types or sizes.

E. The DB CPU time was not spent exclusively for processing SQL statements.

Examine the parameters set for your database instance:

You are asked by a developer to create a table for an application with these requirements:

The table will be used for a DSS application.

High volume bulk loads will be performed.

The table will be used to store archival data on which large full-table scans (FTS) will be performed.

Which attributes are the best for the tablespace in which this table should be created? (Choose the best

answer.)

A. Create it in a locally managed tablespace with ASSM enabled and assign a high value for the PCTFREE attribute.

B. Create it in a locally managed tablespace with manual segment space management.

C. Create it in a locally managed tablespace with a bigger nonstandard block size and ASSM enabled.

D. Create it in locally managed tablespace with ASSM enabled and an additional freelist.

Which two statements are true about Compare Period ADDM? (Choose two.)

A. It is automatically invoked whenever the AWR Compare Period report is invoked.

B. It is automatically invoked whenever ADDM is run by default.

C. It verifies if there is any change in the workload or average resource consumption by the SQL executed during the two specified time periods, to ensure 100% accuracy.

D. It can be used to create a comparison report between the Database Replay workload capture report and the replay report.

Examine the parameters set for a database instance supporting a mixed workload:

The database instance supports shared server and dedicated server connections simultaneously. Users complain about increased response times of a few DSS queries. During investigation, you execute the queries:

Based on the output, which two courses of action would you recommend to improve query performance? (Choose two.)

A. Use a parallel hint in the queries.

B. Increase the number of DBWn processes.

C. Increase the value of the SORT_AREA_SIZE initialization parameter.

D. Increase the size of the temporary tablespace or add a new temporary tablespace.

E. Increase the value of the PGA_AGGREGATE_TARGET initialization parameter.

F. Increase the size of the large pool.

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.

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.

The CUSTOMERS table has 55,500 rows and 620 distinct values in the CUST_CITY_ID column. The number of popular values is 54 and less than 99% of the rows contain popular values.

Which type of histogram should you create to accurately determine the cardinality estimate on the CUST_CITY_ID column?

A. high-frequency histogram

B. height-balanced histogram

C. hybrid histogram

D. frequency histogram

Examine the parameters set for your database instance:

You are administrating a database that supports a DSS workload. You make some changes to the sizes of memory components. Consequently, you receive this error:

Which three actions might resolve the issue?

A. pinning the cursors in the shared pool

B. setting the PGA_AGGREGATE_TARGET parameter to 0 and the MEMORY_TARGET parameter to the value of the PGA_AGGREGATE_TARGET parameter

C. enabling Automatic Shared Memory Management for the database instance

D. setting the value of MEMORY_MAX_TARGET to the value of the SGA_MAX_SIZE parameter

E. increasing the size of the shared pool

F. enabling Automatic Memory Management for the database instance

Your database supports an OLTP system.

Examine the parameter values configured in your database:

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 a series of INSERT, UPDATE, and DELETE operations on 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 a SQL profile

F. creating a KEEP cache