Dontcheff

Oracle Exadata myths and misconceptions

In DBA, Exadata, Oracle database on February 9, 2014 at 04:47

Exadata experience, what does that actually mean? by Martin Bach and Friday Philosophy – I Am An Exadata Expert? by Martin Widlake raise few questions on how is an Exadata DBA different than an Oracle DBA.

This chart provides the 3-month moving total of permanent IT jobs citing Oracle Exadata within the UK as a proportion of the total demand within the Database and Business Intelligence category.

Exadata_demand

Clearly, the demand is high and growing. Several Oracle DB experts are now also Exadata experts. There are various internet blogs and discussion forums dedicated to Exadata. Event conferences: E4.

The aim of this blog is to point out few Oracle Exadata myths and misconceptions I have encountered and discussed with Exadata experts during the past year or so.

1. “When logged into the database (SQL*Plus, SQL Worksheet, etc.) there is no way to see if the database is an Exadata one or not.” Actually there is. If select count(*) from (select distinct cell_name from gv$cell_state) returns a number > 0, then the database runs on Exadata storage. This SQL is also used by the SYS.DBMS_FEATURE_EXADATA procedure:

gv_cell_state

Number 7 tells us also that we are using a half Exadata rack. Full X4 Exadata rack has 14 cells. Full T5-8 SuperCluster rack has 8 cells.

2. “Flash Cache compression is enabled by default.” No, it is not. The reason is that clients must have licensed the advanced compression option on all databases that access the compressed flash cache.

You can enable using the Flash Cache compression by the cell command “alter cell flashCacheCompress=TRUE”. On an X3 Exadata you should also run “alter cell flashCacheCompX3Support= TRUE”.

Note that the Flash Cache compression is supported on X3 or X4 storage servers and it requires F40 or F80 cards. X4 Flash Cache compression is expanded to 88TB (raw) per rack. Monitor the Flash Cache compression by using the cell metric FC_BY_USED!

3. “Exadata is always RAC.” No, it is not. In fact, consider carefully if you want to use the RAC option on your Exadata. Ask for second opinion too! Ask for third opinion as well. You can have more databases on a quarter or 1/8th rack if you do not use the RAC option. And if someone comes with the HA excuse to use RAC, ask if (s)he can walk and run on one foot when the other one is injured. With well implemented instance caging one can run even close to 100 database instances on one database node (yes there are such cases). Often simplicity brings better results.

4. “DBAs should let Oracle decide on what is placed in the Flash Cache.” In general yes but I have seen significant improvement when frequently used tables are manually kept in the Flash Cache. Although the Flash Cache is fully automated for reads and writes, DBAs can decide on object caching by setting cell_flash_cache_keep. The granularity is all the way to the object level, where you can define higher priority for caching (KEEP) or not to cache at all (NONE). The flash cache can differentiate between full table scans and single I/O reads.

Cached:

– Frequently accessed data and index blocks
– Control file reads and writes
– File header reads and writes

Not cached:

– I/Os to mirror copies
– Backup-related I/O
– Data Pump I/O
– Data file formatting
– Table scans do not monopolize the cache

Check Uwe Hesse’s article about the Exadata The Flash Cache.

5. “The bigger SGA the better.” Although I have often seen how increasing the SGA can really boost the application performance, this is not always the case with Exadata. Why? In order to offload the work to the sells, the table should be enough big. But what is meant by a big table?

“_small_table_threshold” is sized in units of database blocks, and is supposed to be set by default to 2% the size of the database buffer cache (1.5% in 12c according to my tests) and _very_large_object_threshold = 500 (value for 12c)

Tables with size > 5 x “_small_table_threshold”, are being treated as ‘large tables’ for their reads result in direct reads. It is rather unclear how these things work for tables with size between 2–5 times the small table threshold. Note that 12c has 108 underscore parameters for different threshold values!

Check out these 2 article:

Smart Scan: Why is “_small_table_threshold” Important?
Exadata Optimization Tips

cell_smart_table_scan

6. “Storage indexes can be managed and maintained.” I wish that could be possible. Kellyn says in the above mentioned paper Exadata optimization tips: “Do not count on storage indexes. The feature, when implemented by Oracle is one requiring the choice of a complicated set of algorithms. They can be your savior or your devil.” Well, Oracle tries to maintain storage index summaries during loads but it is just a best effort – not the most optimal given the memory constraint and the characteristics of writes during loads.

Plus storage indexes are not persistent: after cell reboot the very first query will not benefit. In simple words, whoever comes first to the office on Monday morning (after weekend’s cell maintenance) might expect better performance from his/her reports.

You can disable/enable the storage indexes at cell level:

alter cell events = “immediate cellsrv.cellsrv_storidx(‘disable’, ‘ALL’, 0, 0, 0)”;
alter cell events = “immediate cellsrv.cellsrv_storidx(‘enable’, ‘ALL’, 0, 0, 0)”;
alter cell events = “immediate cellsrv.cellsrv_storidx(‘purge’, ‘ALL’, 0, 0, 0)”;

storage_indexes

I would really like to have a dynamic view showing the existing storage indexes!

7. “Oracle Database 12c running on Exadata Database Machine requires Exadata version 12.1 or later.” This is the recommended approach but you can still use Oracle Database 12.1.0 with Exadata version 11.2.3.3 or Exadata version 11.2.3.2.1.

Oracle Database 12c running on Exadata 11.2 is subject to the following restrictions:

– The 12.1 offload libraries are not present on Exadata version 11.2. Therefore, smart scan offloaded filtering and storage indexes are disabled for 12.1 databases. Smart scans are still issued, and the initiation of IOs is offloaded, but complete blocks are returned instead of just the selected rows and columns. 11.2 databases on the same system have full offload capabilities.
– I/O Resource Management (IORM) plans for 12.1 database are not enforced.
– Database resource plans (intradatabase resource plans) set on 12.1 databases are not enabled on cells.
– An interdatabase plan cannot manage 12.1 databases, therefore an interdatabase plan cannot be used to manage flash log or flash cache for a 12.1 database.
– Other Exadata features like Smart Flash Cache, Smart Flash Log, Hybrid Columnar Compression, and Low Latency and High Throughput InfiniBand messaging continue to work with 12.1 databases. Note that these restrictions only effect performance of 12.1 databases.
– All 12c database functionality, such as Pluggable Databases, is fully available.

A final comment: I see often the power of Exachk tool is somehow underestimated. Use also the ExaWatcher utility which is located in each Exadata database server and storage cell under /opt/oracle.ExaWatcher/. ExaWatcher replaces OSWatcher in Exadata software versions 11.2.3.3 and up.

Advertisements
  1. alter cell events = “immediate cellsrv.cellsrv_storidx(‘enable’, ‘ALL’, 0, 0, 0)”;
    >>Why would you need to enable storage indexes. Aren’t they automatically enabled. Is there any way to enable storage indexes for particular columns on a table?
    thanks

  2. Example: if you suspect that a query with predicates is giving wrong results, then run the query after disabling storage index by using _kcfis_storageidx_disabled = true

  3. “Although I have often seen how increasing the SGA can really boost the application performance, this is not always the case with Exadata. ”

    Can you please tell us more about how it makes sense to decrease one’s SGA just to force more offload processing? If work can be done in the SGA it should be done in the SGA, no?

    Also, your graph of Exadata mentions in job listing shows .6% as “high demand”? Is that 1 out of every 170 or so such postings?

  4. 1 out of 170, right.
    The parameter“_small_table_threshold” is sized in units of database blocks: in 11gR2 it is set by default to 2% the size of the database buffer cache. Here are more details: http://www.centroid.com/knowledgebase/blog/smart-scan-why-is-small-table-threshold-important
    In 12c, after some tests, I think it is not 2% but 1.5% (but these are just my own estimates).

  5. Nice Article…

    exadatacertification.blogspot.com

  6. It is always necessary to be aware about myths and misconceptions about Oracle Exadata machine. You wrote exact points and used screenshots which will easily make aware about these issues. We as XDuce are organizing a workshop for Exadata Database Machine Administration. Connect here for more details and registrations http://xduce.com/exadatadatabase/Exadata-Campaigns.html

  7. You mentioned about not using RAC on Exadata. However, the one command is going to do this automatically. Are you suggesting to use DBCA to create stand alone instances (non-RAC) or how would you suggest setting this up? Just curious.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: