[Free] Download Latest (March 2016) Oracle 1Z0-062 Real Exam 31-40

Ensurepass

QUESTION 31

Which three features work together, to allow a SQL statement to have different cursors for the same statement based on different selectivity ranges?

 

A.

Bind Variable Peeking

B.

SQL Plan Baselines

C.

Adaptive Cursor Sharing

D.

Bind variable used in a SQL statement

E.

Literals in a SQL statement

 

Correct Answer: ACE

Explanation:

In bind variable peeking (also known as bind peeking), the optimizer looks at the value in a bind variable when the database performs a hard parse of a statement.

When a query uses literals, the optimizer can use the literal values to find the best plan. However, when a query uses bind variables, the optimizer must select the best plan without the presence of literals in the SQL text. This task can be extremely difficult. By peeking at bind values the optimizer can determine the selectivity of a WHERE clause condition as if literals had been used, thereby improving the plan.

C: Oracle 11g/12g uses Adaptive Cursor Sharing to solve this problem by allowing the server to compare the effectiveness of execution plans between executions with different bind variable values. If it notices suboptimal plans, it allows certain bind variable values, or ranges of values, to use alternate execution plans for the same statement. This functionality requires no additional configuration.

 

 

QUESTION 32

What happens if a maintenance window closes before a job that collects optimizer statistics completes?

 

A.

The job is terminated and the gathered statistics are not saved.

B.

The job is terminated but the gathered statistics are not published.

C.

The job continues to run until all statistics are gathered.

D.

The job is terminated and statistics for the remaining objects are collected the next time the maintenance window opens.

 

Correct Answer: D

 

 

QUESTION 33

Which three statements are true about adaptive SQL plan management?

 

A.

It automatically performs verification or evolves non-accepted plans, in COMPREHENSIVE mode when they perform better than existing accepted plans.

B.

The optimizer always uses the fixed plan, if the fixed plan exists in the plan baseline.

C.

It adds new, bettor plans automatically as fixed plans to the baseline.

D.

The non-accepted plans are automatically accepted and become usable by the optimizer if they perform better than the existing accepted plans.

E.

The non-accepted plans in a SQL plan baseline are automatically evolved, in COMPREHENSIVE mode, during the nightly maintenance window and a persistent verification report is generated.

 

Correct Answer: ADE

Explanation:

With adaptive SQL plan management, DBAs no longer have to manually run the verification or evolve process for non-accepted plans. When automatic SQL tuning is in COMPREHENSIVE mode, it runs a verification or evolve process for all SQL statements that have non-accepted plans during the nightly maintenance window. If the non-accepted plan performs better than the existing accepted plan (or plans) in the SQL plan baseline, then the plan is automatically accepted and becomes usable by the optimizer. After the verification is complete, a persistent report is generated detailing how the non-accepted plan performs compared to the accepted plan performance. Because the evolve process is now an AUTOTASK, DBAs can also schedule their own evolve job at end time.

Note:

The optimizer is able to adapt plans on the fly by predetermining multiple subplans for portions of the plan.

Adaptive plans, introduced in Oracle Database 12c, enable the optimizer to defer the final plan decision for a statement until execution time. The optimizer instruments its chosen plan (the default plan) with statistics collectors so that it can detect at runtime, if its cardinality estimates differ greatly from the actual number of rows seen by the operations in the plan. If there is a significant difference, then the plan or a portion of it will be automatically adapted to avoid suboptimal performance on the first execution of a SQL statement.

Reference: SQL Plan Management with Oracle Database 12c

 

 

QUESTION 34

Which two statements are true about extents?

 

A.

Blocks belonging to an extent can be spread across multiple data files.

B.

Data blocks in an extent are logically contiguous but can be non-contiguous on disk.

C.

The blocks of a newly allocated extent, although free, may have been used before.

D.

Data blocks in an extent are automatically reclaimed for use by other objects in a tablespaee when all the rows in a table are deleted.

 

Correct Answer: BC

 

 

QUESTION 35

You created an encrypted tablespace:

 

clip_image002

 

You then closed the encryption wallet because you were advised that this is secure.

 

Later in the day, you attempt to create the EMPLOYEES table in the SECURESPACE tablespace with the SALT option on the EMPLOYEE column.

 

Which is true about the result?

 

A.

It creates the table successfully but does not encrypt any inserted data in the EMPNAME column because the wallet must be opened to encrypt columns with SALT.

B.

It generates an error when creating the table because the wallet is closed.

C.

It creates the table successfully, and encrypts any inserted data in the EMPNAME column because the wallet needs to be open only for tablespace creation.

D.

It generates error when creating the table, because the salt option cannot be used with encrypted tablespaces.

 

Correct Answer: C

Explanation:

The environment setup for tablespace encryption is the same as that for transparent data encryption. Before attempting to create an encrypted tablespace, a wallet must be created to hold the encryption key.

Setting the tablespace master encryption key is a one-time activity. This creates the master encryption key for tablespace encryption. This key is stored in an external security module (Oracle wallet) and is used to encrypt the tablespace encryption keys.

Before you can create an encrypted tablespace, the Oracle wallet containing the tablespace master encryption key must be open. The wallet must also be open before you can access data in an encrypted tablespace.

Salt is a way to strengthen the security of encrypted data. It is a random string added to the data before it is encrypted, causing repetition of text in the clear to appear different when encrypted. Salt removes the one common method attackers use to steal data, namely, matching patterns of encrypted text.

ALT | NO SALT By default the database appends a random string, called “salt,” to the clear text of the column before encrypting it. This default behavior imposes some limitations on encrypted columns:

/ If you specify SALT during column encryption, then the database does not compress the data in the encrypted column even if you specify table compression for the table. However, the database does compress data in unencrypted columns and encrypted columns without the SALT parameter.

 

 

QUESTION 36

In a recent Automatic Workload Repository (AWR) report for your database, you notice a high number of buffer busy waits. The database consists of locally managed tablespaces with free list managed segments.On further investigation, you find that buffer busy waits is caused by contention on data blocks.Which option would you consider first to decrease the wait event immediately?

 

A.

Decreasing PCTUSED

B.

Decreasing PCTFREE

C.

Increasing the number of DBWN process

D.

Using Automatic Segment Space Management (ASSM)

E.

Increasing db_buffer_cache based on the V$DB_CACHE_ADVICE recommendation

 

Correct Answer: D

Explanation:

Automatic segment space management (ASSM) is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the pctused,freelists, and freelist groups storage parameters for schema objects created in the tablespace. If any of these attributes are specified, they are ignored.

Oracle introduced Automatic Segment Storage Management (ASSM) as a replacement for traditional freelists management which used one-way linked-lists to manage free blocks with tables and indexes. ASSM is commonly called “bitmap freelists” because that is how Oracle implement the internal data structures for free block management.

Note:

Buffer busy waits are most commonly associated with segment header contention onside the data buffer pool (db_cache_size, etc.).

The most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists (or ASSM), and adding missing indexes.

 

 

QUESTION 37

Which two statements are true?

 

A.

A role cannot be assigned external authentication.

B.

A role can be granted to other roles.

C.

A role can contain both system and object privileges.

D.

The predefined resource role includes the unlimited_tablespace privilege.

E.

All roles are owned by the sys user.

F.

The predefined connect role is always automatically granted to all new users at the time of their creation.

 

Correct Answer: BC

Explanation:

http://docs.oracle.com/cd/E11882_01/network.112/e36292/authorization.htm#DBSEG99878 (the functionality of roles)

 

 

QUESTION 38

You find this query being used in your Oracle 12c database:

 

clip_image004

 

Which method a used by the optimizer to limit the rows being returned?

 

A.

A filter is added to the table query dynamically using ROWNUM to limit the rows to 20 percent of the total rows

B.

All the rows are returned to the client or middle tier but only the first 20 percent are returned to the screen or the application.

C.

A view is created during execution and a filter on the view limits the rows to 20 percent of the total rows.

D.

A TOP-N query is created to limit the rows to 20 percent of the total rows

 

Correct Answer: C

 

 

QUESTION 39

Which three operations can be performed as multipartition operations in Oracle?

 

A.

Merge partitions of a list partitioned table

B.

Drop partitions of a list partitioned table

C.

Coalesce partitions of a hash-partitioned global index.

D.

Move partitions of a range-partitioned table

E.

Rename partitions of a range partitioned table

F.

Merge partitions of a reference partitioned index

 

Correct Answer: ABF

Explanation:

Multipartition maintenance enables adding, dropping, truncate, merge, split operations on multiple partitions.

A: Merge Multiple Partitions:

The new “ALTER TABLE … MERGE PARTITIONS ” help merge multiple partitions or subpartitions with a single statement. When merging multiple partitions, local and global index operations and semantics for inheritance of unspecified physical attributes are the same for merging two partitions.

B: Drop Multiple Partitions:

The new “ALTER TABLE … DROP PARTITIONS ” help drop multiple partitions or subpartitions with a single statement.

Example:

view plaincopy to clipboardprint?

SQL> ALTER TABLE Tab_tst1 DROP PARTITIONS

Tab_tst1_PART5, Tab_tst1_PART6, Tab_tst1_PART7;

Table altered

SQL>

Restrictions:

You can’t drop all partitions of the table.

If the table has a single partition, you will get the error: ORA-14083: cannot drop the only partition of a partitioned.

 

 

QUESTION 40

Which two statements are true about the use of the procedures listed in the v$sysaux_occupants.move_procedure column?

 

A.

The procedure may be used for some components to relocate component data to the SYSAUX tablespace from its current tablespace.

B.

The procedure may be used for some components to relocate component data from the SYSAUX tablespace to another tablespace.

C.

All the components may be moved into SYSAUX tablespace.

D.

All the components may be moved from the SYSAUX tablespace.

 

Correct Answer: AB

 

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

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