Oracle 1z0-064 問題集

質問 1：
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. regathering statistics on the CUSTOMERS table
B. increasing the size of the PGA
C. creating a SQL profile
D. creating a KEEP cache
E. creating a histogram on the COUNTRY_ID column
F. creating a bitmap index on the COUNTRY_ID column
正解：A,B,E

質問 2：
Identify two effects of the DB_FILE_MULTIBLOCK_READ_COUNT parameter on the optimizer. (Choose two.)
A. 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.
B. 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.
C. 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.
D. 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.
E. Decreasing the value of DB_FILE_MULTIBLOCK_READ_COUNT from the default increases the cost of index probes for DSS workloads.
正解：C,D

質問 3：
Examine the output of the query executed to diagnose the reason for performance degradation of queries:

Which three factors will you investigate further to identify the cause of the performance degradation? (Choose three.)
A. Check the indexes on the tables used in queries for clustering factor.
B. Check the number of chained or migrated rows.
C. Check the number of disk sorts.
D. Check for the causes of the full table scans.
E. Check the size of the temporary tablespace for sorting operations.
正解：B,C,D

質問 4：
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. Spread the data files over multiple disks, controllers, and I/O buses to ensure that there are no hotspots in the I/O subsystem.
B. Configure dedicated server connections for the applications.
C. Configure the database instance to make asynchronous I/O available to DBWR.
D. Run the I/O calibration tool.
正解：C

質問 5：
In which three situations does DB time always increase? (Choose three.)
A. when wait time for data to be sent over a network increases
B. when I/O wait time increases for foreground processes
C. when the host is CPU bound for foreground processes
D. when more connections are made to a database instance
E. when CPU consumption by background processes increases
正解：B,D,E

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