Virtual 1Z0-064 Study Guides 2019
Exam Code: 1Z0-064 (), Exam Name: Oracle Database 12c: Performance Management and Tuning, Certification Provider: Oracle Certifitcation, Free Today! Guaranteed Training- Pass 1Z0-064 Exam.
Free demo questions for Oracle 1Z0-064 Exam Dumps Below:
NEW QUESTION 1
Which three actions should you perform to reduce shared pool fragmentation and avoid the “ORA-04031: unable to allocate .... bytes of shared memory” error for the shared pool? (Choose three.)
- A. Configure the Server Result Cache.
- B. Configure shared server mode.
- C. Identify the packages or procedures that are causing the “ORA-04031: unable to allocate .... bytes of shared memory” error and use the DBMS_SHARED_POOL.KEEP to keep them in the shared pool.
- D. Use DBMS_SHARED_POOL.KEEP to keep the SYS.STANDARD,SYS.DBMS_STANDARD, and SYS.DIUTIL packages and frequently executed compiled triggers.
- E. Use more anonymous procedures.
- F. Standardize the type, size, and naming conventions for bind variables and spacing conventions for SQL statements and PL/SQL blocks.
NEW QUESTION 2
Which two statements are true about Compare Period ADDM? (Choose two.)
- A. It is automatically invoked whenever the AWR Compare Period report is invoked.
- B. It is automatically invoked whenever ADDM is run by default.
- C. It verifies if there is any change in the workload or average resource consumption by the SQL executed during the two specified time periods, to ensure 100% accuracy.
- D. It can be used to create a comparison report between the Database Replay workload capture report and the replay report.
NEW QUESTION 3
You are administering a database that supports multiple applications, which make dedicated connections to the database instance by using different services.
You execute the command to enable tracing of the ORCL1 service:
Which two statements are true? (Choose two.)
- A. A single trace file is generated for all sessions mapped to the ORCL1 service.
- B. SQL trace is enabled for all modules and actions for sessions mapped to the ORCL1 service.
- C. An SQL trace file is generated for each session that maps to the ORCL1 service.
- D. An SQL trace file is generated for each of the modules using the ORCL1 service.
- E. SQL trace is not enabled for the service because a module name is not specified.
NEW QUESTION 4
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.
NEW QUESTION 5
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.
NEW QUESTION 6
Examine the partial AWR report taken for a time period of 60 minutes:
Which two inferences can you draw from this report? (Choose two.)
- A. The database user calls are issuing frequent explicit commits.
- B. The CPUs are busy executing server processes and background processes for a considerable amount of CPU time.
- C. The database user calls are spending most of their time in I/O for single block reads.
- D. The database user calls are spending most of their time waiting for sessions that are in more important consumer groups.
NEW QUESTION 7
Examine the query and its output:
Which two statements are true? (Choose two.)
- A. Session 2832 had to wait 2029 seconds for a message to arrive because of a network bottleneck.
- B. Session 4208 is either idle or experiencing poor response time due to a network or resource bottleneck on the client process.
- C. Session 3346 is in wait state because it wants to lock a row in a block in which other sessions have already locked rows, and there is no free ITL slot available in this block.
- D. Session 3346 is in wait state because either it is waiting to update a row that is locked by another session or another session is trying to insert the same key value in a UNIQUE index.
- E. Session 4208 is definitely idle and should be killed to free network resources.
NEW QUESTION 8
In which three situations does DB time always increase? (Choose three.)
- A. when the host is CPU bound for foreground processes
- B. when I/O wait time increases for foreground processes
- C. when more connections are made to a database instance
- D. when CPU consumption by background processes increases
- E. when wait time for data to be sent over a network increases
Explanation: Reference: http://www.oracle.com/technetwork/oem/db-mgmt/s317294-db-perf-tuning-with- db-time-181631.pdf (page 21)
NEW QUESTION 9
Examine the parameters set for a database instance:
The database supports a mixed workload. Users complain about the increased response time of a few DSS queries. During investigation, you execute the query:
Based on the output, which two are possible ways to improve the performance of the queries? (Choose two.)
- A. Enable temporary undo.
- B. Enable Automatic Memory Management.
- C. Increase the number of DBWn processes.
- D. Enable Automatic Shared Memory Management.
- E. Increase the value of the SGA_TARGET parameter.
- F. Increase the value of the PGA_AGGREGATE_TARGET parameter.
NEW QUESTION 10
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
Explanation: Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28274/memory.htm
NEW QUESTION 11
Examine the parameters set for your database instance:
You upgrade your database to Oracle Database 12c. The database supports a mixed workload and works with different workloads at different times. You notice in an ADDM report that the shared pool is inadequately sized. You resize the shared pool by decreasing the sizes of other pools, which results in inadequate sizes for other pools. You want to automate the sizing of SGA components.
Which two actions should you perform? (Choose two.)
- A. Set the SGA_TARGET parameter equal to SGA_MAX_SIZE.
- B. Set the SGA_TARGET parameter to the sum of DB_CACHE_SIZE,SHARED_POOL, and LARGE_POOL_SIZE.
- C. Set the MEMORY_MAX_TARGET parameter to the sum of DB_CACHE_SIZE,SHARED_POOL, and LARGE_POOL_SIZE.
- D. Set DB_CACHE_SIZE,SHARED_POOL, and LARGE_POOL_SIZE to their minimum required values.
- E. Set the PGA_AGGREGATE_TARGET parameter to 0 and the SGA_TARGET parameter to 1.5G.
NEW QUESTION 12
Examine the parameters set for your database instance:
You are administering a database that supports an OLTP workload. Users complain about the degraded performance of some queries. While diagnosing, you notice a large number of hard parses occurring for several syntactically almost identical SQL statements that differ only in literal values in the WHERE clause.
Which two actions would you recommend to improve performance? (Choose two.)
- A. Create the KEEP cache and cache the tables used in the queries.
- B. Set the CURSOR_SHARING parameter to FORCE.
- C. Use bind variables instead of literals.
- D. Create SQL plan baselines for the almost identical SQL statements and load them into the cursor cache.
- E. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE.
NEW QUESTION 13
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);
You then perform several INSERT, UPDATE, and DELETE operations, significantly altering the data in 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. creating a bitmap index on the COUNTRY_ID column
- B. regathering statistics on the CUSTOMERS table
- C. creating a histogram on the COUNTRY_ID column
- D. increasing the size of the PGA
- E. creating an SQL profile
- F. creating a KEEP cache
NEW QUESTION 14
In which three situations can dynamic statistics be used? (Choose three.)
- A. when the sampling time is a small fraction of the total time for a query
- B. when an execution plan is suboptimal because of complex predicates
- C. when extended statistics are not available for SQL statements that use complex predicates
- D. when a query is on a partitioned table with a global index
- E. when index statistics are missing on a column that is used in SQL statements with highly selective filters
Explanation: Reference: https://docs.oracle.com/database/121/TGSQL/tgsql_statscon.htm#TGSQL341
NEW QUESTION 15
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 Access Advisor.
- 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 Performance Analyzer.
NEW QUESTION 16
You have been asked to use table compression for two large tables. Given are the details of the tables:
The TRANS_DET table:
✑ The table is used by an OLTP application.
✑ High volume insert and update operations are performed on the table.
✑ The table is frequently queried using index range scans.
The TRANS_HISTORY table:
✑ The table is used by a DSS application.
✑ High volume bulk loads are performed on the table.
✑ The table is used to store archival data on which large table full-table scans (FTS) are performed.
Which row store compression would you recommend for these tables with minimal overhead on performance? (Choose the best answer.)
- A. basic table compression for both the tables
- B. advanced row compression for both the tables
- C. basic table compression for the TRANS_HISTORY table and advanced row compression for the TRANS_DET table
- D. basic table compression for the TRANS_DET table and advanced row compression for the TRANS_HISTORY table
- E. warehouse compression for the TRANS_DET table and archive compression for the TRANS_HISTORY table
P.S. Easily pass 1Z0-064 Exam with 84 Q&As Certleader Dumps & pdf Version, Welcome to Download the Newest Certleader 1Z0-064 Dumps: https://www.certleader.com/1Z0-064-dumps.html (84 New Questions)