Blog Aggregator - Amardeep Sidhu

We all have encountered this situation many times before: the cost-based optimizer assumes no correlation between two columns (until 11g), and this has the effect of erroneously reducing the cardinality of a row source. Incorrect cardinality estimates are one of many root causes for SQL performance issues. Consider the following example.
This code creates a […]

If they wanted all of the measures by a selection of application stages, or a selection of measures by the same set of application stages we could have used a crosstab and had application stage as one of the dimensions, day of month (say) as the other and we’d have been fine, but as the report used an arbitrary selection of measures and application stages, the only way around this was to “pivot” the fact table by the application stage and derive out the measures using a CASE statement, like this: This would then lead to this measure appearing in the logical fact table as a calculation, rather a normal logical column mapped to a physical column. … I’ve done a fair few OBIEE implementations now and this one reinforced by view that to be good with OBIEE, you need to have three main skills Knowledge of OBIEE, specifically how the physical to logical model translation works, how to use Logical Table Sources, how to derive measures, how to create joins in the Logical and Physical models, and in particular (and this is where experience on deployments, rather than demos comes in) knowledge of all the little quirks, like the non-aggregatibility of calculated columns issue I mentioned earlier on, how to resolve self-joins and multiple joins between tables through Aliases and so on.

I visit Oracle Forums very often. Its a good place to have a look at lots of interesting issues, interact with a lot of genius people from around the globe. I do try to answer questions with my little knowledge. Its an open and free forum but some times , its really weird that how on earth […]

There was a question in Oracle-L mailinglist recently, regarding excessive SQL*Net break/reset to client waiting by a session.
A quote is below:
We are seeing an insert statement reporting “SQL*Net break/reset to client” as over 1/3 of its time.
On the face of it this event suggests network issues but nothing else backs this up as the […]

There was a question in one of the Turkish Oracle mailing lists which got me interested. The question simply was:

I have a table holding the parts of a product and another table holding the suppliers of these parts. How can I find the suppliers which supply all the parts?

Someone suggested using the division operation of relational algebra but did not provide how to do it in Oracle. So I

I spent yesterday looking at the Tablespace Encryption feature in Oracle 11g. If you’ve used the Transparent Data Encryption (TDE) feature in 10g release 2, you’ll see this is more of the same. The difference here is eveything in the tablespace is encrypted, rather than having to decide on a column-by-column basis. Just create the […]

A while ago I explained Nice, what it can do and what it can’t. From this point, whenever someone misunderstood nice, I just pointed at my post.
But yesterday I ran into a misunderstanding that was not covered in the first post:
Boss: Server X is at 100% cpu. Can you renice process 4096?
Me: Sure thing boss. […]

After setting up dg4odbc to connect to a SQL Server database (read ‘Tips on Using dg4odbc on 64-bit Linux’), I spent last week trying to get refreshes of materialized views to work in 11g, using the same DDL as in 8i. I was very frustrated to see the refreshes break in 11g.
The DDL gives no […]