[Free] Download New Updated (December) Oracle 1Z0-054 Exam Questions 61-70

Ensurepass

QUESTION 61

You observed that some of the queries are performing poorly on the SALES_RECORDS table in your database.

 

On further investigation, you find that at the end of each day the contents of the SALES_RECORDS table are transferred to the SALES table and deleted from the SALES_RECORDS table. The deleted operations cause the table to be sparsely populated.

 

The SALES_RECORDS table has Automatic Segment Space Management (ASSM) and row movement enabled. The table is accessible in 24×7 mode.

 

What is the most efficient method to improve the performance?

 

A.

Perform EXPORT, DROP, and IMPORT operations on the SALES_RECORDS table sequentially.

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.

 

Correct Answer: B

 

 

QUESTION 62

Your database has the ACCTG service configured for an accounting application running on a middle tier. These service is used by the middle-tier applications to connect to the database by using connection pools. The application has three modules LEDGER, ACCOUNTS, and BILLING. You asked the developers to invoke the DBMS_APPLICATION_INFO.SET_MODULE procedure to set the module name and action in the code for the application. You enabled tracing at the service level by executing the following command:

 

SQL> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(‘ACCTG’);

 

You want to consolidate the trace output from the trace files created based on module.

 

Which is the appropriate solution for this?

 

A.

Using trace files as input for the tkprof utility to consolidate the tracing of sessions for a module

B.

Using the trace file as input for the trcsess utility to consolidate and process th
e output for a module

C.

Using the trcsess utility to consolidate all trace files into a single output file, which can then be processed by the tkprof utility

D.

Using the trace file as input for the trcsess utility and creating output files that can directly be used by the users for performance or debugging purposes

 

Correct Answer: C

 

 

QUESTION 63

In a database, the values for transactions per second are higher during working hours and lower during nonworking hours. You want to receive alerts in future time periods if the daytime performance varies compared to the captured daytime performance or the nighttime performance varies compared to the captured nighttime performance, over a repeating time period. This wouldpermit the DBA to perform additional performance tuning operations. Which feature would you use to record this variation in performance statistics?

 

A.

SQL tuning set

B.

SQL plan baselines

C.

the Automatic Workload Repository (AWR) baseline

D.

the Automatic Workload Repository (AWR) baseline template

 

Correct Answer: D

 

QUESTION 64

Your company wants to migrate a single instance Oracle 11g Release 1 database to a RAC environment. The database supports middle-tier applications using connection pooling for connecting to database.

 

Being a part of the performance management team, you want to test the performance of the SQL statements and the peak workload of the applications in the new environment before upgrading the production system.

 

Given below are some of the steps in random order to accomplish the task:

 

1. Capture the real workload on the existing system.

 

2. Create a SQL Tuning Set (STS) for the statements executed by the application.

 

3. Transport STS on the test environment and use SQL Tuning Advisor to compare the before and after performance.

 

4. Replay the workload on the test environment.

 

5. Analyze the divergence after replay.

 

6. Use Automatic Workload Repository (AWR) to capture the information for top SQL on the existing system.

 

7. Transport STS on the test environment and use SQL Performance Analyzer to compare the before and after performance.

 

Identify the correct steps required to perform the test.

 

A.

2 and 3 only

B.

6 and 7 only

C.

1, 4, and 5 only

D.

2, 3, and 5 only

E.

2, 3, 4, and 5 only

 

Correct Answer: C

 

 

QUESTION 65

Identify the reason why chained or migrated rows in a table cause performance degradation when you access them.

 

A.

because the ROWID changes for these rows

B.

because these rows make existing indexes invalid

C.

because it is not possible to use indexes to retrieve these rows

D.

because retrieving these rows requires more than one data block to be accessed

E.

because the rows force queries to use temporary segments while retrieving them

 

Correct Answer: D

 

 

 

 

QUESTION 66

Which two activities can be identified as common unscalable causes of suboptimal database performance? (Choose two.)

 

A.

defining multiple buffer pools

B.

applications generating dynamic SQL

C.

large amounts of recursive SQL executed by SYS

D.

recursive SQL executed by a user other than SYS

E.

using cursors with bind variables that open the cursor and execute it many times

 

Correct Answer: BC

 

 

QUESTION 67

Examine the SQL statement given below:

 

SQL> 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.)

 

clip_image002

 

A.

The execution plan is displayed for the executed SQL statement.

B.

The EXPLAIN PLAN command does not actually execute the statement.

C.

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

D.

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

 

Correct Answer: BC

 

 

 

 

 

 

 

QUESTION 68

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?

 

A.

SQL Tuning Advisor

B.

SQL Performance Analyzer

C.

Active Session History report

D.

AWR Compare Periods report

 

Correct Answer: C

 

 

QUESTION 69

You receive a recommendation for a SQL statement through the automatic SQL tuning process that suggests implementing a SQL profile. You implement the SQL profile. The tables associated with the SQL statement grow, and indexes are created and dropped on these tables. What would happen to the SQL profile that is created for the SQL statement?

 

A.

It becomes outdated immediately after the tables change.

B.

It is maintained automatically along with the changes in the tables.

C.

The profile will be used for the changed plans of the SQL statement.


D.

The plan for the SQL statement is frozen until it is regenerated by the SQL Tuning Advisor.

 

Correct Answer: C

 

 

QUESTION 70

You want to capture the performance statistics during a system test that is scheduled for the weekend. Which action would help you to automatically capture the performance statistics?

 

A.

Creating SQL tuning sets

B.

Setting up SQL plan baselines

C.

Configuring new services for the database

D.

Creating Automatic Workload repository (AWR) baseline templates

 

Correct Answer: D

 

Free VCE & PDF File for Oracle 1Z0-054 Real Exam

Instant Access to Free VCE Files: CompTIA | VMware | SAP …
Instant Access to Free PDF Files: CompTIA | VMware | SAP …

This entry was posted in 1Z0-054 Exam Questions (December) and tagged , , , , , , . Bookmark the permalink.