Read Slide 1 text version

·

· · · · · · · · / · · · · · · ·

·

· · · · · B*Tree · · · · · · · OEM:

EE

Diagnostics Pack Tuning Pack

Oracle Database Enterprise Edition Diagnostics Pack Tuning Pack Oracle Database 10g

Copyright© 2007, Oracle. All rights reserved.

2

·

· · · · · · · · / · · · · · · ·

·

· · · · · B*Tree · · · · · · · OEM:

Copyright© 2007, Oracle. All rights reserved.

3

()

XML Type

Copyright© 2007, Oracle. All rights reserved.

4

()

create table

() CREATE TABLE tab01 ( col1 NUMBER(8, 2), col2 VARCHAR2(15), col3 DATE);

alter table

() ALTER TABLE tab01 ADD ( col4 NUMBER);

truncate table

() TRUNCATE TABLE tab01;

drop table

() DROP TABLE tab01;

Copyright© 2007, Oracle. All rights reserved.

5

·

· · · · · · · · / · · · · · · ·

·

· · · · · B*Tree · · · · · · · OEM:

Copyright© 2007, Oracle. All rights reserved.

6

· Oracle

· ( ) · · ·

ROWID ROWIDROWID2 ROWID LONGLOB

ROWIDROWID2 LOBLONG

Oracle8i

Copyright© 2007, Oracle. All rights reserved.

7

B*Tree B*Tree B B B*Tree

Copyright© 2007, Oracle. All rights reserved.

8

CREATE TABLE iot_sample( token char(20), doc_id NUMBER, token_frequency NUMBER, token_offsets VARCHAR2(512), CONSTRAINT pk_admin_docindex PRIMARY KEY (token, doc_id)) ORGANIZATION INDEX TABLESPACE users PCTTHRESHOLD 20 OVERFLOW TABLESPACE users2;

Copyright© 2007, Oracle. All rights reserved.

9

Oracle9i

SQL

CREATE TABLE ... ORGANIZATION EXTERNAL OS OS DMLUPDATEINSERTDELETE SELECT

Copyright© 2007, Oracle. All rights reserved.

10

Oracle9i 1. Oracle 2. DWHDB

Copyright© 2007, Oracle. All rights reserved.

11

(1)

·

Empxt1.dat 360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus 361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper 362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr 363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aalda Empxt2.dat 401,Jesse,Cromwell,HR_REP,203,17-MAY-2001,7000,0,40,jcromwel 402,Abby,Applegate,IT_PROG,103,17-MAY-2001,9000,.2,60,aapplega 403,Carol,Cousins,AD_VP,100,17-MAY-2001,27000,.3,90,ccousins 404,John,Richardson,AC_ACCOUNT,205,17-MAY-2001,5000,0,110,jrichard

·

CONNECT / AS SYSDBA; -- Set up directories and grant access to hr CREATE OR REPLACE DIRECTORY admin_dat_dir AS 'C:¥flatfiles¥data'; CREATE OR REPLACE DIRECTORY admin_log_dir AS 'C:¥flatfiles¥'; CREATE OR REPLACE DIRECTORY admin_bad_dir AS 'C:¥flatfiles¥'; GRANT READ ON DIRECTORY admin_dat_dir TO scott; GRANT WRITE ON DIRECTORY admin_log_dir TO scott; GRANT WRITE ON DIRECTORY admin_bad_dir TO scott;

Copyright© 2007, Oracle. All rights reserved.

12

(2)

·

CREATE TABLE admin_ext_employees (employee_id NUMBER(4), first_name VARCHAR2(20), last_name VARCHAR2(25), job_id VARCHAR2(10), manager_id NUMBER(4), hire_date DATE, salary NUMBER(8,2), commission_pct NUMBER(2,2), department_id NUMBER(4), email VARCHAR2(25) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY admin_dat_dir ACCESS PARAMETERS ( records delimited by newline badfile admin_bad_dir:'empxt%a_%p.bad' logfile admin_log_dir:'empxt%a_%p.log' fields terminated by ',' missing field values are null ( employee_id, first_name, last_name, job_id, manager_id, hire_date char date_format date mask "dd-mon-yyyy", salary, commission_pct, department_id, email ) ) LOCATION ('empxt1.dat', 'empxt2.dat') ) PARALLEL REJECT LIMIT UNLIMITED;

Copyright© 2007, Oracle. All rights reserved.

13

·

·

·

CREATE INDEX · · · · · ·

Copyright© 2007, Oracle. All rights reserved.

14

SQL> connect scott/****** SQL> CREATE GLOBAL TEMPORARY TABLE "SCOTT"."TEMPTESTTAB" 2 ( "COL1" NUMBER(10, 2), "COL2" VARCHAR2(30), "COL3" DATE) 3 ON COMMIT PRESERVE ROWS; SQL> insert into temptesttab values(111.11,'scott_test',sysdate); 1 SQL> select * from temptesttab; COL1 COL2 111.11 scott_test SQL> connect system/***** SQL> select * from scott.temptesttab; COL3 07-04-17 ---------- ------------------------------ -------SQL> connect scott/***** SQL> select * from temptesttab; SQL> select * from scott.temptesttab; COL1 COL2 222.22 system_test COL3 07-04-17 ---------- ------------------------------ -------SQL> insert into scott.temptesttab values(222.22,'system_test',sysdate) 1

Copyright© 2007, Oracle. All rights reserved.

15

· Oracle 8i

· DML

· (Oracle 9i) EE

· (DML)

Copyright© 2007, Oracle. All rights reserved.

16

- -

EE !!

// /// / /STORAGE

- - - -

PL/SQLDBMS_REDEFINITION PL/SQLDBMS_REDEFINITION

Copyright© 2007, Oracle. All rights reserved.

17

(1)

EE

(emp)

(int_emp)

(1)

(2)

Copyright© 2007, Oracle. All rights reserved.

18

EE

(int_emp) (emp)

(int_emp) (emp)

Copyright© 2007, Oracle. All rights reserved.

19

SCOTTemp

empno 100 200 name Steven Jennifer Salary 24,000 43,000 Phone_number 515-123-4567 515-123-4444

EE

Default

deptno 10 10

1.1

empno 100 200 name Steven Jennifer Sal 26,400 47,300

Phone_number 515-123-4567 515-123-4444

Copyright© 2007, Oracle. All rights reserved.

20

EE

SCOTTemp

empno 100 200 name Steven Jennifer Sal 26,400 47,300 deptno 10 10

SCOTTint_emp

empno 100 200 name Steven Jennifer Salary 24,000 43,000 Phone_number 515-123-4567 515-123-4444

Copyright© 2007, Oracle. All rights reserved.

21

·

!!

EE

FLASHBACK!!

1

Copyright© 2007, Oracle. All rights reserved.

UNDO

22

·

· · · UNDO

·

EE

·

· ·

· TRUNCATEDDL

*

Copyright© 2007, Oracle. All rights reserved.

23

EE · UNDO (AUM) UNDO_MANAGEMENT=AUTO UNDO_TABLESPACE=UNDO01 UNDO_RETENTION=3600 · FLASHBACK TABLE TEST1, TEST2 TO TIMESTAMP (SYSTIMESTAMP ­ INTERVAL `10' MINUTE); · TEST1TEST210

Copyright© 2007, Oracle. All rights reserved.

24

· DROP TABLE

Recyclebin

DROP

FLASHBACK

Copyright© 2007, Oracle. All rights reserved.

25

·

· DROP TABLE · · · Recyclebin · ·

Copyright© 2007, Oracle. All rights reserved.

26

·

· · DROPPURGERecyclebin DROP

·

· Oracle9i · DBA_FREE_EXTENTS

Copyright© 2007, Oracle. All rights reserved.

27

Recyclebin DROP TABLE FLASHBACK TABLE TO BEFORE DROP SHOW RECYCLEBIN DROP TABLE PURGE PURGE RECYCLEBIN

Copyright© 2007, Oracle. All rights reserved.

28

·

· · · · · · · · / · · · · · · ·

·

· · · · · B*Tree · · · · · · · OEM:

Copyright© 2007, Oracle. All rights reserved.

29

· Oracle8i

· ·

1 0 0 0 1 0 0 1 ...

:

Copyright© 2007, Oracle. All rights reserved.

30

·

· · · · 10g ALLOCATE/Dictionary Dictionary COALESCE

· 9i R2 ·

· · DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL

Copyright© 2007, Oracle. All rights reserved.

31

·

· AUTOALLOCATE() 64KB, 1MB, 8MB, 64MB · UNIFORM 1MB

·

AUTOALLOCATEUNIFORM

· UNIFORM · AUTOALLOCATE · · AUTOALLOCATE

Copyright© 2007, Oracle. All rights reserved.

32

CREATE TABLESPACE OracleDirect DATAFILE D:¥ORACLE¥ORADATA¥ORA10GEE¥oracledirect1.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Copyright© 2007, Oracle. All rights reserved.

33

(ASSM)

· 9i

· PCTUSEDFREELISTS FREELIST GROUPS ·

·

· CREATE TABLESPACE SEGMENTSPACE MANAGEMENT AUTO

·

· · 9.0.1.3.0 COMPATIBLE 9.0.1.3

Copyright© 2007, Oracle. All rights reserved.

34

· · · · · HWM · · · · · · ·

Copyright© 2007, Oracle. All rights reserved.

35

OLTP

OLTP

HWM

DSS

DSS

­ ­ ­ ­ ­ LONG (CASCADE ) ON COMMIT (ROWID )

Copyright© 2007, Oracle. All rights reserved.

36

OEM Diagnostics Tuning EE Pack Pack

·

· · ·

Copyright© 2007, Oracle. All rights reserved.

37

EE

Diagnostics Tuning Pack Pack

1157KB 5120KB 77.4%

Copyright© 2007, Oracle. All rights reserved.

38

EE

Diagnostics Tuning Pack Pack

Copyright© 2007, Oracle. All rights reserved.

39

EE

Diagnostics Tuning Pack Pack

Copyright© 2007, Oracle. All rights reserved.

40

EE

Diagnostics Tuning Pack Pack

!! 617.84KB 640KB 3.46%

Copyright© 2007, Oracle. All rights reserved.

41

33 28 24 30

EE

Oracle9i

33 28 24 30

42

29

29

Copyright© 2007, Oracle. All rights reserved.

·

EE

Oracle

· · ·

·

1 DWH()

NOCOMPRESS

<rowid> <rowid> <rowid> <rowid> <rowid> <rowid> <rowid> ... `650-506-7000'`650-123-4567' `650-506-7000'`650-506-7001' `650-506-7000'`650-456-7890' `650-506-7000'`650-098-7654' `650-506-7000'`650-123-4567' `650-506-7001'`650-123-4567' `650-506-7001'`650-123-4567'

COMPRESS

<symbol table: <A>= `650-506-7000', <B>=`650-506-7001', <C>=`650-123-4567'> <rowid> <A> <C> <rowid> <A> <B> <rowid> <A> `650-456-7890' <rowid> <A> `650-098-7654' <rowid> <A> <C> <rowid> <B> <C> <rowid> <B> <C> ...

Copyright© 2007, Oracle. All rights reserved.

43

30000 25000 20000 15000 10000 5000 0

EE

42 12

29219 2494

45 40 35 30 25 20 15 10 5 0

10%

30%

Copyright© 2007, Oracle. All rights reserved.

44

CREATE TABLE compress_tbl (col number,) COMPRESS;

EE

CREATE TABLE tablename_name (col...) ... PARTITION prt1 VALUES LESS THAN (....)TABLESPACE tbs1 COMPRESS

CREATE TABLESPACE compress_tbs DATAFILE 'file_name.dbf' SIZE 100M DEFAULT COMPRESS;

Copyright© 2007, Oracle. All rights reserved.

45

·

· · · · · · · · / · · · · · · ·

·

· · · · · B*Tree · · · · · · · OEM:

Copyright© 2007, Oracle. All rights reserved.

46

B*Tree EE EE

Copyright© 2007, Oracle. All rights reserved.

47

()

create index

() CREATE INDEX empno_idx ON emp (empno);

alter index

() ALTER INDEX empno_idx REBUILD;

drop index

() DROP INDEX empno_idx;

Copyright© 2007, Oracle. All rights reserved.

48

·

· · · · · · · · / · · · · · · ·

·

· · · · · B*Tree · · · · · · · OEM:

Copyright© 2007, Oracle. All rights reserved.

49

B*Tree

· B*Tree

· · · ·

Copyright© 2007, Oracle. All rights reserved.

50

·

EE

· · · OLTP · · ANDORNOT

Copyright© 2007, Oracle. All rights reserved.

51

(Customer)

SQL> select * from customer; CUSTOMER_ID MARITAL_ST REGION GENDER INCOME ----------- ---------- ---------- -------- --------------- --------------- --------------101 single east male bracket_1 102 married central female bracket_4 103 married west female bracket_2 104 divorced west male bracket_4 105 single central female bracket_2 106 married central female bracket_3

EE

BIT MAP

(1)

CREATE BITMAP INDEX region_idx ON customer(region);

1 east 1 central 0 west 0

ROWID

2 0 1 0

3 0 0 1

4 0 0 1

5 0 1 0

6 0 1 0

REGION

BIT MAP

(2)

MARITAL_ STATUS

ROWID single married divorced

CREATE BITMAP INDEX marital_idx ON customer(marital_status);

1 1 0 0

2 0 1 0

3 0 1 0

4 0 0 1

5 1 0 0

6 0 1 0

Copyright© 2007, Oracle. All rights reserved.

52

SELECT COUNT(*) FROM customer WHERE marital_status = 'married' AND REGION IN ('central','west');

BIT MAP

EE

1 east 1 central 0 west 0

ROWID

2 0 1 0

3 0 0 1

4 0 0 1

5 0 1 0

6 0 1 0

REGION

or

0 1 1 1 1 1 and

BIT MAP ROWID

MARITAL_ STATUS

single married divorced

1 1 0 0

2 0 1 0

3 0 1 0 ||

4 0 0 1

5 1 0 0

6 0 1 0

0 1 1 0 0 1

Copyright© 2007, Oracle. All rights reserved.

COUNT(*) ---------3

53

EE

Oracle9i

· ·

·

Copyright© 2007, Oracle. All rights reserved.

54

EE

()

()

Copyright© 2007, Oracle. All rights reserved.

55

(Sales)

EE

ROWID 1 2 3 4 5 6 7 8 9 10 S_ID 201 202 203 204 205 206 207 208 209 210 S_DATE 2001/2/1 2001/2/1 2001/2/1 2001/2/1 2001/2/1 2001/2/2 2001/2/2 2001/2/2 2001/2/2 2001/2/2 SALES 200 100 500 340 245 360 410 550 230 190 CUST_ID 1 3 2 5 4 1 2 5 5 1

(Customer)

CUST_ID 1 2 3 4 5

C_NAME REGION aaa bbb ccc ddd eee

SELECT

c.region, s.s_id, s.date, s.sales FROM sales s, customer c

WHERE

c.cust_id = s.cust_id and c.region = '' ;

Copyright© 2007, Oracle. All rights reserved.

56

SALES

EE

S_DATE 2001/2/1 2001/2/1 2001/2/1 2001/2/1 2001/2/1 2001/2/2 2001/2/2 2001/2/2 2001/2/2 2001/2/2 SALES 200 100 500 340 245 360 410 550 230 190 CUST_ID 1 3 2 5 4 1 2 5 5 1

ROWID 1 2 3 4 5 6 7 8 9 10

S_ID 201 202 203 204 205 206 207 208 209 210

Customer

CUST_ID 1 2 3 4 5

C_NAME REGION aaa bbb ccc ddd eee

ROWID

1 0 1 0

2 0 0 1

3 0 0 1

BIT 4 5 1 0 0 1 0 0

MAP 6 7 0 0 1 0 0 1

8 1 0 0

9 10 1 0 0 1 0 0

Key

CREATE BITMAP INDEX cust_sales_bji ON sales(c.region) FROM sales s, customer c WHERE

c.cust_id = s.cust_id

57

Copyright© 2007, Oracle. All rights reserved.

ROWID 1 0 1 0 2 0 0 1 3 0 0 1 BIT 4 5 1 0 0 1 0 0 MAP 6 7 0 0 1 0 0 1 SELECT 8 1 0 0 9 10 1 0 0 1 0 0

EE

Customer() c.region, s.s_id, s.date, s.sales FROM sales s, customer c

WHERE

Key

c.cust_id = s.cust_id and c.region = '' ;

SALES

ROWID 1 2 3 4 5 6 7 8 9 10

S_ID 201 202 203 204 205 206 207 208 209 210

S_DATE 2001/2/1 2001/2/1 2001/2/1 2001/2/1 2001/2/1 2001/2/2 2001/2/2 2001/2/2 2001/2/2 2001/2/2

SALES 200 100 500 340 245 360 410 550 230 190

CUST_ID 1 3 2 5 4 1 2 5 5 1

58

Copyright© 2007, Oracle. All rights reserved.

· · · WHERE Oracle8i

CREATE INDEX uppercase_idx ON employees (UPPER(first_name));

SELECT * FROM employees WHERE UPPER(first_name) = 'RICHARD' ;

Copyright© 2007, Oracle. All rights reserved.

59

· DDLONLINE CREATE INDEX ... ONLINE; CREATE INDEX ... ONLINE; ALTER INDEX ... REBUILD ONLINE; ALTER INDEX ... REBUILD ONLINE; ALTER TABLE ... MOVE ONLINE ... [OVERFLOW]; ALTER TABLE ... MOVE ONLINE ... [OVERFLOW]; · Oracle8i B* · Oracle9i

EE

!!

· ·

Copyright© 2007, Oracle. All rights reserved.

60

()

EE

Oracle

Copyright© 2007, Oracle. All rights reserved.

61

EE

Oracle

Copyright© 2007, Oracle. All rights reserved.

62

EE

Copyright© 2007, Oracle. All rights reserved.

63

·

· · · · · · · · / · · · · · · ·

·

· · · · · B*Tree · · · · · · · OEM:

Copyright© 2007, Oracle. All rights reserved.

64

truncate Truncate (delete) alter index <> rebuild drop index & create index

Copyright© 2007, Oracle. All rights reserved.

65

OEM SQL

EE

Diagnostics Tuning Pack Pack

· SQL ·

AWR

Enterprise Manager

66

Copyright© 2007, Oracle. All rights reserved.

SQL

EE

Diagnostics Tuning Pack Pack

Copyright© 2007, Oracle. All rights reserved.

67

SQL

EE

Diagnostics Tuning Pack Pack

Copyright© 2007, Oracle. All rights reserved.

68

<Insert Picture Here>

Copyright© 2007, Oracle. All rights reserved.

69

scott.employee enameB*Tree

scott.employee Ename enameB*Tree

Empno Ename Salary Phone comments

Primary key

Empno Ename Sal Phone

Primary key

Salary1.1 ( (

LONG

Deptno comments

CLOB

Copyright© 2007, Oracle. All rights reserved.

70

·

create table employee ( empno number(4) primary key, ename varchar2(10), salary number(7,2), phone varchar2(10), comments long ) tablespace users storage (initial 10K next 20K pctincrease 50) / create index emp_ename on employee (ename);

·

exec DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','employee');

Copyright© 2007, Oracle. All rights reserved.

71

·

create table int_employee ( empno number(4) primary key, ename varchar2(100), sal number(7,2), deptno number(2) DEFAULT 10, comments CLOB ) tablespace users PARTITION BY RANGE(empno) (PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE tbs_1, PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE tbs_2) storage (initial 20K next 20K pctincrease 0) /

Copyright© 2007, Oracle. All rights reserved.

72

·

begin DBMS_REDEFINITION.START_REDEF_TABLE( 'scott','employee','int_employee', `empno empno, ename ename,salary*1.10 sal,10 deptno,to_lob(comments) comments', dbms_redefinition.cons_use_pk, 'ename'); end; /

Copyright© 2007, Oracle. All rights reserved.

73

·

declare errornum number; begin DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( 'scott','employee','int_employee', 1,true,true,true,true,errornum); dbms_output.put_line(''); dbms_output.PUT_LINE('ERROR? ==>'||errornum||' !!'); end; /

Copyright© 2007, Oracle. All rights reserved.

74

·

exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('scott','employee','int_employee');

Copyright© 2007, Oracle. All rights reserved.

75

SQL> desc employee NULL? ------------ -------EMPNO NOT NULL ENAME SALARY PHONE COMMENTS -------------NUMBER(4) VARCHAR2(10) NUMBER(7,2) VARCHAR2(10) LONG

PHONE ---------123456790 223456790 323456790 423456790 523456790 623456790 723456790 823456790 923456790 023456790 A23456790 B23456790 C23456790 D23456790 COMMENTS -------A B C D E F G H I J K L M N

SQL> desc employee NULL? ------------ -------EMPNO NOT NULL ENAME SAL DEPTNO COMMENTS

-------------NUMBER(4) VARCHAR2(100) NUMBER(7,2) NUMBER(2) CLOB

SQL> select * from employee; EMPNO ENAME SALARY ---------- ---------- ---------7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300

SQL> select * from employee; EMPNO ENAME SAL DEPTNO COMMENTS ---------- ---------- ---------- ---------- -------7499 ALLEN 1760 10 B 7369 SMITH 880 10 A 7876 ADAMS 1210 10 K 7698 BLAKE 3135 10 F 7782 CLARK 2695 10 G 7902 FORD 3300 10 M 7900 JAMES 1045 10 L 7566 JONES 3272.5 10 D 7839 KING 5500 10 I 7654 MARTIN 1375 10 E 7934 MILLER 1430 10 N 7788 SCOTT 3300 10 H 7844 TURNER 1650 10 J 7521 WARD 1375 10 C

Copyright© 2007, Oracle. All rights reserved.

76

· ·

SQL> connect scott/tiger create index emp_ename2 on int_employee (ename) local; SQL> connect system/manager begin DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( 'scott','employee','int_employee', DBMS_REDEFINITION.cons_index,'scott','emp_ename','emp_ename2'); end; /

Copyright© 2007, Oracle. All rights reserved.

77

4´´

· 4´

select table_name,PARTITIONED from dba_indexes where index_name like 'EMP_ENAM%'; TABLE_NAME -----------------------------EMPLOYEE INT_EMPLOYEE PAR --NO YES

select table_name,PARTITIONED from dba_indexes where index_name like 'EMP_ENAM%'; TABLE_NAME PAR ------------------------------ --EMPLOYEE YES

Copyright© 2007, Oracle. All rights reserved.

78

OraclePeopleSoftJD EdwardsSiebel

Copyright© 2007, Oracle. All rights reserved.

79

Copyright© 2007, Oracle. All rights reserved.

80

Information

Slide 1

80 pages

Report File (DMCA)

Our content is added by our users. We aim to remove reported files within 1 working day. Please use this link to notify us:

Report this file as copyright or inappropriate

1246525


Notice: fwrite(): send of 208 bytes failed with errno=104 Connection reset by peer in /home/readbag.com/web/sphinxapi.php on line 531