v$sql——存储的是具体的SQL语句和执行计划相关信息！

如果知道了session的sid就可以找到当前session正在执行的sql：

SQL> select sql_text,fetches,executions,parse_calls,disk_reads,cpu_time,elapsed_time from v$sql t1,v$session t2 where t1.sql_id = t2.sql_id and t2.sid = 145;

SQL_TEXT                                              FETCHES EXECUTIONS PARSE_CALLS DISK_READS   CPU_TIME ELAPSED_TIME
-------------------------------------------------- ---------- ---------- ----------- ---------- ---------- ------------
insert into t select * from dba_objects                     0          2           2        133    1761441      3754195

在v$sql里面的信息，和在执行计划里面看到的信息差不多。关于v$sql的每一列的作用，可以参考《 Oracle® Database Reference》

这里想说的是（elapsed_time - cpu_time）3754195 - 1761441 = 1992754 这个时间就是在执行sql过程中的等待时间。这样可以看出sql的大部分时间到底是在等待，还是在执行！

都知道shared_pool里面存放的是最近执行的sql语句，可以使用命令alter system flush shared_pool;来清空共享池中的信息：

SQL> select spid from v$process where addr = (select paddr from v$session where sid = (select sid from v$mystat where rownum = 1));

SPID
------------
3427

SQL> variable x number;
SQL> exec :x:=100;

PL/SQL 过程已成功完成。

SQL> alter session set sql_trace = true;

会话已更改。

SQL> select owner,object_name from t where object_id = :x;

OWNER                          OBJECT_NAME
------------------------------ --------------------
SYS                            I_TYPED_VIEW1

SQL> select owner,object_name from t where object_id = :x;

OWNER                          OBJECT_NAME
------------------------------ --------------------
SYS                            I_TYPED_VIEW1

SQL> alter system flush shared_pool;

系统已更改。

SQL> select owner,object_name from t where object_id = :x;

OWNER                          OBJECT_NAME
------------------------------ --------------------
SYS                            I_TYPED_VIEW1

SQL> alter session set sql_trace = false;

aggregate=no的意思是如果同一条sql语句多次执行，在tkprof生成的trace文件中分别列出。默认是aggregate=yes，会进行合并处理。

[oracle@linux udump]$ tkprof orcl_ora_3427.trc trace explain=u1/u1 sys=no aggregate=no

TKPROF: Release 10.2.0.4.0 - Production on 星期日 10月 30 10:01:26 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


[oracle@linux udump]$ cat trace.prf 

TKPROF: Release 10.2.0.4.0 - Production on 星期日 10月 30 10:01:26 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: orcl_ora_3427.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
--------------------------------------------------------------------------------

*** SESSION ID:(145.9) 2011-10-30 09:59:11.823

********************************************************************************

select owner,object_name 
from
 t where object_id = :x


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.11          0          0          0           0
Fetch        2      0.00       0.00          0          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.11          0          5          0           1

Misses in library cache during parse:                1--硬解析
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65  (U1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=5 pr=0 pw=0 time=45 us)
      1   INDEX RANGE SCAN T_IND (cr=4 pr=0 pw=0 time=35 us)(object id 53054)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'T' (TABLE)
      1    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'T_IND' (INDEX)

********************************************************************************

select owner,object_name 
from
 t where object_id = :x


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          5          0           1
Misses in library cache during parse:                0--软解析
Optimizer mode: ALL_ROWS
Parsing user id: 65  (U1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=5 pr=0 pw=0 time=429 us)
      1   INDEX RANGE SCAN T_IND (cr=4 pr=0 pw=0 time=419 us)(object id 53054)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'T' (TABLE)
      1    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'T_IND' (INDEX)

********************************************************************************

alter system flush shared_pool                --清空共享池


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.02       0.02          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.02       0.02          0          0          0           0

Misses in library cache during parse: 0
Parsing user id: 65  (U1)
********************************************************************************

select owner,object_name 
from
 t where object_id = :x


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          5          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          5          0           1

Misses in library cache during parse:           1--硬解析
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 65  (U1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID T (cr=5 pr=0 pw=0 time=30 us)
      1   INDEX RANGE SCAN T_IND (cr=4 pr=0 pw=0 time=26 us)(object id 53054)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'T' (TABLE)
      1    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'T_IND' (INDEX)

********************************************************************************

alter session set sql_trace = false


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.02       0.02          0          0          0           0

Misses in library cache during parse: 0
Parsing user id: 65  (U1)



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.02       0.02          0          0          0           0
Execute      5      0.03       0.15          0          0          0           0
Fetch        6      0.00       0.00          0         15          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.06       0.17          0         15          0           3

Misses in library cache during parse: 2
Misses in library cache during execute: 2


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       54      0.01       0.01          0          0          0           0
Execute    130      0.04       0.04          0          0          0           0
Fetch      405      0.02       0.08          7        396          0         972
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      589      0.08       0.13          7        396          0         972

Misses in library cache during parse: 16
Misses in library cache during execute: 16

    5  user  SQL statements in session.
  130  internal SQL statements in session.
  135  SQL statements in session.
    3  statements EXPLAINed in this session.
********************************************************************************
Trace file: orcl_ora_3427.trc
Trace file compatibility: 10.01.00
Sort options: default

       1  session in tracefile.
       5  user  SQL statements in trace file.
     130  internal SQL statements in trace file.
     135  SQL statements in trace file.
      19  unique SQL statements in trace file.
       3  SQL statements EXPLAINed using schema:
           U1.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
    1498  lines in trace file.
      26  elapsed seconds in trace file.

可以看见第一次是硬解析，第二次就是软解析了。清空了共享池之后，又是硬解析了！！！

捕获排序sql语句：（这里不包含内存排序，是指磁盘排序。）

select distinct a.sid,
                a.serial#,
                a.process,
                to_char(a.logon_time, 'yyyy-mm-dd hh24:mi:ss') logon,
                a.osuser,
                c.tablespace,
                b.sql_text
  from v$session a, v$sql b, v$sort_usage c
 where a.sql_address = b.address(+)
   and a.sql_address = c.sqladdr;

如：

SQL> select distinct a.sid,
  2                  a.serial#,
  3                  a.process,
  4                  to_char(a.logon_time, 'yyyy-mm-dd hh24:mi:ss') logon,
  5                  a.osuser,
  6                  c.tablespace,
  7                  b.sql_text
  8    from v$session a, v$sql b, v$sort_usage c
  9   where a.sql_address = b.address(+)
 10     and a.sql_address = c.sqladdr;

       SID    SERIAL# PROCESS      LOGON               OSUSER                         TABLESPACE                      SQL_TEXT
---------- ---------- ------------ ------------------- ------------------------------ ------------------------------- --------------------------------------------------
       143        166 4278         2011-12-05 05:30:55 oracle                         TEMP                            select * from test order by owner