1Z0-117 Online Practice Questions and Answers

Correct Answer: ACD

Note: In-Memory Parallel Execution

When the parameter PARALLEL_DEGREE_POLICY is set to AUTO, Oracle Database decides if an object that is accessed using parallel execution would benefit from being cached in the SGA (also called the buffer cache). The decision to cache an object is based on a well-defined set of heuristics including the size of the object and frequency on which it is accessed. In an Oracle RAC environment, Oracle Database maps pieces of the object into each of the buffer caches on the active instances. By creating this mapping, Oracle Database automatically knows which buffer cache to access to find different parts or pieces of the object. Using this information, Oracle Database prevents multiple instances from reading the same information from disk over and over again, thus maximizing the amount of memory that can cache objects. If the size of the object is larger than the size of the buffer cache (single instance) or the size of the buffer cache multiplied by the number of active instances in an Oracle RAC cluster, then the object is read using direct-path reads.

Reference: Oracle Database VLDB and Partitioning Guide 11g, How Parallel Execution Works

Correct Answer: E

The Automatic Tuning Optimizer is meant to be used for complex and high-load SQL statements that have non-trivial impact on the entire system. The Automatic Database Diagnostic Monitor (ADDM) proactively identifies high-load SQL statements which are good candidates for SQL tuning.

Note:

* When SQL statements are executed by the Oracle database, the query optimizer is used to generate the execution plans of the SQL statements. The query

optimizer operates in two modes:

a normal mode and a tuning mode.

In normal mode, the optimizer compiles the SQL and generates an execution plan. The normal mode of the optimizer generates a reasonable execution plan for the vast majority of SQL statements. Under normal mode, the optimizer operates with very strict time constraints, usually a fraction of a second, during which it must find a good execution plan.

In tuning mode, the optimizer performs additional analysis to check whether the execution plan produced under normal mode can be improved further. The output of the query optimizer is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly superior plan. When running in the tuning mode, the optimizer is referred to as the Automatic Tuning Optimizer.

Reference: Oracle Database Performance Tuning Guide, Automatic SQL Tuning

Correct Answer: A

Note:

*

Before tracing can be enabled, the environment must first be configured to enable gathering of statistics.

*

(gather statistics): DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE

Enables statistic gathering for a given combination of Service Name, MODULE and ACTION

*

DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE

Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified.

dbms_monitor.serv_mod_act_trace_enable(

service_name IN VARCHAR2,

module_name IN VARCHAR2 DEFAULT ANY_MODULE,

action_name IN VARCHAR2 DEFAULT ANY_ACTION,

waits IN BOOLEAN DEFAULT TRUE,

binds IN BOOLEAN DEFAULT FALSE,

instance_name IN VARCHAR2 DEFAULT NULL,

plan_stat IN VARCHAR2 DEFAULT NULL);

SELECT instance_name

FROM gv$instance;

exec dbms_monitor.serv_mod_act_trace_enable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, TRUE, TRUE, 'orabase');

exec dbms_monitor.serv_mod_act_trace_disable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, 'orabase');

*

When solving tuning problems, session traces are very useful and offer vital information. Traces are simple and straightforward for dedicated server sessions,

but for shared server sessions, many processes are involved. The trace pertaining to the user session is scattered across different trace files belonging to different

processes. This makes it difficult to get a complete picture of the life cycle of a session.

Now there is a new tool, a command line utility called trcsess to help read the trace files. The trcsess command-line utility consolidates trace information from

selected trace files, based on specified criteria. The criteria include session id, client id, service name, action name and module name.

*

Once the trace files have been consolidated (with trcsess), tkprof can be run against the consolidated trace file for reporting purposes.

Correct Answer: ABCF

A: For a given user's session, ALTER SESSION can be used to disable or enable query rewrite for that session only.

B: Bitmap indexes on the join columns would improve performance.

C (not G) : One of the major benefits of creating and maintaining materialized views is the ability to take advantage of query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables.

F:

*

A materialized view is only eligible for query rewrite if the ENABLE QUERY REWRITE clause has been specified, either initially when the materialized view was first created or subsequently with an ALTER MATERIALIZED VIEW statement.

*

Enabling or disabling query rewrite: by the CREATE or ALTER statement for individual materialized views by the initialization parameter QUERY_REWRITE_ENABLED by the REWRITE and NOREWRITE hints in SQL statements

Note:

*

A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.

Correct Answer: D

Note:

* In the execution plan BX, note the keyword 'VIEW PUSHED PREDICATE' indicates that the view has undergone the join predicate pushdown transformation.

Correct Answer: C

Correct Answer: AB

B: PARALLEL_QUEUE_TIMEOUT Parallel Queue Timeout

When you use parallel statement queuing, if the database does not have sufficient resources to execute a parallel statement, the statement is queued until the

required resources become available. However, there is a chance that a parallel statement may be waiting in the parallel statement queue for longer than is

desired. You can prevent such scenarios by specifying the maximum time a parallel statement can wait in the parallel statement queue.

The PARALLEL_QUEUE_TIMEOUT directive attribute enables you to specify the maximum time, in seconds, that a parallel statement can wait in the parallel

statement queue before it is timed out. The PARALLEL_QUEUE_TIMEOUT attribute can be set for each consumer group.

Incorrect:

Not C: ETL_GROUP PARALLEL_TARGET_PERCENTAGE is 50%. So ETL_GROUP can only consume 32 servers.

Note:

* If you want more per-workload management, you must use the following directive attributes:

/ MGMT_Pn

Management attributes control how a parallel statement is selected from the parallel statement queue for execution. You can prioritize the parallel statements of one consumer group over another by setting a higher value for the management attributes of that group.

/ PARALLEL_TARGET_PERCENTAGE

/ PARALLEL_QUEUE_TIMEOUT

/ PARALLEL_DEGREE_LIMIT_P1

* PARALLEL_DEGREE_LIMIT_P1 Degree of Parallelism Limit

You can limit the maximum degree of parallelism for any operation within a consumer group. The degree of parallelism is the number of parallel execution servers that are associated with a single operation. Use the PARALLEL_DEGREE_LIMIT_P1 directive attribute to specify the degree of parallelism for a consumer group.

The degree of parallelism limit applies to one operation within a consumer group; it does not limit the total degree of parallelism across all operations within the consumer group. However, you can combine both the PARALLEL_DEGREE_LIMIT_P1 and the PARALLEL_TARGET_PERCENTAGE directive attributes to achieve the desired control.

* PARALLEL_TARGET_PERCENTAGE Parallel Target Percentage

It is possible for a single consumer group to launch enough parallel statements to use all the available parallel servers. If this happens, when a high-priority parallel statement from a different consumer group is run, no parallel servers are available to allocate to this group. You can avoid such a scenario by limiting the number of parallel servers that can be used by a particular consumer group.

Use the PARALLEL_TARGET_PERCENTAGE directive attribute to specify the maximum percentage of the parallel server pool that a particular consumer group can use. The number of parallel servers used by a particular consumer group is counted as the sum of the parallel servers used by all sessions in that consumer group.

Correct Answer: BE

As per exhibit:

B, not A, Not C: First is line 5 executed, followed by line 4, followed by line 3.

Step 2 is line 4.

E: The Department_ID column has lower cardinality compared to the JOB_ID column, so it is better suited for a bitmapped index.

Note:

*

Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table.

At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information.

*

The real benefit of bitmapped indexing occurs when one table includes multiple bitmapped indexes. Each individual column may have low cardinality. The creation of multiple bitmapped indexes provides a very powerful method for rapidly answering difficult SQL queries.

*

Oracle bitmap indexes are very different from standard b-tree indexes. In bitmap structures, a two-dimensional array is created with one column for every row in the table being indexed. Each column represents a distinct value within the bitmapped index. This two-dimensional array represents each value within the index multiplied by the number of rows in the table.

At row retrieval time, Oracle decompresses the bitmap into the RAM data buffers so it can be rapidly scanned for matching values. These matching values are delivered to Oracle in the form of a Row-ID list, and these Row-ID values may directly access the required information.

Correct Answer: DE

D: For parallel statements, the optimizer automatically decides whether to use dynamic sampling and which level to use. The decision depends on the size of the tables and the complexity of the predicates. The optimizer expects parallel statements to be resource-intensive, so the additional overhead at compile time is worth it to ensure the best plan. The database ignores the For serially processed SQL statements, the dynamic sampling level depends on the value of the OPTIMIZER_DYNAMIC_SAMPLING parameter and is not triggered automatically by the optimizer. Serial statements are typically short-running, so that any overhead at compile time could have a huge impact on their performance. the value is honored.

Reference: Oracle Database Administrator's Guide, About Oracle Database Resource Manager

Correct Answer: CD

D: DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation.

Online transaction processing (OLTP) and batch environments typically have values in the range of 4 to 16 for this parameter. DSS and data warehouse environments tend to benefit most from maximizing the value of this parameter. The optimizer is more likely to choose a full table scan over an index if the value of this parameter is high.

Note:

* See 6) and 7) below.

The oracle optimizer choose the best plan and execute the query according the plan. It is common to hear that my table has indexes but why oracle does not use

indexes rather it is using full table scan.

There are several reasons behind choosing optimizer full table scans.

1)The table has no indexes within it.

2)Table has indexes but they are not appropriate to queries. For example in the table there is normal B- tree indexes but in the query the column used in the

WHERE clause contains function.

3)Query access large amount of data. The table has indexes but query against it select almost all of the rows. In that case optimizer might choose to full access of

table.

4)Index creation order may not appropriate. You have composite indexes on a table but in the where clause the leading column inside indexes are not used rather

trailing columns are used.

5)The table is skewed. For example column gender contains value 'M' 10,000 times but value 'F' only 10 times.

6)The table is small. If a table can read in a single I/O call, then a full table scan might be cheaper than an index range scan. Single I/O call is defined by

DB_FILE_MULTIBLOCK_READ_COUNT parameter and value defined by blocks.

Check it by,

SQL> show parameter DB_FILE_MULTIBLOCK_READ_COUNT

NAME TYPE VALUE

------------------------------------ --------------------------------- ------------------------------ db_file_multiblock_read_count integer 16

7)High degree of parallelism. High degree of parallelism skews the optimizer toward full table scans.

8)In the query if there is no filtering then full table scan is the choice.

If an index has poor cardinality (ie. more than 4% rows with the same index key) *

then it will perform poorly. It will usually be faster to perform a full table scan. eg. Table SALES has an index on the column PAYMENT_METHOD which can

contain values such as COD, CREDIT, CHEQUE, CASH. The statement

SELECT *

FROM sales

WHERE payment_method = 'CASH'

will probably perform so badly that you are better off without the index.

* Oracle uses the full table scan as it assumes that it will have to read a certain part of the table.

Reference: Oracle Database Reference, DB_FILE_MULTIBLOCK_READ_COUNT