Blog moved

I decided to move this blog to different location. All posts published here are also in new place. There will be no more entries here

And this is link to new blog
http://redikx.wordpress.com/

Order of column in index DOES matter

This is example proving this:

SQL> create table t1 pctfree 98 pctused 1 as select
trunc(dbms_random.value(0,10)) COL1,
rpad('DUMMY',dbms_random.value(10,50)) COL2,
trunc(dbms_random.value(0,100)) COL3,
rpad('NOTININDEX',dbms_random.value(10,50)) COL4
from all_objects where rownum < 2001

Table created.

SQL> create index T1_IDX on t1(col1,col2,col3) pctfree 90;

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats('SYS','T1',cascade=>true,method_opt=>'for all columns size auto');

PL/SQL procedure successfully completed.

SQL> select BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS, CLUSTERING_FACTOR,NUM_ROWS from dba_indexes where index_name='T1_IDX';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
---------- ----------- ------------- ----------------- ----------
1 154 1940 2000 2000

SQL> select COLUMN_NAME,NUM_DISTINCT,DENSITY,NUM_NULLS,NUM_BUCKETS from dba_tab_col_statistics where table_name = 'T1';

COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS
------------------------------ ------------ ---------- ---------- -----------
COL1 10 .1 0 1
COL2 40 .025 0 1
COL3 100 .01 0 1
COL4 40 .025 0 1

REMEBER COST CALCULATION:
COST= BLEVEL + ceiling(index_effective_selectivity*LEAF_BLOCKS) + ceiling(table_effective_selectivity*CLUSTERING_FACTOR)

A few examples:

SQL> select /*+ INDEX(t1) */ col4 from t1 where col1=5;
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200 | 6800 | 217 (0)| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 200 | 6800 | 217 (0)| 00:00:03 |
|* 2 | INDEX RANGE SCAN | T1_IDX | 200 | | 17 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

COST= 1 + ceiling(0.1*154) + ceiling(0.1*2000) = 1+16+200 = OK
=================================================================================

SQL> select /*+ INDEX(t1) */ col4 from t1 where col1 In (5,6);
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 400 | 13600 | 432 (1)| 00:00:06 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 400 | 13600 | 432 (1)| 00:00:06 |
|* 3 | INDEX RANGE SCAN | T1_IDX | 400 | | 31 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

COST= 1 + ceiling(0.2*154) + ceiling (0.2*2000) = 1+31+400=432
===================================================================================

SQL> select /*+ INDEX(t1) */ col4 from t1 where col1=4 and col2='DUMMY ';
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 325 | 8 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 5 | 325 | 8 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IDX | 5 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

COST=1+ceil(0.1*0.025*154)+ceil(0.1*0.025*2000)= 1 + ceil(0.39) + ceil(5) = 1 + 1 + 6

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

SQL> select /*+ INDEX(t1) */ col4 from t1 where col1 = 1 and col3=97;

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 19 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 74 | 19 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IDX | 2 | | 17 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

COST=1 + ceil(0.1*0.01*154) + ceil(0.1*0.01*2000) = 1 + ceil(0.15) + ceil(2) = 2 DOESN'T MATCH!!!
Why? Because COL3 is on 3rd place in index, so cbo cannot limit leafs to scan
COST=1 + ceil(0.1*154) + ceil(0.1*0.01*2000) = 1 + ceil(15.4) + ceil(2) = 1 + 16 + 2


And now let's create index with COL3 on 2nd place and COL2 on 3rd:

create index T1_IDX2 on t1(col1,col3,col2) pctfree 90;

SQL> select BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS, CLUSTERING_FACTOR,NUM_ROWS from dba_indexes where index_name='T1_IDX2';

BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS
---------- ----------- ------------- ----------------- ----------
1 154 1940 2000 2000


SQL> select /*+ INDEX(t1) */ col4 from t1 where col1 = 1 and col3=97;
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 2 | 74 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IDX2 | 2 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

COST=1+ceil(0.1*0.01*154)+ceil(0.1*0.01*2000) = 1+ceil(0.154)+ceil(2) = 1+1 +2

So two indexes on the same columns, but diffrent order of column generate different costs because different access to index leafs (limited to one columns only or limited to both columns)

NUM_DISTINCT and DENSITY in all_tab_col_statistics

Both columns are also in ALL_TAB_COLUMNS.

According to definition:
NUM_DISTINCT - number of distinct values in column
DENSITY - fraction of data that will be return by a query of type column=:a

So generally they should store the same data, as
DENSITY = 1 / NUM_DISTINCT

But it is not always the case. Please look at the example:

SQL> create table t1 as
2 select
3 rownum as ID,
4 trunc(dbms_random.value(1,20)) as VAL
5 from all_objects where rownum <= 1000;

Now we gather stats on this table, no histograms data:

SQL> begin
2 dbms_stats.gather_table_stats(user,'T1',estimate_percent=>null,method_opt=>'for all columns size 1');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, density from dba_tab_col_statistics where table_name='T1';

COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------ ------------ ----------
ID 1000 .001
VAL 19 .052631579

Looks like it works how described above:

1/0.001 = 1000
1/19 = .052631579

But now let's change statistics - use histograms

SQL> begin
2 dbms_stats.gather_table_stats(user,'T1',estimate_percent=>null,method_opt=>'for all columns size 100');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select column_name, num_distinct, density from dba_tab_col_statistics where table_name='T1';

COLUMN_NAME NUM_DISTINCT DENSITY
------------------------------ ------------ ----------
ID 1000 .001
VAL 19 .0005

DENSITY for ID hasn't changed, but VAL density changed - now cbo has much more data to estimate density.

So which column cbo use to calculate cardinality (so what percentage of data query returns)? It depends whether you have histograms or no:
1. If there are no histograms, cbo assume that cardinality=num_rows/num_distincts
2. If we have histograms, cardinality=num_rows*density

Library cache lock - find locking session

1.
select saddr from v$session where sid in (select sid from v$session_wait where event like 'library cache lock');

2. FIND BLOCKER:
SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session above' /* BLOCKED SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);

3. FIND BLOCKED:
SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
WHERE SADDR in
(SELECT KGLLKSES FROM X$KGLLK LOCK_A
WHERE KGLLKREQ > 0
AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
WHERE KGLLKSES = 'saddr_from_v$session above' /* BLOCKING SESSION */
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
);

Moved archive logs, what next...

If you had to urgently moved archive log files to different location to release space and allow users to login, you need then to backup them using rman. But of course rman will not see them automatically. This is what you need to do:

rman> catalog archivelog '/archivelogname';

rman> backup archivelog ... delete input;

Convert sql_id into hash_value

Suprise? Hash value can be easly calculated knowing sql_id:

select
lower(trim('&1')) sql_id
, trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('&1')),level,1))-1)
*power(32,length(trim('&1'))-level)),power(2,32))) hash_value
from
dual
connect by
level <= length(trim('&1'))
/

Fine Grain Auditing example

This is the simplest example of FGA package.
Lets assume you want to audit who selects ID column on a table REDDTA.T1.

exec dbms_fga.add_policy (-
object_schema=>'REDDTA',-
object_name=>'T1',-
policy_name=>'T1_REDDTA',-
audit_column => 'ID',-
statement_types => 'SELECT'-
);

As a result you get something like this:

1* SELECT policy_name, object_name, statement_type, os_user, db_user FROM dba_fga_audit_trail

POLICY_NAME OBJECT_NAME STATEME OS_USER DB_USER
----------- ----------- ------- -------------------- --------------------
T1_REDDTA T1 SELECT oracle REDDTA



DBMS_FGA.ADD_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2,
audit_condition VARCHAR2,
audit_column VARCHAR2,
handler_schema VARCHAR2,
handler_module VARCHAR2,
enable BOOLEAN,
statement_types VARCHAR2,
audit_trail BINARY_INTEGER IN DEFAULT,
audit_column_opts BINARY_INTEGER IN DEFAULT);

What's worth to know:
audit_column - The columns to be checked for access. These can include hidden columns. The default, NULL, causes audit if any column is accessed or affected.
statement_types - The SQL statement types to which this policy is applicable: INSERT, UPDATE, DELETE, or SELECT only.

Procedure to move outline between instances or schemas

This procedure can be used to move outline between schemas and/ora databases.


CREATE OR REPLACE procedure SYSTEM.outline_get (source_inst in varchar2, outline_name in varchar2, source_owner varchar2, dest_owner varchar2)
is
TYPE typoutput_1 IS REF CURSOR;
replace_user varchar2(64);
output_1 typoutput_1;
sql_to_tune_orig long;
sql_to_tune long;
sql_to_run varchar2(4000);
outline_creator varchar2(4000);
outline_exists number;
outline_prv_exists number;
cursor replace_user_table is
select user_table_name from system.ol$hints where user_table_name is not null;
user_tbl_name varchar2(64);
user_tbl_name_fixed varchar2(64);

begin
/* checking whether temporary private outline exists, drop if exists */
    select count(*) into outline_prv_exists from system.ol$ where ol_name = 'PRV1_TMP';
        if outline_prv_exists > 0 then
            dbms_output.put_line ('Dropping private outline PRV1_TMP');
            execute immediate 'drop private outline PRV1_TMP';
        end if;

/* get sql statement the outline is created for and modify each schema occurance */
sql_to_run :=
    'select sql_text from dba_outlines@'||SOURCE_INST||' where name='''||outline_name||'''';
open output_1 for sql_to_run;
fetch output_1 into sql_to_tune_orig;
sql_to_tune := REPLACE(sql_to_Tune_orig,upper(SOURCE_OWNER),upper(DEST_OWNER));

/* Create temporary private outline for query */
execute immediate ('create private outline PRV1_TMP on '||sql_to_tune);

/* Replace rows from ol$hint from source */
execute immediate ('delete from system.ol$hints where ol_name = ''PRV1_TMP''');
COMMIT;
sql_to_run := 'insert into system.ol$hints select * from outln.ol$hints@'||SOURCE_INST||' where ol_name = '''||outline_name||'''';
execute immediate (sql_to_run);
COMMIT;
update system.ol$hints set ol_name = 'PRV1_TMP';
COMMIT;

/* Change USER_TABLE_NAME for each row */
open replace_user_table;
loop
fetch replace_user_table into user_tbl_name;
user_tbl_name_fixed := replace(user_tbl_name,upper(SOURCE_OWNER),upper(DEST_OWNER));
update system.ol$hints set user_table_name = user_tbl_name_fixed where user_table_name = user_tbl_name;
commit;
exit when replace_user_table%NOTFOUND;
end loop;

/* Refresh private outline after changes */
dbms_outln_edit.refresh_private_outline('PRV1_TMP');

/* Check if outline exists, skip is exists, create if no */
select count(*) into outline_exists from outln.ol$ where ol_name = ''||outline_name||'';
 if outline_exists > 0 then
 dbms_output.put_line ('PUBLIC WITH THIS NAME EXISTS!!!!!');
    else
    execute immediate ('create outline '||outline_name||' from private PRV1_TMP');
    execute immediate ('drop private outline PRV1_TMP');
 end if;
end;
/

How to find value used in bind variables

If sql statement used bind variables is still in shared pool, and you have Oracle 10g, you can find what values variables have in the following way:

select sql_id, name, position,value_string from v$sql_bind_capture where sql_id='';

Datapump EXPDP - ORA-39127 SYS.DBMS_AW_EXP.schema_info_exp error

Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_AW_EXP.schema_info_exp('REDCTL',1,1,'09.02.00.00.00',newblock)
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
ORA-06512: at "SYS.DBMS_AW_EXP", line 473
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_AW_EXP.schema_info_exp('REDDTA',1,1,'09.02.00.00.00',newblock)
ORA-37002: Oracle OLAP failed to initialize. Please contact Oracle OLAP technical support.
ORA-33262: Analytic workspace EXPRESS does not exist.
ORA-06512: at "SYS.DBMS_AW", line 93
ORA-06512: at "SYS.DBMS_AW", line 122
ORA-06512: at "SYS.DBMS_AW_EXP", line 505
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 5419
Master table "SYSTEM"."EXPDP_METADATA" successfully loaded/unloaded

SOLUTION:

SQL> DELETE FROM sys.exppkgact$ WHERE package='DBMS_AW_EXP';

2 rows deleted.

SQL> commit;

Commit complete.