Online Access Free 1z0-064 Practice Test

Examine the structure of the EMPLOYEES table.

EMPLOYEE_ID is the primary key. No other indexes exist on this table.
View the Exhibit to examine the commands and their output.

Which two actions should you perform to make the cursor bind aware? (Choose two.)

• A.Gather statistics for the index.
• B.Change the default CURSOR_SHARING value to FORCE.
• C.Regather statistics on the table.
• D.Create an index on the DEPARTMENT_ID column.
• E.Create a histogram on the DEPARTMENT_ID column.
• F.Execute the query with the same DEPARTMENT_ID value multiple times.

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 histogram on the COUNTRY_ID column
• B.creating a SQL profile
• C.creating a bitmap index on the COUNTRY_ID column
• D.increasing the size of the PGA
• E.creating a KEEP cache
• F.regathering statistics on the CUSTOMERS table

Which two actions can reduce library cache latch contention for an OLTP application that repeatedly executes queries containing a mix of literals and bind variables? (Choose two.)

• A.configuring shared server connections
• B.avoiding the granting of privileges on objects associated with cursors during peak load
• C.setting the OPEN_CURSORS parameter to hold a sufficient number of concurrently open cursors
• D.coding the application such that a frequently executed statement is parsed only once and executed repeatedly as required
• E.setting the CURSOR_SHARING parameter to EXACT
• F.enabling Automatic Memory Management and allocating at least 50% of the available memory for SHARED_POOL_SIZE

Queries on the SALES table frequently use the PROD_ID and COST_ID columns together in the WHERE clause. Examine the command:

Which three statements are true about executing this command?

• A.It improves the cardinality estimates when both the PROD_ID and CUST_ID columns are used in the WHERE clause.
• B.It gathers individual column statistics for the PROD_ID and CUST_ID columns.
• C.It improves the selectivity estimates of a single predicate in the WHERE clause.
• D.It creates a virtual column for the column group (PROD_ID and CUST_ID).
• E.It creates extended statistics for the column group (PROD_ID and CUST_ID).

Examine the parameters set for a database instance:

The database supports a DSS application that performs huge sort operations on large temporary tables with LOB columns. Schema objects used in queries are updated and new rows are inserted regularly. Users complain about the poor performance of a few queries. During investigation, you execute these queries:


Which two actions would you recommend to improve the performance of these queries?

• A.Enable Automatic Segment Space Management for the temporary tablespace.
• B.Increase the value of the PGA_AGGREGATE_TARGET initialization parameter.
• C.Enable Automatic Memory Management for the instance.
• D.Enable Automatic Shared Memory Management for the instance.
• E.Alter the temporary tablespace to increase the extent size.