오늘 두가지를 해 보았는데
토드에선 기본적으로
view-options-Oracle에 보면
toad_plan_table가 설정 되어있을 것이다.
테이블이 생성이 되어있지 않으면 plan을 보고자 할때 테이블이 없다는 메시지가 나올것이다.
그렇다면
toad_plan_table을 생성해주거나 oracle이 제공하는 plan테이블을 써야 한다.
먼저 토드에서 제공하는 스크립트를 보면
REM This script will create TOAD objects in their own
REM schema. If you DO NOT want to create a unique system
REM schema for TOAD objects, load the file NOTOAD.SQL
REM
REM Otherwise, start a new Oracle connection as SYSTEM ( or
REM any other user with privileges to create a new USER)
REM and, while connected as that user, execute the following
REM by clicking the third toolbar button in a SQL Edit OR
REM by selecting the menu option "SQL_Window/Execute as Script"
REM
REM Ver Date Description
REM === ========== =======================================
REM 1.1 10/06/1999 1. Added STORAGE clauses to the table
REM create commands so that not too much
REM disk space will be allocated.
REM 2. Removed obsolete TOAD_TEMP and
REM TOAD_DEP_TEMP.
REM 1.2 11/17/1999 1. Changed index on TOAD_PLAN_TABLE from
REM unique to non-unique.
REM 1.3 05/23/2001 1. Added partition-related columns and
REM DISTRIBUTION to TOAD_PLAN_TABLE
REM 1.4 01/18/2001 1. Added OBJECTNAME function
REM 1.5 03/29/2002 Added EXECUTE ANY PROCEDURE
REM 1.6 03/31/2002 Reworked entire script so user custimizations
REM can be made via DEFINE variables in one place
REM --------- Make all changes right here --------------------
REM --------- Do not change the name of the TOAD user --------
DEFINE UPW_TOAD=TOAD
DEFINE DEF_TSPACE=USER_DATA
DEFINE TMP_TSPACE=TEMPORARY
REM ------------------ Create the TOAD User ------------------
DROP USER TOAD CASCADE;
CREATE USER TOAD
IDENTIFIED BY &UPW_TOAD
DEFAULT TABLESPACE &DEF_TSPACE
TEMPORARY TABLESPACE &TMP_TSPACE
QUOTA UNLIMITED ON &DEF_TSPACE
QUOTA 0K ON SYSTEM;
GRANT CONNECT TO TOAD;
GRANT RESOURCE TO TOAD;
GRANT CREATE PUBLIC SYNONYM TO TOAD;
--The following grant is only necessary if you intend to install the TOAD
--Profiler objects into the TOAD schema
GRANT EXECUTE ANY PROCEDURE TO TOAD;
REM ----------------- Drop public synonyms --------------------
DROP PUBLIC SYNONYM TOAD_PLAN_SQL;
DROP PUBLIC SYNONYM TOAD_PLAN_TABLE;
DROP PUBLIC SYNONYM TOAD_SEQ;
REM ----------------- Connect as TOAD -------------------------
CONNECT TOAD/&UPW_TOAD;
REM ----------------- Create the Explain Plan objects
DROP TABLE TOAD.TOAD_PLAN_SQL;
DROP TABLE TOAD.TOAD_PLAN_TABLE;
DROP SEQUENCE TOAD.TOAD_SEQ;
CREATE TABLE TOAD.TOAD_PLAN_SQL (
USERNAME VARCHAR2(30),
STATEMENT_ID VARCHAR2(32),
TIMESTAMP DATE,
STATEMENT VARCHAR2(2000) )
STORAGE (INITIAL 40K NEXT 24K);
CREATE UNIQUE INDEX TOAD.TPSQL_IDX ON
TOAD.TOAD_PLAN_SQL ( STATEMENT_ID );
CREATE TABLE TOAD.TOAD_PLAN_TABLE (
STATEMENT_ID VARCHAR2(32),
TIMESTAMP DATE,
REMARKS VARCHAR2(80),
OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(30),
OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(30),
OBJECT_INSTANCE NUMBER,
OBJECT_TYPE VARCHAR2(30),
SEARCH_COLUMNS NUMBER,
ID NUMBER,
COST NUMBER,
PARENT_ID NUMBER,
POSITION NUMBER,
CARDINALITY NUMBER,
OPTIMIZER VARCHAR2(255),
BYTES NUMBER,
OTHER_TAG VARCHAR2(255),
PARTITION_ID NUMBER,
PARTITION_START VARCHAR2(255),
PARTITION_STOP VARCHAR2(255),
DISTRIBUTION VARCHAR2(30),
OTHER LONG)
STORAGE(INITIAL 80K NEXT 36K) ;
CREATE INDEX TOAD.TPTBL_IDX ON
TOAD.TOAD_PLAN_TABLE ( STATEMENT_ID );
CREATE SEQUENCE TOAD.TOAD_SEQ START WITH 1 CACHE 20;
CREATE PUBLIC SYNONYM TOAD_PLAN_SQL FOR TOAD.TOAD_PLAN_SQL;
CREATE PUBLIC SYNONYM TOAD_PLAN_TABLE FOR TOAD.TOAD_PLAN_TABLE;
CREATE PUBLIC SYNONYM TOAD_SEQ FOR TOAD.TOAD_SEQ;
GRANT SELECT, INSERT, UPDATE, DELETE ON TOAD.TOAD_PLAN_SQL TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON TOAD.TOAD_PLAN_TABLE TO PUBLIC;
GRANT SELECT, ALTER ON TOAD.TOAD_SEQ TO PUBLIC;
REM -------- Create the ObjectName function for use in Kill/Trace -----------
DROP FUNCTION TOAD.MYOBJECTNAME;
CREATE OR REPLACE function TOAD.ObjectName(in_object_id in number) return
varchar
is
return_string varchar2(100);
begin
select OWNER||'.'||OBJECT_NAME
into return_string
from all_objects
where object_id = in_object_id;
return return_string;
end ObjectName;
/
GRANT EXECUTE ON TOAD.OBJECTNAME TO PUBLIC;
이 스크립트를 실행하면 toad라는 유저를 생성하고 그 유저의 계정에
toad_plan_table table을 만들고나서 public synonym을 생성하게 된다.
이 스크립트를 실행하고 나서 이제 우리는 토드의 실행계획을 볼수 있게 된다.
물론 오라클에서 제공하는 여러가지 sql이 있으나 툴 사용이 조금더 간편하지 않을까~~
또 하나의 방법이란것은
Connect as SYSTEM (or other DBA account).
Run Oracle's RDBMS/ADMIN/UTLXPLAN.SQL.
GRANT ALL ON SYSTEM.PLAN_TABLE TO PUBLIC
CREATE PUBLIC SYNONYM PLAN_TABLE FOR SYSTEM.PLAN_TABLE
이렇게 하는 방법이 있다.
즉.. system유저로 접속해서 utlxplan.sql을 실행하고
plan테이블에 대한 권한을 public으로 하고 나서
시노님 생성
그리고 토드에서는 toad_plan_table이라는 설정을 plan_table이라는 설정으로 바꾸면 된다~~
=============================
UTLXPLAN.sql
CREATE TABLE PLAN_TABLE
(
STATEMENT_ID VARCHAR2(30 BYTE),
TIMESTAMP DATE,
REMARKS VARCHAR2(80 BYTE),
OPERATION VARCHAR2(30 BYTE),
OPTIONS VARCHAR2(30 BYTE),
OBJECT_NODE VARCHAR2(128 BYTE),
OBJECT_OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(30 BYTE),
OBJECT_INSTANCE INTEGER,
OBJECT_TYPE VARCHAR2(30 BYTE),
OPTIMIZER VARCHAR2(255 BYTE),
SEARCH_COLUMNS NUMBER,
ID INTEGER,
PARENT_ID INTEGER,
POSITION INTEGER,
COST INTEGER,
CARDINALITY INTEGER,
BYTES INTEGER,
OTHER_TAG VARCHAR2(255 BYTE),
PARTITION_START VARCHAR2(255 BYTE),
PARTITION_STOP VARCHAR2(255 BYTE),
PARTITION_ID INTEGER,
OTHER LONG,
DISTRIBUTION VARCHAR2(30 BYTE)
)
