利用SQL monitor生成的运行报告，远远胜过 trace 10046,尤其在RAC的环境里, 方法如下： 1) run your SQL 2) get sql_id and sql_exec_id from gv$sql_monitor 3) create html report by dbms_sqltune.report_sql_monitor
Usage: ora [-u user] [-i instance#] [] General -u user/pass use USER/PASS to log in -i instance# append # to ORACLE_SID -sid set ORACLE_SID to sid -top # limit some large queries to on # rows - repeat Repeat an coomand time. Sleep between two
参加oracle原厂培训，老师给的，很好用，介绍给大家
Produces an HTML report with a list of observations based on
health-checks performed in and around a SQL statement that
may be performing poorly.
Inputs a memory-resident SQL_ID.
In addition to the health_check report, i
//根据用户分组会话
select t.USERNAME,count(*) from v$session t group by t.USERNAME
//查找某一用户正在执行的sql_id值
select * from v$session t where t.USERNAME = 'BUDGET' and t.SQL_ID is not null
//查找对应sql_id的对应sql语句
select m.SQL_TEXT from v$session t , v$sqlarea m where
Oracle的show processlist
代码如下:
set linesize 400;
set pagesize 400;
col sql_text format a100;
col machine format a25;
col username format a15;
SELECT a.username,a.machine, b.sql_id, b.SQL_TEXT
FROM v$session a, v$sqlarea b
WHERE a.sql_address = b.addr
查看逻辑读前10的SQL：
代码如下:
set linesize 300;
set pagesize 300;
set long 50000;
SELECT *
FROM (
SELECT sql_fulltext AS sql, SQL_ID,buffer_gets / executions AS “Gets/Exec”,buffer_gets, executions
FROM V$SQLAREA
WHERE buffer_gets > 10000
OR
在SQL优化过程，有时候需要查看哪些SQL具有多个执行计划（Multiple Executions Plans for the same SQL statement），因为同一个SQL有多个执行计划一般意味着代码有问题或某些其它原因，例如，SQL语句使用绑定变量，但是绑定变量的类型或长度可能不一致会导致同一SQL出现不同执行计划。
–查看数据库里面有多个执行计划的SQL语句的SQL_ID
SELECT SQL_ID， COUNT(1) AS PLAN_NUM
FROM V$SQ
