Yaping's Weblog

November 15, 2008

select count(id) from t where id is null

Filed under: Oracle — Yaping @ 2:04 am
Tags:

select count(id) from t where id is null;

How does Oracle handle query statement like it?
count(id) means add up row number which id is not null. First I think Oracle will parse
this statement, then return the result 0 immediately, not access table or index. Is it so?

I did the test on Oracle 9208 and 10203.
9208
test@CHEN>create table t (id number,a char(100));
Table created.

test@CHEN>insert into t select rownum,’A’ from dual connect by level<50000;
49999 rows created.

test@CHEN>analyze table t compute statistics;
Table analyzed.

test@CHEN>set autotrace on
test@CHEN>select count(id) from t where id is null;
 COUNT(ID)
———-
         0

Execution Plan
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=75 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF ‘T’ (Cost=75 Card=1 Bytes=4)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
        772  consistent gets
          0  physical reads
          0  redo size
        406  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
test@CHEN>create index idx_t_id on t(id);
Index created.

test@CHEN>analyze table t compute statistics;
Table analyzed.

test@CHEN>select count(id) from t where id is null;
 COUNT(ID)
———-
         0
Execution Plan
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=75 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF ‘T’ (Cost=75 Card=1 Bytes=4)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
        772  consistent gets
          0  physical reads
          0  redo size
        406  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
test@CHEN>alter table t modify id not null;
Table altered.

test@CHEN>select count(id) from t where id is null;
 COUNT(ID)
———-
         0

Execution Plan
———————————————————-
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF ‘IDX_T_ID’ (NON-UNIQUE) (Cost=1 Ca
          rd=1 Bytes=4)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        406  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 
10203
test@TEST>create table t (id number,a char(100));
Table created.

test@TEST>insert into t select rownum,’A’ from dual connect by level<50000;
49999 rows created.

test@TEST>analyze table t compute statistics;
Table analyzed.

test@TEST>set autotrace on

test@TEST>select count(id) from t where id is null;
 COUNT(ID)
———-
         0

Execution Plan
———————————————————-
Plan hash value: 2966233522

—————————————————————————
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————
|   0 | SELECT STATEMENT   |      |     1 |     4 |   196   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |     1 |     4 |   196   (2)| 00:00:02 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

   2 – filter(“ID” IS NULL)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
        772  consistent gets
          0  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
test@TEST>create index idx_t_id on t(id);
Index created.

test@TEST>select count(id) from t where id is null;
 COUNT(ID)
———-
         0

Execution Plan
———————————————————-
Plan hash value: 1700799834

——————————————————————————
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————
|   0 | SELECT STATEMENT  |          |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |          |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T_ID |     1 |     4 |     1   (0)| 00:00:01 |
——————————————————————————

Predicate Information (identified by operation id):
—————————————————

   2 – access(“ID” IS NULL)

Statistics
———————————————————-
          1  recursive calls
          0  db block gets
          2  consistent gets
          8  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
There’s some difference between 9208 and 10203 that if there’s index on id column, Oralce uses index range scan instead of full table access. It avoids scan table. We can also gain it on 9208 if the id column can be specified as not null.
test@TEST>select count(id) from t where id is not null;
 COUNT(ID)
———-
     49999

Execution Plan
———————————————————-
Plan hash value: 3570898368

———————————————————————————-
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————-
|   0 | SELECT STATEMENT      |          |     1 |     4 |    31   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE       |          |     1 |     4 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T_ID | 49999 |   195K|    31   (7)| 00:00:01 |
———————————————————————————-

Predicate Information (identified by operation id):
—————————————————

   2 – filter(“ID” IS NOT NULL)

Statistics
———————————————————-
          0  recursive calls
          0  db block gets
        117  consistent gets
          0  physical reads
          0  redo size
        414  bytes sent via SQL*Net to client
        399  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
test@TEST>analyze index idx_t_id validate structure;
Index analyzed.

test@TEST>select HEIGHT,BLOCKS from index_stats;
    HEIGHT     BLOCKS
———- ———-
         2        112

Advertisements

August 30, 2008

V$TRANSACTION.USED_UREC

Filed under: Oracle — Yaping @ 4:20 am
Tags:

We know that v$transaction.USED_UREC records the transaction’s changed records, including table’s rows numbers and associated indexes’ entries numbers.
After test, there’re some puzzle on bitmap index I can’t make it clear.
All test tables/indexes use manual segment space management tablespace.

Oracle 9208
session 1
@>conn test/test
Connected.
@>drop table t1;
Table dropped.
@>create table t1(id number,id2 number);
Table created.
@>create index ind_t1_id on t1(id);
Index created.
@>insert into t1 values (1,1);
1 row created.

Now I expect there’re 2 records in v$transaction.USED_UREC.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
2

session 1
@>commit;
Commit complete.
@>update t1 set id=2;
1 row updated.

Indexes update includes one delete and one insert, so there should be 3 records.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
3

The above result accords with my expectation.
Now what will happen after bitmap index instead of B*Tree index?

session 1
@>drop table t1;
Table dropped.
@>create table t1(id number,id2 number);
Table created.
@>create bitmap index ind_t1_id2 on t1(id2);
Index created.
@>insert into t1 values (1,1);
1 row created.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
4

I expect it should be 2, but it is 4, where do the additional two records come from?

@>alter system checkpoint;
System altered.
@>@exts ind_t1_id2
OWNER EXTENT_ID FILE_ID BLOCK_ID BLOCKS
—————————— ———- ———- ———- ———-
TEST 0 3 17 8
@>@dumpf 3 17 18
PL/SQL procedure successfully completed.
@>
@>@trace
… …
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×000a.010.000000b8 0×0080009c.00d0.15 —- 2 fsc 0×0008.00000000
… …
row#0[8005] flag: —–, lock: 2
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 c0 00 0a 00 00
col 2; len 6; (6): 00 c0 00 0a 00 07
col 3; len 1; (1): 00
row#1[8026] flag: —D-, lock: 2
col 0; NULL
col 1; NULL
col 2; NULL
col 3; NULL
—– end of leaf block dump —–

There’s one delete entry and its value is NULL, how does it be there? Now there’s still one record not found where it comes from. I guess the another one record is insertion the NULL value, finally the NULL value is deleted.

session 1
@>commit;
Commit complete.
@>insert into t1 values (2,1);
1 row created.

Bitmap index entry update will delete old entry and recreate new one.
So there should be 3 records.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
3

session 1

@>commit;
Commit complete.
@>insert into t1 values (3,3);
1 row created.

Bitmap index entry changes will effect its near value index entry be locked, so I think there maybe be 3 records.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
3
@>alter system checkpoint;
System altered.
@>@exts ind_t1_id2
old 1: select OWNER,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=upper(’&1′)
new 1: select OWNER,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=upper(’ind_t1_id2′)
OWNER EXTENT_ID FILE_ID BLOCK_ID BLOCKS
—————————— ———- ———- ———- ———-
TEST 0 3 17 8
@>@dumpf 3 17 18
PL/SQL procedure successfully completed.
@>@trace
/opt/app/oracle/admin/chen/udump/chen_ora_2819.trc
… …
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×000a.029.000000b8 0×0080009c.00d0.10 —- 2 fsc 0×0000.00000000
… …
row#0[7983] flag: —–, lock: 2
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 c0 00 0a 00 00
col 2; len 6; (6): 00 c0 00 0a 00 07
col 3; len 2; (2): c8 03
row#1[7962] flag: —–, lock: 2
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 c0 00 0a 00 00
col 2; len 6; (6): 00 c0 00 0a 00 07
col 3; len 1; (1): 02
—– end of leaf block dump —–

From above dump data, we can find that indexed value 1, its entry is locked.

session 1
@>commit;
Commit complete.
@>update t1 set id2=0 where id=3;
1 row updated.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
5
@>
@>alter system checkpoint;
System altered.
@>
@>@dumpf 3 17 18
PL/SQL procedure successfully completed.
… …
Itl Xid Uba Flag Lck Scn/Fsc
0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×0009.011.000000b5 0×00800090.0250.0c —- 3 fsc 0×001f.00000000
… …
row#0[7936] flag: —–, lock: 2
col 0; len 1; (1): 80
col 1; len 6; (6): 00 c0 00 0a 00 00
col 2; len 6; (6): 00 c0 00 0a 00 07
col 3; len 1; (1): 02
row#1[7983] flag: —–, lock: 0
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 c0 00 0a 00 00
col 2; len 6; (6): 00 c0 00 0a 00 07
col 3; len 2; (2): c8 03
row#2[7962] flag: —D-, lock: 2
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 c0 00 0a 00 00
col 2; len 6; (6): 00 c0 00 0a 00 07
col 3; len 1; (1): 02
row#3[7956] flag: —D-, lock: 2
col 0; NULL
col 1; NULL
col 2; NULL
col 3; NULL
—– end of leaf block dump —–

There’s still one record I can’t know it comes from.
Bitmap index management has changed very much in Oracle 10g. What will be different?

10g
session 1
test@TEST>drop table t1;
Table dropped.
test@TEST>
test@TEST>create table t1 (id number,id2 number);
Table created.
test@TEST>create bitmap index idx_t1_id2 on t1(id2);
Index created.
test@TEST>insert into t1 values (1,1);
1 row created.

session 2
@>select USED_UREC from v$transaction;
USED_UREC
———-
3
@>alter system checkpoint;
System altered.
@>@exts idx_t1_id2
old 1: select OWNER,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=upper(’&1′)
new 1: select OWNER,EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where SEGMENT_NAME=upper(’idx_t1_id2′)
OWNER EXTENT_ID FILE_ID BLOCK_ID BLOCKS
—————————— ———- ———- ———- ———-
TEST 0 5 49 8
sys@TEST>@dumpf 5 49 51
PL/SQL procedure successfully completed.
sys@TEST>@trace
/opt/app/oracle/admin/test/udump/test_ora_5304.trc
… …
Itl Xid Uba Flag Lck Scn/Fsc

0×01 0×0000.000.00000000 0×00000000.0000.00 —- 0 fsc 0×0000.00000000
0×02 0×0002.007.00000374 0×00800072.0388.2f —- 1 fsc 0×0000.00000000
… …
row#0[7986] flag: ——, lock: 2, len=26
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 00 00 00 00 00
col 2; len 6; (6): 01 40 00 2a 00 07
col 3; len 6; (6): c0 aa 9e 80 a2 07
—– end of leaf block dump —–

From the above result, there’s one record in Oracle 10g less than in Oracle 9i.

Comments in SHELL scripts

Filed under: Oracle — Yaping @ 4:18 am
Tags:

Refer to http://www.ixora.com.au/newsletter/2000_08.htm#comments

 

[oracle@chen tmp]$ cat test.sh
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
conn test/test
select count(*) from t1;
#truncate table t1;
select count(*) from t1;
exit
EOF
[oracle@cheney tmp]$ sh test.sh
Connected.

  COUNT(*)
———-
     16512


Table truncated.


  COUNT(*)
———-
         0

[oracle@chen tmp]$  

 

oops, the data was gone. Be careful of comment sql in scripts.

Format v$sql_plan output

Filed under: Oracle — Yaping @ 4:14 am
Tags:
When we query v$sql_plan view to examine execution plan, we expect to query and read the result easily.
List two motheds in oracle 9i and 10g.
Version 9i
=============================
SQL> create or replace view myplan as select to_char(hash_value) statement_id,sysdate timestamp,a.* from v$sql_plan a;
View created.

SQL> grant select on myplan to public;
Grant succeeded.

SQL> create public synonym myplan for myplan;
Synonym created.

SQL> select HASH_VALUE,SQL_TEXT from v$sql where SQL_TEXT like ‘select * from t1%’;
HASH_VALUE SQL_TEXT
———- ————————————————————————
2615572450 select * from t1 where id=100

SQL> select * from table(dbms_xplan.display(‘myplan’,’2615572450′));
PLAN_TABLE_OUTPUT
——————————————————————————–
——————————————————————–
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
——————————————————————–
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  INDEX RANGE SCAN    | IDX_T1_ID   |       |       |       |
——————————————————————–
Predicate Information (identified by operation id):
—————————————————

PLAN_TABLE_OUTPUT
——————————————————————————–
   1 – access(“ID”=100)

Note: rule based optimization

14 rows selected.

Version 10g
=====================================================
SQL> select SQL_ID,CHILD_NUMBER,HASH_VALUE,SQL_TEXT from v$sql where SQL_TEXT like ‘select * from test.t1 where%’;
SQL_ID        CHILD_NUMBER HASH_VALUE SQL_TEXT
————- ———— ———- ————————————————–
6abjbgck9yvn5            0  614428293 select * from test.t1 where id=555

SQL> select * from table(dbms_xplan.DISPLAY_CURSOR(‘6abjbgck9yvn5’,0));
PLAN_TABLE_OUTPUT
—————————————————————————————-
SQL_ID  6abjbgck9yvn5, child number 0
————————————-
select * from test.t1 where id=555

Plan hash value: 3653646128

——————————————————————————
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————
|   0 | SELECT STATEMENT |           |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1_ID |     1 |    13 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
——————————————————————————
——————————————————————————

Predicate Information (identified by operation id):
—————————————————

   1 – access(“ID”=555)

Note
—–
   – dynamic sampling used for this statement

22 rows selected.

If you query v$sql_plan, you should be careful. There’re bugs on this view.

 

 

 

 

 

 

SQL*Plus using prelim connection

Filed under: Oracle — Yaping @ 2:52 am
Tags:

Since Oracle 10g SQL*Plus introduces prelim connection. This connection doesn’t create session, so it doesn’t invoke some internal call, but can access SGA. In some situations, the database has issues, such as hang, even though as SYSDBA can’t logon normally, but we want to dump system information for diagnoses before recycle database. How can we do?

 

[oracle@chen ~]$ sqlplus -prelim

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Jul 8 19:47:30 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter user-name: /as sysdba

ERROR:

ORA-01012: not logged on

@>oradebug setorapid 15

Unix process pid: 2623, image: oracle@chen (TNS V1-V3)

@>oradebug dump hanganalyze 3

Statement processed.

@>oradebug dump systemstate 3

Statement processed.

 

Or

[oracle@chen ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Jul 8 19:57:21 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

@>set _prelim on

@>conn /as sysdba

Prelim connection established

ERROR:

ORA-01012: not logged on

 

Create a free website or blog at WordPress.com.