Franck's Oracle blog
Blog of Franck Pachot, Consultant at dbi services
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
As an Oracle Ace, I participate in the Oracle Community in forums, blogs, articles and presentation. You can follow my activity on this blog: 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?
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?
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.
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.
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
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.
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.
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.