扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
一、通过PL/SQL Dev工具
10余年的辽阳网站建设经验，针对设计、前端、开发、售后、文案、推广等六对一服务，响应快，48小时及时工作处理。全网整合营销推广的优势是能够根据用户设备显示端的尺寸不同，自动调整辽阳建站的显示方式，使网站能够适用不同显示终端，在浏览器中调整网站的宽度，无论在任何一种浏览器上浏览网站，都能展现优雅布局与设计，从而大程度地提升浏览体验。创新互联公司从事“辽阳网站设计”,“辽阳网站推广”以来，每个客户项目都认真落实执行。
1、直接File-New-Explain Plan Window，在窗口中执行sql可以查看计划结果。其中，Cost表示cpu的消耗，单位为n%，Cardinality表示执行的行数，等价Rows。
2、先执行 EXPLAIN PLAN FOR select * from tableA where paraA=1，再 select * from table(DBMS_XPLAN.DISPLAY)便可以看到oracle的执行计划了，看到的结果和1中的一样，所以使用工具的时候推荐使用1方法。
注意：PL/SQL Dev工具的Command window中不支持set autotrance on的命令。还有使用工具方法查看计划看到的信息不全，有些时候我们需要sqlplus的支持。
二、通过sqlplus
1．最简单的办法
Sql set autotrace on
Sql select * from dual;
执行完语句后，会显示explain plan 与 统计信息。
这个语句的优点就是它的缺点，这样在用该方法查看执行时间较长的sql语句时，需要等待该语句执行成功后，才返回执行计划，使优化的周期大大增长。如果不想执行语句而只是想得到执行计划可以采用：
Sql set autotrace traceonly
这样，就只会列出执行计划，而不会真正的执行语句，大大减少了优化时间。虽然也列出了统计信息，但是因为没有执行语句，所以该统计信息没有用处，如果执行该语句时遇到错误，解决方法为：
(1）在要分析的用户下：
Sqlplus @ ?
dbmsadminutlxplan.sql
(2) 用sys用户登陆
Sqlplus @ ?sqlplusadminplustrce.sql
Sqlplus grant plustrace to user_name;
- - user_name是上面所说的分析用户
2．用explain plan命令
(1) sqlplus explain plan for select * from testdb.myuser
(2) sqlplus select * from table(dbms_xplan.display);
上面这2种方法只能为在本会话中正在运行的语句产生执行计划，即我们需要已经知道了哪条语句运行的效率很差，我们是有目的只对这条SQL语句去优化。其实，在很多情况下，我们只会听一个客户抱怨说现在系统运行很慢，而我们不知道是哪个SQL引起的。此时有许多现成的语句可以找出耗费资源比较多的语句，如：
SELECT ADDRESS, substr(SQL_TEXT,1,20) Text, buffer_gets, executions,
buffer_gets/executions AVG FROM v$sqlarea
WHERE executions0 AND buffer_gets 100000 ORDER BY 5;
ADDRESS TEXT BUFFER_GETS EXECUTIONS AVG
-------- ---------------------------------------- ----------- ---------- ------------------------------------------------------------
66D83D64 select t.name, (sel 421531 60104 7.01336017
66D9E8AC select t.schema, t.n 1141739 2732 417.913250
66B82BCC select s.synonym_nam 441261 6 73543.5
从而对找出的语句进行进一步优化。当然我们还可以为一个正在运行的会话中运行的所有SQL语句生成执行计划，这需要对该会话进行跟踪，产生trace文件，然后对该文件用tkprof程序格式化一下，这种得到执行计划的方式很有用，因为它包含其它额外信息，如SQL语句执行的每个阶段(如Parse、Execute、Fetch)分别耗费的各个资源情况(如CPU、DISK、elapsed等)。
3、启用SQL_TRACE跟踪所有后台进程活动：
全局参数设置： .OracleHome/admin/SID/pfile中指定: SQL_TRACE = true (10g)
当前session中设置：
SQL alter session set SQL_TRACE=true;
SQL select * from dual;
SQL alter session set SQL_TRACE=false;
对其他用户进行跟踪设置：
SQL select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
128 54521 B
开启跟踪:SQL exec dbms_system.set_SQL_TRACE_in_session(127,31923,true);
关闭跟踪:SQL exec dbms_system.set_SQL_TRACE_in_session(127,31923,false);
然后使用oracle自带的tkprof命令行工具格式化跟踪文件。
4、使用10046事件进行查询：
10046事件级别：
Lv1 - 启用标准的SQL_TRACE功能,等价于SQL_TRACE
Lv4 - Level 1 + 绑定值(bind values)
Lv8 - Level 1 + 等待事件跟踪
Lv12 - Level 1 + Level 4 + Level 8
全局设定：
OracleHome/admin/SID/pfile中指定: EVENT="10046 trace name context forever,level 12"
当前session设定：
开启:SQL alter session set events '10046 trace name context forever, level 8';
关闭:SQL alter session set events '10046 trace name context off';
对其他用户进行设置：
SQL select sid,serial#,username from v$session where username='XXX';
SID SERIAL# USERNAME
------ ---------- ------------------
127 31923 A
SQL exec dbms_system.set_ev(127,31923,10046,8,'A');
5、使用tkprof格式化跟踪文件： (根据下面SQL语句得到的文件都不存在该目录下,郁闷啊,懵懂啊...)
一般，一次跟踪可以分为以下几步：
1、界定需要跟踪的目标范围，并使用适当的命令启用所需跟踪。
2、经过一段时间后，停止跟踪。此时应该产生了一个跟踪结果文件。
3、找到跟踪文件，并对其进行格式化，然后阅读或分析。
--使用一下SQL找到当前session的跟踪文件：
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name
from
( select p.spid from v$mystat m,v$session s, v$process p
where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
( select t.instance from v$thread t,v$parameter v
where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
( select value from v$parameter where name = 'user_dump_dest' ) d;
-- 其它用户的 session
SELECT d.value|| '/' ||lower(rtrim(i.instance, chr( 0 )))|| '_ora_' ||p.spid|| '.trc' trace_file_name
from
( select p.spid from v$session s, v$process p
where s.sid= '27' and s. SERIAL#= '30' and p.addr = s.paddr) p,
( select t.instance from v$thread t,v$parameter v
where v.name = 'thread' and (v.value = 0 or t.thread# = to_number(v.value))) i,
( select value from v$parameter where name = 'user_dump_dest' ) d;
--查找后使用tkprof命令,将TRACE文件格式为到D盘的explain_format.txt文件中
SQL $tkprof d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.trc d:/explain_format.txt
文件内容大致如下(看不太懂....懵懂啊.....天啊....神啊.....过几时就懂了/////////////)
TKPROF: Release 9.2.0.1.0 - Production on 星期二 4月 20 13:59:20 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Trace file: d:/oracle/admin/FZLGFM/udump/fzlgfm_ora_3468.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
********************************************************************************
alter session set events '10046 trace name context forever, level 8'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 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 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: SYS
oracle里对sql查询的日志进行查看步骤如下：
1、Oracle日志的路径的查看。
2、Oracle日志文件包含哪些内容：日志的数量可能略有不同。
3、Oracle日志的查看方法语句代码。
4、LogMiner的使用方法理解。
5、查看LogMiner工具分析结果数据 。
6、分析结果在GV$LOGMNR_CONTENTS 视图中。
oracle使用词法分析器（lexer）进行全文检索，
lexer使用CTX_DDL包，包api位置：{ORACLE_HOME}/ctx/admin/dr0ddl.pkh
以oracle表分析为例：
drop table test;
select count(*) from test;
--创建测试表
create table test
(
id number(9),
nick varchar2(30)
);
--插入测试数据
begin
for i in 1..100000 loop
insert into test(id) values(i);
end loop;
commit;
end;
select * from test;
--更新nick字段，使数据发生严重倾斜
update test set nick='abc' where rownum99999;
--创建索引
create index idx_test_nick on test(nick);
update test set nick='def' where nick is null;
--只对索引进行分析
analyze index idx_test_nick compute statistics;
select * from user_indexes;
--查看索引名,对应存储的数据块,不同的key数量,记录数（行数）的分析信息
select index_name, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS
from user_indexes
where index_name = 'IDX_TEST_NICK';
--dba_tab_col_statistics
--查看表的统计信息
select COLUMN_NAME, NUM_BUCKETS, num_distinct
from USER_tab_columns
where table_name = 'TEST';
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
select * from test where nick ='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
--由上可以看到,对索引分析之后,sql的执行路径都是基于规则的,索引的字段的偏移
--先根据索引找到rowid,然后再根据rowid读取记录,这个过程肯定比全表扫描读取记录要慢
--user_part_col_statistics 分区分析信息
--分析表的第二列nick
analyze table test compute statistics for columns size 2 nick;
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
--根据上面的执行计划，还是按照规则来执行的
--分析表
analyze table test compute statistics for table;
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=
1499970)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14
99970)
--分析表之后，完全按照成本来执行
--删除所有的统计数据，并只对表与列进行分析，不分析索引，
--ORACLE使用CBO的优化器，并产生了正确的执行计划
analyze table test delete statistics;
--分析列nick
analyze table test compute statistics for table for columns size 2 nick;
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=
1499970)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14
99970)
--
select * from test where nick ='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=30)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=2 Byt
es=30)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
=1 Card=2)
--创建TEST表ID列上的索引，但不对索引进行分析
create index idx_test_id on test(id);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15
000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000
Bytes=15000)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
Card=400)
--当条件中即有id，又有nick时，因为nick上有直方图,ORACLE知道nick='abc'的值特别的多，所以不走IDX_TEST_NICK索引,走IDX_TEST_ID上的索引
select * from test where id=5 and nick='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15
000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000
Bytes=15000)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
Card=400)
--当条件中即有id，又有nick时，因为nick上有直方图,ORACLE知道nick='def'的值特别的少，所以走IDX_TEST_NICK上的索引,不走IDX_TEST_ID索引
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
=1 Card=2)
select * from test where nick='def' and id=5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
=1 Card=2)
--在分析ID列后，ORACLE发现ID列的选择度更高，所以不再选择IDX_TEST_NICK索引，而是选择IDX_TEST_ID
analyze table test compute statistics for columns size 1 id;
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=7)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=7)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
Card=1)
/*
下面来看另外一种情况，我们删除所有的统计数据，然后在ID列上创建唯一索引,在此条件下，
只分析表与分析列nick,我们看到ORACLE走了正确的执行计划，
走了UK_TEST_ID,其实从这里也给我们带来很多的启示：
在主键与唯一键约束的列上是否需要直方图的问题?
如果在这些列上有像这样的查询where id 100 and id 1000,
我们还是需要有直方图的，但除此之外，好像真的没有直方图的必要了!
*/
analyze table test delete statistics;
drop index idx_test_id;
create unique index uk_test_id on test(id);
--分析表的第二列nick
analyze table test compute statistics for table for columns size 2 nick;
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2 1 INDEX (UNIQUE SCAN) OF 'UK_TEST_ID' (UNIQUE) (Cost=1 Car
d=100000)
从以上一系列的实验可以看出，对ORACLE的优化器CBO来说，表的分析与列的分析才是最重要的，索引的分析次之。还有我们可以考虑我们的哪些列上需要直方图，对于bucket的个数问题,oracle的默认值是75个，所以根据你的应用规则，选择合适的桶数对性能也是有帮助的。因为不必要的桶的个数的大量增加，必然会带来SQL语句硬解析时产生执行计划的复杂度问题。
通过命令来查看，如用户权限，表名，存储位置，版本等等。
选中树形目录中监听程序项，再点击左上侧“+”按钮添加监听程序，点击监听程序目录，默认新加的监听器名称是LISTENER，该名称也可以 由任意合法字符命名，选中该名称，选中窗口右侧栏下拉选项中的“监听位置”，点击添加地址按钮。
选中窗口右侧栏下拉选项中的“数据库服务”，点击添加数据库按钮，在出现的数据库栏中输入全局数据库名，如myoracle，注意这里的全局数据库名与数据库SID有所区别，全局数据库名实际通过域名来控制在同一网段内数据库全局命名的唯一性，就如Windows下的域名控制器，如这里可以输入 myoracle.192.168.1.5。
如果客户端需要连接数据库服务器进行操作，则需要配置该客户端，其依附对象可以是任意一台欲连接数据库服务器进行操作的pc机，也可以是数据库服务器自身。
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网