Oracle Others Oracle Certifications 1Z0-515 Questions & Answers

• Question 51:

  You want to create an optimally performing data warehouse hardware configuration for your customer. Which way of creating a hardware configuration will reduce the implementation time the most?

  A. Use reference configurations or an appliance-like configuration.

  B. Use the existing system and add on relevant components.

  C. Customize a configuration from a vendor.

  D. Build the system from scratch.

  Correct Answer: A

  Explanation: Oracle Optimized Warehouse Reference Configurations are best practice guides to choosing the right server, storage and networking components to build an Oracle data warehouse. These best practice guides encapsulate years of configuration expertise from Oracle and its partners, helping customers take the risk out of implementing a data warehouse.

  References:

• Question 52:

  The most performant way to load data from an external table that will also guarantee direct path loading is:

  A. Using Create Table as Select (CTAS)

  B. Using Data Pump

  C. Using Insert as Select (IAS)

  D. Using transparent gateways

  Correct Answer: A

  Explanation: CTAS refers to a CREATE TABLE AS statement - a new table is created and populated with row from a specified query.

  The most common uses of CTAS are in these scenarios:

  *

  Creating a table identical to another table in structure, but with a filter criteria applied to its data.

  *

  Creating a table with small structural differences from an existing table.

  For best performance use Direct Path Load. The conventional path uses standard insert statements whereas the direct path loader loads directly into the Oracle data files and creates blocks in Oracle database block format. During direct-path INSERT operations, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused, and referential integrity constraints are ignored. These procedures combined can enhance performance.

  References:

• Question 53:

  Which best describes Oracle's OLAP Option for Oracle Database 11g Release 2?

  A. Is stored as relational tables and is considered a ROLAP solution

  B. Uses bitmap indexes

  C. Physically stores OLAP cubes as objects within the relational database

  D. Is available both within the Oracle Database and as a stand-alone solution

  Correct Answer: A

  Explanation: Oracle OLAP is a world class multidimensional analytic engine embedded in Oracle Database 11g. Oracle OLAP cubes deliver sophisticated calculations using simple SQL queries - producing results with speed of thought response times. This outstanding query performance may be leveraged transparently when deploying OLAP cubes as materialized views enhancing the performance of summary queries against detail relational tables. Because Oracle OLAP is embedded in Oracle Database 11g, it allows centralized management of data and business rules in a secure, scalable and enterprise-ready platform.

• Question 54:

  What data can you compress using Advanced Compression in Oracle Database 11g?

  A. Read only data

  B. Data that can be updated, inserted and/or deleted (DML)

  C. Only data being archived

  D. Data warehousing data

  Correct Answer: B

  Explanation:

  Oracle Database 11g has new option named as Oracle Advanced Table Compression option which aims

  at reducing space occupied by data for both OLTP and warehouse databases. This option provides the

  following types of compression:

  *

  Compression of data tables even for OLTP environment. (Previous versions had compression option for tables that are mostly read only).

  *

  Compression of unstructured data in SecureFiles.

  *

  Compression of RMAN backups.

  *

  Compression in Data Pump Export files.

  *

  Compression of redo data transmitted to a standby database during redo gap resolution (when data guard is configured).

• Question 55:

  Identify the benefit of using bitmap join indexes. Select one.

  A. Faster query performance for all queries.

  B. Reduced space for indexes.

  C. Faster query performance for some queries.

  D. Lower memory usage.

  Correct Answer: B

  Explanation:

  Oracle benchmarks claim that bitmap join indexes can run a query more than eight times faster than

  traditional indexing methods.

  However, this speed improvement is dependent upon many factors, and the bitmap join is not a panacea.

  Some restrictions on using the bitmap join index include:

  The indexed columns must be of low cardinality--usually with less than 300 distinct values. The query must

  not have any references in the WHERE clause to data columns that are not contained in the index.

  The overhead when updating bitmap join indexes is substantial. For practical use, bitmap join indexes are

  dropped and rebuilt each evening about the daily batch load jobs. This means that bitmap join indexes are

  useful only for Oracle data warehouses that remain read-only during the processing day.

  References:

• Question 56:

  What is the difference between an ETL (Extraction Transformation Load) approach and an ELT (Extraction Load Transformation) approach to data integration? Select one.

  A. ETL can operate between heterogeneous data sources.

  B. ELT requires a separate transformation server.

  C. ELT transforms data on the target server.

  D. ELT cannot be used for incremental data loading.

  Correct Answer: C

  Explanation:

  There are two approaches to consider for data integration: ELT and ETL. The difference between ETL and ELT lies in the environment in which the data transformations are applied. In traditional ETL, the transformation takes place when the data is en route from the source to the target system. In ELT, the data is loaded into the target system, and then transformed within the target system environment.

  References:

• Question 57:

  You are looking for some general design principles that could be used in designing every large scale data warehouse you create. Identify the principle that would have the widest applicability.

  A. Partition your tables appropriately to produce partition-wise joins.

  B. Always use a star schema or snowflake schema design.

  C. Do as much analytics as possible in your BI tools.

  D. Always use Oracle OLAP.

  Correct Answer: A

  Explanation:

  Partition-wise joins can be full or partial. Oracle decides which type of join to use. A full partition-wise join

  divides a large join into smaller joins between a pair of partitions from the two joined tables. To use this

  feature, you must equipartition both tables on their join keys, or use reference partitioning.

  Oracle Database can perform partial partition-wise joins only in parallel. Unlike full partition-wise joins,

  partial partition-wise joins require you to partition only one table on the join key, not both tables.

  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.

  References:

• Question 58:

  Why does partitioning help parallelism with RAC?

  A. The ability to do partition-wise joins reduces interconnect traffic.

  B. Partitioning allows you to split data storage across nodes.

  C. Partitioning reduces storage requirements.

  D. RAC will spawn additional parallel servers to meet the needs of requesting applications.

  Correct Answer: A

  Explanation:

  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.

  References:

• Question 59:

  Your customer wants to implement an ILM strategy. The customer must have which option when deploying Oracle's ILM Assistant to implement this strategy?

  A. RAC

  B. Partitioning

  C. OLAP

  D. Oracle Clusterware

  Correct Answer: B

  Explanation: Information Lifecycle Management (ILM) is a set of policies and procedures for managing data during its lifetime. The ILM Assistant manages information by recommending the correct placement of data on logical storage tiers as specified by a lifecycle definition, where a lifecycle definition describes the stages and storage tiers that data resides on during its lifetime. Each stage specifies a retention period during which the data resides on a logical storage tier. A logical storage tier is a collection of Oracle tablespaces in which partitions may reside.

  Note: Information today comes in a wide variety of types, for example an E-mail message, a photograph, or an order in an Online Transaction Processing System. Therefore, once you know the type of data and how it will be used, you already have an understanding of what its evolution and final destiny is likely to be.

  One of the challenges facing each organization is to understand how its data evolves and grows, monitor how its usage changes over time, and decide how long it should survive, while adhering to all the rules and regulations that now apply to that data. Information Lifecycle Management (ILM) is designed to address these issues, with a combination of processes, policies, software, and hardware so that the appropriate technology can be used for each stage in the lifecycle of the data.

  References:

• Question 60:

  Identify the benefit of using interval partitioning.

  A. Automatic creation of new partitions based on hash values

  B. Automatic creation of new partitions based on the value of data being entered

  C. Improved performance compared to range partitions

  D. Automatic transfer of older partitions lower cost storage

  Correct Answer: B

  Explanation:

  Interval Partitioning was introduced in 11g, interval partitions are extensions to range partitioning. These

  provide automation for equi-sized range partitions. Partitions are created as metadata and only the start

  partition is made persistent. The additional segments are allocated as the data arrives. The additional

  partitions and local indexes are automatically created.

  References: