Tips on SQL Plan Management and Oracle Database In-Memory – Part 3

In Part 1 of this series of tips on SQL Plan Management (SPM) and Oracle Database In-Memory I covered an example where a full table scan query made use of the In-Memory column store immediately without changing the SQL execution plan. In Part 2 I presented an example where the In-Memory column store made an alternative SQL execution plan viable, and where there was a corresponding SQL plan baseline already in place so that this plan could be used immediately. 

In this post I will consider a slightly different scenario:

• SQL plan management is used to stabilize the SQL execution plans for our critical application (that until now has not been using the In-Memory column store).
• The In-Memory column store is subsequently enabled and populated with application tables. 
• The Optimizer identifies new SQL execution plans.
• Most of the new execution plans have never been chosen by the Optimizer before.

Continuar aqui

Achieving Sarbanes-Oxley Compliance with Oracle Identity Management

Buscando alguna otra información dí con este viejo whitepaper, que pese a ser bastante antiguo creo que todavía vigente sobre Oracle y SOX. Una guía de como utilizar Oracle Identity Management para estar "compliance" con SOX (Sarbanes-Oxley). Creo que se prodría darle unas vueltas de tuerca más a este doc. 

INTRODUCTION 

The Sarbanes-Oxley Act of 2002 requires corporate executives to provide and ensure an increased level of financial and operational discipline. To comply with this law, most large corporations are now implementing a variety of new measures that cross financial reporting and processes, as well as internal business operations. This short paper describes how Oracle Identity Management can help ensure compliance with Sarbanes-Oxley (as well as other regulations) while increasing ability to monitor and audit compliance on an ongoing basis. 

Achieving Sarbanes-Oxley Compliancewith Oracle Identity Management 

Tips on SQL Plan Management and Oracle Database In-Memory - Part 2

In Part 1 of this series of tips on SQL Plan Management (SPM) and Oracle Database In-Memory, I covered what would happen if we have a SQL plan baseline for a full table scan query when the table was populating the In-Memory column store. 

In this part I’m going to cover a scenario where a query has more than one SQL plan baseline: 

• There is a query (called Q2, for short).
• Q2 queries a table called MYSALES, which is not yet populating the In-Memory column store.
• Q2 filters rows in MYSALES using a predicate on the SALE_TYPE column.
• Data in SALE_TYPE is skewed, so there’s an index and a histogram on this column.
• Because there is data skew, Q2 has two accepted SQL plan baselines; one with a full table scan and one with an index range scan.

You’ve probably come across this situation many times: the Oracle Optimizer must choose between a full table scan or an index range scan depending on predicate selectivity. The ability to change the execution plan based on the value of bind variables is called adaptive cursor sharing. If you’ve not come across that, then you’ll find it useful to check out the section on this topic in the Database SQL Tuning Guide.

What’s great about SPM is that it allows you to have multiple SQL plan baselines for individual queries, so you're not forced to pick one plan in preference to another. This capability is most relevant in environments where SQL statements use bind variables and there is a good deal of data skew. Queries like this are likely to have their plans affected by Oracle In-Memory Database because in-memory full table scans will have a lower cost than storage-resident table scans. Clearly, the In-Memory column store will affect the point of inflection where a full table scan will become more efficient than an index range scan. How is this going to work with SPM? 

Continuar aqui

Tips on SQL Plan Management and Oracle Database In-Memory Part 1

If you follow Oracle’s In-Memory blog then you probably came across a post mentioning how you should use SQL Plan Management when you’re upgrading to Oracle Database In-Memory. Whether you have read that post or not, you might be wondering what will happen if you have some SQL plan baselines and you begin to populate the In-Memory column store with a bunch of tables as used by those baselines. That’s what this post is about. Well, in fact, I’m going to break the topic up into a few posts because (as ever!) there is a little bit of subtlety to cover. Luckily, this will make your life easier rather than more difficult because you can get immediate benefit from In-Memory even if you don’t evolve SQL plan baselines on day one.  

When I started to think about this post I thought that I would start with the first scenario that probably comes to mind if you’re familiar with SQL Plan Management (SPM): 

• The Optimizer comes up with a new execution plan for a SQL statement because something has changed, and Oracle Database In-Memory would be a very good example of that! 
• If there’s a SQL plan baseline for the statement, the database will use the baseline execution plan and capture the new plan.
• Where appropriate, the new plan will be validated and accepted using SQL plan evolution. 

I will get to that, but first it’s better to start with a couple of more subtle points. With this information in our back pocket it will be easier to understand (and explain) the more traditional aspects of SQL plan evolution in the context of Oracle Database In-Memory.

Continuar aqui

Optimizando Oracle BI Analytics con la opción Oracle 12c: In-Memory Database (Parte II)

Como plataforma de “Business Intelligence” y “Analytics”, Oracle OBIEE Server se conecta a una base de datos física la cual puede ser una “Oracle Database” a través  de ODBC/JDBC , presentando un esquema de vista lógica independiente de la base de datos física. Cuando un usuario ejecuta los reportes, el OBIEE server traduce o mapea los SQL lógicos desde la capa de presentación del reporte a consultas físicas SQL, enviando estos SQL a la base de datos, de esta manera Oracle database ejecuta las consultas SQL físicas, obteniendo los datos correspondientes y retornándolos al OBIEE server para armar el reporte. La herramienta de administración de Oracle BI muestra estas tres capas: Presentación(Presentation), Modelo de negocio y mapeo ( Business Model and Mapping), y  Física  (Physical).

Figura 3: Herramienta de Administración de Oracle BI

TSPITR Using ACFS Snapshots

En este artículo, Anju Garg muestra que los snapshots de ACFS tomadas mientras la base de datos está en modo backup pueden integrarse con RMAN y emplearse para realizar Tablespace Point In Time Recovery (TSPITR).

http://allthingsoracle.com/tspitr-using-acfs-snapshots/