Online Access Free 1z0-064 Practice Test

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 bitmap indexes as the estimated cost of conversion from bitmap to rowid is reduced.
• B.It influences the optimizer to use indexes instead of full table scans as the estimated cost of using an index 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 full table scans instead of index scans as the estimated cost of full table scan is reduced.

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

Your database supports a mixed workload. In an application, multiple complex queries with functions and expressions are executing. You want to analyze the queries that are currently cached in the library cache to receive recommendations about the usage of indexes and materialized views.
What should you do to achieve this? (Choose the best answer.)

• A.Create an STS for the queries cached in the library cache and submit it as an input to SQL Tuning Advisor.
• B.Create an STS for the queries cached in the library cache and submit it as an input to SQL Access Advisor.
• C.Capture the workload in an STS and submit to SQL Tuning Advisor for recommendations.
• D.Create an STS for the queries cached in the library cache and submit it as an input to SQL Performance Analyzer.

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.The table is subject to frequent insert, update, and delete DML activity leading to sparsely populated blocks.
• B.The value of PCTUSED is set to a value lower than the default, causing row chaining.
• C.The value of PCTFREE is set to a value lower than the default, causing row chaining.
• D.Row size is too large to fit into a single block during insert operations.
• E.Row moves from one data block to another data block because the row grows too large to fit in the original block.

Identify two effects of the DB_FILE_MULTIBLOCK_READ_COUNT parameter on the optimizer. (Choose two.)

• A.Increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT within OS limits lowers the cost of full table scans and can result in the optimizer choosing a full table scan over an index scan for both OLTP and DSS workloads.
• B.In DSS workloads where full table scans may run in parallel and bypass the buffer cache, decreasing the value of DB_FILE_MULTIBLOCK_READ_COUNT from the default increases the cost of full table scans.
• C.A full table scan can become cheaper than index scans if the database instance has a high enough DB_FILE MULTIBLOCK_READ_COUNT for both OLTP and DSS workloads.
• D.Decreasing the value of DB_FILE_MULTIBLOCK_READ_COUNT from the default increases the cost of index probes for DSS workloads.
• E.Increasing the value of DB_FILE_MULTIBLOCK_READ_COUNT within OS limits lowers the costing of an index probe that is done in conjunction with a nested loop for OLTP workloads.