这只是一篇个人小结，还有许多工具没被提到,也许以后会有续篇。
1. explain plan: show the explain plan without running the sql
step:
1) @?/rdbms/admin/utlxplan -- to setup plan table
2) explain plan set statement_id='xxxx' for select ....
advantage:
1) doesn't need to run the query
disadvantage:
1) can not be used to show the explain plan of other running sessions
2) except explain plan, no other useful information is displayed. For example:statistics
2. autotrace: show explain plan or/and statistics with running the sql
step:
1) @?/sqlplus/admin/plustrce -- setup plustrace role
2) grant plustrace to user_name
3) set autotrace [off | traceonly | on [explain |stat]]
advantage:
1) except explain plan, it can also show statistics
2) it is not needed to run the command like @?/rdbms/admin/utlxpls to show explain plan
disadvantage:
1) It can only be used in sqlplus
2) can not be used to show the explain plan of other running sessions
3. alter session set events -- to see what oracle did internally
step:
1) grant alter session to user_name
2) alter session set tracefile_identifier='xxx'
3) alter session set events '...., level..'
advantage:
1) more internal details can be seen from the trace file
disadvantage:
1) developer doesn't have read access to user_dump_dest
Two way you can solve this:
a) turn on the _trace_file_public parameter, but it has a risk of openning a security hold.
b) a solution provided by TOM
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1112673450743 2) it's difficult to understand the trace file
3) it can not be used to generate trace file of other running session
4. dbms_support -- to generate the trace file of running session
step:
1) @?/rdbms/admin/dbmssupp
2) grant execute on dbms_support to user_name
3) create public synonym dbms_support for dbms_support
4) grant select on v_$session to user_name
5) select sid, serial# from v$session where username='xxx' and ...
6) exec dbms_support.start_trace_in_session(sid=>sid_val, serial=>serial_val, wait=>true, bind=>true)
7) exec dmbs_support.stop_trace_in_session(sid=>sid_val, serial=>serial_val)
advantage:
1) it is allowed to generate trace file for running sessions
disadvantage:
1) it is a little bit hard to find what you are looking for, since there are too many information
2) it is only recommended to be used by oracle support
3) hard to read if you don't understand the trace file content
5. alter system set events -- to generate trace file for specific oracle error event
step:
alter system set events '....'
advantage:
1) Without people involved, the error can be traced by generated trace file
2) Useful to find issue if you don't know where the problem came from in the big application
disadvantage:
1) only dba can use it
2) too much information in the trace file
