运维不会oracle,oracle日常运维笔记

Oracle日常运维笔记

1.解析rowid含义：

select /*hello>*/ rowid,obj.* from obj where object_id>91990;

ROWID OWNER OBJECT_NAME

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

AAAWdnAAIAAAPl5AAn DBMGR DBLIST

rowid是oracle比较重要的一个伪列，一旦数据生成 rowid就生成了，以后都不会改变。rowid包含的信息比较重要,我们可以通过如下函数来解析rowid信息：

select dbms_rowid.rowid_object(rowid) object_id,

dbms_rowid.rowid_relative_fno(rowid) file_id,

dbms_rowid.rowid_block_number(rowid) block_id,

dbms_rowid.rowid_row_number(rowid) row_num

from dblist

where rownum=1;

OBJECT_ID    FILE_ID   BLOCK_ID        row_num

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

91990          1      97201         37

从上可知对象编号，文件id，快id和行id，这样就能精准的定位到数据。

2.获取sql执行计划：

我一般比较喜欢使用

dbms_xplan.display_cursor(sql_id,child_cursor_number,'advanced')

包获取执行计划。

执行一条sql语句 ：

select * from dblist_test where host1_ip='XX.XX.XX.XX';

获取sql_id：

select sql_id,sql_text,CHILD_NUMBER from v$sql where sql_text like '%hello%';

打出执行计划：

select * from table(dbms_xplan.display_cursor('52gdzd1gbfydg',0,'advanced'));

这里解释一下索引范围扫描和索引唯一扫描的区别：

index_range_scan:

非唯一索引(主键)或者 非等值查询或者返回多行数据的情况下都会走索引范围扫描。队索引列有一个自动排序的操作。

SQL> create index obj_name_idx on obj(object_name);

SQL> select /*dblist*/ rowid,obj.* from obj where object_name='DBLIST';

SQL> select sql_id,sql_text,CHILD_NUMBER from v$sql where sql_text like '%dblist%';

SQL_ID        SQL_TEXT                                                                         CHILD_NUMBER

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

aymsbqzp6ga3q select /*dblist*/ rowid,obj.* from obj where object_name='DBLIST'                           0

SQL> select * from table(dbms_xplan.display_cursor('aymsbqzp6ga3q',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID  aymsbqzp6ga3q, child number 0

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

select /*dblist*/ rowid,obj.* from obj where object_name='DBLIST'

Plan hash value: 1298327290

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

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)

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

|   0 | SELECT STATEMENT            |              |       |       |     4 (100)

|   1 |  TABLE ACCESS BY INDEX ROWID| OBJ          |     1 |   219 |     4   (0)

|*  2 |   INDEX RANGE SCAN          | OBJ_NAME_IDX |     1 |       |     3   (0)

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

index_unique_scan:

只发生在唯一键值索引上等值查询，返回单行数据

例如：

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

Table altered

SQL> select owner,index_name,TABLE_NAME from dba_indexes where table_name='OBJ';

OWNER                          INDEX_NAME                     TABLE_NAME

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

DBMGR                          SYS_C0012520                   OBJ

DBMGR                          OBJ_NAME_IDX                   OBJ

SQL> exec dbms_stats.gather_table_stats('dbmgr','obj');

SQL> exec dbms_stats.gather_index_stats('dbmgr','SYS_C0012520');

SQL> select /*hello*/ rowid,obj.* from obj where object_id=91990;

SQL> select sql_id,sql_text,CHILD_NUMBER from v$sql where sql_text like '%hello%';

SQL_ID        SQL_TEXT                                                                         CHILD_NUMBER

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

52gdzd1gbfydg select /*hello*/ rowid,obj.* from obj where object_id=91990                                 0

SQL> select * from table(dbms_xplan.display_cursor('52gdzd1gbfydg',0,'advanced'));

PLAN_TABLE_OUTPUT

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

SQL_ID  52gdzd1gbfydg, child number 0

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

select /*hello*/ rowid,obj.* from obj where object_id=91990

Plan hash value: 288971904

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

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)

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

|   0 | SELECT STATEMENT            |              |       |       |     2 (100)

|   1 |  TABLE ACCESS BY INDEX ROWID| OBJ          |     1 |   219 |     2   (0)

|*  2 |   INDEX UNIQUE SCAN         | SYS_C0012520 |     1 |       |     1   (0)

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

索引全扫描(index full scan):

当系统CBO评估索引全扫描比全表扫描更有效时会进行index full scan，而且此时查询的结果都必须从索引中可以直接得到。

一般在什么情况下会进行索引全扫描？

排序合并操作SMJ(sort-merge join),由于牵扯到排序操作，因为索引是有序的，这个时候如果要排序的字段上有索引，那么会进行SMJ。

如果order by、group by的字句中的列存在索引的情况下也会SMJ。

与全表扫描相比，索引全扫描得出的数据是有序的。不过全表扫描可以走多块并行读，其效率也是蛮可观的。

/*+ index_fs(dblist obj_idx ) */

索引快速全扫描(index fast full scan)：

扫描索引中所有快，与index full scan 类似，唯一区别不对结果集排序，这种方式可以开启多块并行读，以便获取最大的I/O。

/*+ index_ffs(dblist obj_idx) */

索引跳跃扫描(index skip scan ):

发生在复合索引上，如果where条件字句中只包含复合索引部分列(非引导列)，且引导列的distinct非常小，可能会发生index skip scan。

当然也可以使用hints来强制走index skip scan , /*+ index_ss(dblist obj_idx) */，obj_idx为复合索引。

索引组合扫描(index combine)：