Oracle 1Z0-064 Study Guides 2021

NEW QUESTION 1
Which two statements are true about server-generated alerts? (Choose two.)

• A. They are always logged in the alert log.
• B. They are written to a trace file if the TRACE_ENABLED initialization parameter is set toTRUE.
• C. They are generated only when the STATISTICS_LEVEL initialization parameter is set to ALL.
• D. They can be generated for user-defined metric thresholds.
• E. They appear in the DBA_ALERT_HISTORY view whenever corrective action is taken for an alert.

Answer: DE

NEW QUESTION 2
You are administering a database that supports an OLTP workload. An application performs a large number of small transactions. Users complain about increased response times for transactions. On investigation, you find that the cache hit ratio is 69%. Examine a partial output from V$SYSTEM_EVENT:

Which four can be possible reasons for the increased response time? (Choose four.)

• A. The database buffer cache is inadequately sized.
• B. DBWR is not writing the dirty buffers fast enough.
• C. A large number of blocks are fetched from disks frequently.
• D. Several full table scans are performed by transactions.
• E. Blocks are aging out of the buffer cache frequently.
• F. Many sessions are waiting for buffers that are currently being read into the buffer cache by other sessions.

Answer: ABEF

NEW QUESTION 3
A senior DBA asks you to decrease the values of the connect_time_scale and think_time_scale replay processing parameters to 50 to preprocess the workload for replay.
What three could be reasons for this change? (Choose three.)

• A. to reduce the elapsed time between two successive user calls from a session.
• B. to decrease the number of concurrent users during replay
• C. to increase the number of concurrent users during replay
• D. to reduce the time of replay
• E. to decrease the wait for a query, caused by noncommitted transactions

Answer: CDE

NEW QUESTION 4
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.

Answer: BD

NEW QUESTION 5
You define the warning threshold for the tablespace usage metric for the USERS tablespace to be 60% and the critical threshold to be 80%.
Which two sources should you check for the alert information when either the warning or the critical threshold is exceeded? (Choose two.)

• A. the alert log
• B. Oracle Enterprise Manager Cloud Control
• C. DBA_ALERT_HISTORY
• D. DBA_OUTSTANDING_ALERTS
• E. DBA_ACTIVE_SESSION_HISTORY
• F. DBA_THRESHOLDS

Answer: AF

NEW QUESTION 6
Examine the Load Profile section of an AWR report:

Which two inferences can you derive from the details in this section? (Choose two.)

• A. The values for Redo size and Block changes imply that only updates were performed by transactions.
• B. The values for Parses (SQL) and Hard parses (SQL) imply that cursor sharing occurred quite often.
• C. The values for DB Time and DB CPU imply that the database had a high proportion of idle time during the specified snapshot interval.
• D. The values for SQL Work Area and User calls imply that only sort-based operations were performed.
• E. The values for Logical reads and Physical reads imply that the number of disk reads per second was less than the total number of DB block reads and consistent gets per second.

Answer: BD

NEW QUESTION 7
You execute this query twice in a session:

Then you query V$SQL_SHARED_CURSOR for details about child cursors as shown.

Which two statements are true? (Choose two.)

• A. No statistics were collected during the first execution of the query.
• B. A subsequent execution of the query in this session is likely to undergo a soft parse.
• C. The second execution of the query was hard parsed because the estimated cardinality was inaccurate.
• D. A subsequent execution of the query in this session will undergo a hard parse.
• E. The second execution of the query was hard parsed because extended statistics were collected after the first execution of the query.

Answer: BC

NEW QUESTION 8
Which two situations can lead to sparsely populated index blocks? (Choose two.)

• A. Data is frequently inserted using direct path load into a table with an index.
• B. Indexed columns in a table are frequently updated.
• C. Values in an indexed column are inserted using monotonically incrementing sequences.
• D. Bulk delete operations are performed on a table with indexes.
• E. Online table move operations are performed frequently on a table with indexes.

Answer: BD

NEW QUESTION 9
Examine this list of possible tasks:
1. Ensure that STATISTICS_LEVEL is set to TYPICAL or ALL.
2. Ensure that TIMED_STATISTICS is set to TRUE.
3. Set MAX_DUMP_FILE_SIZE to UNLIMITED and DIAGNOSTIC_DEST to an appropriate destination.
4. Ensure that SQL_TRACE is set to TRUE.
5. Enable tracing at the database instance level by using the
DBMS_MONITOR.DATABASE_TRACE_ENABLE procedure.
6. Enable tracing in the required session by using the DBMS_SESSION.SET_SQL_TRACE procedure.
7. Run TKPROF with the EXPLAIN parameter on the output trace file.
8. Run the trcsess utility on the output trace files, and then run TKPROF on the output of the trcsess utility.
Select the minimum tasks to perform, in the correct order, to generate both a formatted trace file with timing information and an explain plan for each SQL statement for all sessions. (Choose the best answer.)

• A. 1, 2, 5, 8
• B. 1, 3, 6, 7
• C. 2, 4, 5, 8
• D. 1, 3, 4, 5, 6, 7
• E. 1, 2, 4, 8

Answer: C

NEW QUESTION 10
Which two statements are true about the interpretation of Buffer Cache Hit Ratio in the Instance Efficiency Percentages section of an AWR report? (Choose two.)

• A. A high value indicates that the buffer cache is adequately sized for the current workload.
• B. Poor hit ratios indicate that a large number of indexed lookups or small table scans are being performed.
• C. A low hit ratio does not necessarily imply that increasing the size of the buffer cache will improve performance.
• D. A high hit ratio may indicate that repeated scanning of the same large table or index is being performed.
• E. A low hit ratio indicates that a KEEP buffer pool should be configured based on the size of the largest object accessed in the buffer cache.

Answer: CD

NEW QUESTION 11
Examine the parameters set for a database instance:

An application performs a large number of identical queries on small lookup tables very frequently. Users complain about the slow response time of queries on these tables. On investigation, you notice that buffers are getting aged out of the buffer cache. To mitigate the issue, you increase the value of the SGA_MAX_SIZE and SGA_TARGET parameters, but after some time, you notice the same issue again.
Which two would you recommend as long-term solutions for this issue? (Choose two.)

• A. increasing the size of the database buffer cache
• B. configuring Automatic Memory Management
• C. configuring the KEEP buffer pool and altering tables to use the KEEP pool
• D. pinning the cursors of the queries in the library cache
• E. configuring the result cache for the instance

Answer: AB

NEW QUESTION 12
You recently joined a new team administering a database.
You notice that full table scans are performing poorly compared with full table scans on the databases you administered in a previous job.
You decide that performance problems are caused by a misconfiguration of factors affecting full table scans.
Which three factors should you investigate to determine the cause of the poorly performing Full Table Scans (FTS)? (Choose three.)

• A. value of DB_FILE_MULTIBLOCK_READ_COUNT
• B. storing query results in the result cache
• C. setting of the DISK_ASYNC_IO parameter to TRUE
• D. setting of the OPTIMIZER_MODE parameter to ALL_ROWS
• E. use of parallel queries
• F. block size of the tablespaces in which the tables being scanned are stored
• G. value of the OPTIMIZER_DYNAMIC_SAMPLING parameter

Answer: ABC

NEW QUESTION 13
You want to generate statistics for new objects added to an OLTP application without affecting old statistics and the sessions that currently use them.
Which two tasks would you perform to test the new statistics? (Choose two.)

• A. Set the OPTIMIZER_USE_PENDING_STATISTICS initialization parameter to TRUE for the session.
• B. Set the STALE_PERCENT statistics preference to zero and then gather statistics.
• C. Set the PUBLISH statistics preference to FALSE and then gather statistics.
• D. Use the DBMS_STATS.PUBLISH_PENDING_STATS procedure to make pending statistics the current statistics.
• E. Set the NO_INVALIDATE statistics preference to FALSE and gather statistics without affecting old statistics.

Answer: AB

NEW QUESTION 14
Which two actions can reduce library cache latch contention for an OLTP application that repeatedly executes queries containing a mix of literals and bind variables? (Choose two.)

• A. setting the OPEN_CURSORS parameter to hold a sufficient number of concurrently open cursors
• B. coding the application such that a frequently executed statement is parsed only onceand executed repeatedly as required
• C. setting the CURSOR_SHARING parameter to EXACT
• D. avoiding the granting of privileges on objects associated with cursors during peak load
• E. enabling Automatic Memory Management and allocating at least 50% of the available memory for SHARED_POOL_SIZE
• F. configuring shared server connections

Answer: BE

Explanation: Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28274/memory.htm

NEW QUESTION 15
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.

Answer: DE

NEW QUESTION 16
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. ADDM runs in Partial mode to analyze any hung database issues.
• C. Real-Time ADDM can proactively detect and diagnose transient performance issues that last for a few seconds.
• D. Real-Time ADDM is automatically invoked by ADDM at the end of every hour.

Answer: AC