Oracle Others Oracle Certifications 1Z0-515 Questions & Answers

• Question 1:

  Flash in the Oracle Exadata Database Machine is of lower latency (and enables faster access to data) than:

  A. Memory

  B. Memory and locally attached disk

  C. Memory and network-attached disk

  D. Locally attached and network-attached disk

  Correct Answer: D

  Explanation:

  The Exadata Smart Flash Cache is also used to reduce the latency of log write I/O eliminating

  performance bottlenecks that might occur due to database logging.

  Flash memory has very good average write latency. It is faster than locally attached and network- attached

  disks. However, it is not faster than Memory (RAM).

  References:

• Question 2:

  Which feature of Oracle Warehouse Builder can be used to help ensure data quality?

  A. Data extraction

  B. Data profiling

  C. Logical mapping

  D. Exception reporting

  Correct Answer: B

  Explanation: After you connect to your data sources in Oracle Warehouse Builder (including Oracle databases, sources accessed through gateways, and flat file sources) you can apply full-featured data profiling to generate statistics about data quality, and to discover complex patterns, foreign key relationships, and functional dependencies. You can then design complex data rules and create data auditors to monitor compliance with those rules in any source or target system in your landscape, regardless of whether those sources are loaded using Oracle Warehouse Builder or other ETL tools.

  For customers who have selected solutions other than Oracle Warehouse Builder for data profiling and data quality, these can be applied independently of Oracle Warehouse Builder ETL and design features.

  Note: Oracle Warehouse Builder is a full-featured data integration, data warehousing, data quality and metadata management solution designed for the Oracle database. Oracle Warehouse Builder is an integral part of Oracle Database 11g Release 2 (11.2) and is installed as part of every database installation (other than Oracle Database XE).

  The major feature areas of Oracle Warehouse Builder include: *Data modeling *Extraction, Transformation, and Load (ETL) *Data profiling and data quality *Metadata management *Business-level integration of ERP application data *Integration with Oracle business intelligence tools for reporting purposes *Advanced data lineage and impact analysis Oracle Warehouse Builder is also an extensible data integration and data quality solutions platform. Oracle Warehouse Builder can be extended to manage metadata specific to any application, and can integrate with new data source and target types, and implement support for new data access mechanisms and platforms, enforce your organization's best practices, and foster the reuse of components across solutions.

  References:

• Question 3:

  The Analytic Workspace Manager would be used to generate_______.

  A. Materialized views

  B. Oracle OLAP Option cubes

  C. Oracle Data Mining algorithms

  D. Oracle SQL Analytic functions

  Correct Answer: B

  Explanation: You can use Analytic Workspace Manager for creating measures, dimensions and cubes in the OLAP database if the database was installed with the OLAP option. Workspace Manager, a feature of Oracle Database, enables application developers and DBAs to manage current, proposed and historical versions of data in the same database.

  Note: Applications and DBA operations often work with more than one version of the data. Three common reasons to have multiple data versions are concurrency, auditing and scenario creation. Oracle Workspace Manager provides workspaces as a virtual environment to isolate a collection of changes to production data, keep a history of changes to data and create multiple data scenarios for "what if" analysis. It can save money, time and labor over traditional approaches.

• Question 4:

  What would you do to compress data in partitions that are frequently updated in Oracle Database 11g?

  A. Use Hybrid Columnar Compression.

  B. Use Advanced Compression Option.

  C. Use Hybrid Partitions.

  D. Avoid compressing any data.

  Correct Answer: B

  Explanation:

  Advanced Compression features in Oracle Database 11g include:

  * Online Transaction Processing (OLTP) Table Compression: This breakthrough compression feature compresses table data during all types of data manipulation operations, including conventional INSERT or UPDATE. OLTP Table Compression leverages a sophisticated and intelligent algorithm that minimizes compression overhead during write operations, thereby making it viable for highly transactional workloads

  References:

• Question 5:

  A Full Rack Oracle Exadata Database Machine can contain 100 TB of disk space with high performance. How much user data should you conservatively estimate this configuration can hold?

  A. 100 TB

  B. 75 TB

  C. 50 TB

  D. 28 TB

  E. 20 TB

  Correct Answer: A

  Explanation:

  Without any redundancy (high performance is the only goal) you can use 100% of the disk space to data.

  With disk mirroring (with lower performance) you can use 50% of the disk space to data.

• Question 6:

  Your customer is looking to implement ad-hoc analysis in a data warehouse. Which approach is least likely to be used assuming that the customer does not want the expense of managing view?

  A. Star schema

  B. Snowflake schema

  C. Third normal form schema

  D. OLAP

  Correct Answer: C

  Explanation: Data warehouses often use denormalized or partially denormalized schemas (such as a star schema) to optimize query performance. On the other hand OLTP (Online Transaction Processing) systems often use fully normalized schemas to optimize update/insert/delete performance, and to guarantee data consistency.

  References:

• Question 7:

  You have analyzed your client's workload and the SQL Access Advisor in Enterprise Manager recommends that you create some materialized views to improve performance. What should you do in order to most simply implement this change?

  A. Rewrite all the queries in the application to identify materialized view

  B. Rewrite existing queries. New queries will automatically use the views.

  C. Respond positively to the Advisor to create the materialized views.

  D. Build virtual views on a third normal form schema.

  Correct Answer: C

  Explanation: Enterprise Manager provides a very simple interface for the SQL Access Advisor (Advisor Central > SQL Advisor >SQL Access Advisor). The first page allows you to create tasks to test existing indexes, materialized view and partitions, or create tasks to suggest new structures. The "Workload Source" page allows you to define the workload to associate with the task. The basic options allow the workload to be gathered from the cursor cache, an existing SQL tuning set, or a hypothetical workload based on specific schema objects. The "Recommendation Options" page allows you to define which type of recommendations you are interested in (Indexes, Materialized Views and Partitioning). After reviewing the result of the analysis you can decide if you should accept or ignore the suggested recommendations.

  Note: The SQL Access Advisor was introduced in Oracle 10g to make suggestions about additional indexes and materialized views which might improve system performance.

  References:

• Question 8:

  What are two ways in which query performance can be improved with partitioning?

  A. Partition pruning

  B. Partition optimization

  C. Partition compression

  D. Partition-wise joins

  Correct Answer: AD

  Explanation:

  A: Even when you don't name a specific partition in a SQL statement, the fact that a table is partitioned might still influence the manner in which the statement accesses the table. When a SQL statement accesses one or more partitioned tables, the Oracle optimizer attempts to use the information in the WHERE clause to eliminate some of the partitions from consideration during statement execution. This process, called partition pruning, speeds statement execution by ignoring any partitions that cannot satisfy the statement's WHERE clause. To do so, the optimizer uses information from the table definition combined with information from the statement's WHERE clause.

  D: A partition wise join is a join between (for simplicity) two tables that are partitioned on the same column with the same partitioning scheme. In shared nothing this is effectively hard partitioning locating data on a specific node / storage combo. In Oracle is is logical partitioning.

  If you now join the two tables on that partitioned column you can break up the join in smaller joins exactly along the partitions in the data. Since they are partitioned (grouped) into the same buckets, all values required to do the join live in the equivalent bucket on either sides. No need to talk to anyone else, no need to redistribute data to anyone else... in short, the optimal join method for parallel processing of two large data sets.

• Question 9:

  Which two statements are true about the advantages of using a data warehouse?

  A. A data warehouse uses fewer database structures, so access to answers is faster and easier

  B. A data warehouse is typically implemented with a different design, making access faster.

  C. A data warehouse is optimized for ongoing write activity, making response faster.

  D. A data warehouse uses specialized features of the Oracle database, like materialized views and star transformations, making response faster.

  Correct Answer: BD

  Explanation:

  Data warehouses often use denormalized or partially denormalized schemas (such as a star schema) to

  optimize query performance.

  Note: A materialized view is a pre-computed table comprising aggregated or joined data from fact and

  possibly dimension tables. Also known as a summary or aggregate table.

  References:

• Question 10:

  Identity the true statement about a data warehouse

  A. The data warehouse is typically refreshed as often as a transactional system,

  B. Data warehouse queries are simpler than OLTP queries.

  C. A data warehouse typically contains historical data.

  D. Queries against a data warehouse never need summarized information.

  Correct Answer: C

  Explanation: A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.