课程第77/78/79次（2017-12-01星期五，2017-12-03星期日上下午）

botang

1. 索引的使用监控：
   
2. 
   
3. [oracle@station90 ~]$ sqlplus /nolog
   
4. 
   
5. SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 3 15:26:29 2017
   
6. 
   
7. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
8. 
   
9. SQL> conn hr/oracle_4U
   
10. Connected.
    
11. SQL> select index_name from user_indexes where table_name='T_BIG';
    
12. 
    
13. no rows selected
    
14. 
    
15. SQL> conn / as sysdba
    
16. Connected.
    
17. SQL> create table hr.t_big as select * from dba_source;
    
18. 
    
19. Table created.
    
20. 
    
21. SQL> conn hr/oracle_4U
    
22. Connected.
    
23. SQL> create bitmap index i_big on t_big ( type ) ;
    
24. 
    
25. Index created.
    
26. 
    
27. SQL> alter index i_big monitoring usage;
    
28. 
    
29. Index altered.
    
30. 
    
31. SQL> select  type , count(*)  from t_big  group by type where type  in ( 'PACKAGE', 'PACKAGE BODY') ;
    
32. select        type , count(*)  from t_big  group by type where type  in ( 'PACKAGE', 'PACKAGE BODY')
    
33.                                                    *
    
34. ERROR at line 1:
    
35. ORA-00933: SQL command not properly ended
    
36. 
    
37. 
    
38. SQL> select  type , count(*)  from t_big   where type  in ( 'PACKAGE', 'PACKAGE BODY') group by type  ;
    
39. 
    
40. TYPE               COUNT(*)
    
41. ------------ ----------
    
42. PACKAGE          228607
    
43. PACKAGE BODY         344031
    
44. 
    
45. SQL> set autot on
    
46. SQL> select  type , count(*)  from t_big   where type  in ( 'PACKAGE', 'PACKAGE BODY') group by type  ;
    
47. 
    
48. TYPE               COUNT(*)
    
49. ------------ ----------
    
50. PACKAGE BODY         344031
    
51. PACKAGE          228607
    
52. 
    
53. 
    
54. Execution Plan
    
55. ----------------------------------------------------------
    
56. Plan hash value: 3148276265
    
57. 
    
58. --------------------------------------------------------------------------------
    
59. -------
    
60. 
    
61. | Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Tim
    
62. e     |
    
63. 
    
64. --------------------------------------------------------------------------------
    
65. -------
    
66. 
    
67. |   0 | SELECT STATEMENT              |       |   551K|  4306K|    36  (45)| 00:
    
68. 00:01 |
    
69. 
    
70. |   1 |  HASH GROUP BY                      |       |   551K|  4306K|    36  (45)| 00:
    
71. 00:01 |
    
72. 
    
73. |   2 |   BITMAP CONVERSION COUNT     |       |   551K|  4306K|    20        (0)| 00:
    
74. 00:01 |
    
75. 
    
76. |*  3 |    BITMAP INDEX FAST FULL SCAN| I_BIG |       |       |            |
    
77.       |
    
78. 
    
79. --------------------------------------------------------------------------------
    
80. -------
    
81. 
    
82. 
    
83. Predicate Information (identified by operation id):
    
84. ---------------------------------------------------
    
85. 
    
86.    3 - filter("TYPE"='PACKAGE' OR "TYPE"='PACKAGE BODY')
    
87. 
    
88. Note
    
89. -----
    
90.    - dynamic sampling used for this statement (level=2)
    
91. 
    
92. 
    
93. Statistics
    
94. ----------------------------------------------------------
    
95.           0  recursive calls
    
96.           0  db block gets
    
97.          22  consistent gets
    
98.           0  physical reads
    
99.           0  redo size
    
100.         671  bytes sent via SQL*Net to client
     
101.         520  bytes received via SQL*Net from client
     
102.           2  SQL*Net roundtrips to/from client
     
103.           0  sorts (memory)
     
104.           0  sorts (disk)
     
105.           2  rows processed
     
106. 
     
107. SQL> select  * from v$object_usage;
     
108. 
     
109. INDEX_NAME                       TABLE_NAME                      MON USE
     
110. ------------------------------ ------------------------------ --- ---
     
111. START_MONITORING    END_MONITORING
     
112. ------------------- -------------------
     
113. I_BIG                               T_BIG                              YES YES
     
114. 12/03/2017 15:31:36
     
115. 
     
116. 
     
117. 
     
118. Execution Plan
     
119. ----------------------------------------------------------
     
120. ERROR:
     
121. ORA-01039: insufficient privileges on underlying objects of the view
     
122. 
     
123. 
     
124. SP2-0612: Error generating AUTOTRACE EXPLAIN report
     
125. 
     
126. Statistics
     
127. ----------------------------------------------------------
     
128.         105  recursive calls
     
129.           0  db block gets
     
130.          55  consistent gets
     
131.           0  physical reads
     
132.           0  redo size
     
133.         930  bytes sent via SQL*Net to client
     
134.         520  bytes received via SQL*Net from client
     
135.           2  SQL*Net roundtrips to/from client
     
136.           8  sorts (memory)
     
137.           0  sorts (disk)
     
138.           1  rows processed
     
139. 
     
140. SQL>
     
141. 
     
142. 
     
143. --------------------------------------------------
     
144. 
     
145. create table iot1 ( a  number , b varchar2(200 ) , constraint
     
146. pk_iot1 primary key ( a ))
     
147. organization index
     
148. pctthreshold 10
     
149. overflow tablespace users
     
150. ;
     
151. 
     
152. ----
     
153. create table iot1 ( a  number , b varchar2(200 ) , constraint
     
154. pk_iot1 primary key ( a ))
     
155. organization index
     
156. pctthreshold 10
     
157. overflow tablespace users
     
158. ;
     
159. 
     
160. select  * from dba_objects o where o.OWNER='HR'
     
161. and o.OBJECT_NAME='IOT1';
     
162. 
     
163. select  * from dba_tables t where t.TABLE_NAME like '%80694%';
     
164. 
     
165. select  * from SYS_IOT_OVER_80694;
     
166. 
     
167. select  * from SYS_IOT_MAP_80694;
     
168. 
     
169. alter table iot1 move mapping table;
     
170. 
     
171. insert into iot1 values ( 1 ,'AAAA') ;
     
172. 
     
173. -------------------
     
174. 
     
175. 
     
176. select  spid from
     
177. v$session s , v$process p
     
178. where s.PADDR=p.ADDR
     
179. and s.terminal='pts/1';
     
180. 
     
181. ---------
     

复制代码

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. ---------------------
   
7. SELECT s.sid, s.serial#, p.spid as "OS PID",s.username,
   
8. s.module, st.value/100 as "CPU sec"
   
9. FROM v$sesstat st, v$statname sn, v$session s, v$process p
   
10. WHERE sn.name = 'CPU used by this session'
    
11. AND st.statistic# = sn.statistic#
    
12. AND st.sid = s.sid
    
13. AND s.paddr = p.addr
    
14. AND s.last_call_et < 1800
    
15. AND s.logon_time > (SYSDATE - 240/1440)
    
16. ORDER BY st.value;
    
17. ---------------
    
18. 
    
19. select * from v$session_event ;
    
20. ---------------------
    
21. SELECT s.sid, s.serial#, p.spid as "OS PID", s.username,
    
22. s.module, se.time_waited
    
23. FROM v$session_event se, v$session s, v$process p
    
24. WHERE se.event = '&event_name'
    
25. AND s.last_call_et < 1800
    
26. AND s.logon_time > (SYSDATE - 240/1440)
    
27. AND se.sid = s.sid
    
28. AND s.paddr = p.addr
    
29. ORDER BY se.time_waited;
    
30. ------
    
31. 
    
32. SELECT s.sid, s.serial#, p.spid as "OS PID", s.username,
    
33. s.module, st.value/100 as "DB Time (sec)",
    
34. stcpu.value/100 as "CPU Time (sec)",
    
35. round(stcpu.value / st.value * 100,2) as "%CPU"
    
36. FROM v$sesstat st, v$statname sn, v$session s,
    
37. v$sesstat stcpu, v$statname sncpu, v$process p
    
38. WHERE sn.name = 'DB time'
    
39. AND st.statistic# = sn.statistic#
    
40. AND st.sid = s.sid
    
41. AND sncpu.name = 'CPU used by this session'
    
42. AND stcpu.statistic# = sncpu.statistic#
    
43. AND stcpu.sid = st.sid
    
44. AND s.paddr = p.addr
    
45. AND s.last_call_et < 1800
    
46. AND s.logon_time > (SYSDATE - 240/1440)
    
47. AND st.value > 0;
    
48. ------
    
49. 
    
50. 
    
51. 
    
52. 
    

复制代码

最后一条语句，可以代替图形界面找顶级SQL：

1. SQL> exec dbms_application_info.set_module ( 'GL','Asset_Cal' ) ;
   
2. 
   
3. PL/SQL procedure successfully completed.
   
4. 
   
5. SQL> select  count(*)  from t_big a  , t_big b;
   
6. 
   
7. 
   

复制代码

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 3 17:43:20 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn hr/oracle_4U
   
8. Connected.
   
9. SQL> exec dbms_application_info.set_module ( 'GL','Asset_Cal' ) ;
   
10. 
    
11. PL/SQL procedure successfully completed.
    
12. 
    
13. SQL> select  count(*)  from t04209_uname a  , t04209_uname b;
    
14. 
    

复制代码

这个不会被跟踪：

1. [oracle@station90 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 3 17:43:58 2017
   
4. 
   
5. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn hr/oracle_4U
   
8. Connected.
   
9. SQL> select  count(*) from t04209_uname a  , t_big b;
   
10. 
    

复制代码

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