1、简易研究UNDO方法

实验所获取的知识点如下：

1、查看事务的视图名为v$transaction
2、查看事务变化的字段为v$transaction 视图的USED_UBLK字段（每次执行后的增量就是该事务所产生的回滚量)
3、在事务提交或者说无事务的时候，v$transaction的USED_UBLK字段为空值
4、表中，索引列比非索引列产生更大的UNDO

实验如下：

SQL> drop table ljb_test ;

Table dropped

SQL> create table ljb_test as select object_name no_idx, object_name idx from dba_objects;

Table created

SQL> create index idx_ljb_test on ljb_test(idx);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'ljb_test');

PL/SQL procedure successfully completed

SQL> select used_ublk from v$transaction where addr =
  2  (select taddr from v$session where sid =(select sid from v$mystat where rownum = 1));

USED_UBLK
----------
SQL>

SQL> update ljb_test set no_idx=upper(no_idx);

122546 rows updated

SQL> select used_ublk from v$transaction where addr =
  2  (select taddr from v$session where sid =(select sid from v$mystat where rownum = 1));

USED_UBLK
----------
      2010

SQL> update ljb_test set idx=upper(idx);

122546 rows updated

SQL> select used_ublk from v$transaction where addr =
  2  (select taddr from v$session where sid =(select sid from v$mystat where rownum = 1));

USED_UBLK
----------
      4776

---4776-2010>2010,因此可以推断出有索引的列开销的UNDO更大

SQL> commit;

Commit complete

select used_ublk from v$transaction where addr =
(select taddr from v$session where sid =(select sid from v$mystat where rownum = 1));

USED_UBLK
----------

SQL>

2、简易研究REDO方法
实验所获取的知识点如下：
1、研究redo的量可通过数据字典v$mystat 和v$statname关联得到 ，语句为
    select a.name,b.value
    from v$statname a,v$mystat b
    where a.statistic#=b.statistic
    and a.name='redo size';
2、表中，有索引的列产生的redo将远大于无索引列产生的redo
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb

SQL> drop table ljb_test;

Table dropped

SQL>  create table ljb_test as select object_name no_idx, object_name idx from dba_objects;

Table created

SQL> create index idx_ljb_test on ljb_test(idx);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'ljb_test');

PL/SQL procedure successfully completed

SQL>
SQL> select a.name,b.value
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and a.name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         116036376

SQL> update ljb_test set no_idx=lower(no_idx);

122548 rows updated

SQL>
SQL> select a.name,b.value from v$statname a,v$mystat b where
  2  a.statistic#=b.statistic# and a.name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         156722024

SQL> update ljb_test set idx=lower(idx);

122548 rows updated

SQL>
SQL> select a.name,b.value
  2  from v$statname a,v$mystat b
  3  where a.statistic#=b.statistic#
  4  and a.name='redo size';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         265747736

SQL>
---（156722024-116036376）<(265747736-156722024)  可推断出索引列产生的redo将更大。




3、简易分析索引的方法
如何看索引的高度有多高,建立什么样的索引效率更高如何分析

实验获取的知识点如下：
1、分析研究索引用的方法是 analyze index id_normal validate structure ，然后查看index_stats
2、注意analyze index id_normal validate structure 会导致锁表，生产慎用。
3、index_stats的数据只对当前操作过analyze index id_normal validate structure 的SESSION可见
4、HEIGHT为索引高度，值越大，索引开销越大
5、在很多场合下，灵活使用选择行索引（函数索引的一种),往往能提高效率。此外函数索引只在CBO有效

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb

SQL>
SQL> drop table ljb_test;

Table dropped

SQL> create table ljb_test (id int ,status varchar2(2));

Table created

SQL> create index id_normal on ljb_test(status);

Index created

SQL> insert into ljb_test select rownum ,'Y' from dual connect by rownum<=100000;

100000 rows inserted

SQL> insert into ljb_test select 1 ,'N' from dual;

1 row inserted

SQL> analyze table ljb_test compute statistics for table for all indexes for all indexed columns;

Table analyzed

SQL> explain plan for  select * from ljb_test where status='N';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3604305264
--------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    10 |     2   (0)| 0
|   1 |  TABLE ACCESS BY INDEX ROWID| LJB_TEST  |     1 |    10 |     2   (0)| 0
|*  2 |   INDEX RANGE SCAN          | ID_NORMAL |     1 |       |     1   (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='N')

14 rows selected

SQL> analyze index id_normal validate structure;

Index analyzed

--接下来可以看索引的高度和叶子情况

SQL> select name,btree_space,lf_rows,height from index_stats;

NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ----------
ID_NORMAL                          1976032     100001          2

SQL> drop index id_normal;

Index dropped

----以下为建立选择性索引的情况

SQL> create index id_status on  ljb_test (Case when status= 'N' then 'N' end);

Index created

SQL> analyze table ljb_test compute statistics for table for all indexes for all indexed columns;

Table analyzed

SQL> explain plan for  select * from ljb_test where (case when status='N' then 'N' end)='N';

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2720798490
--------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    10 |     2   (0)| 0
|   1 |  TABLE ACCESS BY INDEX ROWID| LJB_TEST  |     1 |    10 |     2   (0)| 0
|*  2 |   INDEX RANGE SCAN          | ID_STATUS |     1 |       |     1   (0)| 0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(CASE "STATUS" WHEN 'N' THEN 'N' END ='N')

14 rows selected

SQL> analyze index id_status validate structure;

Index analyzed

SQL> select name,btree_space,lf_rows,height from index_stats;

NAME                           BTREE_SPACE    LF_ROWS     HEIGHT
------------------------------ ----------- ---------- ----------
ID_STATUS                             8000          1                     1

SQL>




4、简易预估SQL执行进度方法
实验获取的知识点：
1、以下情况可在v$session_longops中跟到SQL语句的进度
Table scan;
Index Fast Full Scan;
Hash join;
Sort/Merge;
Sort Output;
Rollback;
Gather Table's Index Statistics.
In next chapters I’ll look deeper into first three of the above listed types.

2、跟踪到的SQL进度可以从sofar和total_work两个字段的值的差异来比较进度，理论上来说，两者相等之时，就是任务完成之时

3、有很多情况无法跟踪到SQL语句的进度
  3.1表扫描时间低于6秒
  3.2TABLE SCAN的块必须超过10000个
  3.3 索引快速全扫描的块必须超过1000个


来自：
Besides criterion that Table scan has to run more than 6 seconds there is one another criterion that is not so widely known - table has to occupy at least 10000 database blocks. Table scan has to satisfy BOTH criteria to show up in v$session_longops i.e. if the table has less than 10000 blocks but table scan runs more than 6 seconds that’s not enough.
Index FFS as well as Table Scans have another criterion for showing up in v$session_longops. Index has to occupy at least 1000 blocks i.e. 10 times less than threshold for Table Scans.

实验如下：

SQL> select * from  v$mystat where rownum=1;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       143          0          1

SQL> drop table ljb_test;

Table dropped

SQL> create table ljb_test as select * from dba_objects;

Table created

SQL> select count(*) from ljb_test t1,ljb_test  t2  where t1.owner=t2.owner ;

---开另外一窗口观察v$session_longops情况
SQL> SELECT COUNT(*) FROM v$session_longops WHERE sid=143;

  COUNT(*)
----------
         0   --Table scan访问的块要超过10000个，不会显示。


SQL> insert into ljb_test select * from ljb_test;

159466 rows inserted

SQL> insert into ljb_test select * from ljb_test;

318932 rows inserted

SQL> commit;

Commit complete

---现在就可以跟踪到了！

SQL> SELECT COUNT(*) FROM v$session_longops WHERE sid=143;

  COUNT(*)
----------
         1

SQL> SELECT sid,opname,sofar,totalwork FROM v$session_longops WHERE sid=143;

       SID OPNAME                                                                SOFAR  TOTALWORK
---------- ---------------------------------------------------------------- ---------- ----------
       143 Table Scan                                                            15235      15235
       143 Table Scan                                                               53      15235

SQL>