Oracle Others Oracle Certifications 1Z0-515 Questions & Answers

• Question 41:

  Identify the indexing technique you would use to minimize partition maintenance.

  A. Local indexes

  B. Global partitioned indexes

  C. Global nonpartitioned indexes

  D. Both global partitioned and global nonpartitioned indexes

  Correct Answer: A

  Explanation: If your priority is manageability, use a local index. Local partitioned indexes are easier to manage than other types of partitioned indexes. They also offer greater availability and are common in DSS environments. The reason for this is equipartitioning: each partition of a local index is associated with exactly one partition of the table. This enables Oracle to automatically keep the index partitions in sync with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data invalid or unavailable only affect a single partition.

  Local partitioned indexes support more availability when there are partition or subpartition maintenance operations on the table. A type of index called a local nonprefixed index is very useful for historical databases. In this type of index, the partitioning is not on the left prefix of the index columns. References:

• Question 42:

  Exadata uses smart scans, which are executed in________.

  A. Exadata Storage Server cells

  B. Database Server node memory

  C. Database Server node CPUs

  D. Exadata does not use smart scans.

  Correct Answer: A

  Explanation: The Oracle Exadata Database Machine brings database performance to a whole new level, but have you ever wondered what exactly makes it so fast? Several components of the Oracle Exadata Database Machine, such as Oracle Database 11g Release 2; Oracle Exadata's Smart Flash Cache, Hybrid Columnar Compression, and SmartScan features; and InfiniBand interconnect, help deliver high performance. One of the key technologies that supports this performance is the storage index, which is not a regular database index. Storage indexes reside in the memory of the storage servers--also called storage cells--and significantly reduce unnecessary I/O by excluding irrelevant database blocks in the storage cells.

  Oracle Exadata I/O and Smart Scan Storage in Oracle Exadata changes query processing so that not all blocks have to go to the database server for that server to determine which rows might satisfy a query. Oracle Exadata's Smart Scan feature enables certain types of query processing to be done in the storage cell. With Smart Scan technology, the database nodes send query details to the storage cells via a protocol known as iDB (Intelligent Database). With this information, the storage cells can take over a large portion of the data- intensive query processing. Oracle Exadata storage cells can search storage disks with added intelligence about the query and send only the relevant bytes, not all the database blocks, to the database nodes--hence the term smart scan.

  References:

• Question 43:

  What are three advantages provided by proper partitioning in a data warehouse?

  A. Partition pruning will occur

  B. Faster sorting

  C. Efficient parallel joins

  D. Efficient data loading

  E. Reduced disk usage

  Correct Answer: ACD

  Explanation:

  There are three major advantages of partitioning.

  *

  Partition Pruning - Oracle only accesses a limited set of table partitions if the FROM and WHERE clause

  permit it to.

  *

  Partition-wise Joins - Where two tables that have compatible partitioning schemes are joined , Oracle

  improves the efficiency of parallel operations by performing the join between individual partitions of the

  tables.

  *

  Manageability - Partitioning allows DDL operations on a large subset of table rows with some element of

  commonality defined through the partitioning type.

  References:

• Question 44:

  What areas can SQL Access Advisor give advice on?

  A. Partitioning advice, index advice, and materialized views advice

  B. Index advice and compression advice

  C. Index advice and data masking advice

  D. Partitioning advice and compression advice

  Correct Answer: A

  Explanation:

  The SQL Access Advisor was introduced in Oracle 10g to make suggestions about additional indexes and

  materialized views which might improve system performance. Oracle 11g has made two significant

  changes to the SQL Access Advisor:

  The advisor now includes advice on partitioning schemes that may improve performance. The original

  workload manipulation has been deprecated and replaced by SQL tuning sets.

  References:

• Question 45:

  You are looking to size a data warehouse configuration. If the I/O throughput for the CPUs is 25 GB/s, the I/O throughput for the HBA is 18 GB/s, and the I/O throughput for the disk subsystem is 6 GB/s, what is the overall throughput of the data warehouse?

  A. 25 GB/s

  B. 18 GB/s

  C. 6 GB/s

  D. It depends on how many processors are in the servers.

  Correct Answer: C

  Explanation:

  In this scenario the disk subsystem is the bottleneck. It determines the throughput.

  Note: Each of the components must provide sufficient I/O bandwidth to ensure a well-balanced I/O system.

  The end-to-end I/O system consists of more components than just the CPUs and disks. A well-balanced I/

  O system must provide approximately the same bandwidth across all components in the I/O system.

  These components include:

  *

  Host bus adapters (HBAs), the connectors between the server and the storage.

  *

  Switches, in between the servers and a storage area network (SAN) or network attached storage (NAS).

  *

  Ethernet adapters for network connectivity (GigE NIC or Infiniband). In an Oracle Real Application Clusters (Oracle RAC) environment, you need an additional private port for the interconnect between the nodes that you should not include when sizing the system for I/O throughput. The interconnect must be sized separately, taking into account factors such as internode parallel execution.

  *

  Wires that connect the individual components.

  References:

• Question 46:

  Which condition can cause a change in the contents of the SQL Result Set Cache?

  A. SQL result sets age out of the Result Set Cache based on the KEEP parameter.

  B. SQL result sets are invalidated in the Result Set Cache after DML is performed against any of tables in the SQL query.

  C. SQL result sets are pinned in the Result Set Cache with the KEEP parameter.

  D. None of these would cause a change.

  Correct Answer: B

  Explanation:

  The database automatically invalidates a cached result whenever a transaction modifies the data or

  metadata of any of the database objects used to construct that cached result.

  Note: DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete,

  insert and update data in database. References:

• Question 47:

  Which is NOT an advantage provided by partitioning?

  A. Reduces storage requirements for tables

  B. Can add to the benefits of parallelism through parallel partition-wise joins

  C. Can improve performance by reducing I/O

  D. Provides added flexibility for maintenance operations

  Correct Answer: A

  Explanation:

  Table storage requirements would increase, but the benefits are huge.

  Oracle partitioning is a divide-and-conquer approach to improving Oracle maintenance and SQL performance. Anyone with un-partitioned databases over 500 gigabytes is courting disaster. Databases become unmanageable, and serious problems occur:

  *

  SQL may perform poorly - Without Oracle partitioning, SQL queries with full-table scans take hours to complete. In a full scan, the smaller the Oracle partition, the faster the performance. Also, index range scans become inefficient.

  *

  Recovery - Files recovery takes days, not minutes

  *

  Maintenance - Rebuilding indexes (important to re-claim space and improve performance)

  Oracle partitioning has many benefits to improve performance and manageability:

  *

  Stable

  *

  Robust

  *

  Faster backups

  *

  Less overhead

  *

  Easier management Maintenance of Oracle partitioned tables is improved because maintenance can be focused on particular portions of tables. For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks. (not D)

  *

  Faster SQL Oracle is partition-aware, and some SQL may improve is speed by several orders of magnitude (over 100x faster).

  -Index range scans Oracle partitioning physically sequences rows in index-order causing a dramatic improvement (over 10x faster) in the speed of partition-key scans.

  -Full-table scans Oracle partition pruning only accesses those data blocks required by the query.

  -Table joins Oracle partition-wise joins take the specific sub-set of the query partitions, causing huge speed improvements on nested loop and hash joins. (not C)

  -You can also improve the performance of massive join operations when large amounts of data (for example, several million rows) are joined together by using partition-wise joins. (not B)

  -Updates Oracle parallel query for partitions improves batch load speed.

  References:

• Question 48:

  You want to enable result set caching to quickly see if this feature will help the performance of your application. Which is the quickest way to make this determination?

  A. Set RESULT_CACHE_MODE = FORCE in the initialization file.

  B. Set RESULT_CACHE = ENABLED in the initialization file.

  C. Set RESULT_CACHE_MAX_SIZE = 0.

  D. Set RESULT_CACHE = ENABLED in the initialization file and use a RESULT_CACHE hint in queries.

  Correct Answer: A

  Explanation: The RESULT_CACHE_MODE initialization parameter determines the SQL query result cache mode. The parameter specifies when a ResultCache operator is spliced into a query's execution plan. The parameter accepts the following values: FORCE The ResultCache operator is added to the root of all SELECT statements, if that is possible. However, if the statement contains a NO_RESULT_CACHE hint, then the hint takes precedence over the parameter setting. MANUAL The ResultCache operator is added, only if you use the RESULT_CACHE hint in the SQL query.

  References:

• Question 49:

  What would you use to evenly distribute data across the disk in your Oracle data warehouse?

  A. Range Partitioning

  B. Automatic Storage Management (ASM)

  C. List Partitioning

  D. RAC

  Correct Answer: B

  Explanation: Automatic Storage Management (ASM) is a feature provided by Oracle Corporation within the Oracle Database from release Oracle 10g (revision 1) onwards. ASM aims to simplify the management of database files. To do so, it provides tools to manage file systems and volumes directly inside the database, allowing database administrators (DBAs) to control volumes and disks with familiar SQL statements in standard Oracle environments. Thus DBAs do not need extra skills in specific file systems or volume managers (which usually operate at the level of the operating system).

  With ASM:

  *

  IO channels can take advantage of data striping and software mirroring

  *

  DBAs can automate online redistribution of data, along with the addition and removal of disks/storage

  *

  the system maintains redundant copies and provides 3rd-party[citation needed] RAID functionality

  *

  Oracle supports third-party multipathing IO technologies (such as failover or load balancing to SAN access) the need for hot spares diminishes

  References:

• Question 50:

  Which statement is true for you to get the benefits of partition-wise joins?

  A. The parent table must be partitioned on the join Key and the child table must be partitioned on] the join key.

  B. The parent table must be partitioned on the primary key and the child table must be partition the join key.

  C. The child table must use a reference partition.

  D. The parent table must be partitioned on the primary key and the child table must use a ref partition.

  Correct Answer: A

  Explanation:

  Note:

  Partition-wise joins reduce query response time by minimizing the amount of data exchanged among

  parallel execution servers when joins execute in parallel. This significantly reduces response time and

  improves the use of both CPU and memory resources. In Oracle Real Application Clusters (RAC)

  environments, partition-wise joins also avoid or at least limit the data traffic over the interconnect, which is

  the key to achieving good scalability for massive join operations. Partition-wise joins can be full or partial.

  Oracle decides which type of join to use.