Bloom Filter bug – Crash 10.2.0.2

ORA-600 [kghfrh:ds] errors leading to a PMON instance crash.

Solution:
Change the _BLOOM_FILTER_ENABLED to false.

“The Bloom filter, conceived by Burton H. Bloom in 1970, is a space-efficient probabilistic data structure that is used to test whether an element is a member of a set. False positives are possible, but false negatives are not. Elements can be added to the set, but not removed (though this can be addressed with a counting filter). The more elements that are added to the set, the larger the probability of false positives.” Definition of Bloom filter by wikipedia

Whole map of views

How to resolve SQL object and column names all the way to base tables and columns in Oracle?

Filed under: Performance, SQL, Tools — tanelp @ 1:23 pm

If you have been involved in tuning SQL code which you have never seen before, you are probably familiar with the challenges of understanding what the code is trying to do. This can be especially time consuming when the SQL references lots of views, which reference views, which reference more views etc. So there may be a large information gap between the SQL statement (like select * from some_crazy_10_level_view) and the actual execution plan (referencing 10s of tables, with evidence of query transformations).

So unless you see something really obvious from the execution plan, you need to start mapping the SQL query and view texts back to the physical base tables which Oracle eventually has to access. This can be a tedious and boring (!) process.

The good news is that in Oracle 10.2+ there’s a hidden parameter that can do this mapping task for us. Let’s see an example:

I create a view on a view to illustrate the point:

SQL> create view myview as select * from all_users;  View created.  

Now let’s set that parameter _dump_qbc_tree to 1 and run a query against the view:

SQL> alter session set "_dump_qbc_tree"=1;  Session altered.  SQL> select count(*) from myview;    COUNT(*) ----------         31  

Now let’s look into the server process tracefile:

*** ACTION NAME:() 2007-09-16 12:19:57.500 *** MODULE NAME:(SQL*Plus) 2007-09-16 12:19:57.500 *** SERVICE NAME:(SYS$USERS) 2007-09-16 12:19:57.500 *** SESSION ID:(146.1984) 2007-09-16 12:19:57.500 QCSDMP: ------------------------------------------------------- QCSDMP:  SELECT: (qbc=2B8D1C28) QCSDMP:    . (COUNT(*)) (opntyp=2 opndty=0) QCSDMP:  FROM: QCSDMP:    .MYVIEW QCSDMP:      VQB: QCSDMP:        SELECT: (qbc=2B8D163C) QCSDMP:          .USERNAME QCSDMP:        FROM: QCSDMP:          .ALL_USERS QCSDMP:            VQB: QCSDMP:              SELECT: (qbc=2B8CAF78) QCSDMP:                U.NAME (USERNAME) QCSDMP:              FROM: QCSDMP:                SYS.TS$ (TTS) QCSDMP:                SYS.TS$ (DTS) QCSDMP:                SYS.USER$ (U)  

Here it is, the query text generated directly from parse tree, showing the base tables regardless that they had been hidden behind multiple views.

Also there’s few interesting things to note:

By Tanel Poder

http://blog.tanelpoder.com/category/performance/