[Free] 2018(Jan) Dumps4cert Braindumps Oracle 1z0-054 Dumps with VCE and PDF 1-10

Dumps4cert.com : Latest Dumps with PDF and VCE Files
2018 Jan Oracle Official New Released 1z0-054
100% Free Download! 100% Pass Guaranteed!
http://www.Dumps4cert.com/1z0-054.html

Oracle Database 11g: Performance Tuning

Question No: 1

You notice that two wait events, Latch:cache buffer chains and Latch:cache buffer LRU chains, appear consistently in the Top 5 Timed Events for your database.Which two options describe what these wait events indicate? (Choose two.)

  1. Latch:cache buffer LRU chains – indicates excessive block replacement

  2. Latch:cache buffer LRU chains – indicates insufficient size of application cursor cache

  3. Latch:cache buffer chains – indicates inefficient SQL that accesses many blocks repeatedly

  4. Latch:cache buffer chains – indicates that there are several sessions waiting for a buffer that is presently being read into the buffer cache by another session

Answer: A,C

Question No: 2

You are working on an online transaction processing (OLTP) system. By day most of theapplication users perform queries accessing the most recently added or modified rows. The applications have most of the queries based on multiple tables. But at night, some batch processing is also done. Which two actions would you recommend to choose a goal for the optimizer based on the needs of your application? (Choose two.)

  1. setting the OPTIMIZER_MODE parameter to ALL_ROWS at the instance level

  2. setting the OPTIMIZER_MODE parameter to FIRST_ROWS_n at the instance level

  3. asking the developer to add a hint /*ALL_ROWS*/ in the long-running batch processing queries

  4. asking the developer to add a hint /*FIRST_ROWS_n*/ in the long-running batch processing queries

Answer: B,C

Question No: 3

You are a DBA in ABC Corp. You are working on a DSS system.. The applications directly connect to the database to perform transactions. The application users also perform direct loads. The company wants to upgrade from Oracle Database 10g version 10.2.0.1 to

Oracle Database 11g. You want to test the performance of the SQL statements in the new environment before upgrading the production database. To accomplish this, you set up the test database with Oracle Database 11g, with the same data and schemas as in production.

What would you recommend to accomplish the task?

  1. gathering all the SQL statements and using SQL Tuning Advisor on the test database for performance analysis

  2. capturing the workload on the production database, replaying it on the test machine, and comparing the performance

  3. creating a SQL Tuning Set by capturing the SQL workload on the production database and using SQL Performance Analyzer for performance analysis

  4. configuring AWR to capture the maximum number of SQL statements, transporting it to the test system, and using SQL Access Advisor for performance analysis

Answer: C

Question No: 4

View Exhibit1 and examine the indexes on the CUSTOMERS table.

Dumps4Cert 2018 PDF and VCE

The statistics for the CUSTOMERS table have been updated recently by using the following command:

SQLgt; EXEC DBMS_STATS.GATHER_TABLE_STATS(#39;SH#39;,#39;CUSTOMERS#39;,method_opt=gt;#39;FOR ALL

INDEXED COLUMNS SIZE AUTO#39;);

View Exhibit2 to examine a query plan. Even though the index is present on the

COUNTRY_ID and CUST_GENDER columns, the query uses a full table scan. What could be the reason?

Dumps4Cert 2018 PDF and VCE

  1. because the histogram statistics for the COUNTRY_ID column are not updated

  2. because the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a high value

  3. because the optimizer calculates the cost of accessing blocks by using a full table scan to be less as compared to index scans, even though indexes are available

  4. because indexes on CUST_GENDER and COUNTRY_ID columns are of different types, the index on the CUST_GENDER column is bitmap index, and on COUNTRY_ID columns is btree index.

Answer: C

Question No: 5

You are working on an online transaction processing (OLTP) system. You are investigating the reasons for performance degradation in the database.

View the Exhibit named TOP5EVENTS and note the top five events.

Dumps4Cert 2018 PDF and VCE

View the Exhibit named TOPSQL and note the problems related to these statements.

Dumps4Cert 2018 PDF and VCE

View the Exhibit named INSTACT and note the table scans.

Dumps4Cert 2018 PDF and VCE

Which is the most appropriate solution?

  1. Create indexes on short tables.

  2. Use the CACHE hint to access long tables.

  3. Keep the long tables in the keep buffer pool.

  4. Keep the short tables in the keep buffer pool.

  5. Keep the short tables in the recycle buffer pool.

Answer: D

Question No: 6

View the Exhibit and examine the content. ALLOCATED_SPACE and FREE_SPACE are not matching with the TABLESPACE_SIZE value. What would you conclude from this?

Dumps4Cert 2018 PDF and VCE

  1. A new temporary file is added to the tablespace.

  2. The temporary space was not sufficient for sort operations.

  3. The temporary space allocated to large sort operations is not deallocated.

  4. TEMP_L is a default temporary tablespace because the numbers show some overhead on this tablespace.

Answer: C

Question No: 7

Examine the SQL statement given below: SQLgt; EXPLAIN PLAN for

SELECT department_name,count(*) FROM hr.employees e, hr.departments d WHERE e.department_id=d.department_id GROUP BY d.department_name;

To view the execution plan for the statement, you queried plan_table. View the Exhibit and examine the execution plan.Which two statements are true about the execution plan?

(Choose two.)

Dumps4Cert 2018 PDF and VCE

  1. The execution plan is displayed for the executed SQL statement.

  2. The EXPLAIN PLAN command does not actually execute the statement.

  3. The row with the id column having a value of 4 is the first step executed in the execution plan.

  4. The row with the id column having a value of 0 is the first step executed in the execution plan.

Answer: B,C

Question No: 8

In which two scenarios would you recommend using RAID level 5 as an option for database storage?(Choose two.)

  1. For a database with lower recovery (MTTR) overhead

  2. For a database in which the applications primarily perform read operations and predominantly sequential I/O is performed

  3. For a database in which performance is the primary goal of the database because applications are demanding in terms of I/O operations

  4. For a database in which the database size grows rapidly, the cost of storage is critical and data accessibility is more important than performance

Answer: B,D

Question No: 9

View the Exhibit exhibit1 to examine the series of SQL commands and parameter settings.

Dumps4Cert 2018 PDF and VCE

View the Exhibit exhibit2 to examine the plans available in the SQL plan baseline.

Dumps4Cert 2018 PDF and VCE

The first plan (in red) is created when OPTIMIZER_MODE is set to ALL_ROWS and the second plan (in blue) is created when OPTIMIZER_MODE is set to FIRST_ROWS.Which SQL plan baseline would be used if the SQL query in exhibit1 is executed again when the value of OPTIMIZER_MODE is set to FIRST_ROWS?

  1. the second plan, because it is a fixed plan

  2. the first plan, because it is an accepted plan

  3. the second plan, because it is the latest generated plan in FIRST_ROW mode

  4. A new plan, because the second plan in FIRST_ROW mode is not an accepted plan

Answer: B

Question No: 10

As part of a proactive tuning activity, you have the Automatic Workload Repository (AWR) and ADDM enabled for your database with the default setting of snapshot retention as 8 days and snapshot interval as 60 minutes.Which performance issue does NOT appear in the ADDM and needs alternative tools for investigation?

  1. suboptimal parameter settings

  2. application-level block contention

  3. performance issues related to the Resource Manager

  4. short-lived or intermittent performance issues lasting for less than 5 minutes

Answer: D

100% Dumps4cert Free Download!
Download Free Demo:1z0-054 Demo PDF
100% Dumps4cert Free Guaranteed!
1z0-054 Dumps

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

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.