Free Oracle 1z0-064 Exam Dumps Questions & Answers

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

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

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.  PGA size should be increased at least four times its current size for significant improvement in performance and disk space management.
• C.  The current PGA size is sufficient and does not require the memory manager to allocate more memory.
• D.  The current PGA size requires the use of a temporary tablespace for sorting operations.

You are administrating a database on a symmetric multiprocessing (SMP) machines. The database supports a mixed workload. You notice a large number of free buffer waits and buffer busy waits that are affecting the performance of the database. To mitigate the issue, you increase the buffer cache size, but after some time, you notice the same wait events with a high number.
Examine the parameters set for the instance:

Which two actions would you recommend to resolve the issue?

• A.  enabling asynchronous I/O for the file system
• B.  increasing the value of the DB_FILE_MULTIBLOCK_READ_COUNT parameter
• C.  decreasing the idle time resource limit in the profile assigned to users
• D.  increasing the number of DBWn processes
• E.  configuring the KEEP buffer pool

You have been asked to assess if using column store compression (previously known as hybrid columnar compression or HCC) would help improve the performance of queries on some large tables.
Which three aspects should you consider before you choose this compression method? (Choose three.)

• A.  Check whether the table has LOB columns as it will minimize I/O for the queries.
• B.  Check whether the table blocks are sparsely populated as this will defragment the blocks.
• C.  Check whether direct path load operations are used to insert rows in the table.
• D.  Check whether the table is frequently updated because it will have overhead for insert and update operations.
• E.  Check whether the table is frequently queried using full table scans as column store compression only minimizes I/O during full table scans.