sqlplus plan table 생성및 plustrace 권한 부여
2011.03.20 21:45
초기 설치 후 scott /tiger 계정등에서 plan 확인을 위해 수행
[oracle@localhost ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 20 21:31:26 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
plan table 생성
SQL> conn scott/tiger
Connected.
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
Table created.
PLUSTRACE 룰 생성
SQL> conn sys/manager as sysdba
Connected.
SQL>
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL>
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL>
SQL> set echo off
SQL> grant plustrace to scott;
Grant succeeded.
PLAN 보기 확인
SQL> conn scott/tiger
Connected.
SQL> set linesize 120
SQL> set autot on
SQL> select * from emp where rownum=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
Execution Plan
----------------------------------------------------------
Plan hash value: 1973284518
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=1)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
243 recursive calls
0 db block gets
56 consistent gets
6 physical reads
0 redo size
826 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
7 | 오라클 18c 계정생성 오류 ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다. | 남송휘 | 2019.07.26 | 19909 |
6 | 오라클 DBMS 윈도우 버전(2014년 03월 현재) | balto | 2014.02.28 | 26455 |
5 | 버전별 Oracle Online Documentation Library [2] | 실천하자 | 2011.04.26 | 170443 |
» | sqlplus plan table 생성및 plustrace 권한 부여 [1] | 휘휘 | 2011.03.20 | 160261 |
3 | Oracle 10gr2 / 11gr2 설치 on cent os 5.5 (vmware 사용) [2] | 휘휘 | 2011.03.20 | 123564 |
2 | ORA-28000: the account is locked | 휘휘 | 2010.06.06 | 144818 |
1 | ORACLE 설치후 SCOTT/TIGER 실습계정이 없을경우 [1] | 남송휘 | 2010.06.06 | 154257 |
실시간으로 글이 막 올라오는군요 ㅎ 좋은 정보 잘 쓰겠습니다~