[Free] 2018(Jan) EnsurePass Testking Oracle 1z0-515 Dumps with VCE and PDF 21-30

Ensurepass.com : Ensure you pass the IT Exams
2018 Jan Oracle Official New Released 1z0-515
100% Free Download! 100% Pass Guaranteed!

Data Warehousing 11g Essentials

Question No: 21

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

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

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

  3. The child table must use a reference partition.

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

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.

Question No: 22

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

  1. Range Partitioning

  2. Automatic Storage Management (ASM)

  3. List Partitioning

  4. RAC

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

Reference: http://en.wikipedia.org/wiki/Automatic_Storage_Management

Question No: 23

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?

  1. Set RESULT_CACHE_MODE = FORCE in the initialization file.

  2. Set RESULT_CACHE = ENABLED in the initialization file.


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

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#39;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.


The ResultCache operator is added, only if you use the RESULT_CACHE hint in the SQL query.

Reference: http://www.globusz.com/ebooks/Oracle11g/00000014.htm

Question No: 24

Which is NOT an advantage provided by partitioning?

  1. Reduces storage requirements for tables

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

  3. Can improve performance by reducing I/O

  4. Provides added flexibility for maintenance operations

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.

Reference: http://www.dba-oracle.com/oracle_tips_partitioning.htm

Question No: 25

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

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

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

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

  4. None of these would cause a change.

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.

Reference: From oracle documentation


Question No: 26

What areas can SQL Access Advisor give advice on?

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

  2. Index advice and compression advice

  3. Index advice and data masking advice

  4. Partitioning advice and compression advice

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. Reference:


Question No: 27

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?

  1. 25 GB/s

  2. 18 GB/s

  3. 6 GB/s

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

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.

    Reference: http://docs.oracle.com/cd/E11882_01/server.112/e10578/tdpdw_system.htm#autoId2

    Question No: 28

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

    1. Partition pruning will occur

    2. Faster sorting

    3. Efficient parallel joins

    4. Efficient data loading

    5. Reduced disk usage

    Answer: A,C,D

    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.


http://www.databasejournal.com/features/oracle/article.php/3317621/A-Practical-Guide-to- Data-Warehousing-in-Oracle–Part-3.htm

Question No: 29

Exadata uses smart scans, which are executed in .

  1. Exadata Storage Server cells

  2. Database Server node memory

  3. Database Server node CPUs

  4. Exadata does not use smart scans.

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.

Reference: http://www.oracle.com/technetwork/issue-archive/2011/11-may/o31exadata- 354069.html

Question No: 30

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

  1. Local indexes

  2. Global partitioned indexes

  3. Global nonpartitioned indexes

  4. Both global partitioned and global nonpartitioned indexes

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#39;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.

Reference: OracleŚļź Database VLDB and Partitioning Guide, 11g Release 1 (11.1), 2

Partitioning Concepts


Local indexes are indexes create on each partition in a table. A local index automatically creates an index partition for each partition in the table. The index is partitioned by the same key as the partition key of the table.

A local index is always partitioned by the same partition key as the parent table. You cannot add or remove partitions in a local index, or in a global index for that matter. You

must add and remove partitions from the parent table. A local index does not need to include the partition key in the list of indexed columns.

Local indexes provide the best throughput of a query and are used primarily in OLAP and DSS type environments.

100% Ensurepass Free Download!
Download Free Demo:1z0-515 Demo PDF
100% Ensurepass Free Guaranteed!
1z0-515 Dumps

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No