Last week I hit an interesting performance issue:
A Table (T) has 2 index (of interest). One (IX_1) is only on column S, the other (IX_2) on (C, S).
The optimizer calculates the plan with IX_1 more expensive than the plan with IX_2 - as this should be more selective.
But the gets for the plan with IX_1 were less than those the optimizer preferred.
Here the information about the statement, index and plans.
wrote a blog about differences between bind peeking and SYS_CONTEXT
in SQL queries. This even led to an proposal in Database Ideas: CBO should peek at SYS_CONTEXT values just like bind values
(feel free to vote and comment, if you like it)
As I have a friend who really loves SYS_CONTEXT; I showed him the blog.
In the following discussion he stated (more/less
I should not have this problem at all, as (more...)
This post is inspired
by Tim Hall
Thinking about "what is my single favourite feature of Oracle", when all the products of Oracle (or at least all I know about) can be covered sounds stupid or impossible. There are far to many features which could be interesting, worth or made my life easier over my years in IT.
So I decided to pick something more generic.
The "feature" I like most is ONLINE.
Oracle DIRECTORY objects are very nice to handle access for external tables or other procedures.
It just can be challenging to enable proper access to those files for other users than the database user (I call it oracle
If there are any concerns to allow any access to the databases host (except for DBAs and OS admins) it get's tricky fast.
One possibility is to use a NFS mount and make the directory available (more...)
Today I had some fun identifying how a ORA-08176 can happen.
It started with a ticket similar to "we get an ORA-08176 during a select, please fix the database."
After some questions it refined to "we do a CREATE TABLE x AS SELECT in one session, and a SELECT * FROM X@db_link throws the ORA-08176.
That's enough for me to do a test-case, but I needed to change the isolation level to produce the (more...)
Today I got a ticket from a developer where he claimed Oracle has a bug somewhere. The reason for this claim was a statement similar toSELECT *
WHERE tab1col1 IN
( SELECT tab1col1
This query returned rows.
But when he run the inner query on it's own, he receivedORA-00904: "TAB1COL1": invalid identifier00904. 00000 - "%s: invalid (more...)
In Version 12.1 Oracle introduced the Grid Infrastructure Management Repository
) called ‑MGMTDB.
This self managed pluggable database is a required component of Grid Infrastructure and should never require direct interactions. (there can be some interactions when you want to migrate to different diskgroups, but also those activities are covered within wrapper scripts provided by Oracle).
Every interaction with the data stored in this DB is done through applications - oclumon might be (more...)
About 6 years ago I wanted to know which instance parameters are derived from cpu_count
. So it tested a 188.8.131.52 DB - in that version there 21 parameters changed based on the value of cpu_count.
Some DB versions passed by so I decided it's time for another check. This time it's 184.108.40.206 without any PSUs/patches. The machine is the same class as previous, so it took some time.
A proper management of ASM Disks can be a complicated task.
I discussed with Martin Bach
about the concept in my current company, where we implemented a setting which is consistent, robust and enables Storage, Linux and DB admins to work together easily.
As we started to think about ASM when 10.1 was out we tried to evaluate our possibility. asmlib
was discarded quite early as it only increased complexity without additional (more...)
In Oracle database the vie DBA_FEATURE_USAGE_STATISTICS is given to
display information about database feature usage statistics.
That's all I can find in The (12.1) Docu
Just in case anyone asks - DBA_FEATURE_USAGE_STATISTICS is not mentioned in the license guide at all:
Still for me it's interesting what's behind the view, and how it's content is populated.
So I started to analyze it's anatomy.
Starting with the view definition (slightly rewritten for better readability):
can fail if doing manual out of place patching, when the new ORACLE_HOME
is not cloned from previous one but a fresh installation. The problem is a one-of patch in the old ORACLE_HOME
, which is included in something else (like a PSU) in the new ORACLE_HOME
In my specific situation the old O_H
had PSU 220.127.116.11.3 + some one-off + Patch:21355879
This Patch is not needed anymore in my new O_H (more...)
is a nice new feature in recent Oracle database installations. It helps to ensure the databases objects match the binaries after any kind of patching and so avoid situations which can be an operational and support nightmare - and very hard to identify.
Unfortunately it has some drawbacks as well.
One of those I hit recently when running datapatch on 2 instances which uses the same ORACLE_HOME.
At some time in it's progress (more...)
In my current company we are preparing a migration of our billing application to a new version. During this migration there are the ordinary changes of infrastructure as well. Application servers from HPUX to Linux, database servers from RH5 to RH6, different storage subsystem, Oracle from 10.2 to 12c, different application partitioning, and so on ...
At least from the management perspective the expectation is clear: everything is shiny new and costed a lot of (more...)
As you might know already, I'm a big fan of Oracle Traces. Consequently I'm a fan of Method-R tools
to work with these files as well.
A very important part in the chain of tools is Method R Trace - an extension to SQL Developer - as it speeds up developers a lot: When they generate a tracefile, they can access it immediately and do not need to ask and wait for a DBA to (more...)
These days SQL Developer
4.1 GA was released. If you use SQL Developer, I'd recommend an update.
Nearly at the same time Method R Trace
18.104.22.168 extension for SQL Developer was released as well. Especially for developers that's a very important news - at least if they concern about performance.
A new Version of this extension is needed as SQL Developer changed it's extension framework at the upgrade from version 3 (more...)
this is just an online docu of ORADEBUG DOC in 22.214.171.124.
The general comments from Tanel Poder
apply to this version as well.
In my current company we try to stabilize the performance of our Peoplesoft application. So on a more or less regular base I get a call to investigate as "it" is "slow" now.
During my research I found one error-pattern I'd like to show here: SQLs which where parsed for very selective BINDs (like customer-id) are executed with a single space (" ") as bind.
Unfortunately in Peoplesoft this character is used similar to NULL - (more...)
As a huge friend of Oracles SQL trace I also see it's limitations as well. One of these limitations is visible in it's underlying infrastructure: the wait interface. It's only about waits
There is another source of information as well, you could call it the DO interface, but the common term is session and system statistics. there Oracle counts what it is doing.
Unfortunately I do not know a simple way to show both realities (more...)
Recently I received an email with an simple content:
"It" is slow again :-)
The email explained a package to be slow.
Ok, I started with my "not enough information" reply and an email conversation began.
We are still not ready to identify the problem, but as there is no (more...)
Recently I was hunting a performance problem at work.
A developer read about adaptive cursor sharing
and therefore guessed, the optimizer would know about the number of rows when he passes a collection for a table(:bind) function. I can totally understand it, as there where no limitations in the statement (more...)