查看执行计划plustrace：set autotrace trace exp stat（SP2-0618、SP2-0611）

执行计划是SQL获取和处理数据的途径和方法.

执行计划和性能

 

SQL -- 数据库性能的始作俑者

1. 所有的数据库性能，几乎全部来自SQL。
2. 优秀的SQL是数据库最大的福祉。
3. 一条很烂的SQL，可以搞瘫一台性能极好的服务器。

为什么高效的 SQL 这么难？

1. 语言的效率，是SQL语言的最难的地方
1. – tablesan
2. – index range scan
3. – index fast scan
4. – nested loop join
5. – merge join
6. – hash join
7. ... …
2. 优化器机制开发者无法掌控

 

 

如何查看SQL语句的执行计划？

SQL> set autotrace trace exp;   SQL> set autotrace trace exp stat;--------可以查看SQL语句的效率，查看一致性读consistent gets     SQL> set autotrace off;--------关闭

plustrace：set autotrace trace exp stat（SP2-0618、SP2-0611）

1、报错：当前用户不能使用autotrace获得执行计划 SQL> set autotrace trace exp stat; SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report   2、切换为sys用户， SQL> conn /as sysdba Connected.   3、授予用户scott plustrace Role，报错plustrace角色不存在 SQL> grant plustrace to scott; grant plustrace to scott       * ERROR at line 1: ORA-01919: role 'PLUSTRACE' does not exist   4、进入$ORACLE_HOME/sqlplus/admin/plustrce.sql目录，通过运行如下SQL：plustrce.sql创建   [root@WHOST admin]# pwd /u01/app/oracle/product/11.2.4/dbhome_1/sqlplus/admin [root@WHOST admin]# ls -al total 28 drwxr-xr-x 3 oracle dba 4096 Jun 4 22:23 . drwxr-xr-x 7 oracle dba 4096 Jun 4 22:28 .. -rw-r--r-- 1 oracle dba 368 Apr 10 2011 glogin.sql drwxr-xr-x 2 oracle dba 4096 Jun 4 22:23 help -rw-r--r-- 1 oracle dba 226 Jul 17 2013 libsqlplus.def -rw-r--r-- 1 oracle dba 813 Mar 7 2006 plustrce.sql -rw-r--r-- 1 oracle dba 2118 Feb 16 2003 pupbld.sql   1）查看$ORACLE_HOME/sqlplus/admin下的plustrce.sql发现如下内容： -- -- Copyright (c) Oracle Corporation 1995, 2002. All Rights Reserved. -- -- NAME --   plustrce.sql -- -- DESCRIPTION --   Creates a role with access to Dynamic Performance Tables --   for the SQL*Plus SET AUTOTRACE ... STATISTICS command. --   After this script has been run, each user requiring access to --   the AUTOTRACE feature should be granted the PLUSTRACE role by --   the DBA. -- -- USAGE --   sqlplus "sys/knl_test7 as sysdba" @plustrce -- --   Catalog.sql must have been run before this file is run. --   This file must be run while connected to a DBA schema.   set echo on   drop role plustrace; create role plustrace;   grant select on v_$sesstat to plustrace; grant select on v_$statname to plustrace; grant select on v_$mystat to plustrace; grant plustrace to dba with admin option;   set echo off 2）执行该sql创建plustrace角色。 SQL> @./plustrce.sql SQL> SQL> drop role plustrace; drop role plustrace * ERROR at line 1: ORA-01919: role 'PLUSTRACE' does not exist   SQL> create role plustrace; Role created. SQL> SQL> grant select on v_$sesstat to plustrace; Grant succeeded. SQL> grant select on v_$statname to plustrace; Grant succeeded. SQL> grant select on v_$mystat to plustrace; Grant succeeded. SQL> grant plustrace to dba with admin option; Grant succeeded. SQL> SQL> set echo off SQL> SQL> grant plustrace to rhys; Grant succeeded.     5、授予用户 plustrace 角色 SQL> grant plustrace to scott;   Grant succeeded. 启用 autotrace SQL> set autotrace on 至此问题解决