[Free] 2018(Jan) EnsurePass Passguide Oracle 1z0-054 Dumps with VCE and PDF 41-50

2018 Jan Oracle Official New Released 1z0-054
100% Free Download! 100% Pass Guaranteed!

Oracle Database 11g: Performance Tuning

Question No: 41

You are working on an online transaction processing (OLTP) system. The CUST table in the SH schema was populated by direct load and after that it has gone through a lot of updates and deletions. The statistics for the CUST and SALES tables were updated recently.View the Exhibit and examine the query plan.The query is performing a lot of I/O for a query that fetches only 168 rows. To investigate further, you queried the ALL_TABLES view to find out PCTUSED, PCTFREE, and the number of rows in the CUST table, as given below:

SQLgt; SELECT table_name ,blocks, pct_used, pct_free, num_rows 2 FROM all_tables

3* WHERE table_name = #39;CUST#39;;


—– —– —– —– —– CUST 13768 80 111060

What would you recommend to reduce the I/O?

Ensurepass 2018 PDF and VCE

  1. reorganizing the table to use fewer blocks

  2. rebuilding the index on the CUST_FIRST_NAME column

  3. increasing the value for the PCTFREE attribute for the CUST table

  4. increasing the value for the PGA_AGGREGATE_TARGET initialization parameter

Answer: A

Question No: 42

You have the automatic optimizer statistics gathering enabled for a 24×7 database as part of the automatic maintenance task. Because the automatic optimizer statistics collection runs during maintenance window(in the night), the statistics on tables that are significantly modified throughout the day becomes stale. This leads to poor query performance on these tables and you want to minimize the

overhead in statistics collection. Identify a solution to this problem.

  1. Change the STALE_PERCENT preference to zero for the tables.

  2. Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.

  3. Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to zero during day time.

  4. Gather statistics on the table when the tables have a representative number of rows, and then lock the statistics.

Answer: D

Question No: 43

View the Exhibit.You decide to quickly configure the adaptive threshold. What would happen to the existing metric thresholds shown in the Exhibit?

Ensurepass 2018 PDF and VCE

  1. It would be overwritten.

  2. It would remain in effect.

  3. Only the thresholds set with the SYSTEM_MOVING_WINDOW baseline will remain in effect.

  4. Only the thresholds set with baselines other than the SYSTEM_MOVING_WINDOW baseline will remain in effect.

Answer: A

Question No: 44

You work as a DBA for a company and you have the responsibility of managing one of its online transaction processing (OLTP) systems. The database encountered performance- related problems and you generated an Automatic Workload Repository (AWR) report to investigate it further. View the Exhibits and examine the AWR report.

Ensurepass 2018 PDF and VCE

Ensurepass 2018 PDF and VCE

Ensurepass 2018 PDF and VCE

Ensurepass 2018 PDF and VCE

What could be the problem in this database?

  1. Java pool is not configured.

  2. The CPU in the system is slow.

  3. The shared pool size is inadequate.

  4. The database buffer cache is inadequate.

  5. The OPEN_CURSORS parameter is set to a small value.

Answer: C

Question No: 45

After running SQL Performance Analyzer (SPA), you observe a few regressed SQL statements in the SPA output. Identify the two actions that you would suggest for these regressed SQL statements. (Choose two.)

  1. Running SQL Access Advisor

  2. Adding them to SQL Plan Baseline

  3. Submitting them to SQL Tuning Advisor

  4. Running Automatic Database Diagnostic Monitor (ADDM)

Answer: B,C

Question No: 46

The Automatic Workload Repository (AWR) snapshot interval is set to 1 hour. A user of your database notifies you that the system was slow between 10:00 PM and 10:10 PM, but the Automatic Database Diagnostic Management (ADDM) analysis for the time period between 10:00 PM and 11:00 PM does not show this performance problem.What would you suggest to analyze this performance problem for the user?

  1. SQL Tuning Advisor

  2. SQL Performance Analyzer

  3. Active Session History report

  4. AWR Compare Periods report

Answer: C

Question No: 47

On examining the Automatic Workload Repository (AWR) report, you find that log file sync appears among the top 5 wait events.On further investigation, you notice that the average time waited for this event is low, but the number of waits are high.What does this imply?

  1. DBWR is slow.

  2. LGWR is slow due to slow I/O.

  3. Redo log buffer size is insufficient.

  4. The application might be committing frequently.

Answer: D

Question No: 48

View the Exhibit and examine the plans in the SQL plan baseline.You observe that the last two SELECT statements (outlined in red and blue) are identical, but have two different plans. Also, the second plan (in blue) is not being used by the optimizer.Which two steps do you perform to ensure that the optimizer uses the second plan (in blue) if the plan is more efficient? (Choose two.)

Ensurepass 2018 PDF and VCE

  1. Mark the second plan (in blue) as fixed.

  2. Use SQL Tuning Advisor to tune the plan.

  3. Use SQL Access Advisor to tune the plan.

  4. Use the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function to evolve the plan.

Answer: B,D

Question No: 49

The users in your online transaction processing (OLTP) environment complain that the query response time has increased considerably. You checked the Program Global Area (PGA)- related information in the AWR report to investigate it further.

View the Exhibit named PGAHIT and note the PGA cache-hit percentage.

Ensurepass 2018 PDF and VCE

View the Exhibit named PGAADV and analyze the content.

Ensurepass 2018 PDF and VCE

If you want to increase the size of PGA, you have sufficient memory available in the system. What would be the correct decision?

  1. Double the size of PGA.

  2. Increase the size of PGA to 120 MB.

  3. Increase the size of PGA to 160 MB or more.

  4. Leave the PGA at its current size and investigate other possible causes.

Answer: C

Question No: 50

Examine the following information from the AWR report: Top 5 Timed Events

~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Call Time

——————- —— —— —–

CPU time 559 88.80

log file parallel write 2,181 28 4.42

SQL*Net more data from client 516,611 27 4.24

db file parallel write 13,383 13 2.04

db file sequential read 563 2 .27

Which could cause waits on the db file sequential read event?

  1. too many full table scans

  2. too large a database buffer cache

  3. too many waits occurring while reading multiple blocks synchronously

  4. too many index range scans occurring which refers to blocks not in the database buffer cache

Answer: D

100% Free Download!
Download Free Demo:1z0-054 Demo PDF
100% Pass Guaranteed!
Download 2018 EnsurePass 1z0-054 Full Exam PDF and VCE

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

2018 EnsurePass IT Certification PDF and VCE

Leave a Comment


Your email address will not be published. Required fields are marked *