Monthly Archives: 九月 2011

10g新增列方式指定HINT

Posted on by

在9i和以前的版本，索引提示的格式为/*+ index(table_alias) */或/*+ index(table_alias index_name) */，但是在10g中不仅可以通过索引名称来确定HINT的索引，还可以通过指定列名的方式，格式为/*+ index(table_alias (column_names)) */

SQL> create table test_hint   
  2  as
  3  select * from dba_objects;

Table created.

SQL> create index ind_hint on test_hint(owner,object_type);

Index created.

SQL>  exec dbms_stats.gather_table_stats(user, 'TEST_HINT', method_opt => 'for all indexed columns size 100',cascade=>true);

PL/SQL procedure successfully completed.

SQL> set autot trace exp
SQL> select * from test_hint where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 11101196

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 23272 |  2113K|   161   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_HINT | 23272 |  2113K|   161   (1)| 00:00:02 |
-------------------------------------------------------------------------------

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

   1 - filter("OWNER"='SYS')

SQL> select /*+index(a)*/ * from test_hint  a where owner = 'SYS';


Execution Plan
----------------------------------------------------------
Plan hash value: 890897193

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

   2 - access("OWNER"='SYS')

SQL> select /*+index(a ind_hint)*/ * from test_hint  a where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 890897193

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

   2 - access("OWNER"='SYS')

SQL> select /*+index(a (owner,object_type))*/ * from test_hint  a where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 890897193

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

   2 - access("OWNER"='SYS')
--指定和index完全一致的列，走index

SQL> select /*+index(a (owner))*/ * from test_hint  a where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 890897193

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           | 23272 |  2113K|  1122   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT | 23272 |  2113K|  1122   (1)| 00:00:14 |
|*  2 |   INDEX RANGE SCAN          | IND_HINT  | 23272 |       |    84   (0)| 00:00:02 |
-----------------------------------------------------------------------------------------

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

   2 - access("OWNER"='SYS')
--指定列和where条件一致，也可以使用该index

SQL> select /*+index(a (object_id))*/ * from test_hint  a where owner = 'SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 11101196

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           | 23272 |  2113K|   161   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_HINT | 23272 |  2113K|   161   (1)| 00:00:02 |
-------------------------------------------------------------------------------

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

   1 - filter("OWNER"='SYS')
--虽然是index中的一个列，但是由于不是where条件中，所以不能被使用

SQL> select /*+index(a (owner))*/ * from test_hint  a where object_type = 'TABLE';

Execution Plan
----------------------------------------------------------
Plan hash value: 1755360976

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |  1752 |   159K|   104   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_HINT |  1752 |   159K|   104   (0)| 00:00:02 |
|*  2 |   INDEX SKIP SCAN           | IND_HINT  |  1752 |       |    25   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("OBJECT_TYPE"='TABLE')
       filter("OBJECT_TYPE"='TABLE')
--指定index的第一列，虽然不在where中，但是还是会使用index

--说明：使用/*+ index(table_alias (column_names)) */方式的hint，需要先测试，有可能不能达到预期效果

TOM的SHOW_SPACE过程使用

Posted on by

TOM写了个好工具SHOW_SPACE,这个工具对于Oracle来讲其实就是个存储过程，这个存储过程可以用来分析空间使用情况，有了此工具，就不用再通过写SQL语句来看每条记录或表占用表空间的大小了，使用起来很方便。

create or replace procedure show_space
( p_segname_1 in varchar2,
p_owner_1 in varchar2 default user,
p_type_1 in varchar2 default 'TABLE',
p_space in varchar2 default 'AUTO',
p_analyzed in varchar2 default 'Y'
)
as
p_segname varchar2(100);
p_type varchar2(10);
p_owner varchar2(30);

l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number;
l_fs1_bytes number;
l_fs2_blocks number;
l_fs2_bytes number;
l_fs3_blocks number;
l_fs3_bytes number;
l_fs4_blocks number;
l_fs4_bytes number;
l_full_blocks number;
l_full_bytes number;

l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;

procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
p_num );
end;
begin
p_segname := upper(p_segname_1); -- rainy changed
p_owner := upper(p_owner_1);
p_type := p_type_1;

if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
p_type := 'INDEX';
end if;

if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
p_type := 'TABLE';
end if;

if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
p_type := 'CLUSTER';
end if;


dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );

if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );

p( 'Free Blocks', l_free_blks );
end if;

p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );


/*IF the segment is analyzed */
if p_analyzed = 'Y' then
dbms_space.space_usage(segment_owner => p_owner ,
segment_name => p_segname ,
segment_type => p_type ,
unformatted_blocks => l_unformatted_blocks ,
unformatted_bytes => l_unformatted_bytes,
fs1_blocks => l_fs1_blocks,
fs1_bytes => l_fs1_bytes ,
fs2_blocks => l_fs2_blocks,
fs2_bytes => l_fs2_bytes,
fs3_blocks => l_fs3_blocks ,
fs3_bytes => l_fs3_bytes,
fs4_blocks => l_fs4_blocks,
fs4_bytes => l_fs4_bytes,
full_blocks => l_full_blocks,
full_bytes => l_full_bytes);
dbms_output.put_line(rpad(' ',50,'*'));
dbms_output.put_line('The segment is analyzed');
p( '0% -- 25% free space blocks', l_fs1_blocks);
p( '0% -- 25% free space bytes', l_fs1_bytes);
p( '25% -- 50% free space blocks', l_fs2_blocks);
p( '25% -- 50% free space bytes', l_fs2_bytes);
p( '50% -- 75% free space blocks', l_fs3_blocks);
p( '50% -- 75% free space bytes', l_fs3_bytes);
p( '75% -- 100% free space blocks', l_fs4_blocks);
p( '75% -- 100% free space bytes', l_fs4_bytes);
p( 'Unused Blocks', l_unformatted_blocks );
p( 'Unused Bytes', l_unformatted_bytes );
p( 'Total Blocks', l_full_blocks);
p( 'Total bytes', l_full_bytes);

end if;

end;
/

用法

SQL> create table t 
  2  as
  3  select * from dba_objects;

Table created.

SQL> set serverout on
SQL>  exec show_space('T','TEST');
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................52
Unused Bytes............................425984
Last Used Ext FileId....................4
Last Used Ext BlockId...................1033
Last Used Block.........................76
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................696
Total bytes.............................5701632

PL/SQL procedure successfully completed.

SQL> delete from t;

50602 rows deleted.

SQL> exec show_space('T','TEST');
Total Blocks............................768
Total Bytes.............................6291456
Unused Blocks...........................52
Unused Bytes............................425984
Last Used Ext FileId....................4
Last Used Ext BlockId...................1033
Last Used Block.........................76
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........696
75% -- 100% free space bytes............5701632
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................0
Total bytes.............................0

PL/SQL procedure successfully completed.

SQL> alter table t move;

Table altered.

SQL> exec show_space('T','TEST');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................4
Last Used Ext BlockId...................401
Last Used Block.........................3
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................0
Total bytes.............................0

PL/SQL procedure successfully completed.

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Posted on by

truncate清空一张表记录发现ORA-02266，进行模拟测试

SQL> truncate table p;
truncate table p
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

--错误原因
SQL> !oerr ora 02266
02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"
// *Cause: An attempt was made to truncate a table with unique or 
//         primary keys referenced by foreign keys enabled in another table.
//         Other operations not allowed are dropping/truncating a partition of a
//         partitioned table or an ALTER TABLE EXCHANGE PARTITION.
// *Action: Before performing the above operations the table, disable the 
//          foreign key constraints in other tables. You can see what 
//          constraints are referencing a table by issuing the following 
//          command:
//          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

--查询是否有主键或者唯一index
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF 
SET LONG 999999    
SET PAGESIZE 1000
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);--不显示存储信息
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','P') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','P')
--------------------------------------------------------------------------------

  CREATE TABLE "CHF"."P"
   (    "X" NUMBER(*,0),
         PRIMARY KEY ("X")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS

--查询主外键关系
SQL> col 主键表名 for a20
SQL> col 主键列名 for a20
SQL> col 外键表名 for a20
SQL> col 外键列名  for a20
SQL> select b.table_name  主键表名,
       b.column_name 主键列名,
       a.table_name  外键表名,
       a.column_name 外键列名
  from (select a.constraint_name,
               b.table_name,
               b.column_name,
               a.r_constraint_name
          from dba_constraints a, dba_cons_columns b
         WHERE a.constraint_type = 'R'
           and a.constraint_name = b.constraint_name) a,
       (select distinct a.r_constraint_name, b.table_name, b.column_name
          from dba_constraints a, dba_cons_columns b
         WHERE a.constraint_type = 'R'
           and a.r_constraint_name = b.constraint_name) b
 where a.r_constraint_name = b.r_constraint_name
and b.table_name='P'; 

主键表名             主键列名             外键表名             外键列名
-------------------- -------------------- -------------------- --------------------
P                    X                    C                    X

1 row selected.

--处理方法一
SQL> alter table p disable primary key cascade;

Table altered.

SQL> truncate table p;

Table truncated.

SQL> alter table p enable primary key;

Table altered.

--处理方法二
SQL> delete from t;

1 row deleted.


SQL> commit;

Commit complete.

自治事件引起死锁

Posted on by

今天遇到一个比较特殊的死锁现象，记录下来

DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-00090022-000002ba        15     145     X             15     145           X
session 145: DID 0001-000F-00000019     session 145: DID 0001-000F-00000019
Rows waited on:
Session 145: obj - rowid = 0000E0A3 - AAAOCjAAFAAAAA8AAA
  (dictionary objn - 57507, file - 5, block - 60, slot - 0)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE T SET Y = Y WHERE X = :B1 
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x67987910         4  CHF.T
===================================================

问题原因：自治事件导致(重现)

drop table t;                                                                                                        
create table t ( x int, y int );
                                                                                                                 
create or replace trigger t before update on t
for each row
declare
    pragma autonomous_transaction;
begin
    update t set y = y where x = :new.x;
    commit;
end;
/
                                                                                                              
insert into t values ( 1, 1 );
commit;
update t set y = y where x = 1;

DUMPING ORACLE BLOCKS

Posted on by

我见识过的，讲的最好的关于dump相关的文章，这个里面关于dump block,dump index,dump controlfile说的比较详细，看了收获不小，现在贡献出来和大家分享
如何阅读oracle数据块的dump文件