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

Ensurepass

QUESTION 21

View Exhibit1 and examine the indexes on the CUSTOMERS table.

 

clip_image002

 

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

 

SQL> EXEC

DBMS_STATS.GATHER_TABLE_STATS(‘SH’,’CUSTOMERS’,method_opt=>’FOR ALL

 

INDEXED COLUMNS SIZE AUTO’);

 

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?

 

clip_image003

 

A.

because the histogram statistics for the COUNTRY_ID column are not updated

B.

because the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter is set to a high value

C.

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

D.

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

 

Correct Answer: C

 

 

QUESTION 22

You are hired by ABC Pvt Ltd., and assigned the task of managing one of its development databases. The company has recently upgraded this database to Oracle Database 11g from Oracle Database 10g.

 

You noticed that SGA is undersized as shown in Exhibit 1.

 

clip_image005

 

To investigate further, you checked the related parameters as sh
own in Exhibit 2.

 

clip_image007

 

You executed the following command to solve the problem:

 

ALTER SYSTEM SET MEMORY_TARGET=800M;

 

What would happen?

 

A.

The implementation of AMM fails because the MEMORY_MAX_TARGET parameter is not set.

B.

It implements AMM by setting MEMORY_TARGET and MEMORY_MAX_TARGET parameters to 800M.

C.

It implements Automatic Memory Management (AMM) by setting MEMORY_TARGET to 768M and the MEMORY_MAX_TARGET parameter to 800M.

D.

The implementation of AMM fails because the MEMORY_TARGET parameter cannot be set to a value less than the sum of the current PGA_AGGRAGATE_TARGET and SGA_MAX_SIZE values.

 

Correct Answer: A

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 23

You are working on a development database that was upgraded to Oracle Database 11g from Oracle Database 9i. An ADDM finding in this database says that the shared pool is inadequately sized, as shown in the Exhibit.

 

clip_image009

 

You diagnosed that this is due to different kinds of workloads and this occurs only during peak hours. You tried to resize this by shrinking the database buffer cache but that caused inadequate buffer cache problems. The following are the related parameter settings:

 

SQL> show parameter sga

 

NAME TYPE VALUE

 

—————————– ————— —————–

 

lock_sga boolean FALSE

 

pre_page_sga boolean FALSE

 

sga_max_size big integer 300M

 

sga_target big integer 0

 

SQL> show parameter target

 

NAME TYPE VALUE

 

—————————– ————— —————–

 

…..

 

fast_start_mttr_target integer 0

 

memory_max_target big integer 0

 

memory_target big integer 0

 

pga_aggregate_target big integer 100M

 

sga_target big integer 0

 

You want to balance the memory between the System Global Area (SGA) components within SGA without affecting the size of the Program Global Area (PGA). Which action would solve this problem?

 

A.

Set the SGA_TARGET parameter to 300M.

B.

Set the SGA_MAX_SIZE parameter to 400M.

C.

Set the MEMORY_TARGET parameter to 100M.

D.

Set the MEMORY_MAX_TARGET parameter to 300M.

 

Correct Answer: A

 

 

QUESTION 24

You work for a company as a DBA. The company has an application to manage the details of its business and customer base. However, application users complain that updating the SALES record takes more time than it used to earlier. On investigation, you notice that the application performance degrades when a call is made to the UPDATE_INV procedure. Further, you execute the following query to investigate:

 

SQL> SELECT name, type, sharable_mem, kept

 

2 FROM v$db_object_cache

 

3 WHERE sharable_mem > 4000

 

4 AND EXECUTIONS > 5

 

5 AND (type=’FUNCTION’ OR type=’PROCEDURE’)

 

6 /

 

NAME TYPE SHARABLE_MEM KEPT

 

—————— ————– ————— ———–

 

GETEMKEY FUNCTION 13695 YES

 

UPDATE_INV PROCEDURE 14766 NO

 

SETEMUSERCONTEXT PROCEDURE 13703 YES

 

DECRYPT FUNCTION 17790 YES

 

UPDATE_DEPT PROCEDURE 18765 NO

 

What would you do to improve the performance?

 

A.

Enable the result cache if not already enabled.

B.

Flush the shared pool to make space for this procedure.

C.

Keep the UPDATE_INV procedure by using the DBMS_SHARED_POOL.KEEP procedure.

D.

Increase the size of the keep buffer pool to accommodate the UPDATE_INV procedure.

E.

Ask the developers to modify and use literals instead of bind variables in the UPDATE_INV procedure.

 

Correct Answer: C

 

 

 

 

 

QUESTION 25

View the Exhibit and analyze a portion of the Active Session History (ASH) report marked with a red rectangle.Which two can be inferred from the output? (Choose two.)

 

clip_image011

 

A.

increase in the number of active sessions

B.

unable to grow the database buffer cache as per demand

C.

increase in number of COMMITS and ROLLBACKS per second

D.

increase in the number of processes waiting for the buffer busy wait event

 

Correct Answer: AD

 

 

QUESTION 26

You identified a SQL statement with SQL ID 9g485acn2n30m, which is expensive on the resources. To view the execution plan for the statement, you executed the following command:

 

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(‘9g485acn2n30m’,1));

 

What would this command accomplish?

 

A.

It formats and displays the contents of the execution plan for the cursor with the SQL_ID ‘9g485acn2n30m’ from the library cache.

B.

It formats and displays the contents of the execution plan but does not guarantee accuracy if the SQL statement has bind variables.

C.

It formats and displays the contents of the execution plan but does not guarantee that the same execution plan would be executed.

D.

It formats and displays the contents of the execution plan for the cursor with the SQL_ID ‘9g485acn2n30m’ from the DBA_HIST_SQL_PLAN view.

 

Correct Answer: A

 

 

QUESTION 27

You observed in your database that the queries formerly executed in 5 seconds now take longer time.View the Exhibit and analyze the output of the query that you executed to find the problem.Which is the primary concern?

 

clip_image012

 

A.

the number of disk sorts

B.

the number of full table scans

C.

the number of chained and migrated rows

D.

the number of rows fetched based on rowid

 

Correct Answer: C

 

 

QUESTION 28

You are working for a company that uses huge applications to manage its customer details. During your regular performance checks, you executed the following query:

 

SQL> show parameter shared_pool_reserved_size

 

NAME TYPE VALUE

 

—————————– ——————— —————–

 

shared_pool_reserved_size big integer 6920601

 

SQL> SELECT free_space, used_space, request_misses, request_failures from

 

V$SHARED_POOL_RESERVED;

 

FREE_SPACE USED_SPACE REQUEST_MISSES REQUEST_FAILURES

 

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

6469776 555528 0 113

 

You observed that REQUEST_FAILURES is 113 but not increasing, and also that REQUEST_MISSES is not increasing.

 

What would you interpret from this?

 

A.

The reserved pool is very large. Consider reducing the size of the shared pool.

B.

The reserved pool is large and has free space to satisfy requests. Consider reducing the size of the reserved pool.

C.

The reserved pool is very small and is not finding enough memory to satisfy requests. Increase the size of the shared pool.

D.

The reserved pool is very small and is not finding enough memory to satisfy requests. Increase the size of the reserved pool.

 

Correct Answer: B

 

 

QUESTION 29

View the Exhibit and examine the output for the query on V$IOSTAT_FUNCTION.Which two statements are true about the interpretation of the output? (Choose two.)

 

clip_image014

 

A.

The I/O statistics are only for the single block read and write operations.

B.

The I/O statistics are for single block or multi-block read and write operations.

C.

If there is a conflict of I/O functions, the I/O is placed in the bucket with the lower FUNCTION_ID.

D.

If there is a conflict of I/O functions, the I/O is placed in the bucket with the higher FUNCTION_ID.

 

Correct Answer: BC

 

QUESTION 30

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

 

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.