Oracle Oracle Certifications 1Z0-070 Questions & Answers

• Question 21:

  Which two options should be used to identify a damaged or failing flash card on an X6 Database Machine high-capacity storage server?

  A. Using the CELLCLI CALIBRATE command on the storage server after logging in as the root user

  B. Using the CELLCLI LIST LUN DETAIL command as the celladmin user

  C. Using the CELLCLI CALIBRATE command on the storage server after logging in as the celladmin user

  D. Hardware monitoring using the storage server ILOM

  Correct Answer: AD

  Explanation:

  The CALIBRATE command runs raw performance tests on cell disks, enabling you to verify the disk

  performance before the cell is put online. You must be logged on to the cell as the root user to run

  CALIBRATE.

  Oracle ILOM automatically detects system hardware faults and environmental conditions on the server. If a

  problem occurs on the server, Oracle ILOM will automatically do the following:

  Illuminate the Service Required status indicator (LED) on the server front and back panels.

  Identify the faulted component in the Open Problems table.

  Record system information about the faulted component or condition in the event log.

  

  References: http://docs.oracle.com/cd/E80920_01/SAGUG/exadata-storage-server-cellcli.htm#SAGUG20617

• Question 22:

  Which two must be true for a Smart Scan to occur on a table?

  A. Sessions querying the table must set cell_offload_processing = true.

  B. It must be stored in an ASM diskgroup with a 4 MByte AU size.

  C. It must be heap organized.

  D. It must be accessed using direct path reads.

  E. The table must not be compressed.

  Correct Answer: AD

• Question 23:

  Which three must be true for a Smart Scan to occur for a table?

  A. cell_offload_processing must be true in sessions issuing SQL statements that access the table.

  B. The query must be executed serially.

  C. The query must be executed in parallel.

  D. Direct path reads must be used at run time.

  E. The ASM diskgroup containing the table's tablespace must have a 4 Mbyte AU size.

  F. The ASM diskgroup containing the table's tablespace must have cell.smart_scan_capable set to true.

  Correct Answer: ADF

  Explanation:

  A: pt_param( ‘cell_offload_processing’ ’TRUE’) hint is used to enable the exadata smart scan feature in sql execution process.

  D: Direct reads bypass the buffer cache and go directly into the process PGA. Cell offload operations occur for direct reads only.

  F: The cell.smart_scan_capable attribute must be set to TRUE.

  Incorrect Answers:

  B, C: The query can run either serially or in parallel.

  

  References: http://blog.tanelpoder.com/2013/05/29/forcing-smart-scans-on-exadata-is-_serial_direct_read-parameter-safe-to-use-in-production/

• Question 24:

  Which four are true about Exadata features?

  A. Storage Indexes persist across Exadata storage server reboots.

  B. Data Warehouse workloads will benefit from Smart Flash Cache configured in Write-Through mode.

  C. Hybrid Columnar Compressed tables can be compressed and decompressed on Exadata storage servers.

  D. Hybrid Columnar Compressed tables can be compressed and decompressed on the database servers.

  E. OLTP workloads will benefit from Smart Flash Cache configured in Write-Back mode.

  F. Storage Indexes persist across database server reboots.

  Correct Answer: CDEF

  Explanation:

  CD: That data remains compressed not only on disk, but also remains compressed in the Exadata Smart

  Flash Cache, on Infiniband, in the database server buffer cache, as well as when doing back-ups or log

  shipping to Data Guard.

  E (not B): Use the Write-Back Flash Cache feature to leverage the Exadata Flash hardware and make

  Exadata Database Machine a faster system for Oracle Database Deployments. Write-through cache mode

  is slower than write-back cache mode. However, write-back cache mode has a risk of data loss if the

  Exadata Storage Server loses power or fails.

  F: Storage indexes are not stored on disk; they are resident in the memory of the storage cell servers.

  Incorrect Answers:

  A: Storage indexes are not stored on disk; they are resident in the memory of the storage cell servers. They are created automatically after the storage cells receive repeated queries—with predicates-for columns. No user intervention is needed to create or maintain storage indexes. And because they are memory-resident structures, they disappear when the storage cells are rebooted.

  References:

• Question 25:

  You issued these commands to all Exadata Storage Servers in an X6 Exadata Database Machine using dcli:

  alter iormplan objective = low_latencyalter iormplan active

  There are no database or category plans defined.

  You are encountering disk I/O performance problems at certain times, which vary by day and week.

  DSS and Batch workloads perform well some of the time.

  Further investigation shows that at times, the workloads are all OLTP I/Os, at other times all batch I/Os, and sometimes a bit of each.

  You wish to have disk I/O managed so that performance will be optimized for all workloads.

  Which statements would you issue to all Exadata Storage Servers to achieve this?

  A. alter iormplan objective=high_throughput

  B. alter iormplan objective=balanced

  C. alter iormplan objective=low_latency

  D. alter iormplan objective=auto

  E. alter iormplan objective=‘ ’

  Correct Answer: D

  Explanation:

  The supported IORM objectives are auto, low_latency, balanced, and high_throughput. The recommended

  objective option is auto which allows IORM to continuously monitor the workloads, and select the best

  mode based on the active workloads currently on the cells.

  References: http://docs.oracle.com/cd/E80920_01/SAGUG/exadata-storage-server-iorm.htm

• Question 26:

  You plan to migrate a database supporting both DSS and OLTP workloads to your new X5 Database Machine.

  The workloads contain many complex aggregating functions and expensive joins on large partitioned tables in the DSS workload and indexed access for OLTP workloads.

  Which three benefits accrue as a result of this migration?

  A. Superior compression capability designed specifically for OLTP workloads

  B. Columnar storage capability for data in row major data blocks that is held in flash cache

  C. Superior compression capability designed specifically for data warehouse tables

  D. Superior compression capability designed specifically for archival data

  E. Superior flash cache compression technique

  F. Cell offload processing for indexed-organized table access

  Correct Answer: ACD

  Explanation:

  There are 6 different kinds of Table Data Compression methods:

  1.

  BASIC compression, introduced in Oracle 8 already and only recommended for Data Warehouse

  2.

  OLTP compression, introduced in Oracle 11 and recommended for OLTP Databases as well

  3.

  QUERY LOW compression (Exadata only), recommended for Data Warehouse with Load Time as a critical factor

  4.

  QUERY HIGH compression (Exadata only), recommended for Data Warehouse with focus on Space Saving

  5.

  ARCHIVE LOW compression (Exadata only), recommended for Archival Data with Load Time as a critical factor

  6.

  ARCHIVE HIGH compression (Exadata only), recommended for Archival Data with maximum Space Saving

  Incorrect Answers:

  B:

  1.

  BASIC compression, introduced in Oracle 8 already and only recommended for Data Warehouse

  2.

  OLTP compression, introduced in Oracle 11 and recommended for OLTP Databases as well

  3.

  QUERY LOW compression (Exadata only), recommended for Data Warehouse with Load Time as a critical factor

  4.

  QUERY HIGH compression (Exadata only), recommended for Data Warehouse with focus on Space Saving

  5.

  ARCHIVE LOW compression (Exadata only), recommended for Archival Data with Load Time as a critical factor

  6.

  ARCHIVE HIGH compression (Exadata only), recommended for Archival Data with maximum Space Saving

  Method 1 and 2 are working by compressing the rows inside of Oracle Blocks – the more redundant values inside the block, the better the compression ratio. OLTP compression might be used with Exadata Database Machine, because Exadata V2 is designed to support OLTP as well as Data Warehouse. Methods 3 to 6 use Hybrid Columnar Compression (HCC), which is designed to achieve better compression ratios by organizing sets of rows into compression units and then detaching the columns from the rows and collecting them. It is more likely that the same columns store similar (redundant) data, therefore the compression ratio improves with HCC.

  References: https://uhesse.com/2011/01/21/exadata-part-iii-compression/

• Question 27:

  Which three statements are true about Automatic Hard Disk Scrubbing and repair on high-capacity storage servers in an X5 Database Machine?

  A. Disk repair is made immediately from Smart Flash Cache for corruption on disk when the corresponding data is clean in the Smart Flash Cache.

  B. Hard disks are scanned every two weeks by default but only when disks are idle.

  C. Hard disks are scanned every two weeks by default in all situations.

  D. Disk repair is made immediately from Smart Flash Cache for corruption on disk when the corresponding data is dirty in the Smart Flash Cache.

  E. Disk repair may be made from another mirror copy if the corrupt area is part of a Normal or High Redundancy Disk Group.

  Correct Answer: ACE

  Explanation:

  The default schedule of scrubbing is every two weeks.

  References: http://docs.oracle.com/cd/E80920_01/DBMMN/maintaining-exadata-storage-servers.htm

• Question 28:

  You plan to migrate a database supporting an OLTP workload to your new X5 Database Machine.

  The current database instance supports a large number of short duration sessions and a very high volume of short transactions.

  Which three X5 Database Machine features can improve performance for this type of workload?

  A. An improved highly efficient undo and redo architecture

  B. Faster optimization due to an Exadata-specific optimizer

  C. Reduced I/O latency for writes due to writeback flashcache on all Exadata X5 and later models

  D. Ultra high I/O performance for reads and writes when using Exadata Extreme Flash in X5 and later models

  E. Reduced I/O latency for reads due to read flashcache on all Exadata X5 and later models

  Correct Answer: ACD

  Explanation:

  A: To further accelerate OLTP workloads, the Exadata Smart Flash Cache also implements a special algorithm to reduce the latency of log write I/Os called Exadata Smart Flash Logging.

  C: Use the Write-Back Flash Cache feature to leverage the Exadata Flash hardware and make Exadata Database Machine a faster system for Oracle Database Deployments.

  D: Exadata X5-2 introduces Extreme Flash Storage Servers. Each Extreme Flash storage server contains eight 1.6 TB state-of-the-art PCI Flash drives. PCI flash delivers ultra-high performance by placing flash memory directly on the high speed PCI bus rather than behind slow disk controllers and directors.

  References: http://www.oracle.com/technetwork/database/exadata/exadata-x5-2-ds-2406241.pdf http://www.oracle.com/technetwork/articles/database/exadata-write-back-flash-2179184.html

• Question 29:

  Your X6 Exadata Database Machine is running Oracle Database 12c, and has a large database with some very large tables supporting OLTP workloads.

  High-volume insert applications and high-volume update workloads access the same tables.

  You wish to compress these tables without causing unacceptable performance overheads to the OLTP workload.

  Which three are true regarding this requirement?

  A. Compression is performed on database servers when using row store compress advanced in an Exadata environment.

  B. Using row store compress advanced will compress the data more than if using column store compress for archive low.

  C. The compression method column store compress for archive high is the worst fit for this requirement.

  D. Compression is performed on Exadata Storage Servers when using row store compress advanced in an Exadata environment.

  E. Using row store compress advanced will compress the data less than if using column store compress for query low.

  Correct Answer: ABD

  Explanation:

  A: Creating a Table with Advanced Row Compression

  The following example enables advanced row compression on the table orders:

  CREATE TABLE orders ... ROW STORE COMPRESS ADVANCED;

  B: ARCHIVE LOW compression (Exadata only), recommended for Archival Data with Load Time as a critical factor

  Incorrect Answers:

  E: QUERY LOW compression (Exadata only), recommended for Data Warehouse with Load Time as a critical factor.

  References: https://docs.oracle.com/database/121/ADMIN/tables.htm#ADMIN015 https://uhesse.com/2011/01/21/exadata-part-iii-compression/

• Question 30:

  You have altered an index supporting a constraint to be invisible on a large data warehouse table in an X5 Database Machine.

  Which two statements are true?

  A. You might retain the index, and leave it as invisible, and the constraint will still be recognized and enforced.

  B. You must retain the index and make it visible again for the constraint to be recognized and enforced.

  C. You must retain the index and set the constraint to DISABLE NOVALIDATE RELY for the constraint to be recognized.

  D. You might drop the index and use a constraint with the DISABLE NOVALIDATE RELY flags for the constraint to be recognized.

  E. You might drop the index and make the constraint invisible, for the constraint to be recognized and enforced.

  Correct Answer: BC

  Explanation:

  B: With making indexes invisible, we can easily check whether indexes are useful without having to drop (and in case recreate) them actually. While this may be of interest for “ordinary” Oracle Databases already, it is particular a useful feature for Exadata where we expect some conventional indexes to become obsolete after a migration.

  C: DISABLE NOVALIDATE RELY means: "I don't want an index and constraint checking to slow down my batch data loading into datawarehouse, but the optimizer can RELY on my data loading routine and assume this constraint is enforced by other mechanism". This information can greatly help optimizer to use correct materialized view when rewriting queries. So if you don't use materialized views for query rewrite then you can put RELY for all your constraints (or NORELY for all your constraints) and forget about it.