Infrastructure at your Service

Oracle Archives - Page 32 of 37 - Blog dbi services

Franck Pachot

Oracle 12c: comparing TTS with noncdb_to_pdb

By | Database management, Oracle | No Comments

How to migrate from non-CDB to CDB? Of course all known migration methods works. But there is also another solution: upgrade to 12c if necessary and then convert the non-CDB to a PDB. This is done with the noncdb_to_pdb.sql which converts a non-CDB dictionary to a PDB one, with metadata and object links. But do you get a clean PDB after that ? I tested it and compared the result with same database migrated by…

 
Read More
Franck Pachot

When Oracle resets session statistics

By | Database management, Oracle | No Comments

During our Oracle 12c New Features workshop I had a very good question about whether the session statistics are reset or not when doing ALTER SESSION SET CONTAINER. My initial thought was that they were not reset because it’s the same session (same SID and SERIAL#). But when I’m not 100% sure about something, I test it. And once again, it proves that even the instructor can learn something new when giving a workshop, thanks…

 
Read More
Grégory Steulet

Oracle AVDF – Database Firewall Policies

By | Database management | 2 Comments

The successful deployment of a Database Firewall depends on an effective policy. Oracle AVDF includes preconfigured firewall policies as described in the Firewall Policy page in the Policy tab of the Audit Vault Server console. These include policies that log all SQL statements, or log only unique SQL statements. In addition, the Database Firewall policy editor enables you to design your own policies quickly and efficiently. Policy rules can depend on any combination of the…

 
Read More
Franck Pachot

Oracle 12c privilege analysis rocks

By | Database management, Oracle | 4 Comments

12c came with a very nice feature: privilege analysis. You don’t know which privileges are required? then just grant DBA, run your application, and check which minimal privileges are needed. And today, I’ve discovered how it is very powerful: you can even see privileges used internally, even when not done by SQL, and even not documented. It starts like that, with a question from Vladimir Sitnikov (who publishes very interesting stuff from his twitter account)…

 
Read More
Franck Pachot

Oracle locks: Identifying blocking sessions

By | Database management, Oracle | 5 Comments

When you have sessions blocked on locks, you probably have all information about the waiters (they call you and anyway their waiting session is visible in v$session our ASH). But you usually need to get enough information that help to identify the blocker. Here is a query I use to get that quickly, based on V$WAIT_CHAINS Here is the result I want to get: session wait event minutes USER PRO ———————– ———————————————– ——- —- —…

 
Read More
Franck Pachot

12c SQL Plan Directive: state has changed between 12.1.0.1 and 12.1.0.2

By | Database management, Oracle | 9 Comments

SQL plan Directives have been introduced in 12c. If you have scripts that check their states (and I have that as I prefer to monitor closely the features that are new) you probably have seen a difference when going from 12.1.0.1 – the first release of 12c and the only one avilable yet in Standard Edition – and 12.1.0.2 – the first patchest. I’ll explain here what are the SQL Plan Directive states and how…

 
Read More
Franck Pachot

AWR does not store explain plan predicates

By | Database management, Oracle | One Comment

What is the most important part in an execution plan? It’s probably the predicate section. It helps to see implicit conversions. It helps to understand why an index is used or not. Or to see additional predicates coming from constraints. When you get an execution plan from shared pool, the dbms_xplan.display_cursor() shows the predicates. But when you retrieve a plan from the past, dbms_xplan.display_awr() does not show them. Statspack With Statspack, you can store execution…

 
Read More
Franck Pachot

12.1.0.2 CDB views are now using CONTAINERS()

By | Database management, Oracle | 2 Comments

I’ve blogged about multitenant internals at the time when 12.1.0.1 was just released. Something has changed in 12.1.0.2 and blogging about it was in my todo list for a long time. Now the occasion to do it has been triggered by a question on Oracle Forums about CDB_DATA_FILES not showing PDB$SEED datafiles because there is an important change we must be aware of. In the previous blog, I have described how you can query PDB…

 
Read More
Franck Pachot

Oracle SQL Profile: why multiple OPT_ESTIMATE?

By | Database management, Oracle | 5 Comments

In a previous blog I’v shared my script to retrieve the OPT_ESTIMATE hints from a SQL Profile. In the example I made, I had two lines for each table: — PROFILE HINTS from dbiInSite (1) statement 4fz1vtn0w8aak: /*+ OPT_ESTIMATE(@”SEL$2CBA5DDD”, TABLE, “EMPLOYEES”@”SEL$1″, SCALE_ROWS=2) OPT_ESTIMATE(@”SEL$58A6D7F6″, TABLE, “EMPLOYEES”@”SEL$1″, SCALE_ROWS=2) OPT_ESTIMATE(@”SEL$6AE97DF7″, TABLE, “DEPARTMENTS”@”SEL$1″, SCALE_ROWS=5.185185185) OPT_ESTIMATE(@”SEL$58A6D7F6″, TABLE, “DEPARTMENTS”@”SEL$1″, SCALE_ROWS=5.185185185) */ The reason is that when the optimizer do some transformations to the query, then the query block identifiers can change….

 
Read More
Franck Pachot

Oracle SQL Profiles: Check what they do before accepting them blindly

By | Database management, Oracle | 8 Comments

People often ask me if I use the SQL Tuning Advisor. The answer is no, at least not through the end of it. But don’t take me wrong. SQL Tuning Advisor is a really nice feature. If you like the CBO then you must love the Tuning Advisor, because it is just the CBO with more time to optimize, and more ways to optimize. The fact is that when a customer calls me, they expect…

 
Read More