Blog - comments

Hi, yes it's MB. Thanks Hemant. I'm correcting the post.

Is it GBs or MBs ?

Hemant K Chitale
Hi Lothar, Thanks for your comment. I'm looking forward to see you radical idea. Only half solutions...
Hi Lothar, Thanks for your comment. I'm looking forward to see you radical idea. Only half solutions...
I testet BIND_AWARE a year ago, Some improvement, but still not the plan I wanted. I talked to Moham...
Blog dbi bloggers Franck Pachot

Franck's Oracle blog

Blog of Franck Pachot, Consultant at dbi services

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
Franck Pachot

Franck Pachot

I'm a Senior Consultant, and Oracle Technology Leader at dbi services (Switzerland).
Certified DBA (OCM 11g, OCP 12c, Performance Tuning Expert, Exadata Implementation) I cover all database areas: architecture, data modeling, database design, tuning, operation, and training.
My preferred area is troubleshooting oracle and performance tuning, especially when I acheive to enable an efficient collaboration between the developers and the
operational team.

As an Oracle Ace, I participate in the Oracle Community in forums, blogs, articles and presentation. You can follow my activity on this blog: RSS and my twitter account: @FranckPachot





The Oracle Database Appliance is really interresting for small enterprises. It's very good hardware for very good price. It's capacity on demand licensing for Enteprise Edition. But small companies usually go to Standard Edition for cost reasons.

Then does it make sense to propose only Enterprise Edition to the small companies that are interrested by ODA?

Hits: 271
Rate this blog entry:
Continue reading 0 Comments

Can we shutdown abort a PDB? Let's try:

SQL> show con_id
SQL> shutdown abort;
Pluggable Database closed.

But is it really a shutdown abort?

Hits: 286
Rate this blog entry:
Continue reading 0 Comments

Sometimes we have to index on ( trunc(date) ) because a SQL statement uses predicate on it instead of giving a range from midnight to midnight. When you do that you probably keep the index on the column. That's two indexes to maintain for DML. Do we need it?

Tagged in: Oracle 11g
Hits: 346
Rate this blog entry:
Continue reading 0 Comments

Various methods of table compression have been introduced at each release. Some require a specific storage system Some requires specific options. Some are only for static data. And it's not always very clear for the simple reason that their name has changed. 

Name change for technical reasons (ROW/COLUMN STORE precision when a columnar compression has been introduced) or for marketing reason (COMPRESS FOR OLTP gave the idea that other - Exadata - compression level may not be suited for OLTP).

Of course that brings a lot of ambiguity such as:

  • HCC is called 'COLUMN STORE' even if it has nothing to do with the In-Memory columns store
  • COMPRESS ADVANCED is only one part of Advanced Compression Option
  • EHCC (Exadata Hybrid Columnar Compression) is not only for Exadata
  • COMPRESS FOR OLTP is not called like that anymore, but is still the only compression suitable for OLTP
  • HCC Row-Level Locking is not for ROW STORE but for COLUMN STORE. It's suited for DML operation but is different than FOR OLTP. Anyway COLUMN STORE compression can be transformed to ROW STORE compression during updates. And that locking feature is licenced with the Advanced Compression Option, and available in Exadata only... 
  • When do you need ACO (Advanced Compression Option) or not?

Let's make it clear here.

Tagged in: Oracle 12c
Hits: 330
Rate this blog entry:
Continue reading 0 Comments

In the previous post I explained the performance issue encountered when using a generic query to deal with optional search criteria on multiple columns. The statement was shared by all executions, was marked as bind sensitive, but never became bind aware. Let's use the BIND_AWARE hint.

Tagged in: Oracle Performance
Hits: 384
Rate this blog entry:
Continue reading 3 Comments

You have a multicriteria search screen on the EMPLOYEE table where you can enter an employee id, a department id, a manager id or a job id. Either you put the value you want to filter on, or you leave it null when you don't want to filter on it. How will you code that? You can build the query on the fly with dynamic SQL or use a generic query like this one:

       SELECT *
       FROM employees
       WHERE (job_id = NVL(:job_id, job_id))
       AND (department_id = NVL(:department_id, department_id))
       AND (manager_id = NVL(:manager_id, manager_id))
       AND (employee_id = NVL(:employee_id, employee_id))
This is good for the code maintainability, but having a one-fit-all query will not be optimial for each cases. Markus Winand (every database developer should read his book) describes the danger ot that in his website: Use The Index, Luke

Hits: 395
Rate this blog entry:
Continue reading 0 Comments




RAC is the most complex installation you can have for an oracle database. A RAC DBA is involved not only on database, but storage, network, and system as well. It involves also the application in order to be sure that the application service can follow the database service high availability. It's also brings every database skills to the highest level: small contention on single instance database can become a big bottleneck in RAC.

But RAC is also fascinating. It's the highest service availability. When correctly configured you can stop a node without any impact on your users. It's the highest scalability: you are not limited by the number of CPU or the amount of memory that you can put in a server. Just add a node. RAC is not new. Oracle 6 was already able to open the same database from several instances. It was called parallel server.

Do you think it's impossible to learn and practices that king of infrastructure when you don't have already one in your data center? No. You can install and practice RAC on your laptop. This is what RAC Attack! is about: at various events, experienced RAC Attack volunteers (ninjas) will help you address any related issues and guide you through the setup process and you will have a RAC on your laptop. Next month in Las Vegas is the IOUG event: COLLABORATE15. I'll be there as a speaker and I'm also very happy to help as a RAC Attack! Nija. 

Here you can find all information about it:

Hope to see you there.  



Hits: 205
Rate this blog entry:
Continue reading 0 Comments

Enterprise Manager (Cloud Control for example) gathers a lot of metrics. You can display them from the GUI, but you can also query the SYSMAN views directly. Today, I wanted to get the history of free space in an ASM disk group for the previous week. Here is how I got it.

Hits: 410
Rate this blog entry:
Continue reading 0 Comments

I've written about 12c online statistics gathering in a UKOUG OracleScene article. My opinion is clear about it: you sill need to gather stale stats afterwards or you have mising, stale and inconsistent object statistics. This post is about cases where online statistics gathering does not occur (and are not documented) - which is another reason why we can't rely on it.

Tagged in: Oracle 12c
Hits: 309
Rate this blog entry:
Continue reading 0 Comments

I'm using Oracle SQL Developer 4.1 Early Adopter for a while and I like it. That version comes with a command line (in beta) which goal is to be fully compatible with sqlplus but running in java, and having a lot more features. 

Becuse it's connecting with thin java driver by default, it doesn't use NLS_LANG. It's java. It's unicode. So here is how to set the language and characterset with the java options.

Tagged in: Java Oracle
Hits: 195
Rate this blog entry:
Continue reading 0 Comments
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)


Contact us now!

Send us your request!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter