Oracle 1Z0-515 Online Practice Questions and Exam Preparation

Oracle 1Z0-515 Online Questions & Answers

• Question 1:

  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

  ---

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

  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?

  A. 25 GB/s
  B. 18 GB/s
  C. 6 GB/s
  D. It depends on how many processors are in the servers.
  C. 6 GB/s

  ---

  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.

• Question 3:

  You will be implementing a data warehouse for one of your customers. In your design process, which index type is most likely to be used to improve the performance of some queries where the data is of low cardinality?

  A. Bitmap indexes
  B. B*-tree indexes
  C. Reverse indexes
  D. Invisible indexes
  A. Bitmap indexes

  ---

  Bitmap indexes are a highly compressed index type that tends to be used primarily for data warehouses. Characteristic of Bitmap Indexes

  *

  For columns with very few unique values (low cardinality)

  *

  Columns that have low cardinality are good candidates (if the cardinality of a column is <= 0.1 % that the column is ideal candidate, consider also 0.2% 1%)

  *

  Tables that have no or little insert/update are good candidates (static data in warehouse)

  *

  Stream of bits: each bit relates to a column value in a single row of table

• Question 4:

  Which can be used in scenario where there are large data loads of a sensitive nature into a data warehouse?

  A. Direct path loading
  B. External tables for loading flat files
  C. Partition exchange loading
  D. Any of these are valid for certain situations.
  A. Direct path loading

  ---

  Instead of filling a bind array buffer and passing it to the Oracle database with a SQL INSERT statement, a direct path load uses the direct path API to pass the data to be loaded to the load engine in the server. The load engine builds a column array structure from the data passed to it. The direct path load engine uses the column array structure to format Oracle data blocks and build index keys. The newly formatted database blocks are written directly to the database (multiple blocks per I/O request using asynchronous writes if the host platform supports asynchronous I/O).

  Internally, multiple buffers are used for the formatted blocks. While one buffer is being filled, one or more buffers are being written if asynchronous I/O is available on the host platform. Overlapping computation with I/O increases load performance.

  http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_modes.htm#i1008815

• Question 5:

  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
  A. Using Create Table as Select (CTAS)

  ---

  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.

• Question 6:

  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.
  C. A data warehouse typically contains historical data.

  ---

  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.

• Question 7:

  What two types of results can be cached in the Result Set Cache?

  A. Results of an SQL query
  B. Results from a PL/SQL function
  C. Sequence object results
  D. Result sets derived from data dictionary tables
  A. Results of an SQL query
  B. Results from a PL/SQL function

  ---

  Your applications sometime send repetitive queries to the database. To improve the response time of repetitive queries, results of queries, query fragments, and PL/SQL functions can be cached in memory. A result cache stores the results of queries shared across all sessions. When these queries are executed repeatedly, the results are retrieved directly from the cache memory.

  You must annotate a query or query fragment with a result cache hint to indicate that results are to be stored in the query result cache. The query result set can be cached in the following ways:

  *

  Server-side Cache

  *

  Client Result Cache

  Oracle Database 11g Release 1 (11.1) provides support for server-side Result Set caching for both JDBC types. The server-side result cache is used to cache the results of the current queries, query fragments, and PL/SQL functions in memory and then to use the cached results in future executions of the query, query fragment, or PL/SQL function. The cached results reside in the result cache memory portion of the SGA. A cached result is automatically invalidated whenever a database object used in its creation is successfully modified. The server-side caching can be of the following two types:

  *

  SQL Query Result Cache (A)

  *

  PL/SQL Function Result Cache (B)

• Question 8:

  Which unique method of improving performance is NOT used by the Oracle Exadata Database Machine?

  A. Flash to improve query performance
  B. Reduces the amount of data required to flow through I/O
  C. Increases the I/O using InfiniBand
  D. Performs analysis in a special in-memory database
  D. Performs analysis in a special in-memory database

• Question 9:

  For which type of query is the SQL result cache automatically disabled?

  A. Queries that access data which changes frequently
  B. Queries that return large amounts of data
  C. Queries that use SQL functions such as SYSDATE
  D. Queries that are used infrequently
  C. Queries that use SQL functions such as SYSDATE

  ---

  SYSDATE produces a new value every time it is used. Caching such a value would make no sense.

• Question 10:

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

  A. SQL result sets age out of the Result Set Cache based on the KEEP parameter.
  B. SQL result sets are invalidated in the Result Set Cache after DML is performed against any of tables in the SQL query.
  C. SQL result sets are pinned in the Result Set Cache with the KEEP parameter.
  D. None of these would cause a change.
  B. SQL result sets are invalidated in the Result Set Cache after DML is performed against any of tables in the SQL query.

  ---

  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.