RDS Oracle 雑多なメモ#16 - 再び:) / FAQ Tweet
再び忘れがちなので、備忘録。
RDS Oracleでマスターユーザー以外で、SQL*PLusの Auto trace そして、DBMS_XPLAN.DISPLAY や DBMS_XPLAN.DISPLAY_CURSOR を使おうとすると以下のようなエラーに遭遇！
なにも準備してないと。（explain plan for文だけは準備していなくても可能なのでが）
TEST> set autot trace exp stat
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
とか
...略...
TEST> select * from table(dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
User has no SELECT privilege on V$SESSION
なんてことに、
RDS Oracle、マスターユーザーでは可能なのですが、PLUSTRACEロールも作成されていない、かつ、 plustrce.sql がない.
AWSUSER> select role from dba_roles where role = 'PLUSTRACE';
no rows selected
ということで、いちいち調べるのも面倒なFAQとなっているので、備忘録として書いておきました。
マスターユーザー以外のユーザーに alter session システム権限を付与しておきます。
該当システム権限が付与されていないと、セッションレベルで statistics_level パラメータを変更できません。
このパラメータは、dbms_xplan.display_cursor でactual planを取得する際に必要になるのですが、最悪付与されていない場合には、SQL文に以下のヒントを追加することで代替可能ではありますが、いちいちSQL文に以下のヒントを追加しなければならないので面倒。
とはいえ、alter sessionは付与したくないということもなくはなく、そんな時は以下のヒントで頑張っください。
ex.
SELECT
/*+ gather_plan_statistics */
*
FROM
hoge
WHERE
id = 1;
次に必要なのは、 v$sessionなどを含むいくつかのパフォーマンスビューへのSELECTオブジェクト権限、グローバルな一時表として定義されているplan_tableへの全オブジェクト権限です。
管理面を考えてロールを作成し関連権限をロールに付与、作成したロールを対象ユーザーに付与するようにすると便利です。
alter sessionsシステム権限を含む最低限必要なオブジェクト権限とそれを付与するロール作成スクリプトの例は以下の通り。
foobar$ cat create_dev_role.sql
-- create developer role
create role dev_role;
-- for show parameter
grant alter session to dev_role;
-- for dbms_xplan.display_cursor and auto trace
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SESSION', 'DEV_ROLE', 'SELECT');
-- for dbms_xplan.display_cursor
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL_PLAN_STATISTICS_ALL', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$PARAMETER', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SQL_PLAN', 'DEV_ROLE', 'SELECT');
-- for auto trace
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$STATNAME', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$MYSTAT', 'DEV_ROLE', 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('V_$SESSTAT', 'DEV_ROLE', 'SELECT');
-- for auto trace (plan_table - temporary table)
exec rdsadmin.rdsadmin_util.grant_sys_object('PLAN_TABLE$', 'DEV_ROLE', 'ALL');
参考
Oracle DB インスタンスの一般的な DBA タスク
ということで、動作確認を兼ねたサンプルは以下のとおり。
まずは、RDS Oracleのマスターユーザーで.
マスターユーザー以外のユーザーの作成と権限とロールの付与（チューニングが必要とされる開発者等)を想定
foobar$ sqlplus awsuser/xxxxxxx@xxxx.xxxxxxxx.rds.amazonaws.com:1521/HOGE
SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 2 22:05:14 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Last Successful login time: Fri Nov 02 2018 22:01:24 +09:00
...中略...
AWSUSER> l
1 create user test identified by xxxxxx
2 default tablespace users
3 temporary tablespace temp
4* quota unlimited on users
AWSUSER> /
User created.
AWSUSER> grant connect, resource to test;
Grant succeeded.
AWSUSER> @create_dev_role
Role created.
Grant succeeded.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
AWSUSER> grant dev_role to test;
Grant succeeded.
AWSUSER> exit
作成したユーザーで接続して、各方法で実行計画を取得できるか確認！
クエリーを実行するため適当な表を作成してデータを登録しておく。
foobar$ sqlplus test/xxxxxxx@xxxx.xxxxxxxx.rds.amazonaws.com:1521/HOGE
SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 2 22:15:23 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
...中略...
TEST>
TEST> l
1 create table hoge (
2 id number not null primary key
3 ,foobar varchar2(20)
4* ) nologging
TEST> /
Table created.
TEST>
TEST> l
1 begin
2 for i in 1..10000 loop
3 insert into
4 hoge (
5 id
6 ,foobar
7 )
8 values (
9 i
10 ,to_char(i)
11 );
12 end loop;
13 commit;
14* end;
TEST> /
PL/SQL procedure successfully completed.
SQL*Plusのauto traceが行えるか確認！
TEST> set autot trace exp stat
TEST> select * from hoge where id = 1;
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 2757398040
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005687 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
342 bytes sent via SQL*Net to client
488 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
参考
文のトレースについて
explain plan for文は準備なしで問題ないのですが、念のための確認！ DBMS_XPLAN.DISPLAYプロシージャを利用して実行計画を取得
TEST>
TEST> explain plan for
2 select * from hoge where id = 1;
explained.
TEST> @show_explain
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 2757398040
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005687 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
14 rows selected.
Elapsed: 00:00:00.05
スクリプト例は以下の通り。$ORACLE_HOME/rdbms/admin/utlxpls.sql や、utlxplp.sqlが利用できれば楽なんでが、それら中身は、DBMS_XPLAN.DISPLAYなので大差ない内容。
$ cat show_explain.sql
set linesize 200
set long 100000
set longchunk 200
set tab off
SELECT
*
FROM
TABLE(
DBMS_XPLAN.DISPLAY(
format => 'ALL -PROJECTION -ALIAS'
)
)
;
Actual planをDBMS_XPLAN.DISPLAY_CURSORプロシージャで取得〜
TEST>
TEST> @show_realplan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
Session altered.
*** SQL that you want to get an actual plan ***
1* select * from hoge where id = 1
***********************************************
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID 6f67zkz43kr76, child number 1
-------------------------------------
select * from hoge where id = 1
Plan hash value: 2757398040
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX UNIQUE SCAN | SYS_C005687 | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
19 rows selected.
Elapsed: 00:00:00.11
Session altered.
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
Actual planを取得するスクリプト例
SQL*Plusのコマンドを駆使してはいますが、ポイントはset statistics_level = allにすることと、DBMS_XPLAN.DISPLAY_CURSORをコールする際のformatパラメータです
set termout off/onでSQL文の結果を表示しないようにしています。この設定はSQLファイルからSQL文を実行した場合にだけ有効です。（ちょっとしたTips :)
$ cat show_realplan.sql
show parameter statistics_level
alter session set statistics_level = all;
set linesize 200
set long 100000
set longchunk 200
set tab off
PROMPT *** SQL that you want to get an actual plan ***
select * from hoge where id = 1
.
l
PROMPT ***********************************************
set termout off
r
set termout on
-- get the actual plan
set timi on
SELECT
*
FROM
TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
format => 'ALLSTATS LAST'
)
)
;
set timi off
alter session set statistics_level = typical;
show parameter statistics_level
Apple Store Ginzaで、息子が ”iPadでムービーを作ろう” に参加するので、その合間に、パタパタブログを書き、その足で英会話に向かう日曜日の午後w
Previously on Mac De Oracle
・RDS Oracle 雑多なメモ#1 / FAQ
・RDS Oracle 雑多なメモ#2 / FAQ
・RDS Oracle 雑多なメモ#3 / FAQ
・RDS Oracle 雑多なメモ#4 / FAQ
・RDS Oracle 雑多なメモ#5 / FAQ
・RDS Oracle 雑多なメモ#6 / FAQ
・RDS Oracle 雑多なメモ#7 / FAQ
・RDS Oracle 雑多なメモ#8 / FAQ
・RDS Oracle 雑多なメモ#9 / FAQ
・RDS Oracle 雑多なメモ#10 / FAQ
・RDS Oracle 雑多なメモ#11 / FAQ
・RDS Oracle 雑多なメモ#12 / FAQ
・RDS Oracle 雑多なメモ#13 / FAQ
・RDS Oracle 雑多なメモ#14 - おまけ / FAQ
・RDS Oracle 雑多なメモ#15 - おまけのおまけ / FAQ
| 固定リンク | 0
コメント