Free Oracle 1z0-064 Exam Dumps Questions & Answers

Your database supports an OLTP system.
Examine the parameter values configured in your database:
sga_max_size = 480M
sga_target = 480M
pga_aggregate_target = 160M
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 two options can improve the performance of the query without significantly slowing down the DML operations? (Choose two.)

• A.increasing the size of the PGA
• B.regathering statistics on the CUSTOMERS table
• C.creating a SQL profile
• D.creating a histogram on the COUNTRY_ID column
• E.creating a bitmap index on the COUNTRY_ID column
• F.gathering statistics on the COUNTRY_IDX index
• G.creating a KEEP cache

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 Performance Analyzer.
• 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 Access Advisor.

The SALES table has over one million rows. Statistics for the SALES table and its dependent objects are gathered by the Oracle AutoTask task during a predefined maintenance window.
Examine the query:

Which two actions can improve the accuracy of cardinality estimates for this query?

• A.ensuring that histograms exist for the CUST_ID and PROD_ID columns
• B.setting the OPTOMIZER_DYNAMIC_SAMPLING parameter to 4 to augment the default standard statistics
• C.gathering system statistics during peak workload time to augment the standard statistics
• D.setting the FORCE parameter of the GATHER_*_STATS procedure to TRUE and gathering statistics for the SALES table and its dependent objects

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.Chick whether the indexes have only a few entries per block.
• D.Check the size of the large pool.
• E.Check the size of the shared pool.

Which two statements are true about ADDM or Real-Time ADDM? (Choose two.)

• A.ADDM can be run manually by selecting any range of AWR snapshots available within the AWR retention period, provided they do not cover a time period when the instances were restarted.
• B.Real-Time ADDM can proactively detect and diagnose transient performance issues that last for a few seconds.
• C.Real-Time ADDM is automatically invoked by ADDM at the end of every hour.
• D.ADDM runs in Partial mode to analyze any hung database issues.