Exam Code: 1Z0-064 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database 12c: Performance Management and Tuning
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-064 Exam.
2021 Jul 1Z0-064 test question
Q31. You observe that queries are performing poorly on the SALES_RECORDS table in your database. On investigation, you find that at the end of each day the contents of the SALES_RECORDS table are moved to the SALES_HISTORY table. The delete operations cause the table to be sparsely populated.
The SALES_RECORDS table is created in a tablespace using Automatic Segment Space Management (ASSM) and row movement is enabled. The table must be accessible 24x7.
Which two tasks would you recommend to improve the performance? (Choose two.)
A. Perform EXPORT, DROP, and IMPORT operations on the SALES_RECORDS table.
B. Shrink the SALES_RECORDS table by using the ALTER TABLE…SHRINK SPACE command.
C. Move the SALES_RECORDS table to a different location by using the ALTER TABLE…MOVE command.
D. Deallocate the space in the SALES_RECORDS table by using the ALTER TABLE…DEALLOCATE UNUSED command.
E. Move the SALES_RECORDS table to a tablespace by using manual segment space management.
F. Reorganize the SALES_RECORDS table online by using the DBMS_REDEFINITION package.
Q32. You are administering a database that supports an OLTP workload. An application regularly creates global temporary tables and a large number of transactions are performed on them. You notice that performance is degraded because of excessive generation of undo due to a large number of transactions on the global temporary tables.
What is the recommended action to improve performance? (Choose the best answer.)
A. Increase the size of the undo tablespace and enable undo retention guarantee.
B. Increase the size of the database buffer cache.
C. Enable temporary undo.
D. Increase the size of the temporary tablespace or make it autoextensible.
E. Enable Automatic Segment Space Management (ASSM) for the undo tablespace.
Q33. 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. Run the I/O calibration tool.
B. Configure the database instance to make asynchronous I/O available to DBWR.
C. Spread the data files over multiple disks, controllers, and I/O buses to ensure that there are no hotspots in the I/O subsystem.
D. Configure dedicated server connections for the applications.
Q34. Which two statements are true about DB time in V$SYS_TIME_MODEL? (Choose two.)
A. DB time cannot exceed the total elapsed time (walk clock time) since the database instance started.
B. DB time cannot exceed the maximum number of concurrent sessions multiplied by the actual elapsed time for each session.
C. DB time includes the time spent on client processes and background processes.
D. Reducing DB time allows a database instance to support more user requests by using the same resources.
E. DB time is always greater than or equal to the DB CPU time.
Q35. You are administering a database that supports an OLTP workload. RESULT_CACHE_MODE is set to the default value and a result cache is configured for the instance. Multiple sessions execute syntactically similar queries without dblinks, containing functions and expressions, on tables with no DML activity. Some users complain about poor performance of these queries.
You investigate and find that the queries are frequently performing physical I/O, even though the results fetched by the queries are similar.
Which two actions do you recommend to overcome the problem affecting these queries? (Choose two.)
A. Set the RESULT_CAHCE_MODE parameter to FORCE for the instance.
B. Use the result cache hint in the queries.
C. Use bind variables for similar queries instead of literals.
D. Set the RESULT_CACHE_REMOTE_EXPIRATION parameter to a nonzero value.
E. Configure the KEEP pool and cache the queried tables used in the KEEP pool.
Renewal 1Z0-064 answers:
Q36. You are administering a database that supports an OLTP workload. CURSOR_SHARING is set to EXACT for the instance. An application is frequently executing almost identical queries that vary in literal values in the WHERE clause, causing a large number of hard parses to occur.
Which four statements would be true if you use bind variables for these queries? (Choose four.)
A. Mutex contention in the library cache will be reduced.
B. The optimizer will use one parent cursor and one child cursor for each SQL statement with different literal values.
C. Hard parses will be reduced for the queries.
D. The optimizer will use bind peeking and subsequent execution of the queries will always generate the same plans irrespective of the cardinality.
E. The optimizer will generate the same plan for all bind values if no histograms exist on the columns used in the WHERE clause of these queries.
F. The optimizer will use bind peeking and use the literal value to determine the execution plan for these queries.
Q37. Examine the parameters set for a database instance supporting a mixed workload:
The database instance supports shared server and dedicated server connections simultaneously. Users complain about increased response times of a few DSS queries. During investigation, you execute the queries:
Based on the output, which two courses of action would you recommend to improve query performance? (Choose two.)
A. Use a parallel hint in the queries.
B. Increase the number of DBWn processes.
C. Increase the value of the SORT_AREA_SIZE initialization parameter.
D. Increase the size of the temporary tablespace or add a new temporary tablespace.
E. Increase the value of the PGA_AGGREGATE_TARGET initialization parameter.
F. Increase the size of the large pool.
Q38. 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. Row moves from one data block to another data block because the row grows too large to fit in the original block.
C. The table is subject to frequent insert, update, and delete DML activity leading to sparsely populated blocks.
D. The value of PCTUSED is set to a value lower than the default, causing row changing.
E. The value of PCTFREE is set to a value lower than the default, causing row chaining.
Q39. You want to capture AWR data to monitor performance variation every Monday between
9:00 AM and 12:00 PM for three months and automatically remove the older AWR data every fortnight.
How would you achieve this? (Choose the best answer.)
A. Create AWR baselines.
B. Create SQL plan baselines.
C. Create repeating baseline templates.
D. Create database services and make sure that user connections use them to connect to the database instance.
E. Create a single baseline template.
Q40. Examine the Time Model Statistics section of an AWR report:
Which two inferences can be definitely derived from this section? (Choose two.)
A. The available CPU resources were not utilized to their maximum capacity.
B. All sequence numbers used during this AWR time interval were cached.
C. A large number of connected user sessions were idle.
D. New child cursors were created because of new bind values or usage of literal values as well as different bind types or sizes.
E. The DB CPU time was not spent exclusively for processing SQL statements.