oracle日常运维笔记

2019独角兽企业重金招聘Python工程师标准>>>

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）：

 

转载于:https://my.oschina.net/u/3862440/blog/1924010