Bo's Oracle Station

查看: 2696|回复: 0

课程第77/78/79次(2017-12-01星期五,2017-12-03星期日上下午)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2017-12-3 17:01:25 | 显示全部楼层 |阅读模式
  1. 索引的使用监控:

  2. [oracle@station90 ~]$ sqlplus /nolog

  3. SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 3 15:26:29 2017

  4. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  5. SQL> conn hr/oracle_4U
  6. Connected.
  7. SQL> select index_name from user_indexes where table_name='T_BIG';

  8. no rows selected

  9. SQL> conn / as sysdba
  10. Connected.
  11. SQL> create table hr.t_big as select * from dba_source;

  12. Table created.

  13. SQL> conn hr/oracle_4U
  14. Connected.
  15. SQL> create bitmap index i_big on t_big ( type ) ;

  16. Index created.

  17. SQL> alter index i_big monitoring usage;

  18. Index altered.

  19. SQL> select  type , count(*)  from t_big  group by type where type  in ( 'PACKAGE', 'PACKAGE BODY') ;
  20. select        type , count(*)  from t_big  group by type where type  in ( 'PACKAGE', 'PACKAGE BODY')
  21.                                                    *
  22. ERROR at line 1:
  23. ORA-00933: SQL command not properly ended


  24. SQL> select  type , count(*)  from t_big   where type  in ( 'PACKAGE', 'PACKAGE BODY') group by type  ;

  25. TYPE               COUNT(*)
  26. ------------ ----------
  27. PACKAGE          228607
  28. PACKAGE BODY         344031

  29. SQL> set autot on
  30. SQL> select  type , count(*)  from t_big   where type  in ( 'PACKAGE', 'PACKAGE BODY') group by type  ;

  31. TYPE               COUNT(*)
  32. ------------ ----------
  33. PACKAGE BODY         344031
  34. PACKAGE          228607


  35. Execution Plan
  36. ----------------------------------------------------------
  37. Plan hash value: 3148276265

  38. --------------------------------------------------------------------------------
  39. -------

  40. | Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Tim
  41. e     |

  42. --------------------------------------------------------------------------------
  43. -------

  44. |   0 | SELECT STATEMENT              |       |   551K|  4306K|    36  (45)| 00:
  45. 00:01 |

  46. |   1 |  HASH GROUP BY                      |       |   551K|  4306K|    36  (45)| 00:
  47. 00:01 |

  48. |   2 |   BITMAP CONVERSION COUNT     |       |   551K|  4306K|    20        (0)| 00:
  49. 00:01 |

  50. |*  3 |    BITMAP INDEX FAST FULL SCAN| I_BIG |       |       |            |
  51.       |

  52. --------------------------------------------------------------------------------
  53. -------


  54. Predicate Information (identified by operation id):
  55. ---------------------------------------------------

  56.    3 - filter("TYPE"='PACKAGE' OR "TYPE"='PACKAGE BODY')

  57. Note
  58. -----
  59.    - dynamic sampling used for this statement (level=2)


  60. Statistics
  61. ----------------------------------------------------------
  62.           0  recursive calls
  63.           0  db block gets
  64.          22  consistent gets
  65.           0  physical reads
  66.           0  redo size
  67.         671  bytes sent via SQL*Net to client
  68.         520  bytes received via SQL*Net from client
  69.           2  SQL*Net roundtrips to/from client
  70.           0  sorts (memory)
  71.           0  sorts (disk)
  72.           2  rows processed

  73. SQL> select  * from v$object_usage;

  74. INDEX_NAME                       TABLE_NAME                      MON USE
  75. ------------------------------ ------------------------------ --- ---
  76. START_MONITORING    END_MONITORING
  77. ------------------- -------------------
  78. I_BIG                               T_BIG                              YES YES
  79. 12/03/2017 15:31:36



  80. Execution Plan
  81. ----------------------------------------------------------
  82. ERROR:
  83. ORA-01039: insufficient privileges on underlying objects of the view


  84. SP2-0612: Error generating AUTOTRACE EXPLAIN report

  85. Statistics
  86. ----------------------------------------------------------
  87.         105  recursive calls
  88.           0  db block gets
  89.          55  consistent gets
  90.           0  physical reads
  91.           0  redo size
  92.         930  bytes sent via SQL*Net to client
  93.         520  bytes received via SQL*Net from client
  94.           2  SQL*Net roundtrips to/from client
  95.           8  sorts (memory)
  96.           0  sorts (disk)
  97.           1  rows processed

  98. SQL>


  99. --------------------------------------------------

  100. create table iot1 ( a  number , b varchar2(200 ) , constraint
  101. pk_iot1 primary key ( a ))
  102. organization index
  103. pctthreshold 10
  104. overflow tablespace users
  105. ;

  106. ----
  107. create table iot1 ( a  number , b varchar2(200 ) , constraint
  108. pk_iot1 primary key ( a ))
  109. organization index
  110. pctthreshold 10
  111. overflow tablespace users
  112. ;

  113. select  * from dba_objects o where o.OWNER='HR'
  114. and o.OBJECT_NAME='IOT1';

  115. select  * from dba_tables t where t.TABLE_NAME like '%80694%';

  116. select  * from SYS_IOT_OVER_80694;

  117. select  * from SYS_IOT_MAP_80694;

  118. alter table iot1 move mapping table;

  119. insert into iot1 values ( 1 ,'AAAA') ;

  120. -------------------


  121. select  spid from
  122. v$session s , v$process p
  123. where s.PADDR=p.ADDR
  124. and s.terminal='pts/1';

  125. ---------
复制代码
  1. select  spid from
  2. v$session s , v$process p
  3. where s.PADDR=p.ADDR
  4. and s.terminal='pts/1';

  5. ---------------------
  6. SELECT s.sid, s.serial#, p.spid as "OS PID",s.username,
  7. s.module, st.value/100 as "CPU sec"
  8. FROM v$sesstat st, v$statname sn, v$session s, v$process p
  9. WHERE sn.name = 'CPU used by this session'
  10. AND st.statistic# = sn.statistic#
  11. AND st.sid = s.sid
  12. AND s.paddr = p.addr
  13. AND s.last_call_et < 1800
  14. AND s.logon_time > (SYSDATE - 240/1440)
  15. ORDER BY st.value;
  16. ---------------

  17. select * from v$session_event ;
  18. ---------------------
  19. SELECT s.sid, s.serial#, p.spid as "OS PID", s.username,
  20. s.module, se.time_waited
  21. FROM v$session_event se, v$session s, v$process p
  22. WHERE se.event = '&event_name'
  23. AND s.last_call_et < 1800
  24. AND s.logon_time > (SYSDATE - 240/1440)
  25. AND se.sid = s.sid
  26. AND s.paddr = p.addr
  27. ORDER BY se.time_waited;
  28. ------

  29. SELECT s.sid, s.serial#, p.spid as "OS PID", s.username,
  30. s.module, st.value/100 as "DB Time (sec)",
  31. stcpu.value/100 as "CPU Time (sec)",
  32. round(stcpu.value / st.value * 100,2) as "%CPU"
  33. FROM v$sesstat st, v$statname sn, v$session s,
  34. v$sesstat stcpu, v$statname sncpu, v$process p
  35. WHERE sn.name = 'DB time'
  36. AND st.statistic# = sn.statistic#
  37. AND st.sid = s.sid
  38. AND sncpu.name = 'CPU used by this session'
  39. AND stcpu.statistic# = sncpu.statistic#
  40. AND stcpu.sid = st.sid
  41. AND s.paddr = p.addr
  42. AND s.last_call_et < 1800
  43. AND s.logon_time > (SYSDATE - 240/1440)
  44. AND st.value > 0;
  45. ------




复制代码



最后一条语句,可以代替图形界面找顶级SQL:
Screenshot.png

Screenshot-1.png

  1. SQL> exec dbms_application_info.set_module ( 'GL','Asset_Cal' ) ;

  2. PL/SQL procedure successfully completed.

  3. SQL> select  count(*)  from t_big a  , t_big b;


复制代码
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 3 17:43:20 2017

  3. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  4. SQL> conn hr/oracle_4U
  5. Connected.
  6. SQL> exec dbms_application_info.set_module ( 'GL','Asset_Cal' ) ;

  7. PL/SQL procedure successfully completed.

  8. SQL> select  count(*)  from t04209_uname a  , t04209_uname b;

复制代码
这个不会被跟踪:
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 3 17:43:58 2017

  3. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  4. SQL> conn hr/oracle_4U
  5. Connected.
  6. SQL> select  count(*) from t04209_uname a  , t_big b;

复制代码

进行端对端跟踪,必备trcsess这个工具。


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 18:27 , Processed in 0.129429 second(s), 27 queries .

快速回复 返回顶部 返回列表