Oracle 1Z0-515 Online Practice Questions and Exam Preparation

Oracle 1Z0-515 Online Questions & Answers

• Question 21:

  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.
  A. The ability to do partition-wise joins reduces interconnect traffic.

  ---

  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.

• Question 22:

  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
  B. Data that can be updated, inserted and/or deleted (DML)

  ---

  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 23:

  Which feature would enable higher availability during maintenance operations while also improving query response performance?

  A. Partitioning
  B. Materialized views
  C. Bitmap Indexing
  D. OLAP
  A. Partitioning

  ---

  Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides a rich variety of partitioning strategies and extensions to address every business requirement. Moreover, since it is entirely transparent, partitioning can be applied to almost any application without the need for potentially expensive and time consuming application changes.

• Question 24:

  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.
  A. Partition your tables appropriately to produce partition-wise joins.

  ---

  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.

• Question 25:

  How many Exadata Storage Server cells can be used in a grid?

  A. 7
  B. 14
  C. 128
  D. No practical limit
  D. No practical limit

  ---

  There is no practical limit to number of cells that can be in the grid.

• Question 26:

  Your BI tool (for example, Oracle Business Intelligence Enterprise Edition Cognos) will be used to query an Oracle database that includes the Oracle BI tool generate in submitting queries that might include data stored in cubes?

  A. SQL
  B. PIVSQL
  C. Proprietary API code
  D. SQL for relational and proprietary API code for OLAP
  A. SQL

  ---

  Oracle Business Intelligence Enterprise Edition is most commonly used with the Oracle Database using SQL as the query language. Although the OLAP cube is a multidimensional data type, it is represented in the Oracle database as a

  collection of relational views and is easily queried by SQL. Note #1: The wording of the question is strange. SQL can be used and is the first choice. So it seems to be the best answer.

  Note #2: Oracle Business Intelligence Enterprise Edition (OBI EE) is a product suite based on the OBI EE Server. The OBI EE Server can map a logical business model to many different physical data sources and present the logical model for

  query to variety of client applications including Interactive Dashboards, Answers and Oracle Business Intelligence Plug-in for Microsoft Office.

• Question 27:

  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
  A. Reduces storage requirements for tables

  ---

  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.

• Question 28:

  You want partitions to be automatically created when data that does not fit into current date range loaded. Which type of partitioning would you implement?

  A. Hash
  B. List
  C. Invisible
  D. Interval
  D. Interval

  ---

  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.

  Note: Partitioning is one of the most sought after options for data warehousing. Almost all Oracle data warehouses use partitioning to improve the performance of queries and also to ease the day-to-day maintenance complexities. Starting with 11G, more partitioning options have been provided and these should reduce the burden of the DBA to a great extent.

• Question 29:

  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.

  ---

  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 30:

  Your customer wants to determine "market baskets." What do you recommend?

  A. Use Oracle OLAP Option.
  B. Use Oracle SQL Analytic Functions.
  C. Use associations algorithm in Oracle Data Mining.
  D. Use regression analysis in Oracle Data Mining
  C. Use associations algorithm in Oracle Data Mining.

  ---

  Association is a data mining function that discovers the probability of the co-occurrence of items in a collection. The relationships between co-occurring items are expressed as association rules.

  Market-Basket Analysis

  Association rules are often used to analyze sales transactions. For example, it might be noted that customers who buy cereal at the grocery store often buy milk at the same time. In fact, association analysis might find that 85% of the

  checkout sessions that include cereal also include milk. This relationship could be formulated as the following rule.

  Cereal implies milk with 85% confidence

  This application of association modeling is called market-basket analysis. It is valuable for direct marketing, sales promotions, and for discovering business trends. Market-basket analysis can also be used effectively for store layout, catalog

  design, and cross-sell.

  Association Algorithm

  Oracle Data Mining uses the Apriori algorithm to calculate association rules for items in frequent itemsets.