利用不同的方法查看執(zhí)行計(jì)劃有對應(yīng)不同的授權(quán)方法。

一、?explain plan與dbms_xplan.display

最常用的方法，plsqldev的F5對應(yīng)也是這個(gè)。這個(gè)命令會(huì)產(chǎn)生執(zhí)行計(jì)劃，并把執(zhí)行計(jì)劃保存到"PLAN_TABLE"中，查看執(zhí)行計(jì)劃使用 select * from table(dbms_xplan.display);

啟用方法（9i需手動(dòng)啟用）：

--創(chuàng)建PLAN_TABLE，存儲(chǔ)執(zhí)行計(jì)劃 SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql--創(chuàng)建同義詞使多個(gè)用戶可以共享一個(gè)plan_table，并授權(quán)給public create public synonym plan_table for plan_table; grant all on plan_table to public;

Prerequisites

To issue an?EXPLAIN?PLAN?statement, you must have the privileges necessary to insert rows into an existing output table that you specify to hold the execution plan.

You must also have the privileges necessary to execute the SQL statement for which you are determining the execution plan. If the SQL statement accesses a view, then you must have privileges to access any tables and views on which the view is based. If the view is based on another view that is based on a table, then you must have privileges to access both the other view and its underlying table.

To examine the execution plan produced by an?EXPLAIN?PLAN?statement, you must have the privileges necessary to query the output table.

The?EXPLAIN?PLAN?statement is a data manipulation language (DML) statement, rather than a data definition language (DDL) statement. Therefore, Oracle Database does not implicitly commit the changes made by an?EXPLAIN?PLAN?statement. If you want to keep the rows generated by an?EXPLAIN?PLAN?statement in the output table, then you must commit the transaction containing the statement.

參考?EXPLAIN PLAN

二、 autotrace

實(shí)際上它需要兩個(gè)東西：PLAN_TABLE 和?plustrace角色，另外plsqldev是不支持用autotrace的。

執(zhí)行以下腳本創(chuàng)建plustrace角色，并執(zhí)行g(shù)rant plustrace to public授權(quán)，這樣所有用戶都可以使用autotrace跟蹤工具。

@?/sqlplus/admin/plustrce.sql grant plustrace to public;

腳本內(nèi)容如下

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

三、?dbms_xplan.display_cursor

基本信息：select * from table(dbms_xplan.display_cursor);

grant select on v_$session to tempuser; grant select on v_$sql_plan to tempuser; grant select on v_$sql to tempuser;

高級統(tǒng)計(jì)：select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED? ALLSTATS LAST PEEKED_BINDS'));

grant select on v_$sql_plan_statistics_all to tempuser; --這里statistics_level =all讓統(tǒng)計(jì)信息最為詳盡 alter session set statistics_level =all;

查看真實(shí)執(zhí)行計(jì)劃

set linesize 200 pagesize 999 alter session set statistics_level=all; -- 執(zhí)行sql select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION iostats,last'));

參考

oracle 9i啟用Execution Plan的方法 | 學(xué)步園

utlxplan_ITPUB博客

https://www.iteye.com/blog/falchion-616234

授權(quán)普通非DBA用戶可以有權(quán)限查看執(zhí)行計(jì)劃的方法_weixin_33923762的博客-CSDN博客

授予普通用戶查看執(zhí)行計(jì)劃權(quán)限_ITPUB博客

總結(jié)

以上是生活随笔為你收集整理的Oracle授权普通用户查看执行计划的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。