普通用户获取详细执行计划需要哪些权限
近日换了工作,上来第一天就要求优化sql,众所周知,sql优化最重要的就是执行计划,然后开发人员因为权限限制,时常无法获取最为详细的执行计划,那么获取这些详细信息需要哪些权限呢?
1.实验环境
SQL>show user
USER is "SYS"
SQL> set linesize 1000;
SQL> select version from v$instance;
VERSION
-----------------
11.2.0.1.0
--创建用户
SQL> create user tempuser identified by tempuser;
User created.
--授予必要的权限
SQL> grant resource to tempuser;
Grant succeeded.
SQL> grant connect to tempuser;
Grant succeeded.
2.tempuser用户调试执行计划
SQL> show user
USER is "TEMPUSER"
SQL> create table test(id int);
Table created.
SQL> select count(1) from test;
COUNT(1)
----------
0
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
User has no SELECT privilege on V$SESSION
--这里我们看到提示需要对V$SESSION的select 权限
--赋权
SQL> grant select on v_$session to tempuser;
Grant succeeded.
--这里注意对象是 v_$session 而不是我们经常使用的 v$session 因为v$session是v_$session的同义词
--再调试
SQL> select count(1) from test;
COUNT(1)
----------
0
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN
--这里看到提示变了,继续赋权
SQL> grant select on v_$sql_plan to tempuser;
Grant succeeded.
SQL> select count(1) from test;
COUNT(1)
----------
0
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN
--然而这里我们看到的提示没变,我们明明已经赋予权限了呀?
SQL> show user;
USER is "TEMPUSER"
SQL> select count(1) from v$sql_plan;
COUNT(1)
----------
4060
--我们看到权限已经赋予,但提示却还是一样,我百度了一些资料,得到答案,这里还需要v$sql的权限,至于为什么,我没有找到相关文档,也许可以这样理解,如果我们平常需要查询一句sql的执行计划,首先需要该sql的sql id,而这个sqlid 一般我们都是需要去v$sql 里去查询的,而oracle内部的算法,不外乎也是这种思路吧。
--继续赋权
SQL> grant select on v_$sql to tempuser;
Grant succeeded.
SQL> select count(1) from test;
COUNT(1)
----------
0
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7zrgxh35ctntg, childnumber 1
-------------------------------------
select count(1) from test
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used forthis statement (level=2)
18 rows selected.
--ok,这边已经有执行计划了,继续详细信息
SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
User has no SELECT privilege on V$SQL_PLAN_STATISTICS_ALL
--继续吧
SQL> grant select on v_$sql_plan_statistics_all to tempuser;
Grant succeeded.
--这里statistics_level =all让统计信息最为详尽
SQL> alter session set statistics_level =all;
Session altered.
SQL> select count(1) from test;
COUNT(1)
----------
0
SQL> select * fromtable(dbms_xplan.display_cursor(null,null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------
SQL_ID 7zrgxh35ctntg, childnumber 2
-------------------------------------
select count(1) from test
Plan hash value: 1950795681
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost(%CPU)| E-Time | A-Rows | A-Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| | 1 |00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:00.01 |
| 2 | TABLE ACCESS FULL| TEST | 1 | 1 | 2 (0)| 00:00:01 | 0 |00:00:00.01 |
---------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TEST@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1""TEST"@"SEL$1")
END_OUTLINE_DATA
*/
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
Note
-----
- dynamic sampling used forthis statement (level=2)
43 rows selected.
--ok,已经非常详细了,但好像缺了点什么,数据块的读取,没有显示,换个工具autotrace
SQL> set autotrace on;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
--显示需要一个角色名叫做 PLUSTRACE
--查询官方文档 PLUSTRACE是需要自己创建的一个角色,代码在@$ORACLE_HOME/sqlplus/admin/plustrce.sql
--代码如下
[oracle@panyc admin]$ cat $ORACLE_HOME/sqlplus/admin/plustrce.sql
--
-- Copyright (c) Oracle Corporation 1995, 2002. All Rights Reserved.
--
-- NAME
-- plustrce.sql
--
-- DESCRIPTION
-- Creates a role with accessto Dynamic Performance Tables
-- for the SQL*Plus SETAUTOTRACE ... STATISTICS command.
-- After this script hasbeen run, each user requiring access to
-- the AUTOTRACE featureshould be granted the PLUSTRACE role by
-- the DBA.
--
-- USAGE
-- sqlplus"sys/knl_test7 as sysdba" @plustrce
--
-- Catalog.sql must havebeen run before this file is run.
-- This file must be runwhile 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
--从代码上看出,还需要三张表的权限
SQL> grant select on v_$sesstat to tempuser;
Grant succeeded.
SQL> grant select on v_$statname to tempuser;
Grant succeeded.
SQL> grant select on v_$mystat to tempuser;
Grant succeeded.
--这个时候就不报错了。
SQL>set autotrace on;
SQL>
SQL> select count(1) from test;
COUNT(1)
----------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 1 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used forthis statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
345 bytes sent via SQL*Net toclient
364 bytes received via SQL*Netfrom client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
--注意:红色部分是只有autotrace显示的。
3.结论
使用dbms_xplan.display_cursor(基于内部视图)
必要的表:V_$SESSION,V_$SQL_PLAN,V_$SQL
高级统计:V_$SQL_PLAN_STATISTICS_ALL
其他环境变量:statistics_level =all 或者 hints
使用autotrace(基于plan_table)
必要的表:plan_table
高级统计: v_$sesstat,v_$statname,v_$mystat