【Oracle】28.dbsnake-printsql脚本的用法

1.创建存储过程

SQL> start PRINTSQL.prc
Enter value for 1: --回车
old  40: from v$sql where hash_value=&1
new  40: from v$sql where hash_value=

Procedure created.

2.执行全表扫描 

SQL> select * from test01;

3.通过TOP查看 SPID;

[oracle@oracle11gdg ~]$ top
top - 10:00:54 up 9 min,  5 users,  load average: 1.42, 0.61, 0.28
Tasks: 227 total,   2 running, 225 sleeping,   0 stopped,   0 zombie
%Cpu(s):  6.7 us, 23.3 sy,  0.0 ni, 66.7 id,  0.0 wa,  0.0 hi,  3.3 si,  0.0 st
KiB Mem :  8581928 total,  6557972 free,   628580 used,  1395376 buff/cache
KiB Swap:  4390908 total,  4390908 free,        0 used.  7193788 avail Mem 

   PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND                                                                                              
  3018 oracle    20   0   98676  14296  10168 S  37.5  0.2   0:41.79 sqlplus                                                                                              
  2304 root      20   0  161656   6140   4692 S  12.5  0.1   0:16.58 sshd                                                                                                 
  3023 oracle    20   0 4098272  80304  77736 R   6.2  0.9   0:08.60 oracle                                                                                               
     1 root      20   0  191292   4248   2584 S   0.0  0.0   0:02.25 systemd                                                                                              
     2 root      20   0       0      0      0 S   0.0  0.0   0:00.01 kthreadd                                                                                             
     3 root      20   0       0      0      0 S   0.0  0.0   0:00.00 kworker/0:0                                                                                          
     4 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 kworker/0:0H                                                                                         
     6 root      20   0       0      0      0 S   0.0  0.0   0:00.06 ksoftirqd/0                                                                                          
     7 root      rt   0       0      0      0 S   0.0  0.0   0:00.07 migration/0                                                                                          
     8 root      20   0       0      0      0 S   0.0  0.0   0:00.00 rcu_bh                                                                                               
     9 root      20   0       0      0      0 S   0.0  0.0   0:00.39 rcu_sched                                                                                            
    10 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 lru-add-drain                                                                                        
    11 root      rt   0       0      0      0 S   0.0  0.0   0:00.00 watchdog/0                                                                                           
    12 root      rt   0       0      0      0 S   0.0  0.0   0:00.00 watchdog/1                                                                                           
    13 root      rt   0       0      0      0 S   0.0  0.0   0:00.08 migration/1                                                                                          
    14 root      20   0       0      0      0 S   0.0  0.0   0:00.16 ksoftirqd/1                                                                                          
    15 root      20   0       0      0      0 S   0.0  0.0   0:00.00 kworker/1:0                                                                                          
    16 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 kworker/1:0H                                                                                         
    18 root      20   0       0      0      0 S   0.0  0.0   0:00.00 kdevtmpfs                                                                                            
    19 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 netns                                                                                                
    20 root      20   0       0      0      0 S   0.0  0.0   0:00.00 khungtaskd                                                                                           
    21 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 writeback                                                                                            
    22 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 kintegrityd                                                                                          
    23 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 bioset                                                                                               
    24 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 bioset                                                                                               
    25 root       0 -20       0      0      0 S   0.0  0.0   0:00.00 bioset          

4.查看进程3023 的SQL和执行计划是什么。

SQL> set serveroutput on size 1000000
SQL> exec PRINTSQL(3023,'SPID');
--------------------------------------------------------------------------------
------
select * from test01
--------------------------------------------------------------------------------
------
The session id is 21
The status is INACTIVE
The sql hash value is 1952671315
The prev hash value is 1952671315
The osuser is oracle
The machine is oracle11gdg
The terminal is pts/4
The program is sqlplus@oracle11gdg (TNS V1-V3)
The event is SQL*Net message from client
--------------------------------------------------------------------------------
------
alter system kill session '21,11' immediate;
--------------------------------------------------------------------------------
------
The hash_value is 1952671315
The child_number is 0
The plan_hash_value is 262542483
The execution is 4
The buffer_gets is 272512
The gets_per_exec is 68128
The rows_processed is 3857836
The rows_per_exec is 964459
The disk_reads is 3993
The reads_per_exec is 998.25
The cpu_time is 2.28516
The cpu_per_exec is .57129
The ELAPSED_TIME is 2.299903
The ela_per_exec is .57497575
--------------------------------------------------------------------------------
HASH_VALUE: 1952671315	 CHILD_NUMBER: 0
--------------------------------------------------------------------------------
select * from test01
Plan hash value: 262542483
-----------------------------------------------
| Id   | Operation	    | Name   | E-Rows |
-----------------------------------------------
|    0 | SELECT STATEMENT   |	     |	      |
|    1 |  TABLE ACCESS FULL | TEST01 |	1000K |
-----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* parameter 'statistics_level' is set to 'ALL', at session or system level
PL/SQL procedure successfully completed.

5.总结

此脚本由DBSNAKE,编写,我这里只测试及学习如何使用。
由此可见此脚本对于正在运行的进程，则可以通过进程号找到正在运行的SQL,
同时打印它的SQL文本和执行计划,非常好用。同时它还打印了会话ID,以及
杀死会话的SQL语句，太智能了。我们可以看出，通过理解原理，编写脚本 
是DBA运维过程中的比较高端而且非常有价值的一项工作。