1Z0-064 Online Practice Questions and Answers

Examine the partial TKPROF output for an SQL statement: Which two inferences can definitely be made from this output? (Choose two.)

A. Array fetch operations were not performed for this query.

B. No hard parse was performed for this query.

C. The number of logical I/Os is almost equal to the number of physical I/Os.

D. Another transaction held a shared lock on the table, thereby causing a significant delay.

E. An uncommitted transaction made a series of updates to the NAME_ID column just before the execution of this query.

Which two result in the least internal fragmentation within segments and the least external fragmentation within tablespaces? (Choose two.)

A. automatic segment space managed segments

B. locally managed tablespaces with uniform extent size

C. freelist managed segments with one freelist

D. dictionary managed tablespaces

E. locally managed tablespaces that were converted from dictionary managed tablespaces

F. freelist managed segments with multiple freelists

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.

Examine the initialization parameters set for a database instance:

The database supports an OLTP workload. Applications connect to the instance using shared server connections and perform small, random I/Os. All the data files are on the same disk. You notice free buffer wait events for sessions in the database instance.

To solve the problem, you increase the size of the buffer cache. But after some time, you notice sessions waiting again on free buffer waits.

What will you recommend to alleviate the issue? (Choose the best answer.)

A. Run the I/O calibration tool.

B. Configure the database instance to make asynchronous I/O available to DBWR.

C. Spread the data files over multiple disks, controllers, and I/O buses to ensure that there are no hotspots in the I/O subsystem.

D. Configure dedicated server connections for the applications.

You are administering a database that supports an OLTP workload. CURSOR_SHARING is set to EXACT for the instance. An application is frequently executing almost identical queries that vary in literal values in the WHERE clause, causing a large number of hard parses to occur.

Which four statements would be true if you use bind variables for these queries? (Choose four.)

A. Mutex contention in the library cache will be reduced.

B. The optimizer will use one parent cursor and one child cursor for each SQL statement with different literal values.

C. Hard parses will be reduced for the queries.

D. The optimizer will use bind peeking and subsequent execution of the queries will always generate the same plans irrespective of the cardinality.

E. The optimizer will generate the same plan for all bind values if no histograms exist on the columns used in the WHERE clause of these queries.

F. The optimizer will use bind peeking and use the literal value to determine the execution plan for these queries.

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.

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 a relative priority by assigning them to application users and using the DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE procedure to trace the services.

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

C. Create services for the applications and set the relative priority of services within an instance by mapping the services directly to consumer groups.

D. Create services for the applications and set a relative priority by assigning them to application users.

Examine the parameters set for a database instance:

An application performs a large number of identical queries on small lookup tables very frequently. Users complain about the slow response time of queries on these tables. On investigation, you notice that buffers are getting aged out of the buffer cache. To mitigate the issue, you increase the value of the SGA_MAX_SIZE and SGA_TARGET parameters, but after some time, you notice the same issue again.

Which two would you recommend as long-term solutions for this issue? (Choose two.)

A. increasing the size of the database buffer cache

B. configuring Automatic Memory Management

C. configuring the KEEP buffer pool and altering tables to use the KEEP pool

D. pinning the cursors of the queries in the library cache

E. configuring the result cache for the instance

Your database supports an OLTP workload.

An application frequently performs update operations on an indexed column of a table; some bulk delete

and insert operations are also performed.

The table is created in a locally managed tablespace with manual segment space management.

A user complains about poor response time when querying the table.

The queries mostly perform index range scans.

You notice a high number of buffer busy waits and contention on segment headers.

Which three would you investigate further to diagnose the poor response time?

A. Check for freelist contention.

B. Check table blocks for migrated or chained rows.

C. Check the size of the shared pool.

D. Check the size of the large pool.

E. Chick whether the indexes have only a few entries per block.

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