1Z0-064 | Improve Oracle Database 12c: Performance Management And Tuning 1Z0-064 Dumps Questions
Master the 1Z0-064 Oracle Database 12c: Performance Management and Tuning content and be ready for exam day success quickly with this Ucertify 1Z0-064 exam question. We guarantee it!We make it a reality and give you real 1Z0-064 questions in our Oracle 1Z0-064 braindumps.Latest 100% VALID Oracle 1Z0-064 Exam Questions Dumps at below page. You can use our Oracle 1Z0-064 braindumps and pass your exam.
Free demo questions for Oracle 1Z0-064 Exam Dumps Below:
NEW QUESTION 1
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.
NEW QUESTION 2
Examine the command to change a parameter value from the default to 50: SQL> ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ = 50;
What is the effect of changing the value of the parameter? (Choose the best answer.)
- A. It influences the optimizer to use full table scans instead of index scans as the estimated cost of full table scan is reduced.
- B. It influences the optimizer to use bitmap indexes as the estimated cost of conversion from bitmap to rowid is reduced.
- C. It influences the optimizer to always use fast full index scans as the estimated cost of using an index is reduced.
- D. It influences the optimizer to use indexes instead of full table scans as the estimated cost of using an index is reduced.
NEW QUESTION 3
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.
NEW QUESTION 4
Users complain about increased response time for queries in your production database that supports an OLTP workload. On investigation, you notice a large number of db file scattered read, latch: cache buffers lru chain, and latch: cache buffers chains wait events:
Identify three possible reasons for the increased response time. (Choose three.)
- A. too many sort operations being performed
- B. repeated simultaneous access to a block or small number of blocks
- C. the shared pool is inadequately sized
- D. queries not using indexes and performing full table scans
- E. queries repeatedly fetching blocks that are not in the database buffer cache
- F. cursors are closed explicitly after each execution
NEW QUESTION 5
You want to capture the performance of your database during the last ten days of the first quarter of the current financial year, so that you can compare this performance against the remaining quarter ends of the current financial year.
Which method should you use? (Choose the best answer.)
- A. Create a static baseline that can be used with AWR compare reports.
- B. Create a new moving window baseline and enable adaptive thresholds for relevant metrics.
- C. Use a repeating baseline template to create and drop baselines based on a repeating time schedule and set adaptive thresholds at a high significance level.
- D. Use fixed baseline templates to create a new moving window baseline and set relevant warning alerts that are computed as a percentage multiple of the maximum value observed for the data in the moving window baseline.
NEW QUESTION 6
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.
NEW QUESTION 7
Your database supports an online transaction processing (OLTP) workload. The database uses ASM storage. One of the ASM disks goes offline because of hardware failure. When the disk is replaced and then added back to the diskgroup, database performance is affected by rebalance operations.
Which two actions would you recommend to lower the impact of rebalance operations on the performance of the database? (Choose two.)
- A. Increase the number of ASMB processes.
- B. Decrease the value of the ASM_POWER_LIMIT parameter.
- C. Set the DISK_REPAIR_TIME disk attribute to a lower value.
- D. Specify the POWER clause with a lower value in an ALTER DISKGROUP statement.
- E. Set the DISK_REPAIR_TIME disk attribute to a higher value.
NEW QUESTION 8
Examine the parameters set for your database instance:
You notice that for one particular SQL statement, the optimizer generates a new better plan than the plans in the SQL Plan Management Base.
Which action is taken by the optimizer? (Choose the best answer.)
- A. It adds the newly generated plan as an accepted but non-fixed plan.
- B. It adds the newly generated plan as enabled and accepted.
- C. It adds the newly generated plan as enabled but not accepted.
- D. It adds the newly generated plan as a fixed plan, which will be used each time the SQL statement is executed.
NEW QUESTION 9
Examine the partial PLAN_TABLE output:
Which is the correct sequence of execution? (Choose the best answer.)
- A. 3, 2, 1, 4, 0
- B. 0, 1, 2, 3, 4, 1
- C. 0, 4, 1, 3, 2, 1
- D. 3, 2, 4, 1, 0
- E. 3, 2, 4, 1, 0, 2
NEW QUESTION 10
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.
NEW QUESTION 11
Which two statements are true about viewing the details of Real-Time Database Operations? (Choose two.)
- A. In V$SQL_MONITOR monitoring, statistics are cumulative over several executions of the SQL statement that is being monitored in a session.
- B. SQL Developer can be used to view running database operations.
- C. Oracle Enterprise Manager Database Express can be used to view running database operations.
- D. When the SQL statement that is being monitored is executing, V$SQL_MONITOR is refreshed once every minut
- E. After the execution ends, the monitoring information in V$SQL_MONITOR is deleted immediately.
- F. Oracle Enterprise Manager Cloud Control can be used to view running database operations.
NEW QUESTION 12
You are administering a database that supports a mixed workload. You upgrade your database from Oracle Database 11g to 12c and after the upgrade, users complain about degraded performance of some queries. The SQL plan baselines imported from the previous version are present for the queries and are loaded to the SQL Management Base as accepted plans. On further investigation, you find that better plans are generated but not used by the optimizer.
Examine the parameters set for the instance:
Which three tasks would you perform to improve the performance of these queries? (Choose three.)
- A. Gather statistics for the objects used in the queries.
- B. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve new plans and fix the plans for the statements.
- C. Create an SQL Tuning Set (STS) and run it through the SQL Access Advisor to generate recommendations.
- D. Create an STS and run it through the SQL Tuning Advisor to generate recommendations.
- E. Set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE.
- F. Use the DBMS_SPM.ALTER_SQL_PLAN_BASELINE function to alter the acceptedplans as fixed plans.
NEW QUESTION 13
Which two result in the latest fragmentation within segments and the least external fragmentation within tablespaces? (Choose two.)
- A. automatic segment space managed segments
- B. locally managed tablespaces with uniform extent size
- C. freelist managed segments with one freelist
- D. dictionary managed tablespace
- E. locally managed tablespaces that were converted from dictionary managed tablespaces
- F. freelist managed segments with multiple freelist
NEW QUESTION 14
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
NEW QUESTION 15
Your database supports an OLTP workload during the day and batch processing at night. You want to monitor performance metrics to detect any degradation of performance in both types of workloads over a time period of 30 days.
Examine this list of possible steps:
1. Create a fixed baseline.
2. Create a baseline template.
3. Create a new moving window baseline.
4. Increase the retention period default value to 30 days.
5. Increase the size of the existing moving window baseline to 30 days.
6. Create warning and critical alerts for the relevant metrics.
7. Enable adaptive thresholds to detect the workload patterns and specify a high- significance-level threshold type.
8. Enable adaptive thresholds to detect the workload patterns and set different threshold values as a percentage of the maximum value.
Which option represents the required steps in the correct order? (Choose the best answer.)
- A. 5, 7
- B. 2, 4, 3
- C. 3, 4, 8
- D. 4, 5, 7
- E. 5, 1, 6, 8
NEW QUESTION 16
Which four objectives are achieved by using Resource Manager to manage multiple concurrent user sessions that are competing for resources? (Choose four.)
- A. distributing available CPU by allocating percentages of CPU time to different users and applications
- B. limiting the degree of parallelism of any operation performed by members of a group of users
- C. limiting queries based on resource consumption of runaway sessions or calls that consume more than a specified amount of CPU, physical I/O, logical I/O, or elapsed time
- D. limiting the number of concurrent sessions for a user
- E. limiting the number of user sessions allowed to be concurrently active within a group of users
- F. limiting the number of parallel executions that can be executed by a user
NEW QUESTION 17
You are administering a database that supports an OLTP workload. Users complain about the degraded response time of a query. You want to gather new statistics for objects accessed by the query and test query performance with the new statistics without affecting other sessions connected to the instance.
The STALE_PERCENT statistic preference is set to a default value and the STATISTICS_LEVEL parameter is set to TYPICAL.
Which two actions would you take to accomplish the task? (Choose two.)
- A. Set the STALE_PERCENT statistic preference to a higher value than the default, and then gather statistics.
- B. Set the STATISTICS_LEVEL parameter to ALL for the instance.
- C. Set the INCREMENTAL preference to TRUE, and then gather statistics.
- D. Set the OPTIMIZER_USE_PENDING_STATISTICS parameter to TRUE for the session in which you want to test the query.
- E. Set the PUBLISH statistic preference to FALSE, and then gather statistics.
- F. Set the NO_INVALIDATE statistic preference to TRUE, and then gather statistics.
NEW QUESTION 18
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. The current PGA size requires the use of a temporary tablespace for sorting operations.
- C. The current PGA size is sufficient and does not require the memory manager to allocate more memory.
- D. PGA size should be increased at least four times its current size for significant improvement in performance and disk space management.
NEW QUESTION 19
You plan to upgrade your production database from Oracle Database 11g to 12c. As part of the upgrade, you want to introduce new indexes and materialized views. You have already created a test system with Oracle Database 12c, having the same structure and data as the production database, along with new schema objects to be added to the production database.
You want to identify regressed SQL statements, if any, which may have been caused by schema changes and the change in the optimizer version.
Which two methods would you use to achieve this? (Choose two.)
- A. Create an SQL Tuning Set (STS) for the SQL statements on the production database and submit as input to the SQL Tuning Advisor on the test database.
- B. Create an STS for the SQL statements on the production database and submit as input to the SQL Performance Analyzer with the OPTIMIZER_FEATURES_ENABLE parameter first set to 188.8.131.52, and then to 184.108.40.206 on the test database.
- C. Generate an Automatic Workload Repository (AWR) compare periods report with snapshots taken before and after schema changes on the test database.
- D. Capture the production database workload, replay it on the test system by using Database Replay, and analyze by using the workload replay compare period report.
- E. Create an STS for the SQL statements on the production database and submit as input to the SQL Access Advisor on the test database.
- F. Create an STS for the SQL statements on the production database before and after changes and submit as input to the SQL Performance Analyzer on the test database.
NEW QUESTION 20
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
NEW QUESTION 21
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 22
P.S. Easily pass 1Z0-064 Exam with 84 Q&As Surepassexam Dumps & pdf Version, Welcome to Download the Newest Surepassexam 1Z0-064 Dumps: https://www.surepassexam.com/1Z0-064-exam-dumps.html (84 New Questions)