Oracle 1Z0-515 Online Practice Questions and Exam Preparation

Oracle 1Z0-515 Online 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
  D. Locally attached and network-attached disk

  ---

  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).

• 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
  B. Data profiling

  ---

  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.

• 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
  B. Oracle OLAP Option cubes

  ---

  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.
  B. Use Advanced Compression Option.

  ---

  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

• 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
  A. 100 TB

  ---

  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:

  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.
  C. Respond positively to the Advisor to create the materialized views.

  ---

  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.

• Question 7:

  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
  C. Third normal form schema

  ---

  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.

• 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
  A. Partition pruning
  D. Partition-wise joins

  ---

  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.
  B. A data warehouse is typically implemented with a different design, making access faster.
  D. A data warehouse uses specialized features of the Oracle database, like materialized views and star transformations, making response faster.

  ---

  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.

• Question 10:

  Identify the statement about ASM that is NOT true.

  A. ASM is easier to manage than file systems.
  B. ASM delivers the performance of raw partitions.
  C. ASM is an extra cost option for Oracle databases.
  D. ASM delivers automatic striping and mirroring.
  B. ASM delivers the performance of raw partitions.

  ---

  explanation:

  ASM is a management tool, not a RAW performance tool.

  Note:

  Automatic Storage Management (ASM) is a new feature that has be introduced in Oracle 10g to simplify the storage of Oracle datafiles, controlfiles and logfiles.

  Automatic Storage Management (ASM) simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an

  extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems,

  along with OMF and manually managed files.

  The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight.

  The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are known as ASM files.

  The locations and names for the files are controlled by ASM, but user- friendly aliases and directory structures can be defined for ease of reference. The level of redundancy and the granularity of the striping can be controlled using templates.

  Default templates are provided for each file type stored by ASM, but additional templates can be defined as needed. Failure groups are defined within a disk group to support the required level of redundancy. For two- way mirroring you would

  expect a disk group to contain two failure groups so individual files are written to two locations.

  In summary ASM provides the following functionality:

  *Manages groups of disks, called disk groups.

  *Manages disk redundancy within a disk group.

  *Provides near-optimal I/O balancing without any manual tuning. *Enables management of database objects without specifying mount points and filenames.

  *Supports large files.