Partition stats usage with Bind peeking disabled

Last week, I was working on a package, which is used to collect stats.

I realized we collect stats of all partioned tables,indexes at both partition and global level.

While working on that package, i was pondering when will we actually use partition/local level stats (over global stats) in real-world scenario as most of our "where" clause 

values are passed using bind variables. How will optimizer will come to know which partition (stats) to use because value passed will checked run time except in the case when it 

is run for the first time and Bind peek happens.

A bit confused with this I performed a test, where my test case was inspired by David Aldridge's blog post (however i conducted a different set of tests).

I first checked the difference in 10053 trace in sqls where we pass literals Vs bind variables - when bind peek is enabled (default - enabled, but not in our case).

Test case:

============

drop table test_par

/

create table test_par

   (

   col1 number not null,

   col2 number not null

   )

nologging compress pctfree 0

partition by range (col1)

(partition p1_to_4 values less than (5),

partition p5 values less than (6)

)

/

insert /*+ append */ into test_par

select mod(rownum,4)+1,rownum

from dual

connect by level <= 100000

/

insert into test_par values(5,10);

commit;

create index idx02_test_par

on test_par (col2)

local nologging

/

begin

DBMS_STATS.GATHER_TABLE_STATS (

   ownname          => user,

   tabname          => 'test_par',

   partname         => null,

   estimate_percent => 100,

   block_sample     => false,

   method_opt       => 'for all columns size 1',

   degree           => null,

   granularity      => 'ALL',

   cascade          => true,

   stattab          => NULL, 

   statid           => NULL,

   statown          => NULL,

   no_invalidate    => FALSE);

end;

/

So - i have 75000 rows in partition p1_to_4 and 1 row in p5 

SQL> select count(*),col1 from test_par partition(p1_to_4) group by col1;

  COUNT(*)       COL1

---------- ----------

     25000          1

     25000          2

     25000          4

     25000          3

SQL>  select count(*),col1 from test_par partition(p5) group by col1;

  COUNT(*)       COL1

---------- ----------

         1          5

Now we have the set up ready, we have to enable the trace and check which stats are used when.

Case 1: - Bind peek enable. I use literal value to query table and see which stats are used.

SQL> alter session set "_optim_peek_user_binds"=true;

SQL> alter session set events '10053 trace name context forever, level 2';

SQL> select * from test_par where col1=2 and col2=5;

Now checking the trace file, we see:

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TEST_PAR  Alias: TEST_PAR  Partition [0]  -- Do you see Partition stats getting used?

    #Rows: 100000  #Blks:  149  AvgRowLen:  7.00

    #Rows: 100000  #Blks:  149  AvgRowLen:  7.00

Index Stats::

  Index: IDX02_TEST_PAR  Col#: 2  PARTITION [0]

    LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 149.00

    LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 149.00

***************************************

So, with Bind peek enabled - we use partition stat (when we should) when literals are used

Case 2: Bind peek enable. I use Bind value to query table and see which stats are used.

SQL> alter session set "_optim_peek_user_binds"=true;

SQL> alter session set events '10053 trace name context forever, level 2';

SQL> variable lcol1 number;

SQL> variable lcol2 number;

SQL> execute :lcol1:=2;

SQL> execute :lcol2:=5;

SQL> select * from test_par where col1=:lcol1 and col2=:lcol2;

Trace file:

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TEST_PAR  Alias: TEST_PAR  Partition [0]  -- Here again, partition stats getting used

    #Rows: 100000  #Blks:  149  AvgRowLen:  7.00

    #Rows: 100000  #Blks:  149  AvgRowLen:  7.00

Index Stats::

  Index: IDX02_TEST_PAR  Col#: 2  PARTITION [0]

    LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 149.00

    LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 149.00

***************************************

Since this was the first time, we used this sql, Bind peek had to happen.

This is confirmed by following section of trace file:

*******************************************

Peeked values of the binds in SQL statement

*******************************************

kkscoacd

 Bind#0

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=00 csi=00 siz=48 off=0

  kxsbbbfp=2a973589d8  bln=22  avl=02  flg=05

  value=2

 Bind#1

  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

  oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24

  kxsbbbfp=2a973589f0  bln=22  avl=02  flg=01

  value=5

CASE 3: Bind peek enabled - I use Bind value to query table and see which stats are used. -- This will be 2nd time - so this time i dont expect peeking to happen.

SQL> alter session set "_optim_peek_user_binds"=true;

SQL> alter session set events '10053 trace name context forever, level 2';

SQL> variable lcol1 number;

SQL> variable lcol2 number;

SQL> execute :lcol1:=5;

SQL> execute :lcol2:=10;

SQL> select * from test_par where col1=:lcol1 and col2=:lcol2;

Trace file:

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TEST_PAR  Alias: TEST_PAR  Partition [0] -- Part Stats .. again

    #Rows: 100000  #Blks:  149  AvgRowLen:  7.00

    #Rows: 100000  #Blks:  149  AvgRowLen:  7.00

Index Stats::

  Index: IDX02_TEST_PAR  Col#: 2  PARTITION [0]

    LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 149.00

    LVLS: 1  #LB: 222  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 149.00

***************************************

In this case - we still use old execution path and old stats - which is agony of 10g. 

We had only one row for this partition and we ended up using INDEX access when direct access would be really fast.

I heard about Adaptive cursor sharing in 11g will avoid this, but still to run tests on it.

Now we will run the same tests by keeping the bind peeking disabled (as in my environment)

CASE 4: Bind peek disabled - we use literals

SQL> alter system flush shared_pool;

SQL> alter session set "_optim_peek_user_binds"=false;

SQL> alter session set events '10053 trace name context forever, level 2';

SQL> select * from test_par where col1=5 and col2=10;

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TEST_PAR  Alias: TEST_PAR  Partition [1] -- Picked the right stats of right partition

    #Rows: 1  #Blks:  1  AvgRowLen:  6.00

    #Rows: 1  #Blks:  1  AvgRowLen:  6.00

Index Stats::

  Index: IDX02_TEST_PAR  Col#: 2  PARTITION [1]

    LVLS: 0  #LB: 1  #DK: 1  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00

    LVLS: 0  #LB: 1  #DK: 1  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00

***************************************

Literals - as seen knows where to go.

CASE 5: Bind peek disabled - we use binds

SQL> variable lcol1 number;

SQL> variable lcol2 number;

SQL> execute :lcol1:=5;

SQL> execute :lcol2:=10;

SQL> alter session set "_optim_peek_user_binds"=false;

SQL> alter session set events '10053 trace name context forever, level 2';

SQL> select * from test_par where col1=:lcol1 and col2=:lcol2;

Trace file:

***************************************

BASE STATISTICAL INFORMATION

***********************

Table Stats::

  Table: TEST_PAR  Alias: TEST_PAR  (Using composite stats)  -- These are global stats 

    #Rows: 100001  #Blks:  75  AvgRowLen:  7.00

Index Stats::

  Index: IDX02_TEST_PAR  Col#: 2

    USING COMPOSITE STATS

    LVLS: 1  #LB: 223  #DK: 100000  LB/K: 1.00  DB/K: 1.00  CLUF: 150.00

***************************************

So - the conclusion is :

When we have BIND peeeking disabled, and with usage of bind values - we are most likely (any one of you could come up where above doesnt hold good) NOT using partiton stats.

Oracle Bitmap indexes - implementation

Oracle uses BBC (Byte aligned Bitmap Code) 1-sided algorithm to store Bitmap indexes.

 

What is BBC?

 

Thinking of Bitmap indexes, most of us recall a simple Bitmap Index such as the one mention below:

Columns	Row 1	Row 2	Row 3	Row 4	Row 5	Row 6	Row 7	Row 8	Row 9
Red	0	0	0	0	0	0	0	1	1
Green	1	1	0	0	0	0	1	0	0
Blue	0	0	1	1	1	1	0	0	0

Fig: 1 - Table having 9 rows with data distribution shown as 0 and 1.

 

In the above style of storing bits is uncompressed i.e. all 0s and 1s are stored as it is. So, while doing any bitwise operation (and/or/xor), we use the uncompressed version of bitmaps.

 

Potential drawbacks of using this technique:

1. high storage cost
2. high query cost

 

Can compression be achieved?

Yes, we can compress 0s and 1s which are consecutive. Think of compressing seven 0s as one with a special header saying that this 0 is equivalent to seven 0s. 

 

But this has its own disadvantage. For any bit-wise operation, I have to first decompress bitmap indexes in question and do the “and”/”or” operation, which outweighs any savings in storage costs.

 

So, the need was for a compress bitmap technique using which bit-wise operations could be done on the compressed state.

One such example of such techniques is BBC (used by Oracle) – called Byte aligned bitmap code.

 

This technique of bitmap compression is based on run-length encoding. Using this encoding BITMAPs are aligned along the BYTE boundaries. Each aligned BYTE is called a gap-byte (GBYTE) if all the bits of that BYTE store the same logical value (0 or 1). If it’s a mix of bits having different logical values, its called a non-gap-byte or map-byte (MBYTE). Adjacent bytes of the same class are grouped and controlled by a control byte (CBYTE), which keeps the information whether the bytes following it are MBYTE or GBYTE and if its GBYTE what is the length of bytes? And if it’s a MBYTE, which type of MBYTE (is it an offset or not? – offset is a MBYTE having only single bit different from other bits in that byte)

 

In the above explanation, a gap-byte could be for 0 or for 1. so, it is called 2-sided BBC.

Oracle uses 1-sided BBC, in which only 0s are candidate for GBYTE not 1s. All 1s will still make an MBYTE in Oracle. I’m not sure why Oracle uses 1-sided BBC technique. It could be due to integration complexities with other part of RDBMS-engine. 

But going back on the question, why do we need Byte alignment when I can compress without it?

1. Computers are happy dealing with BYTEs than BITS.
2. I can do BIT-WISE operations as efficiently (in fact better) as I can do in decompress form.
3. Control Bytes (CBYTES) are very handy in decoding the compress BITMAP.

 

Can this BITMAP compression and query response get better?

From what I see after doing research on net, the answer is Yes.

 

The technique of handling the bits BYTEs way has gotten better by handling the WORD way.

 

The concepts are similar to the ones followed in BBC, the major difference is aligning the bits using “word” rather than “byte”. Computers today are more friendly dealing in words (multiple bytes) than on byte.

 

 

I will write more on word aligned hybrid compression technique in perspective of oracle in coming days.

 

Thanks for reading!

 

 

Inputs:

Patent# 5363098 – by Antoshenkov, Gennady  (Oracle corp).

ORA-14097 - when using materialized view and partition table

This one was an interesting issue which came up few days back. I spent quite sometime before I solved it.

Issue was - A developer came upto me and told that he is getting "ORA-14097 - : column type or size mismatch in ALTER TABLE EXCHANGE PARTITION" while exchanging partitions.

This guy is working on a warehouse design in which huge data loads will happen on base tables through out the day and there are mviews based on which it mines and these mviews' data should be moved fact tables at midnight 12.

Now we strategize in a way that at midnight when base tables will be truncated for fresh load of next day, we exchange the partitions of these mviews with temporary partitioned tables and use "Insert /*+ append */" to move it to final fact tables. We could not have directly exchanged partitions of fact table as they might not be partitioned by day.

Now the above is all background. While investigating the issue, I was perplexed for some time when he showed me the issue. He created a temporary partitioned table using "create table as select .. the mview where 1=2" and while doing exchange partition he was getting ORA-14097.

Let me give you a simple test case:

SQL> create table test(a number);

Table created.

SQL> create materialized view log on test with rowid including new values;

Materialized view log created.

SQL> create materialized view test_mv refresh fast on demand with rowid as select * from test;

Materialized view created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('test_mv','F')

PL/SQL procedure successfully completed.

Now both table and mview have on erow each.

Let's try and exchange partition of this mview with another table.

SQL> select partition_name from user_tab_partitions where table_name='TEST_PART';

PARTITION_NAME
------------------------------
SYS_P3446

SQL> alter table test_part exchange partition SYS_P3446 with table test_mv;
alter table test_part exchange partition SYS_P3446 with table test_mv
*
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION


I used CTAS, there is no question abt data type and column order.

SQL> desc test_part
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER

SQL> desc test_mv
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER


After doing some research, i got through this metalink article: 72332.1

According to that : "If a table has a FUNCTIONAL index on it there is an extra hidden column in COL$ which will cause an EXCHANGE to fail. Comparing USER_TAB_COLUMNS will not show this, nor will USER_UNUSED_COL_TABS but COL$ will show the difference."

SQL> select col#, name
2 from sys.col$
3 where obj# in
4 (select object_id from user_objects where object_name = 'TEST_MV');

COL# NAME
---------- ------------------------------
0 M_ROW$$
1 A

Now there you go - this M_ROW$$ was creating the problem for me.

Old saying - identifying is problem is 80% of tak that entails solving it.

Now - i created test_mv using primary key (not using rowid) and the whole exchange process worked fine!

For those who donot have PK in their tables can consider having a separate column which can be seeded using a sequence and treat that as PK to combat this issue.

Though it was trivial issue and solution. it kept me thinking for some time!

Query result cache in 11g

In 11g its possible to store the result of the SQL in a special section of shared pool called RESULT CACHE.

As the name suggests, this cache stores results. This makes subsequent SQLs run damn fast resulting in better performance.

To me, this features sounds like Materialized view - because those also when introduced boosted the performance of queries which used to take a lot of time while computing the result. The resultant data of mview was stored in table (i.e. segment/disk). And this case the resultant data is stored in memory. Though memory not being persistent, this feature still helps in subsequent runs of the same query.

The feature is broadly governed by init.ora parameter - RESULT_CACHE_MODE - which can be changed using "alter system" or "alter session". The default value of this parameter is MANUAL. You can switch it to FORCE (not recommended).

Now after this parameter has taken effect, you can modify your SQL to see its affect.

SQL> select /*+ result_cache */ deptno,sum(sal) from emp group by deptno;

DEPTNO SUM(SAL)
---------- ----------
30 9400
20 10875
10 8750

NOw run this query again - with hint
and this time with autotrace traconly explain option:

SQL> set autotrace traceonly exp
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 4067220884

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 |
| 1 | RESULT CACHE | bw8bd7rpb6h0sg1rcrwyx3rz0x | | | | |
| 2 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Here you see data being fetched from result cache.

There are some dynamic views which can give more insight.

SQL> select table_name from dict where lower(table_name) like '%result%';

TABLE_NAME
------------------------------
DBA_METHOD_RESULTS
USER_METHOD_RESULTS
ALL_METHOD_RESULTS
V$CLIENT_RESULT_CACHE_STATS
GV$RESULT_CACHE_DEPENDENCY
GV$RESULT_CACHE_MEMORY
GV$RESULT_CACHE_OBJECTS
GV$RESULT_CACHE_STATISTICS
V$RESULT_CACHE_DEPENDENCY
V$RESULT_CACHE_MEMORY
V$RESULT_CACHE_OBJECTS

TABLE_NAME
------------------------------
V$RESULT_CACHE_STATISTICS
GV$CLIENT_RESULT_CACHE_STATS
CLIENT_RESULT_CACHE_STATS$



I used V$RESULT_CACHE_OBJECTS:

1* select id,status,block_count,name from V$RESULT_CACHE_OBJECTS
SQL> /

ID STATUS BLOCK_COUNT
---------- --------- -----------
NAME
----------------------------------------------------------------------------

0 Published 1
SCOTT.EMP

1 Published 1
select /*+ result_cache */ deptno,sum(sal) from emp group by deptno


Similar to the hint "result_cache" - there is another hint - "no_result_cache"

>> no_result_cache is to be used when you have set database parameter: RESULT_CACHE_MODE to FORCE - which means oracle will try to cache the result of all queries.

Ex: select /*+ no_result_cache */ deptno,sum(sal) from emp group by deptno

To see the memory utlization - there are few dynamic views, database functions available.

SQL> set serverout on
SQL> exec dbms_result_cache.memory_report
R e s u l t C a c h e M e m o r y R e p o r t
[Parameters]
Block Size = 1K bytes
Maximum Cache Size = 3488K bytes (3488 blocks)
Maximum Result Size = 174K bytes (174 blocks)
[Memory]
Total Memory = 136340 bytes [0.064% of the Shared Pool]
... Fixed Memory = 5140 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 131200 bytes [0.061% of the Shared Pool]
....... Overhead = 65664 bytes
....... Cache Memory = 64K bytes (64 blocks)
........... Unused Memory = 29 blocks
........... Used Memory = 35 blocks
............... Dependencies = 1 blocks (1 count)
............... Results = 34 blocks
................... SQL = 34 blocks (34 count)

PL/SQL procedure successfully completed.

There is again an init.ora parameter governing the size of max result cache size i.e result_cache_max_size

This value is also seen in dbms_result_cache.memory_report output in "Maximum Cache Size " header.

Conclusion: SQL Result cache seems to be a great feature which can immensly improve the sql performance of many applications SQLs. I wish there are no major bugs in this :)

Note: Its just the begining that I have started looking into these features. If you happen to research something which I may have missed, please add to the note.

Password is case sensitive now - Oracle 11g

Good news for people who always wanted oracle schema passwords to be case sensitive.
Oracle has done this in 11g.

Just to show an example:

C:\Documents and Settings\sachin.arora>sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Mar 28 15:02:56 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user test identified by AroRa default tablespace users;

User created.

SQL> grant create session to test;

Grant succeeded.

SQL> connect test/arora
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect test/AroRa
Connected.

With 11g, Oracle has made significant changes in password maintenence algorithm.
The password verifier is based on 160 bit SHA-1 hashing algorithm. This empowers Oracle to not only allow case sensitive passwords, but also enable the users to keep a strong password that can include special characters.

For pre-11g users, when they migrate to 11g, their password still behave password insensitive. However, i feel its good to take the advantage of this useful feature and change the password.

For applications which cant afford to change their passwords for xyzee purpose, Oracle has supplied an init.ora i.e. SEC_CASE_SENSITIVE_LOGON that can be set to true/false.
Default value of this parameter is true which means passwords should be case sensitive.

Memory parameters in 11g - MEMORY_TARGET and MEMORY_MAX_TARGET

Few days back, I was checking metalink and came across few good notes on 11g memory management.
Though we are not using 11g in our development environment, i have downloaded a copy of 11g rel1 on my windows machine.

Testing out these new paarmeters gave me an insight on how Oracle has improvised its memory management.

Oracle has introduced 2 new parameters (along with many others!) - MEMORY_TARGET and MEMORY_MAX_TARGET

Using these parameters, you can manage SGA and PGA together rather than managing them separately (using SGA_TARGET, SGA_MAX_SIZE , PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY in 10g)

If you set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_SIZE to 0 and set MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to non zero value, Oracle will manage both SGA components and PGA together within the limit specified by you.

For instance :
If MEMORY_TARGET is set to 1024MB, Oracle will manage SGA and PGA components within itself.

If MEMORY_TARGET is set to non zero value:

• SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET is allocated to SGA and rest 40% is kept for PGA.
• SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum values.
• SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).
• PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes. SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).

With this version, oracle has become smart as in exchanging memory between SGA and PGAs. This is a huge achievement.
When starting up, Oracle takes up memory equal to MEMORY_TARGET (or MEMORY_MAX_TARGET if mentioned) from Operating System RAM and manage its reqources within itself.
This feature helps DBA to allocate chunk of memory to a particular instance without worrying about the subcateogary allocations of different components.


I tested out few of these experiments mentioned above. Here are the results:

Test 1: When Memory_target is 1G

init.ora parameter
> memory_target=1073741824
> sga_max_size=0

SQL> startup
ORACLE instance started.

Total System Global Area 640303104 bytes
Fixed Size 1335024 bytes
Variable Size 226492688 bytes
Database Buffers 406847488 bytes
Redo Buffers 5627904 bytes
Database mounted.
Database opened.

>> It seems around 600M is used for SGA. The decesion of using 600 MB of SGA is of Oracle itself.

SQL> sho parameter sga

NAME TYPE VALUE
------------------------------------ ----------- -------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 612M
sga_target big integer 0

Test 2: When MEMORY_TARGET is 2g (with no change in any other parameter)

SQL> alter system set memory_target=2048m scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1288978432 bytes
Fixed Size 1339344 bytes
Variable Size 226492464 bytes
Database Buffers 1052770304 bytes
Redo Buffers 8376320 bytes
Database mounted.
Database opened.

SQL> sho parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1232M
sga_target big integer 0

Test 3: When sga_max_size/sga_target=700M and memory_target=1G

SQL> startup
ORACLE instance started.

Total System Global Area 732352512 bytes
Fixed Size 1335696 bytes
Variable Size 192941680 bytes
Database Buffers 532676608 bytes
Redo Buffers 5398528 bytes
Database mounted.
Database opened.
SQL> sho parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 700M
sga_target big integer 700M
SQL>
SQL> sho parameter memory

NAME TYPE VALUE
------------------------------------ ----------- ---------------------
hi_shared_memory_address integer 0
memory_max_target big integer 1G
memory_target big integer 1G
shared_memory_address integer 0

This time, oracle has acknowledged the SGA_MAX_SIZE parameter.

Cost, selectivity and cardinality - all changes in 10g rel2 10053 trace file

Few days back o one of the google groups, there was issue posted by a gentleman about "index selectivity". Per him the "index selectivity" should be different than what is being shown by the optimizer. He was inquiring whether the procedure he followed to calculate "index selectivity" is correct or not.

Here is what he had to say:

I don't know how the optimizer has calculated ix_sel of the index
IDT_FCMO_TMS.
Oracle version: 10.2.0.3.0
Any idea?

SQL Statement:
select /* PRUEBA */ count(*) FROM TDTC_CAPACIDAD_MODALIDAD WHERE
CMO_CODTSG = 'HT' AND CMO_CODTMS = 'DBL

10053 Trace:

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TDTC_CAPACIDAD_MODALIDAD Alias: TDTC_CAPACIDAD_MODALIDAD
#Rows: 82240 #Blks: 1132 AvgRowLen: 82.00
Index Stats::
Index: IDT_FCMO_CHA Col#: 5
LVLS: 1 #LB: 170 #DK: 939 LB/K: 1.00 DB/K: 14.00 CLUF:13425.00
Index: IDT_FCMO_TMS Col#: 34 <----- the index
LVLS: 1 #LB: 214 #DK: 471 LB/K: 1.00 DB/K: 26.00 CLUF:12648.00 <----- the index
Index: IDT_FCMO_TSU Col#: 31
LVLS: 0 #LB: 1 #DK: 58 LB/K: 1.00 DB/K: 6.00 CLUF: 370.00
Index: RDT_PCMO Col#: 1 2
LVLS: 1 #LB: 206 #DK: 82061 LB/K: 1.00 DB/K: 1.00 CLUF:28928.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#3): CMO_CODTSG(VARCHAR2)
AvgLen: 3.00 NDV: 21 Nulls: 0 Density: 0.047619 <---------- first column of the index
Column (#4): CMO_CODTMS(VARCHAR2)
AvgLen: 5.00 NDV: 387 Nulls: 0 Density: 0.002584 <---------- second column of the index
Table: TDTC_CAPACIDAD_MODALIDAD Alias:
TDTC_CAPACIDAD_MODALIDAD
Card: Original: 82240 Rounded: 10 Computed: 10.12 Non Adjusted:
10.12
Access Path: TableScan
Cost: 184.60 Resp: 184.60 Degree: 0
Cost_io: 181.00 Cost_cpu: 29454495
Resp_io: 181.00 Resp_cpu: 29454495
Access Path: index (index (FFS))
Index: IDT_FCMO_TMS
resc_io: 36.00 resc_cpu: 15484960
ix_sel: 0.0000e+00 ix_sel_with_filters: 1
Access Path: index (FFS)
Cost: 37.89 Resp: 37.89 Degree: 1
Cost_io: 36.00 Cost_cpu: 15484960
Resp_io: 36.00 Resp_cpu: 15484960
Access Path: index (AllEqRange)
Index: IDT_FCMO_TMS
resc_io: 1.00 resc_cpu: 42971
ix_sel: 0.0021231 ix_sel_with_filters: 0.0021231 <-------------------- wrong ix_sel?
Cost: 1.01 Resp: 1.01 Degree: 1
Best:: AccessPath: IndexRange Index: IDT_FCMO_TMS
Cost: 1.01 Degree: 1 Resp: 1.01 Card: 10.12 Bytes: 0
***************************************

IX_SEL should be 0.047619 * 0.002584 (which is far from what optimizer showed i.e 0.0021231)

According to Jonathan lewis book "Cost-Based Oracle Fundamentals", it is well explain in Chapter-4 P 66,67 that in case of select using multiple column index, index selectivity is calculated using the multiplication of individual selectivities of columns.
And that is precisely what the person seeking support showed.

While testing this result on 10.2.0.2, I found the index selectivity never gets multiplied in case of multicolumn index. In fact optimizer calculates index selectivity based on "distinct keys" of index.In this case (per the trace shown above) it is 471.
So the index selectivity comes out to be 1/(DK of index)=1/471=0.0021231, which is what is shown by optimizer.

I think, by the time Jonathan's book was published, not all the changes were tested. In fact I visited his blog and some couple of finding similar to this posted there.

ORA-01115 and ORA-27067 during crash recovery

Yesterday, one of our development server got a power outage because of which all our databases running on that server crashed.

After the server was brought up, I started these databases manually. One of the db refused to startup with normal "startup" command.

Here was the front-end error.

SQL> startup
ORACLE instance started.

Total System Global Area 432013312 bytes
Fixed Size 2169944 bytes
Variable Size 356115368 bytes
Database Buffers 62914560 bytes
Redo Buffers 10813440 bytes
Database mounted.
ORA-01115: IO error reading block from file 2 (block # 575701)
ORA-01110: data file 2: '/mnt/user/oracle/data/initdb/undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1572864

And here is what I saw in alert.log

ALTER DATABASE OPEN
Wed Mar 12 10:01:14 2008
Beginning crash recovery of 1 threads
parallel recovery started with 2 processes
Wed Mar 12 10:01:15 2008
Started redo scan
Wed Mar 12 10:01:16 2008
Completed redo scan
130874 redo blocks read, 760 data blocks need recovery
Wed Mar 12 10:01:16 2008
Started redo application at
Thread 1: logseq 49756, block 4723
Wed Mar 12 10:01:16 2008
Recovery of Online Redo Log: Thread 1 Group 1 Seq 49756 Reading mem 0
Mem# 0 errs 0: /mnt/filer15adapp/data/adap_dev/redo1.log
Wed Mar 12 10:01:16 2008
Errors in file /mnt/oracle-dev1/admin/adap_dev/bdump/adap_dev_p001_4721.trc:
ORA-01115: IO error reading block from file 2 (block # 575701)
ORA-01110: data file 2: '/mnt/filer15adapp/data/adap_dev/undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1409024
Wed Mar 12 10:01:19 2008
Aborting crash recovery due to slave death, attempting serial crash recovery
Wed Mar 12 10:01:19 2008
Beginning crash recovery of 1 threads
Wed Mar 12 10:01:19 2008
Started redo scan
Wed Mar 12 10:01:20 2008
Completed redo scan
130874 redo blocks read, 760 data blocks need recovery
Wed Mar 12 10:01:25 2008
Aborting crash recovery due to error 1115
Wed Mar 12 10:01:25 2008
Errors in file /mnt/oracle-dev1/admin/adap_dev/udump/adap_dev_ora_4717.trc:
ORA-01115: IO error reading block from file 2 (block # 575701)
ORA-01110: data file 2: '/mnt/filer15adapp/data/adap_dev/undotbs01.dbf'
ORA-27067: size of I/O buffer is invalid
Additional information: 2
Additional information: 1572864
ORA-1115 signalled during: ALTER DATABASE OPEN...
Wed Mar 12 10:02:37 2008
Shutting down instance: further logons disabled


This was the first time i got this error in my experience. Db being running in noarchive log mode (this being dev server) just added to my worries.

Googling didnt help. After some research work, i encountered a note on metalink Note:423128.1.

Though they didnt explain the cause of the problem, but suggested the workaround.

i.e.

1) Mount the database
2) Issue "recover database" command
-- Wait for "Media recovery complete"
3) alter database open

I'm s not sure about the cause as this Note on metalink refers to a Bug 5362935 - "CRASH RECOVERY FAILS WITH ORA-27067 ERROR", which is closed with status "Suspended, Req'd Info not Avail" and lots of useful information is hidden so people like us, who are not working in Oracle Corp cant see it.

How to pass a value from Oracle to Unix shell environment variable?

Though its too trivial, but I thought its not a bad idea to put it here to help the newbies.
Following information can be of help when you want to store a table record or some computation in a shell variable.

env_variable=`sqlplus username}/{password} <<>
set echo off verify off feed off termout off pages 0
{{SELECT STMT}};
exit
EOF`


For example:

-bash-3.00$ DB_USED=`sqlplus -s "/ as sysdba" << EOF
> set echo off verify off feed off termout off pages 0
> select sum(tot.bytes/1024-sum(nvl(fre.bytes,0))/1024)/3.8
> from dba_free_space fre,
> (select tablespace_name, sum(bytes) bytes
> from dba_data_files
> group by tablespace_name) tot,
> dba_tablespaces tbs
> where tot.tablespace_name = tbs.tablespace_name
> and fre.tablespace_name(+) = tbs.tablespace_name
> group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
> /
> exit
> EOF`

-bash-3.00$ echo $DB_USED
4189795.79

This technique can be used in various places where you need to pass a value from Oracle to Unix shell environment.

How to rename Materialized view? ORA-32318

Now - this is what you call a long break .. almost a year. Well - The personal life has been rocking all during last year and I have good news to share, I'm a proud father of a cute little girl. We are still searching a name for her. That is some relief in Hindu religion that we can take up to some days before finalizing the name of baby.

I hope to be more punctual in writing my experiences here on the blog.
Today, I have something on materialized views. How to rename them?

Last week, I had this requirement to rename a materialized view and initial search on how to the same landed me on to ORA-32318.

ORA-32318: cannot rename a materialized view
Cause: Renaming a materialized view or its base table is not supported.
Action: Do not rename the base table of a materialized view.

Now, this is what I dont expect Oracle to do. when rename of table is possible, then why not rename of mat view possible? If any one knows a logical reason, please share.

Anyways, I gave it a further thought on how to accomplish this and an idea struck me.

I'm giving the information of our system but with changed names.
DB1 - where base table exists - 10.2.0.2
DB2 - where mview exists - 10.2.0.2
T1 - name of the table on DB1
M1 - name of the mview - Fast/Incremental refresh on demand

Now as discussed the requirement is to convert M1 to M2, with least down-time (at least for selects).

Here is some test setup scripts:

On DB1:

SQL> create table t1 as select * from all_objects;

Table created.

SQL> alter table t1 add primary key (object_id);

Table altered.

SQL> create materialized view log on t1;

Materialized view log created.

On DB2:


SQL> create materialized view m1 refresh fast on demand as select * from t1@db1; - db1 is database link

Materialized view created.

SQL> select count(*) from m1;

COUNT(*)
----------
62551

Now steps to change the name:

Step1: On DB1
- Stop DML/DDL activities on the base table T1

Step2: On DB2
- Refresh the M1 mview to ensure that there are no changes in there, so we can plan to change the name.
- Create another table with new name. This new name should be the name of mview intended.

SQL> create table m2 as select * from m1;

Table created.

>> Well this can help you in moving non-partition base-table/mview to partitioned base-table/mview

Step3: On DB2

Create new materialized view using "prebuilt" option.

SQL> create materialized view m2 ON PREBUILT TABLE REFRESH fast on demand as select * from t1@db1;

Materialized view created.

Step4: On DB2

Drop the old materialized view.

SQL> drop materialized view m1;

Materialized view dropped.

Thats it!

Now to check if thats working.

Delete few rows on t1@db1

SQL> delete from t1 where object_id in (2,3);

2 rows deleted.

SQL> commit;

Commit complete.

>> And now refresh the new mview.

SQL> exec dbms_mview.refresh('M2')

PL/SQL procedure successfully completed.

>> Check the no of rows:
SQL> select count(*) from m2;

COUNT(*)
----------
62549

Finally check the way oracle refreshed this mview.


SQL> select LAST_REFRESH_TYPE from user_mviews where MVIEW_NAME='M2';

LAST_REF
--------
FAST


Well that's just a confirmation :)

Note: I havent tested other functionalities of this test. So please do a thorough testing of this code before pushing into production database system.
BTW - we are doing the same.