Dontcheff

Archive for the ‘Database tuning’ Category

Artificial stupidity as a DBA limitation of artificial intelligence

In Data, Database tuning, Databases, DBA on December 6, 2017 at 07:47

“Artificial intelligence is no match for natural stupidity” ― Albert Einstein

What about introducing Artificial Intelligence into the database to an extent it tunes itself into all possible dimensions?

You have probably either seen the question above or have already asked yourself if that was at all possible. On Ask Tom, John from Guildford wrote the following:

As for Artificial Intelligence, well Artificial Stupidity is more likely to be true. Humanity is not privy to the algorithm for intelligence. Anyone who’s had the pleasure of dealing with machine generated code knows that software is no more capable of writing a cohesive system than it is of becoming self-aware.

Provided you’re not trying to be a cheap alternative to an automaton you just need to think. That one function alone differentiates us from computers, so do more of it. The most sublime software on the planet has an IQ of zero, so outdoing it shouldn’t be all that hard.

Stephen Hawking thinks computers may surpass human intelligence and take over the world. Fear artificial stupidity, not artificial intelligence!

Einstein is credited with saying (but it was probably Alexandre Dumas or Elbert Hubbard who deserve the recognition): “The difference between genius and stupidity is that genius has its limits.”

Explore artificial stupidity (AS) and/or read Charles Wheelan’s book Naked Statistics to understand this kind of AI danger. By the way, according to Woody Allen, 94.5% of all statistics are made up!

So what are the limitations of AI? Jay Liebowitz argues that “if intelligence and stupidity naturally exist, and if AI is said to exist, then is there something that might be called “artificial stupidity?” According to him three of these limitations are:

  • Ability to possess and use common sense
  • Development of deep reasoning systems
  • Ability to easily acquire and update knowledge
  • But does artificial intelligence use a database in order to be an artificial intelligence? Few very interesting answers to that question are give by Douglas Green, Jordan Miller and Ramon Morales, here is a summary:

    Although AI could be built without a database, it would probably be more powerful if a database were added. AI and databases are currently not very well integrated. The database is just a standard tool that the AI uses. However, as AI becomes more advanced, it may become more a part of the database itself.

    I don’t believe you can have an effective Artificial Intelligence without a database or memory structure of some kind.

    While it is theoretically possible to have an artificial intelligence without using a database, it makes things a LOT easier if you can store what the AI knows somewhere convenient.

    As Demystifying Artificial Intelligence explains, AI hass been embedded into some of the most fundamental aspects of data management, making those critical data-driven processes more celeritous and manageable.

    Amazon Mechanical Turk is worth looking into and Oracle are also ready for business with AI.

    Matt Johnson, a Navy pilot turned AI researcher, at a conference this simmer by saying that one of the places we are not making a lot of advances is in that teaming, in that interaction (of humans and AI) – Artificial Stupidity: When Artificial Intelligence + Human = Disaster

    Bottom line: if AI uses a database, then the intelligent database should be at least autonomous and have most tasks automated but not relying on artificial stupidity as a DBA limitation of artificial intelligence. Whatever it means… I do not want to curb your enthusiasm but we need to first fill in the skills gap: we need data engineers who understand databases and data warehouses, infrastructure and tools that span data cleaning, ingestion, security, predictions. And in this aspect Cloud is critical and a big differentiator.

    P.S. Is Artificial Intelligence Progress a Bubble? was published 4 days after this blog post.

    Advertisements

    ORAchk replaces EXAchks and RACcheck

    In Database tuning, DBA, Oracle utilities on June 21, 2014 at 12:48

    The new tool from Oracle ORAchk includes EXAchks and RACcheck functionality and replaces the older tools. It is no longer just a database tool but also runs health check on E-Business Suite Financials, Enterprise Manager Cloud Control repository, GoldenGate and Sun Systems.

    ORAchk

    Going memory lane, have a look at how Statspack compares with BSTAT/ESTAT. Well, those days are long over. DBAs used to have their own scripts, ran their own healthchecks but some special scripts can still shed some more light on the system health.

    One such example is Tanel Poder’s Exadata Snapper.

    For details on the ORAchk tool, how to download it, what’s new in ORAchk, read MOS Note 1268927.2. If you do not have access to MOS, here are 2 blog posts that describe the tool quite well:

    1. ORAchk Health Checks for the Oracle Stack (including Solaris) by Gerry Haskins

    2. ORAchk Health Checks For The Oracle Stack by Levi Pereira

    Here is how System Health Score is calculated:

    ORAchk2

    I would recommend glancing first at the ORAchk Known Issues before starting using the tool: MOS 1509746.1

    Note that ORAchk’s requirements are as follows:

    Supported Platforms:
    ————–
    Linux x86-64* (Enterprise Linux, RedHat and SuSE 9, SuSE 10 and SuSE 11)
    Oracle Solaris SPARC (Solaris 10 and 11)
    Oracle Solaris x86-64 (Solaris 10 and 11)
    AIX **
    HPUX**

    * 32-bit platforms not supported, no planned support for Linux Itanium
    **Requires BASH Shell 3.2 or higher to be installed

    Supported Oracle Releases:
    ————–
    10gR2
    11gR1
    11gR2
    12cR1

    Still, no Windows…

    And, do not aim at a 100% score, you will not get it easily. Points get deducted if you have more than 1 instance on the server:

    WARNING OS: Multiple RDBMS instances discovered, observe database consolidation best practices

    According to the tool, there is a “risk for shared resource contention leading for instability, performance degradation or scalability problems”. Then it says: “if the database consolidation best practices have already been reviewed and observed then this message can be ignored”.

    Not to mention that you might run the report twice in a row and get a different score.

    But there is more: ORAchk Collection Manager is a companion application to ORAchk, RACcheck and Exachk. When having lots of systems, auditing them with ORAchk might be a tedious piece of work. ORAchk has long had the ability to upload the results of its audit checks into a database automatically at run time.

    However, it was up to us to create a custom front-end to that data for reporting purposes and trend analysis. Now, with ORAchk Collection Manager, Oracle provides this Application Express application to be used as a dashboard in which they can track their ORAchk, RACcheck and Exachk collection data in one easy to use interface.

    Download the ORAchk Collection Manager and go through the ORAchk Collection Manager Users Guide for more details.

    Online reorganization enhancements in Oracle 12c

    In Database tuning, DBA, Oracle database, Oracle utilities on March 27, 2014 at 18:38

    The Online Reorganization and Redefinition feature in Oracle Database offers DBAs significant flexibility not just to modify the physical attributes of a table but also to defragment and compress the table while allowing online access to the database.

    DB_reorg

    A 5TB database can have 1TB of white space, in fact it can even have 4TB for all I have seen! Reclaiming the wasted space is important from both storage and performance perspective.

    Before discussing the new 12c features related to online database reorganization, I will show you how easy it is to reorganize a table in 12c while at the same time all operations on the table are allowed. I have a tablespace called EM_DATA with just one datafile em_data_01.dbf and a table called EM_OBJECTS having more than 11 million rows. The file is almost 4GB in size. There are no another segments within the EM_DATA tablespace.

    EM_OBJECTS

    As you can see the segment EM_OBJECTS was 1774MB in size before the reorganization and after compressing it online we managed to shrink it to 644MB. The data in the table was not artificially ordered in any way whatsoever. Moreover, after the reorganization, I was able to resize the file down to 640MB and thus release more than 3GB of storage space on filesystem level!

    Clearly, in 12c the complete procedure is very simple, there is no need to create explicitly the interim table but if an errors occurs during the execution, then the interim table is dropped, and the REDEF_TABLE procedure must be re-executed.

    Now, here are all new features related to online redefinition in Oracle 12c:

    1. The REDEF_TABLE procedure enables you to perform online redefinition on the table’s storage properties in a single step when you want to change the following properties:

    – Tablespace changes, including a tablespace change for a table, partition, index, or LOB columns
    – Compression type changes (as shown in the example above), including a compression type change for a table, partition, index key, or LOB columns
    – For LOB columns, a change to SECUREFILE or BASICFILE storage

    Check out these examples.

    2. In 11g, it was only possible to add or drop columns when redefining tables online. In 12c, we can now set an unused column online. Check Doc ID 1120704.1 for details.

    3. New in Oracle Database 12c: drop index online, alter index visible/invisible, alter index unusable online, and drop constraint online. I would suggest the master note “An overview of online redefinition of tables” Doc ID 1357825.1 for detailed examples and known bugs.

    4. Since 10gR2, it has been possible to reorganize a single partition with online reorganization. With 12c, the redefinition of multiple partitions in a single redefinition session gives us the possibility to reduce the completion time to redefine multiple partitions.

    5. Redefinition of tables that have Virtual Private Database (VPD) policies defined on them helps us eliminate downtime for redefining these tables. On top of that, there is a new function called EXPAND_SQL_TEXT which has been added to the DBMS_UTILITY packages. It makes seeing what SQL is actually executed pretty easy.

    6. The performance of SYNC_INTERIM_TABLE with Materialized View Log processing has been significantly improved in 12c. I really hope not to see any more ORA-32416.

    7. There is also improved resilience of FINISH_REDEF_TABLE with better lock management: you can avoid user cancellations or indefinite waits by using the new dml_lock_timeout parameter when running DBMS_REDEFINITION.FINISH_REDEF_TABLE procedure. Amit Bansal gives more details here.

    Finally, here is all about DBMS_REDEFINITION in 12c from Dan Morgan’s library.

    junk_data

    Online database reorganization is something of paramount importance, especially nowadays, when downtime is hard to get and data grows rather exponentially. Removing the unnecessary data is something few people in the companies care about although often more than 50% of database data is nothing else but junk data. Buying and adding more storage seems to be the easier option although reorganized and defragmented databases have better performance and require less memory and CPU.

    Additional resources:
    Understanding, detecting, and eliminating harmful database fragmentation by Craig A. Shallahamer
    Reorganizing Tables in Oracle – is it worth the effort? by Uwe Hesse
    Oracle table reorganization: Online or Offline by FJ Franken
    Oracle Tablespace Reorg to reclaim space by AppsDBA
    Using DBMS_REDEFINITION package to reorganize tables online by Marco Vigelini
    Reclaiming Unused Space in Datafiles by Tim Hall

    Oracle in-database archiving and invisible columns in 12c

    In Database tuning, DBA, Oracle database, Oracle internals, SQL on September 30, 2013 at 01:15

    “The true mystery of the world is the visible, not the invisible.” Oscar Wilde

    invisible_woman

    In-Database archiving is new to Oracle 12c. The idea behind it is to make rows invisible. This is very similar to the invisible column concept.

    The two features, combined together, can make big data in the table look not that big to the application.

    Check the demonstration from orafaq to see how it works. It is simple and rather trivial. Another example is given by Contract Oracle Limited.

    What is really interesting is John Watson’s comment and example at the very end of the orafaq article. He shows the need for filters that cannot be accomplished with access. John suggest that “all indexes need to include the ora_archive_state column, perhaps appended to primary and unique key indexes and prepended to other indexes. If you don’t do this, you will find that many operations that could previously be completed without touching the table will require table scans”.

    The AMIS Techonology blog pays attention to the very same thing:

    The Oracle Documentations says the following: “In-Database Archiving enables you to archive rows within a table by marking them as inactive. These inactive rows are in the database and can be optimized using compression, but are not visible to an application. The data in these rows is available for compliance purposes if needed by setting a session parameter.

    With In-Database Archiving you can store more data for a longer period of time within a single database, without compromising application performance. Archived data can be compressed to help improve backup performance, and updates to archived data can be deferred during application upgrades to improve the performance of upgrades.”

    The AMIS Technology Blog says: Note the emphasis on ‘without compromising performance’. It would seem that such in line archived records are skipped in a smart way during full table scans and other records retrieval operations.

    Yong Woo, shows in his short note 12c: In-Database Archival …good, but not good with an example how a ‘TABLE ACCESS BY INDEX ROWID’ can be brought to the execution plan because of the new pseudo column.

    What I would like to show, is in a way also the opposite, that performance can be even better with Oracle in-database archiving. I have a table called clients and visibility is set to ACTIVE. Almost all rows of the table are “invisible”. Check how bytes and cost increase, once I set the visibility to ALL. And of course, the full table scan!

    
    SQL> select * from clients;
    
    

    ep1

    
    SQL> alter session set row archival visibility = all;
    
    Session altered.
    
    SQL> select * from clients;
    
    

    ep2

    It is interesting to note that virtual columns can be made invisible:

    
    SQL> alter table clients add (vip_status number 
         generated always as (power(status,status)) virtual);
    
    Table altered.
    
    SQL> alter table clients modify (vip_status invisible);
    
    Table altered.
    
    

    INVISIBLE columns are not supported in external tables, cluster tables, or temporary tables and you cannot make a system-generated hidden column visible.

    You can not yet make a table invisible. If you start marking all columns from the table as invisible, at the very end you will get:

    SQL> alter table clients modify (client_id invisible);
    alter table clients modify (client_id invisible)
                *
    ERROR at line 1:
    ORA-54039: table must have at least one column that is not invisible
    

    On a final note, I wonder what developers will do if they cannot insert a row into a table if a DBA adds an invisible column without a default value declared as NOT NULL 🙂

    DBA tips for tuning Siebel on RAC and Exadata

    In Database tuning, DBA, Exadata, Oracle database, Oracle internals, RAC, Siebel on April 21, 2013 at 11:27

    More than 100 years ago, Mark Twain said that “the elastic heart of youth cannot be compressed into one constrained shape long at a time”.

    In modern technology, “the elastic cloud can be compressed with the constraints of long datatypes”. Let me explain the meaning of my statement in terms of Siebel applications running on RAC and Exadata.

    COTS_Exadata

    Chained and migrated rows are often a big problem in a Siebel database. The issue of chained rows can be resolved by using big block size while migrated rows require segment reorganization with higher PCTFREE (20, 30 or even 50). Chained tables in Siebel are often S_ORG_EXT, S_ASSET, S_ORDER_ITEM and S_ORDER_ITEM_OM. At least these will require using a bigger block size.

    For LONG and CLOB details in a Siebel CRM, check CLOB Physical Type in Siebel.

    But as Joel Goodman noted in How Smart is Your Smart Scan?, there is an Exadata specific situation that causes migrated rows.

    When a row is updated in a Hybrid Columnar Compressed (HCC) table, then it is migrated to another block in the segment that is managed using “OLTP compression“. Any HCC Compression Unit (CU) containing at least one migrated row, will also cause the block containing that row to be accessed by the server using a “cell single block physical read“.

    Look at the “Top 5 Timed Foreground Events” in a Siebel on Exadata OLTP DB using HCC:

    cell single block physical read

    The situation with HCC in OLTP is tricky for the following 3 reasons:

    1. Every update of a record stored in HCC format results in a migrated row
    2. The new row is stored in a new block that is marked for OLTP compression
    3. Non-direct path inserts will be loaded into OLTP compressed blocks as opposed to HCC format

    For the above reasons, mixing HCC with DML is not recommended. Partitioning can provide a mechanism for avoiding these issues since each partition can have its own storage format.

    Only after decompressing the OLTP tables, the event “cell single block physical read“ disappeared and the performance got significantly improved.

    Another good tip for Siebel on RAC is the usage of high number of hash partitions for hot indexes. High means 256 and more (should be a power of 2).

    Look at the situation with gc buffer busy waits before the high hash partitioning:

    gc_buffer_busy_segments

    As you can see the “gc buffer busy acquire” was the top event:

    Siebel_hot_blocks_before

    Looking at issues with these events in MOS might incline you to believe that this is a bug. However, this was not the case as you can see what happened after making the number of hash partitions for some of the indexes 256, the database performance was back to normal:

    Siebel_hot_blocks_after

    Note that also non-partitioned tables can have hash partitioned indexes!

    Another tip: SecureFiles is a feature introduced in Oracle Database 11g that is *specifically* engineered to deliver *high performance* for this type of unstructured data. I have seen several queries getting even 10 times faster after migrating LOBs to SecureFiles.

    About sequences: in RAC/Exadata, using the CACHE and NOORDER options together results in the best performance for a sequence. For example, in a Siebel database the S_DOCK_TXN_LOG_S sequence is used to generate the transaction ID used by S_DOCK_TXN_LOG table. The default cache size for sequences in Oracle is 20. If you are having thousands of concurrent users, Siebel/Oracle suggest you increase the cache size to be at least 10000.

    Last 2 things:

    – For gathering Siebel database statistics use always the latest version of coe_siebel_stats.sql. As of now, the latest version is 11.4.4.5
    – The script coe_siebel_profile.sql provides a list of columns that are not indexed but potentially are good candidates for indexing according to their usage by the Optimizer.

    For additional tips, check Oracle’s white paper Siebel on Exadata!

    And using Oracle Enterprise Manager makes all tuning so much easier!

    OEM

    Reducing “library cache: mutex X” concurrency with dbms_shared_pool.markhot

    In Database tuning, DBA, Oracle database, Oracle internals, PL/SQL on February 12, 2013 at 16:41

    Mutexes or mutual exclusion algorithms are lighter and more granular concurrency mechanism than latches and are used in concurrent programming to avoid the simultaneous use of a common resource.

    Oracle started using mutexes from 10g for certain operations in the library cache but from 11g all library cache latches were replaced by mutexes which lead to the common to all DBAs “library cache: mutex X” concurrency event:

    library_cache_mutex_X_top5

    The irony here is that the reason for obtaining a mutex is to ensure that certain operations are properly managed for concurrency but the management itself leads to concurrency. These are the top 3 differences between mutexes and latches:

    – A mutex can protect a single structure, latches often protect many structures
    – A mutex get is about 30-35 instructions in the algorithm, compared to 150-200 instructions for a latch get
    – A mutex is around 16 bytes in size, compared to 112-200 bytes for a latch

    Last month, I found a very interesting and detailed article by Anju Garg on latches and mutexes. It is worth reading.

    The aim of this post is to suggest on how to proceeded if you hit the “library cache: mutex X” concurrency event which simply means that a library cache operation is being performed and Oracle is trying to get the library cache mutex in eXclusive mode.

    Do the following:

    1. Run a query against the x$kglob table to find the names of the objects being pinned most of the time. x$kglob is the resource structure table for library cache locks and pins. Jagjeet’s note gives a very good example.

    select * from (
     select case when (kglhdadr = kglhdpar) then 'Parent' else 'Child '||kglobt09 end cursor,
     kglhdadr ADDRESS, substr(kglnaobj,1,20) NAME, kglnahsh HASH_VALUE, kglobtyd TYPE, 
     kglobt23 LOCKED_TOTAL, kglobt24 PINNED_TOTAL,kglhdexc EXECUTIONS, kglhdnsp NAMESPACE
     from x$kglob -- where kglobtyd != 'CURSOR'
    order by kglobt24 desc)
    where rownum <= 20;
    

    2. If you have issues with PL/SQL packages and procedures, use dbms_shared_pool.markhot() to mark them as hot. As suggested by Andrey Nikolaev, Oracle creates multiple copies of marked objects in the library cache, but frequently uses only one of them. That is why, in order to achieve uniform distribution, mark the PL/SQL objects as hot immediately after the instance restart.

    The documentation for the dbms_shared_pool.markhot procedure can be found here.

    3. If the issue is sequence related, then either verify with Oracle that it is not a bug, get rid of the sequence if possible or at least modify its parameters (cache, order, etc.).

    As you can see from Dom Brook’s article, issues are often package related. Have a look at my screenshot from ASH. The data source is V$ACTIVE_SESSION_HISTORY.

    You may review on Metalink “Waitevent: library cache: mutex X” (Document ID 727400.1) for the list of known Oracle bugs but for all I have seen, sometimes patches do not help, so finding a workaround is all the DBA is left with. An ASH report will be very helpful:

    library_cache_mutex_X_plsql

    If you see “library cache: mutex X” as a top event, note that ADDM will first find that there is contention for latches related to the shared pool that were consuming significant
    database time (no surprise). For the top SQL statements, ADDM will claim something like “Waiting for event library cache: mutex X in wait class ‘Concurrency’ accounted for 97% of the database time spent in processing the SQL statement with SQL_ID d2svmdafangyq.” Look then at the “Rationale” sections and hunt for the names of the PL/SQL packages 🙂

    SQL Tuning Advisor Internals

    In Database tuning, DBA, Oracle database, Oracle internals, SQL on January 27, 2013 at 19:07

    Douglas Adams, the author of “The Hitchhiker’s Guide to the Galaxy” said: “A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools”.

    I am talking here about Oracle’s Cost Based Optimizer.

    STA_01

    The optimizer needs to make decisions about execution plans in a very short time: not all execution plans can be scanned. Staring Oracle 10g, Oracle allows the optimizer to run in tuning mode where it can gather additional information and make recommendations about how specific statements can be tuned further.

    Funny thing here is that we rely on the same component (CBO) to improve the SQL statement that could not generate an optimal plan in the first place.

    In this post, I would like to show something about Oracle 11gR2 that cannot be found either on Google or on Metalink. It is about tracing the SQL Tuning Advisor.

    A very good review of what STA does was written by Tim Hall. Worth checking is also what Kerry Osborne writes about SQL Profiles. Cool stuff!

    Christian Antognini’s article on SQL Profiles and how to trace it is probably the best written on that subject. Using the undocumented parameter _STN_TRACE, it is possible to trace what is happening during the SQL Tuning Advisor performance analysis. I find slight amusing the choice of the name of the domain used for Oracle FAQ but I guess this is not intentional.

    However, if you check Jonathan Lewis’s article on SQL Profiles and the comments after it, you will notice that: _stn_trace does not seem to be available anymore in 11gR2″. That is visible from the _STN_TRACE documentation available on Oracle FAQ.

    Starting 11gR2, there is an event trace[sql_manage.*] disk=low. Disk can be set to one of these 3 values: low, medium, or highest. The ora_debug_table is no longer needed or used.

    You can set the event either at session or system level. If using OEM, then set it at system level, just like this:

    alter system set events 'trace[sql_manage.*] disk=highest';
    

    Once you run the SQL Tuning Advisor, go to either user_dump_dest or diagnostic_dest/diag/rdbms/SID/trace. You will find a long text file containing all analysis performed by STA.

    The screenshot on the top of this post shows you the alternative execution plan offered by the SQL Tuning Advisor, the one below shows you the recommendations:

    STA_02

    The trace file generated because of enabling STA tracing on the highest level is way too big (20MB) but I am anyway posting it here.

    This is twenty megabytes of text fitting into 8670 pages for what Oracle does for a single SQL statement!

    STA_03

    Tuning Exadata with parallel profiles and SPM baselines

    In Database tuning, DBA, Exadata, SQL on October 29, 2012 at 06:34

    Harold Geneen used to say: “It is much more difficult to measure nonperformance than performance”.

    Without automation database management becomes increasingly difficult. An Exadata database can strongly benefit performance-wise from fully enabled Automatic SQL Tuning:

    BEGIN
      DBMS_SQLTUNE.set_tuning_task_parameter(
        task_name => 'SYS_AUTO_SQL_TUNING_TASK',
        parameter => 'ACCEPT_SQL_PROFILES',
        value     => 'TRUE');
    END;
    /
    

    I will explain why.

    The SQL management base (SMB) is a part of the data dictionary that resides in the SYSAUX tablespace. It stores statement logs, plan histories, SQL plan baselines, and SQL profiles.

    The difference between SQL Profiles and SQL Plan Management Baselines is very well explained by Maxym Kharchenko and Maria Colgan.

    The process of automatic evolution of SQL Plan Baselines is described with a perfect example by Christian Antognini.

    Converting baselines into profiles is almost straightforward but not simple, Sumit Bhatia explains how.

    Now, for complex and long running SQL statements in Exadata, it is quite important to reduce the amount of data being transferred from the storage nodes to the database and to have the right degree of parallelism.

    The former is achieved very much by smart scans which take place only when there are full scans or direct path reads. The blocks are not cached but they are returned to the PGA. The latter is even trickier. How in fact to determine the right degree of parallelism for an SQL statement?

    Most often parallel_degree_policy is set to manual (default) and parallel_max_servers is sort of guessed. In my opinion, the right way is to use Oracle’s Auto DOP solution by enabling parallel_degree_policy to a non-default value. Let me quote Gwen Shapira:

    There are two levels to Auto DOP:
    Limited: when accessing tables and indexes that have been declared with parallel clause, Oracle will decide on the degree of parallelism based on the query and system resources
    Auto: Oracle will decide on degree of parallelism for every query. In addition two new features are enabled: parallel statement queuing and in-memory parallel execution.

    Even doing all that, because of Oracle’s unpredictable behavior at times, some SQL statements might not get the right number of parallel slaves or might not go for smart scans. For such statements creating an SPM baseline is a good thing to start from. Then, during the Automatic SQL Tuning maintenance window, the SQL Tuning Advisor might notice that there is a non-accepted plan which is better than the accepted one. The recommended SQL profile will simple do nothing else than to evolve the SPM baseline execution plan into an accepted plan.

    All that happens automatically due to the fact that we have advised Oracle to automatically accept SQL Profiles.

    But there is more to that: how to get a good degree of parallelism for the SQL statement? Oracle will automatically do so if needed by creating a parallel SQL Profile. Note that there are two types of profiles:

    – REGULAR_PROFILE – profile without a change to parallel execution
    – PX_PROFILE – regular profile with a change to parallel execution

    Here is an example:

    Automatically accepted parallel profiles can really boost the Exadata performance. But as PX_Profiles can consume also more resources than expected, it is important to have Instance Caging enabled.

    The most important step for configuring instance caging is to determine the value of CPU_COUNT for each database instance and the sum of CPU_COUNTs across the server. There are two approaches:

    – Partitioned approach for mission-critical pools sum(CPU_COUNT) < 75% x Total CPUs
    – Over-subscribed approach for non-critical pools sum (CPU_COUNT) <= up to 3 x Total CPUs

    Second step for instance caging: ensure resource_manager_plan is enabled.

    Bottom line: tuning an Exadata database is not exactly the same things as tuning a traditional database. Actual tuning of an Exadata database is in fact a lot easier once understood.

    And a lot of that tuning work can be really automated!

    Bloom filters for DBAs

    In Database tuning, DBA, Exadata, Oracle database, RAC on August 28, 2012 at 19:59

    Lady Bird Johnson said: Where flowers bloom so does hope. I would re-phrase: Where Bloom filters bloom, so does database performance.

    A Bloom filter is a probabilistic algorithm for doing existence tests in less memory than a full list of keys would require. In other words, a Bloom filter is a method for representing a set of n elements (also called keys) to support membership queries. John Rose wrote a very detailed blog article about Bloom filters. Another good one is by Christian Antognini entitled simply Bloom Filters.

    These underscore parameters are all related to Bloom filters:

    The Oracle database makes use of Bloom filters in the following 4 situations:

    – To reduce data communication between slave processes in parallel joins: mostly in RAC
    – To implement join-filter pruning: in partition pruning, the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access list
    – To support result caches: when you run a query, Oracle will first see if the results of that query have already been computed and cached by some session or user, and if so, it will retrieve the answer from the server result cache instead of gathering all of the database blocks
    – To filter members in different cells in Exadata: Exadata performs joins between large tables and small lookup tables, a very common scenario for data warehouses with star schemas. This is implemented using Bloom filters as to determine whether a row is a member of the desired result set.

    You can identify a bloom pruning in a plan when you see :BF0000 in the Pstart and Pstop columns of the execution plan and PART JOIN FILTER CREATE in the operations column:

    Two hints can be used: px_join_filter and no_px_join_filter; and there are a couple of views for monitoring Bloom filters:

    – v$sql_join_filter: information about Bloom filters; number of rows filtered out and probed by Bloom filters
    – v$pq_tqstat: check reduced communication due to usage of Bloom filters

    There are two undocumented Oracle functions: SYS_OP_BLOOM_FILTER and SYS_OP_BLOOM_FILTER_LIST:

    In 11gR2 execution plans you may see :BF0000 replaced by KEY(AP). This is due to new functionality that performs partition pruning based on AND (= And Pruning) multiple pruning descriptors.

    In 11.2.0.2 and later a SQL query on a table might not deliver any results. Unfortunately, it might not deliver any error message either. The solution is to disable Bloom filters:

    alter system set “_bloom_filter_enabled” = false scope=both;

    Restrictions:

    – For Exadata customers, I would suggest to set a low degree of parallelism on small tables and indexes in order to take advantage of Bloom filters.
    – Bloom filters are not supported in partition-wise joins. This is documented as Bug 14325392 but will be fixed in 12.1.0.
    – About small tables with indexes: If Oracle goes for an index range scan of that small index, then it will be done in serial and not parallel and thus no Bloom filter will be used (just drop those small indexes).
    – CURRENT_DATE does not use Bloom filters while SYSDATE does.
    – Bloom filters are not used in DML statements: Bug 13801198 says that a simple two table join shows use of Bloom filter/pruning when running as a query and not when running as part of a INSERT as SELECT. The performance delta is 4:24 vs 0:24.

    Exadata Consolidation: “You must spend money to make money”

    In Cloud, Database tuning, DBA, Exadata, Oracle database on July 20, 2012 at 20:28

    Titus Maccius Plautus, a roman poet and philosopher who lived from 254 BC to 184 BC, said: “Wisdom is not attained by years, but by ability”. And also the famous quote in the title above.

    What do I have in mind? Even midsize companies have nowadays dozens, if not hundreds, of database instances. Corporate IT departments would not surprise anyone if they support a couple of thousand databases.

    The consolidation of these databases can be achieved in several ways. But here are some general guidelines on how to accomplish this task by using Exadata in the most optimal way in order to maximize cost reduction, high availability, secure separation of administrative duties and ease of performance tuning, management and monitoring.

    It is really surprising how slowly companies adopt database consolidation granted the pressure IT management has in every possible direction. We can speculate for hours why so, but what I will concentrate on, are the technical aspects of Exadata consolidation.

    Oracle recommends the creation of Exadata Hardware Pools. Hardware Pool is a machine or group of machines used as the target consolidation platform.

    According to Oracle’s white paper “Best Practices For Database Consolidation On Exadata Database Machine“, an enterprise might create multiple Hardware Pools to make each consolidation target platform more manageable. The recommended minimum Hardware Pool size Oracle Maximum Availability Architecture Exadata Consolidation Best Practices is Exadata X2-2 Half Rack and the maximum recommended Hardware Pool size is two Exadata Database Machines Full Racks (plus additional Exadata storage expansion racks if required). Hardware Pools that fall within this range are the most common Exadata configurations for consolidation and provide sufficient capacity to efficiently achieve objectives for database consolidation.

    The recommended storage configuration is one shared Exadata storage grid for each Hardware Pool. This storage grid contains all Exadata cells and Exadata disks, and is configured with either ASM high or normal redundancy. The recommended setup for Oracle Grid Infrastructure (which includes Oracle Clusterware and Oracle ASM) is to use one cluster per Hardware Pool.

    Oracle has recommended several parameter settings for Exadata database consolidation:

    If PageTables in /proc/meminfo is set to more than 2% of the physical memory size, then set the operating system parameter HugePages to the sum of all shared memory segments. Starting in 11.2.0.2, setting the database initialization parameter USE_LARGE_PAGES=ONLY on all instances prevents any instance from starting unless sufficient HugePages are available. Hugepages can only be used for SGA, so do not over-allocate. Also, the database parameters MEMORY_MAX_TARGET and MEMORY_TARGET are not compatible when HugePages are enabled. This is only for Linux. On Solaris, HugePages are automatically configured and used via intimate shared memory (ISM).

    Operating system setting:

    Set the number of shared memory segments (kernel.shmmni) greater than the number of databases.
    Set the maximum shared memory segment size (kernel.shmmax) to 85% of physical memory size, which is the default.
    Set the maximum total number of system semaphores (SEMMNS) greater than the sum of all database processes.
    Set the maximum number of semaphores in a semaphore set (SEMMSL) greater than the largest number of processes in any single database.

    Exadata Memory:

    1. Exadata X2-2 based on the Sun Fire X4170 Oracle Database Servers (also known as V2) has 72 GB per database server.
    2. Exadata X2-2 has 96 gigabytes (GB) of memory in the default configuration, with an option to expand to 144 GB of memory (with the Exadata memory expansion kit).
    3. Exadata X2-8 has 1 terabyte (TB) (with the X4800) or 2 TB (with the X4800M2) per database server.

    An important rule:

    OLTP applications: SUM of databases (SGA_TARGET + PGA_AGGREGATE_TARGET) + 4 MB * (Maximum PROCESSES) < Physical Memory per Database Node
    DW/BI applications: SUM of databases (SGA_TARGET + 3 * PGA_AGGREGATE_TARGET) < Physical Memory per Database Node

    Remember to enable instance caging!

    For the Oracle ASM instance, set PROCESSES= 50 * MIN ( # database instances on db node+ 1, 11) + 10 * MAX (# database instances on db node – 10, 0).

    Limit PARALLEL_MAX_SERVERS:

    1. X2-2: sum(PARALLEL_MAX_SERVERS) for all instances <= 240
    2- X2-8: sum(PARALLEL_MAX_SERVERS) for all instances RECOVER MANAGED STANDBY DATABASE … PARALLEL 16.

    Limit the number of processes and connections to the database servers:



    For Exadata running Exadata Storage Server Software 11.2.3.1 or higher, configure a maximum of 60,000 processes per Hardware Pool. Upper limit target is 7,500 processes per node for X2-2. Upper limit target is 30,000 processes per node for X2-8. For Exadata running Exadata Storage Server Software 11.2.2.4.2 or less, configure a maximum of 20,000 processes per Hardware Pool. Upper limit target is 2,500 processes per node for X2-2. Upper limit target is 10,000 processes per node for X2-8.

    The temporary tablespace should be:

    BigFile Tablespace,
    Located in DATA or RECO, whichever one is not HIGH redundancy,
    Sized 32GB initially,
    Configured with AutoExtend on at 4GB,
    Configured with a MaxSize defined to limit out of control growth.

    Answers to several additional Exadata related questions can be found in the following blogs/articles:

    Who Manages the Exadata Machine? by Arup Nanda
    Upgrade Exadata to 11.2.0.3 by Gleb Otochkin from Pythian
    Best Practices For Database Consolidation On Exadata by Javier Puerta
    Consolidation Strategies for Oracle Exadata and Oracle Database Machine by Dan Norris, X Team, Oracle Exadata Development
    Expert Oracle Exadata by Kerry Osborne, Randy Johnson, Tanel Põder
    Oracle Exadata – A platform for consolidation by Umesh Tanna

    An excellent reference is the Consolidation Parameters Reference Table available at MOS: “Oracle Sun Database Machine Setup/Configuration Best Practices” [ID 1274318.1]

    Back to the saying of Plautus: one does not have to wait for years in order to start implementing an Exadata consolidation. The product has the perfect ability to serve its many purposes: this is software, not wine!