美女视频软件下载大全,韩国美女dj视频

啟用sqlplus的Autotrace來跟蹤sql執(zhí)行

學(xué)習(xí)愛 >《我的圖書館》

2010.10.14

關(guān)注

啟用sqlplus的Autotrace來跟蹤sql執(zhí)行

1.首先創(chuàng)建plan_table表，創(chuàng)建該表的語句為(假設(shè)數(shù)據(jù)庫安裝在D盤)

@D:\oracle\ora92\rdbms\admin\utlxplan.sql

2.創(chuàng)建plustrace角色,并將該角色賦予需要的用戶

SQL> conn /as sysdba

SQL> @D:\oracle\ora92\sqlplus\admin\plustrce.sql
drop role plustrace;

Role dropped
create role plustrace;

Role created
grant select on v_$sesstat to plustrace;

Grant succeeded
grant select on v_$statname to plustrace;

Grant succeeded
grant select on v_$session to plustrace;

Grant succeeded
grant plustrace to dba with admin option;

Grant succeeded
set echo off

SQL> grant plustrace to dfzc;

Grant succeeded

3.創(chuàng)建同義詞

SQL> create public synonym plan_table for plan_table;

Synonym created

SQL> grant all on plan_table to dfzc;

Grant succeeded

4.然后就可以在sqlplus中使用autotrace了

SQL>set autotrace on

即可。

SQL> select count(*) from dba_objects;

COUNT(*)

----------

31820

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 SORT (AGGREGATE)

2 1 VIEW OF 'DBA_OBJECTS'

3 2 UNION-ALL

4 3 FILTER

5 4 TABLE ACCESS (BY INDEX ROWID) OF 'OBJ$'

6 5 NESTED LOOPS

7 6 TABLE ACCESS (FULL) OF 'USER$'

8 6 INDEX (RANGE SCAN) OF 'I_OBJ2' (UNIQUE)

9 4 TABLE ACCESS (BY INDEX ROWID) OF 'IND$'

10 9 INDEX (UNIQUE SCAN) OF 'I_IND1' (UNIQUE)

11 3 NESTED LOOPS

12 11 TABLE ACCESS (FULL) OF 'USER$'

13 11 INDEX (RANGE SCAN) OF 'I_LINK1' (NON-UNIQUE)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

0 bytes sent via SQL*Net to client

0 bytes received via SQL*Net from client

0 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

5.關(guān)于Autotrace幾個(gè)常用選項(xiàng)的說明:

SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 報(bào)告，這是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只顯示優(yōu)化器執(zhí)行路徑報(bào)告
SET AUTOTRACE ON STATISTICS -- 只顯示執(zhí)行統(tǒng)計(jì)信息
SET AUTOTRACE ON ----------------- 包含執(zhí)行計(jì)劃和統(tǒng)計(jì)信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不顯示查詢輸出

補(bǔ)充：

1.ORA-01039: 視圖基本對(duì)象的權(quán)限不足的解決方法

ORA-01039: 視圖基本對(duì)象的權(quán)限不足
Current SQL statement for this session:
EXPLAIN PLAN SET STATEMENT_ID='PLUS561' FOR select table_name from user_tables

I think this is because the user doesn't have access to base tables for USER_TABLES view which belongs to SYS user.

DBA role will do it, "SELECT ANY TABLE" (in 8i & 9i) , and "SELECT ANY DICTIONARY"(in 9i & 10g) system privileges should also do it. Try one of the following 3 ways and run your autotrace again:-

1. 8i & 9i:-
grant select any table to USER123;

2. 9i and 10g:-
grant select any dictionary to USER123;

3. in 8i and 9i, you can also grant accees to the base tables explicitly ( or create a role to hold the grants ) :
grant select on OBJ$ to USER123;
grant select on USER$ to USER123;
grant select on SEG$ to USER123;
grant select on TS$ to USER123;
grant select on TAB$ to USER123;

2.在SQPPLUS中得到更新成功或者插入成功的記錄數(shù)

SQL> set feedback 1;

3.在SQPPLUS中得到語句總執(zhí)行的時(shí)間

SQL> set timing on;

4.使用sys進(jìn)行autotrace的話統(tǒng)計(jì)信息statistic都會(huì)為0

本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊舉報(bào)。

打開APP，閱讀全文并永久保存查看更多類似文章

sqlplus的autotrace開關(guān)

ORACLE EXPLAIN PLAN的總結(jié)

ORACLE AUTOTRACE介紹

Oracle autotrace使用說明

[精華] Oracle FAQ 整理

通過分析SQL語句的執(zhí)行計(jì)劃優(yōu)化SQL(三)第五章ORACLE的執(zhí)行計(jì)劃2

更多類似文章 >>

国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

啟用sqlplus的Autotrace來跟蹤sql執(zhí)行