Archive for July, 2013

How NLS settings can affect Oracle’s join behavior – v$ example

Recently, I had a problem with accessing Active Session History being not fast enough on Oracle Database 11.2 and 12.1. Looking at the explain plan, wow, no doubt why: Accessing two fixed tables with TABLE ACCESS FULL and joining them with NESTED LOOP. This couldn’t have been “works as designed”, it would render gv$active_session_history nearly useless. But from the beginning.

My query

select * 
 from gv$active_session_history
 where SAMPLE_TIME>sysdate -1
 and sql_id='f29fxwd5kh2pq';

And it simply does not come back within an hour. So what?

Read more…


By Martin Klier in Oracle  .::. (Add your comment)

Oracle SQL: Optimizing a WHERE predicate inequality with a Function Based Index

Intro

Recently, on Oracle 11.2.0.3, I saw a join of three tables, using three times TABLE ACCESS FULL and HASH JOIN, without an obvious reason. During separating and understanding its components, the following scenario turned out to be the culprit.

select *
 from CARDTEST
 where OBJECT_ID<DATA_OBJECT_ID;

The optimizer is unable to calculate the cardinality of the “OBJECT_ID<DATA_OBJECT_ID“ predicate, and thus, it ends up with underestimating the cardinality, and using a mass access path. (The “gather_plan_statistics” hint was added to see details as real buffer gets and does not change the behavior.)

Execution Plan 1

Execution Plan 1

Fighting a loosing battle(?)

So how does this come? Oracle statistics (even histograms) only store information about the distribution of values in a column. The optimizer can’t combine two column statistics in a way, that would allow to quantify “one is smaller than the other” over the whole table. The CBO can’t combine column statistics anyway, that’s why we have to use extended statistics. But in this case, we simply can’t create an extended statistic representing an inequality predicate.

Read more…


By Martin Klier in Oracle  .::. Read Comments (4)


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.