在SQL调优的过程中，常见的跟踪事件有10046,10053，除了这些我们还可以利用10031,10032,10033来tracking sql order by、group by等排序的信息，包括pga的分配以及I/O的读取情况。某些情况下利用他们可以帮助我们分析一些棘手的问题。
[mayp@dbtest ~]$ tbs getevent |grep "^ORA-1003" ORA-10030: session logoff (KSU) ORA-10031: sort debug event (S*) ORA-10032: sort statistics (SOR*) ORA-10033: sort run information (SRD*/SRS*) ORA-10035: Write parse failures to alert log file ORA-10036: create remote row source (QKANET) ORA-10037: allocate remote row source (QKARWS) ORA-10038: dump row source tree (QBADRV) ORA-10039: type checking (OPITCA)
SQL> create table sort1(id number,name varchar2(20));
Table created.
SQL> insert into sort1 values(1,'first insert one');
1 row created.
SQL> commit;
Commit complete.
SQL> alter session set tracefile_identifier='SORT_TRACE';
Session altered.
SQL> alter session set events '10031 trace name context forever,level 100';
Session altered.
SQL> alter session set events '10032 trace name context forever,level 100';
Session altered.
SQL> alter session set events '10033 trace name context forever,level 100';
Session altered.
SQL> select * from sort1 order by id;
ID NAME
---------- ----------------------------------------
1 first insert one
SQL> alter session set events '10031 trace name context off';
Session altered.
SQL> alter session set events '10032 trace name context off';
Session altered.
SQL> alter session set events '10033 trace name context off';
Session altered.
SQL> exit
[mayp@dbtest udump]$ cd /oracle/product/admin/rac/udump/ [mayp@dbtest udump]$ ls -l |grep "SORT_TRACE" -rw-r----- 1 mayp oinstall 1567 Aug 26 15:32 rac1_ora_9704_SORT_TRACE.trc [mayp@dbtest udump]$ more rac1_ora_9704_SORT_TRACE.trc .................... ---- Sort Parameters ------------------------------ sort_area_size 65536 sort_area_retained_size 65536 sort_multiblock_read_count 1 max intermediate merge width 3 ---- Sort Statistics ------------------------------ Input records 1 Output records 1 Total number of comparisons performed 0 Total amount of memory used 2048 Uses version 2 sort Does not use asynchronous IO ---- End of Sort Statistics ----------------------- ...................
一条记录的排序，用了2K内存有点不可思议。
