Monday

1Z0-054 Dumps Oracle Database Performance Tuning

Name of Exam -- 1Z0-054 Oracle Database 11g: Performance Tuning

Number of Questions = 200
Pdf File download link Given Below

Dumps Updated December 2012

Some Important Questions 


QUESTION 1

Your system has been upgraded from Oracle Database 10g to Oracle Database 11g. You imported SQL Tuning Sets (STS) from the previous version that contains important SQL statements. You are not sure about the elapsed time that the SQL statements will have in the new system due to the version change of the
optimizer. Which tool provides a comparative report with respect to the elapsed time in both versions of the optimizer?


A. SQL Access Advisor
B. SQL Tuning Advisor (STA)
C. SQL Performance Analyzer (SPA)
D. Automatic Workload Repository (AWR)compare period report

Correct Answer: C


QUESTION 2

Examine the following block that executes a SQL Performance Analyzer task:
begin dbms_sqlpa.execute_analysis_task(task_name => 'SPA_SKD4', execution_type => 'TEST EXECUTE',
execution_name => 'before');
end;
Which two statements are true about the execution? (Choose two.)

A. It generates execution plans and execution statistics.
B. It executes only the query part of the DML statements.
C. The test plans are generated and added to plan baseline.
D. The test plans are executed, but not generated if they are available in the plan baseline.

Correct Answer: AB


QUESTION 3

You have the automatic optimizer statistics gathering enabled for a 24x7 database as part of the automatic maintenance task. You observed that a few tables are being updated regularly, but not being queried often. However statistics are being gathered for these tables automatically and regularly, thereby affecting the
database performance. What would you do to increase the interval of statistics collection for these tables so that the statistics collection jobs spend less time during the automatic maintenance task?

A. Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.
B. Increase the value of the STALE_PERCENT preference for these tables.
C. Mark those associated indexes that are less frequently used as invisible.
D. Increase the value of the ESTIMATE_PERCENT preference for these tables

Correct Answer: B


QUESTION 4

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 5

Identify the type of recommendation that can automatically be implemented by the Automatic Tuning Advisor
as part of automatic SQL tuning task?

A. recommendation for SQL profiles
B. recommendation about effective indexing
C. recommendation about stale or no statistics
D. recommendation about the structure of SQL statements

Correct Answer: A


QUESTION 6

The OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is set to TRUE for a 24x7 database
instance. The space consumption has significantly increased by the automatically captured plan baselines.
You are suggested to run the following block:
BEGIN
dbms_spm.configure('SPACE_BUDGET_PERCENT', 30);
END;
After a few weeks of work, you observe warnings on exceeding space usage by SQL Management Base (SMB) in alert log file. Which two actions can be solutions for this? (Choose two.) 

A. increasing the AWR retention period
B. purging the older SQL management objects
C. increasing the size of the SYSTEM tablespace
D. increasing the size of the SYSAUX tablespace

Correct Answer: BD


QUESTION 7

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 8

You work for a small manufacturing company as a DBA. The company has various applications to manage
the details of its business and customer base. The company has only one sales outlet where the operator
updates the SALES table in the sales application with the details when a product is sold. The company has
many other applications for various other businesses that use 
the same database.
The sales operator, while generating a report, noticed that there are some gaps generated in the serial number in the SLNO column during peak hours of business. On investigating, you find that the SLNO column in the SALES table uses the SLNSEQ sequence to generate the serial numbers when a record is inserted. During peak hours, you noticed the following:
When the operator entered the sales details the last time, the sequence number was 1056300, but when you checked the sequence as follows, you found a gap of 100 numbers:
SQL> SELECT sequence_name, last_number FROM user_sequences WHERE sequence_name='SLNSEQ';
SEQUENCE_NAME LAST_NUMBER
------------------------------ -----------
SLNSEQ 1056400
You observed this behavior many times and only during peak hours. What could be the problem and solution for this?
A. The reserved pool is not configured. Configure the reserved pool.
B. The keep buffer pool is not configured. Configure the keep buffer pool.
C. The sequence was created with a CACHE of 100 numbers. Re-create the sequence with a higher CACHE number.
D. The sequence is aging out of the shared pool.Keep the sequence in the shared pool using the DBMS_SHARED_POOL.KEEP procedure.

Correct Answer: D


QUESTION 9

You work as a DBA in ABC Corp. You are working on an online transaction processing (OLTP) database. The database size has grown to 100 GB. The database will support new applications and more users will be added. Presently, the database uses normal file system storage. Before the new applications are added, you want to move the storage to RAID. You are in consultation with the OS Administrator for finalizing the RAID
option for the storage.
You have the following requirements for the database:

1) Less mean time to recover (MTTR)
2) Striped for performance
3) Mirrored for safety
Which RAID option would you recommend?

A. RAID level 0
B. RAID level 1
C. RAID level 0 over 1(1+0)
D. RAID level 1 over 0(0+1)

Correct Answer: C

QUESTION 10

You are working as a DBA in ABC Corp. You are working on an online transaction processing (OLTP) database. The database size has grown to 100 GB. The database will support new applications and more users will be added. Presently, the database uses normal file system storage. However, before the new
applications are added, you want to move the storage to RAID. You are in consultation with the OS Administrator for finalizing the RAID option.Performance is the primary goal of the database because applications are demanding in terms of I/O operations and it should have lower mean recovery time (MTTR)
than the current MTTR. Which RAID option would you recommend?

A. RAID level 0
B. RAID level 1
C. RAID level 5
D. RAID level 1 over 0(0+1)

Correct Answer: B

QUESTION 11

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

A. For a database with lower recovery (MTTR) overhead
B. For a database in which the applications primarily perform read operations and predominantly sequential
I/O is performed
C. For a database in which performance is the primary goal of the database because applications are
demanding in terms of I/O operations
D. For a database in which the database size grows rapidly, the cost of storage is critical and data
accessibility is more important than performance

Correct Answer: BD

Download link 







2 comments:

  1. Anonymous3:34 am

    link is not valid

    ReplyDelete
    Replies
    1. Admin9:37 am

      Link Updated....

      Delete