摘要:《Set Autotrace》
因為公司的關係，最近在開發資料庫上提供同事們產出SQL執行計劃及相關統計資料的功能，
期望讓同事們更深入了解自己的SQL在資料庫上的反應。
希望藉此提升同事們的SQL語法及對資料庫的認識。
以下是在oracle 10g的環境下測試。據google大師的回覆，在Oracle 10g已預設有plan_table，
只是Table 名稱改成Plan_Table$，所以只要執行此測試的後半段，就是建立plustrace這個role。
因為公司是Oracle 9i，所以還是按表實作了一下。
------------------------------------------------------------------------------------------------
C:\oracle\product\10.2.0\db_A\RDBMS\ADMIN>dir utlx*.sql
磁碟區 C 中的磁碟沒有標籤。
磁碟區序號: 741A-1F28
C:\oracle\product\10.2.0\db_A\RDBMS\ADMIN 的目錄
2004/07/27 上午 04:33 1,925 utlxaa.sql
2001/02/22 下午 04:42 5,335 utlxmv.sql
2004/06/06 上午 12:05 3,341 utlxplan.sql
2002/01/24 下午 11:13 1,571 utlxplp.sql
2002/02/27 下午 06:48 1,695 utlxpls.sql
2005/04/29 上午 09:16 2,420 utlxrw.sql
6 個檔案 16,287 位元組
0 個目錄 19,494,526,976 位元組可用
C:\oracle\product\10.2.0\db_A\RDBMS\ADMIN>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期四 7月 23 22:34:41 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn sys as sysdba
輸入密碼:
已連線.
------------------------------------------------------------------------------------------------
-- Oracle 9i 要建立，Oracle 10g 已預設Plan Table是 Plan_Table$
-- 就不需要執行utlxplan.sql
------------------------------------------------------------------------------------------------
SQL> @utlxplan.sql
已建立表格.
SQL> @c:\oracle\product\10.2.0\db_a\sqlplus\admin\plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR 在行 1:
ORA-01919: 角色 'PLUSTRACE' 不存在
SQL> create role plustrace;
已建立角色.
SQL>
SQL> grant select on v_$sesstat to plustrace;
順利授權.
SQL> grant select on v_$statname to plustrace;
順利授權.
SQL> grant select on v_$mystat to plustrace;
順利授權.
SQL> grant plustrace to dba with admin option;
順利授權.
SQL>
SQL> set echo off
SQL> conn ting
輸入密碼:
已連線.
SQL> set autotrace on
SP2-0618: 找不到階段作業 ID. 請檢查是否啟用 PLUSTRACE 角色
SP2-0611: 啟動 STATISTICS 報表時發生錯誤
SQL> conn sys as sysdba
輸入密碼:
已連線.
------------------------------------------------------------------------------------------------
-- 為了開放給所有使用者使用，所以直接授權給public
-- plustrace、plan_table
-- 一定要記得建立plan_table的同義字
-- 如果是10g 記得plan_table要改成plan_table$
------------------------------------------------------------------------------------------------
SQL> grant plustrace to public;
順利授權.
SQL> grant all on plan_table to public;
順利授權.
SQL> create public synonym plan_table for sys.plan_table;
已建立同義字.
SQL> conn ting
輸入密碼:
已連線.
SQL> set autotrace on
SQL> select count(*) from user_tables;
COUNT(*)
----------
0
執行計畫
----------------------------------------------------------
ERROR:
ORA-01039: 權限不足, 無法存取構成視觀表的基底物件
SP2-0612: 產生 AUTOTRACE EXPLAIN 報表時發生錯誤
統計值
----------------------------------------------------------
27 recursive calls
0 db block gets
853 consistent gets
634 physical reads
0 redo size
407 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
------------------------------------------------------------------------------------------------
-- 一開始以為是少了什麼授權，為什麼會出現權限不足
-- 原來是平常太習慣查些資料庫相關的表，user_tables沒有權限讀取
-- 但是還是可以取到統計值唷。
------------------------------------------------------------------------------------------------
SQL> select *from t;
F1
----------
1
2
3
4
5
6
7
8
9
已選取 9 個資料列.
執行計畫
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 63 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 9 | 63 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
統計值
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
495 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed