lo siento, no hablo español - peoug€¦ · sql> set autotrace traceonly stat sql> select *...

13/08/2018

1

Lo siento, no hablo español :-(

Connor McDonald

13/08/2018

2

3

4

13/08/2018

3

Typical speaker slide

youtube tinyurl.com/connor-tube

blog connor-mcdonald.com

twitter @connor_mc_d

6https://asktom.oracle.com

13/08/2018

4

7https://asktom.oracle.com/officehours

why ?

13/08/2018

5

so...here

iswhat

happened

13/08/2018

6

13/08/2018

7

maybe they were right ?

it's not about manuals

13/08/2018

8

it's not about re-invention

it's about sharing

13/08/2018

9

it's about community

that’s why

13/08/2018

10

hints and tips

or...

13/08/2018

11

some guy who

hasn’t got enough

stuff on one topic ...

22

13/08/2018

12

1

rogue session

13/08/2018

13

SQL> select sid, last_call_et, status,

3 from v$session s;

SID LAST_CALL_ET STATUS

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

39 7376 ACTIVE

40 412 INACTIVE

44 421 INACTIVE

46 12 ACTIVE

51 9 ACTIVE

53 15 ACTIVE

58 8 ACTIVE

69 22 ACTIVE

...

80 453 INACTIVE

old days

13/08/2018

14

idiot in database

much more modern approach...

13/08/2018

15

idiot in database

SQL> alter system kill session ...

13/08/2018

16

# kill -9 pid

32

13/08/2018

17

SQL> oradebug setorapid nnn

SQL> oradebug suspend

SID LAST_CALL_ET STATUS

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

39 7376 ACTIVE

40 412 INACTIVE

41 412 INACTIVE

44 421 INACTIVE

46 12 ACTIVE

51 9 ACTIVE

53 15 ACTIVE

58 8 ACTIVE

SQL> oradebug resume

2

13/08/2018

18

maybe ...

... you don't want to resume

13/08/2018

19

rogue session

38

13/08/2018

20

SQL> alter system kill session ...

40

13/08/2018

21

18c

SQL> alter system cancel sql '123,456';

13/08/2018

22

3

do I need an index ?

13/08/2018

23

“I need a new index

on this 6TB table...”

13/08/2018

24

“will it speed up things ?”

“I think so”

13/08/2018

25

virtual indexes

SQL> create index TEST on

2 MYTAB ( SALES_DATE ) NOSEGMENT;

Index created.

data dictionary only

13/08/2018

26

SQL> begin

2 dbms_stats.generate_stats(

3 'SCOTT',

4 'MYTAB');

5 end;

6 /

SQL> alter session

2 set "_use_nosegment_indexes" = true;

SQL> explain plan for

2 select * from MYTAB

3 where sales_date > sysdate-7;

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

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

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

| 0 | SELECT STATEMENT | | 950 | 90109 | 962 (34)|

| 1 | TABLE ACCESS BY INDEX ROWID| MYTAB | 950 | 90109 | 962 (34)|

|* 2 | INDEX RANGE SCAN | TEST | 950 | | 962 (50)|

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

13/08/2018

27

4

54

invisible indexes

13/08/2018

28

55

SQL> create table T

2 as select * from all_objects;

Table created.

SQL> create index OBJ_IX on T ( OBJECT_ID);

Index created.

SQL> analyze table T estimate statistics;

Table analyzed.

56

SQL> set autotrace traceonly explain

SQL> select * from T

2 where OWNER = 'SCOTT'

3 and created > sysdate - 1

4 /

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


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


|* 1 | TABLE ACCESS FULL| T | 6 | 546 | 281 (1)|

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

13/08/2018

29

57

“Can I index OWNER..”

58

“will it speed up things ?”

13/08/2018

30

59

“I know so”

60

SQL> create index NEW_IX on T ( owner);

Index created.


2 where OWNER = 'SCOTT'

3 and created > sysdate - 1

4 /

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


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


|* 1 | TABLE ACCESS BY INDEX ROWID| T | 6 | 546 | 122 (0)|

|* 2 | INDEX RANGE SCAN | NEW_IX | 4107 | | 10 (0)|

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

13/08/2018

31

61

62

everyone elses queries

13/08/2018

32

63

before the new index

64



2 where OWNER = 'SYS'

3 and OBJECT_ID between 10 and 8000

4 /

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


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



|* 2 | INDEX RANGE SCAN | OBJ_IX | 7851 | | 20 (0)|

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

13/08/2018

33

65

SQL> set autotrace traceonly stat


2 where owner = 'SYS'

3 and object_id between 10 and 8000;

4967 rows selected.

Statistics

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

0 recursive calls

0 db block gets

784 consistent gets

15 physical reads

0 redo size

66

after the new index

13/08/2018

34

67



3 and object_id between 10 and 8000

4 /

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


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




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

the new index

68

SQL> set autotrace traceonly stat



3 and object_id between 10 and 8000;

4967 rows selected.

Statistics

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

0 recursive calls

0 db block gets

1522 consistent gets

62 physical reads

0 redo size

13/08/2018

35

69

70

the solution ?

13/08/2018

36

71

invisible indexes

72

SQL> alter index NEW_IX invisible;

Index altered.

13/08/2018

37

73




4 /

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


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



|* 2 | INDEX RANGE SCAN | OBJ_IX | 7851 | | 20 (0)|

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

74

“gee... thanks for nothing”

13/08/2018

38

75

SQL> alter session set

2 optimizer_use_invisible_indexes = true;

Session altered.





4 /

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


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




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

76

wrap your SQL to protect others

13/08/2018

39

5

better data guard use...

...for free

13/08/2018

40

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

10.2

open standby read/write

13/08/2018

41

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY

2 DATABASE DISCONNECT;

10.2

flashback to resetlogs

13/08/2018

42

resume managed recovery

archives still transmitted

13/08/2018

43

6

faster queries ... no code changes

13/08/2018

44

SQL> create table cust_trans

2 ( txn_id int,

3 txn_date date,

4 cust_id int,

5 amt number(10,2)

6 );

87

SQL> insert /*+ APPEND */ into cust_trans

2 select rownum,

3 sysdate-1000+rownum/1000,

4 trunc(dbms_random.value(1,1000)),

5 dbms_random.value(1,100)

6 from

7 ( select 1 from dual

8 connect by level <= 10000000 );

10000000 rows created.

SQL> create index cust_trans_ix

2 on cust_trans (cust_id );

Index created.88

13/08/2018

45

SQL> select max(amt)

2 from cust_trans

3 where cust_id = 123;

89

CUST 123 CUST 47 CUST 123 CUST 76 CUST 95 CUST 47 CUST 123

13/08/2018

46


2 from cust_trans


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


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


| 1 | SORT AGGREGATE | | 1 | 8 | |

| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| CUST_TRANS | 10010 | 80080 | 8803 (1)|

|* 3 | INDEX RANGE SCAN | CUST_TRANS_IX | 10010 | | 23 (0)|

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

Statistics

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

0 recursive calls

0 db block gets


8854 physical reads

0 redo size

543 bytes sent via SQL*Net to client

607 bytes received via SQL*Net from client

91

SQL> alter table cust_trans

2 add clustering by linear order(cust_id);

Table altered.

SQL> alter table cust_trans move online;

Table altered.

12.1

12.2

92

13/08/2018

47


2 from cust_trans


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


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


| 1 | SORT AGGREGATE | | 1 | 8 | |

| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| CUST_TRANS | 10010 | 80080 | 61 (0)|

|* 3 | INDEX RANGE SCAN | CUST_TRANS_IX | 10010 | | 23 (0)|

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

Statistics

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

0 recursive calls

0 db block gets

62 consistent gets

23 physical reads

0 redo size

543 bytes sent via SQL*Net to client

607 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

93

7

13/08/2018

48

DBMS_XPLAN

SQL> explain plan for ...

SQL> select * from

2 table(dbms_xplan.display_plan)

13/08/2018

49

extensions

... the (in)famous hint ignore issue

98

13/08/2018

50

99

SQL> select *

2 from emp e,

3 dept d

4 where e.deptno = d.deptno

5 and d.dname = 'SALES';

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

| Id | Operation | Name | Rows |

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

| 0 | SELECT STATEMENT | | 5 |

| 1 | MERGE JOIN | | 5 |

|* 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |

| 3 | INDEX FULL SCAN | PK_DEPT | 4 |

|* 4 | SORT JOIN | | 14 |

| 5 | TABLE ACCESS FULL | EMP | 14 |

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

100

"I want a hash join"

13/08/2018

51

101

SQL> select /*+ use_hash(d) */ *

2 from emp e,

3 dept d



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


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


| 1 | MERGE JOIN | | 5 |



|* 4 | SORT JOIN | | 14 |


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

102

13/08/2018

52

103

/*+ use_hash(d) */

if joining into "d"...

use a hash join

we're not...

104

13/08/2018

53

105

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


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


| 1 | MERGE JOIN | | 5 |



|* 4 | SORT JOIN | | 14 |


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

106

if joining into "d"...

we must be starting with "e"

13/08/2018

54

107

SQL> select /*+ leading(e) use_hash(d) */ *

2 from emp e,

3 dept d



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

| Id | Operation | Name | Rows | Bytes |

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

| 0 | SELECT STATEMENT | | 5 | 285 |

|* 1 | HASH JOIN | | 5 | 285 |

| 2 | TABLE ACCESS FULL| EMP | 14 | 518 |

|* 3 | TABLE ACCESS FULL| DEPT | 1 | 20 |

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

not enough hints

108

13/08/2018

55

109

"hints are like violence…if they do not work, use more"

back to dbms_xplan

110

13/08/2018

56

111

SQL> SELECT *

2 from table(dbms_xplan.display(.... ,

format=>'typical +OUTLINE))

/*+ BEGIN_OUTLINE_DATA

USE_HASH(@"SEL$1" "B"@"SEL$1")

USE_HASH(@"SEL$1" "D"@"SEL$1")

LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1" "B"@"SEL$1")

FULL(@"SEL$1" "B"@"SEL$1")

FULL(@"SEL$1" "D"@"SEL$1")

FULL(@"SEL$1" "E"@"SEL$1")

OUTLINE_LEAF(@"SEL$1")

ALL_ROWS

DB_VERSION('11.2.0.2')

OPTIMIZER_FEATURES_ENABLE('11.2.0.2')

END_OUTLINE_DATA

*/

8

13/08/2018

57

finding bad sql

114

SQL> select sql_fulltext

2 from v$sql

3 where buffer_gets > 1000000 or

4 executions > 10000 or

5 disk_reads > 100000;

SQL_FULLTEXT

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

SELECT ...

FROM ...

13/08/2018

58

you can do better

115

116

SQL> select sql_fulltext

2 from v$sqlstats

3 where buffer_gets > 1000000 or

4 executions > 10000 or

5 disk_reads > 100000;

SQL_FULLTEXT

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

SELECT ...

FROM ...

13/08/2018

59

"The column definitions for columns in V$SQLSTATS are identical to those in the V$SQL and V$SQLAREA views. However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a greater data retention (the statistics may still appear in this view, even after the cursor has been aged out of the

shared pool)."

117

its not the "shared pool"

13/08/2018

60

9

120

sqlplus hash tags

13/08/2018

61

121

122

SQL> select SAL

2 from EMP

3 where "hmmmm....."

13/08/2018

62

123

SQL> select SAL

2 from EMP

3 where

4 #desc EMP

Name Null? Type

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

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

...

4 job = 'CLERK';

SAL

----------

800

1100

124

SQL> declare

2 x int;

3 begin

4 select max(sal)

5 into x

6 from emp;

7

8 dbms_output.put_line(x);

9 #set serverout on

9 end;

10 /

10000

13/08/2018

63

10

126

sqlplus error logging

13/08/2018

64

127

SQL> set errorlogging on

128


SQL> desc SPERRORLOG

Name Type

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

USERNAME VARCHAR2(256)

TIMESTAMP TIMESTAMP(6)

SCRIPT VARCHAR2(1024)

IDENTIFIER VARCHAR2(256)

MESSAGE CLOB

STATEMENT CLOB

13/08/2018

65

129

SQL> select * from THE_WRONG_NAME;

select * from THE_WRONG_NAME

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> desc THE_WRONG_NAME;

ERROR:

ORA-04043: object THE_WRONG_NAME does not exist

130

SQL> select timestamp, message, statement

2 from SPERRORLOG;

TIMESTAMP

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

MESSAGE

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

STATEMENT

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

01-APR-08 02.29.58.000000 PM

ORA-00942: table or view does not exist

select * from THE_WRONG_NAME

01-APR-08 02.29.58.000000 PM

ORA-04043: object THE_WRONG_NAME does not exist

desc THE_WRONG_NAME;

13/08/2018

66

131

installation scripts


SQL> @create_all_objects

11

13/08/2018

67

133

sqlplus transaction safety

134

13/08/2018

68

135

SQL> set exitcommit

12

13/08/2018

69

context

8.1.5

13/08/2018

70

virtual private database

“... within a namespace, define, set and

access variable-length application

attributes and values in a secure data

cache available in UGA and SGA.”

13/08/2018

71

141

in-memory table structure

SURNAME McDonald

FORENAME Connor

DOB 20FEB1990

DETAILS

context

attributesvalues

13/08/2018

72

SQL> procedure LOGON_PROC is

2 begin

3 ...

4 ...

5 dbms_session.set_context('DETAILS','SURNAME',v_sname);

6 ...

7 end;

8 /

LOGON_PROC DETAILS

SQL> select SYS_CONTEXT('DETAILS','SURNAME') sname

2 from dual;

SNAME

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

McDonald

13/08/2018

73

a) pass parameters to views

create view POLICE_FORCE

select ...

from OFFICERS

connect by prior MANAGER_ID = OFFICER_ID

start with OFFICER_ID =

SYS_CONTEXT('DETAILS', 'MY_ID_NUMBER')

13/08/2018

74

SQL> select *

2 from POLICE_FORCE

SQL> select *

2 from POLICE_FORCE

13/08/2018

75

create view MY_VIEW

select ...

from ...

where COL1 = PLSQL_GLOBAL_VARIABLE

SQL> create or replace

2 package GLOBALS is

3 my_id number;

4 end;

5 /

Package created.


2 view MY_VIEW as

3 select ...

4 from ...

5 where COL1 = globals.my_id

6 /

ORA-06553: 'MY_ID' is not a procedure or is undefined

13/08/2018

76


2 package GLOBALS is

3 my_id number;

4 function get_my_id return number;

5 end;

6 /

Package created.


2 package body GLOBALS is

3 function get_my_id return number is

4 begin

5 return my_id;

6 end;

7 end;

8 /

Package body created.

13/08/2018

77

SQL> select executions from v$db_object_cache

2 where name = 'GLOBALS';

EXECUTIONS

----------

18

SQL> select count(*)

2 from all_objects

3 where object_id = globals.get_my_id

SQL> select executions from v$db_object_cache

2 where name = 'GLOBALS';

EXECUTIONS

----------

49967


2 from all_objects

3 where object_id = globals.get_my_id

4 /

COUNT(*)

----------

0

Statistics

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

5050 recursive calls

0 db block gets


0 physical reads

0 redo size

13/08/2018

78


2 from all_objects

3 where object_id = sys_context('NS','MY_ID')

4 /

COUNT(*)

----------

0

Statistics

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

8 recursive calls

0 db block gets

3 consistent gets

0 physical reads

0 redo size

even more global ?

13/08/2018

79


2 context globctx using ctxpkg

3 ACCESSED GLOBALLY

4 /

Context created.

instance wide


2 package CTXPKG as

3 procedure who_am_i(p_id varchar2);

4 procedure set_globid(p_value number);

5 end;

6 /

Package created.

13/08/2018

80


2 package body CTXPKG as

3

4 procedure who_am_i(p_id varchar2) is

5 begin

6 dbms_session.set_identifier( p_id );

7 end;

8

9 procedure set_globid( p_value number) is

10 begin

11 dbms_session.set_context(

12 NAMESPACE=>'GLOBCTX',

13 ATTRIBUTE=>'OBJ_ID',

14 VALUE =>p_value,

15 USERNAME =>null,

16 CLIENT_ID=>g_id);

17 end;

18 end;

SQL> exec ctxpkg.who_am_i('SALES');

PL/SQL procedure successfully completed.

SQL> exec ctxpkg.set_globid(123123);


SQL> select sys_context('GLOBCTX','OBJ_ID') obj2 from dual;

OBJ------------------------------------------------123123

SQL> exec ctxpkg.who_am_i('SALES');


SQL> select sys_context('GLOBCTX','OBJ_ID') obj 2 from dual;

OBJ-------------------------------------------123123

13/08/2018

81

b) default values

lucky

13/08/2018

82

SQL> create table PEOPLE

2 ( person_id number,

3 forename1 varchar2(20),

4 surname varchar2(30),

5 created_by varchar2(10) default USER );

Table created.





5 created_by varchar2(10)

6 default <some session specific value> );

Table created.

13/08/2018

83

triggers


2 trigger WHO_DID_THIS_ROW

3 before insert or update

4 on PEOPLE

5 for each row

6 begin

7 :new.created_by := my_package.global_var;

8 end;

9 /

Trigger created.

13/08/2018

84

over rated ...

insert /* without trigger */ into PEOPLE

select rownum, 'x','y', null

from all_objects

call count cpu elapsed disk query current

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

Parse 1 0.03 0.02 0 0 0

Execute 1 1.39 1.37 0 64619 1257

Fetch 0 0.00 0.00 0 0 0

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

total 2 1.42 1.40 0 64619 1257

13/08/2018

85

insert /* with trigger */ into PEOPLE


from all_objects


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

Parse 1 0.08 0.07 0 0 0

Execute 1 2.73 2.73 0 64631 51763

Fetch 0 0.00 0.00 0 0 0

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

total 2 2.81 2.80 0 64631 51763






6 default <some session specific value> );

Table created.

13/08/2018

86

“... within a namespace, define, set and

access variable-length application

attributes and values in a secure data

cache available in UGA and SGA.”






6 default SYS_CONTEXT('USERENV','SESSION_USER') );

Table created.

13/08/2018

87

insert /* with context */ into PEOPLE


from all_objects


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

Parse 1 0.03 0.02 0 0 0

Execute 1 1.38 1.40 0 64623 1257

Fetch 0 0.00 0.00 0 0 0

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

total 2 1.41 1.43 0 64623 1257

it costs you nothing !

13/08/2018

88

13

176

speaking of triggers

13/08/2018

89

177

178


2 trigger CHECK_SAL_ON_MON

3 after insert or update on EMP

4 for each row

5 begin

6 if updateing then

7 if new.sal > 20 and to_char(sysdate,'DY') = 'MON'

8 then

9 raise_application_error(-20000,'Too high');

10 end if;

11 end;

12 end;

13/08/2018

90

179




4 for each row

5 begin

6 if updateing then


8 then


10 end if;

11 end;

12 end;

LINE/COL ERROR

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

2/3 PL/SQL: Statement ignored

8/9 PLS-00201: identifier 'UPDATEING' must be declared

180




4 for each row

5 begin

6 if updateing then


8 then


10 end if;

11 end;

12 end;

LINE/COL ERROR

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

3/5 PL/SQL: Statement ignored

3/11 PLS-00201: identifier 'NEW.SAL' must be declared

13/08/2018

91

181




4 for each row

5 begin

6 if updateing then


8 then


10 end if;

11 end;

12 end;

LINE/COL ERROR

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

3/5 PLS-00103: Encountered the symbol ";" when

expecting one of the following:

182

etc etc etc

13/08/2018

92

183

EMP

184

SQL> update EMP

2 set DEPTNO = 10

3 where EMPNO = 7365;

update EMP

*

ERROR at line 1:

ORA-04098: trigger

'SCOTT.CHECK_SAL_ON_MON' is invalid and

failed re-validation

13/08/2018

93

185

it is all dml

186

SQL> update EMP

2 set DEPTNO = 10


update EMP

*

ERROR at line 1:

SQL> select * from EMP


no rows selected

13/08/2018

94

187

disabled triggers

188




4 for each row

5 DISABLE

6 begin

7 if updateing then


9 then


11 end if;

12 end;

13 end;

13/08/2018

95

14

190

/*+ GATHER_PLAN_STATISTICS */

190

13/08/2018

96

191

for the optimizer

191

192

cardinality is everything

192

13/08/2018

97

193

194

13/08/2018

98

195

SQL> explain plan for

2 select count(*)

3 from VEHICLE

4 where MAKE = 'VOLKSWAGEN' and MODEL = 'GOL';

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_PLAN);

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

| Id | Operation | Name | Rows | Bytes |

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

| 1 | SORT AGGREGATE | | 1 | 1 |

|* 2 | TABLE ACCESS FULL| VEHICLE | 120K | 270K |

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

195

196

13/08/2018

99

197

actual versus estimate

197

198

SQL> select /*+ GATHER_PLAN_STATISTICS */ count(*)

2 from VEHICLE

3 where MAKE = 'VOLKSWAGEN' and MODEL = 'GOL';

COUNT(*)

----------

2714468

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(

2 NULL, NULL, 'ALLSTATS LAST'));

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

| Id | Operation | Name | Starts | E-Rows | A-Rows |

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

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |

|* 2 | TABLE ACCESS FULL| VEHICLE | 1 | 120K| 2714K|

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

198

13/08/2018

100

15

tracing

13/08/2018

101

alter session set sql_trace = true

too many trace files

13/08/2018

102

SQL> alter session

2 set tracefile_identifier = SALESPGM;

Session altered.

SQL> host ls *.trc

db10r2_ora_3248.trc

db10r2_ora_3284.trc

db10r2_ora_3356.trc

db10r2_ora_3492.trc

db10r2_ora_3504_SALESPGM.trc

db10r2_ora_3608.trc

db10r2_ora_3620.trc

...

...

too much trace data

13/08/2018

103

SQL> alter session

2 set events

3 'immediate trace name trace_buffer_on level 1048576';

Session altered.

SQL> host ls –l *.trc

drw------- oracle dba Jun17 15:10 1048576 db10r2_ora_3248.trc

rolling trace window

16

13/08/2018

104

index compression

SQL> create index IX on T ( col1, col2, col3)

2 compress 2;

Index created.

reduce storage for repeated keys

"compress" leading columns col1, col2

= dense index blocks

= better use of buffer cache

13/08/2018

105

which indexes ?

how much compression ?

13/08/2018

106

SQL> analyze index IX validate structure;

Index analyzed.

SQL> desc INDEX_STATS

Name Null? Type

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

HEIGHT NUMBER

BLOCKS NUMBER

...

...

OPT_CMPR_COUNT NUMBER

OPT_CMPR_PCTSAVE NUMBER

wrap up

13/08/2018

107

poaching ideas is fine

learn something new

13/08/2018

108

ORA-00028“your session has been killed”

Enjoy the rest of the day !

youtube tinyurl.com/connor-tube

blog connor-mcdonald.com

twitter @connor_mc_d

lo siento, no hablo español - peoug€¦ · sql> set autotrace traceonly stat sql> select *...

Documents