Transcript Forgotten Features

Forgotten
Features
Julian Dyke
Independent Consultant
Web Version
1
© 2005 Julian Dyke
juliandyke.com
Agenda



2
© 2005 Julian Dyke
Introduction
Forgotten Features
 Tracing and Auditing
 Testing and Benchmarking
 Administration
 Execution Plans
 Tables
 Indexes
 SQL
 PL/SQL
Conclusion
juliandyke.com
Criteria for an Oracle feature
3

Easy to understand

Easy to implement

Works in first release

Documented in first release

Compatible with other features

Improves Productivity or Manageability
 Saves resources / money

Intellectually stimulating
© 2005 Julian Dyke
juliandyke.com
Tracing
and
Auditing
4
© 2005 Julian Dyke
juliandyke.com
TRACEFILE_IDENTIFIER


Initialisation Parameter
Introduced in Oracle 8.1.7
tracefile_identifier = '<identifier>'

e.g. in Oracle 9.2 if a trace file is called
ss92001_ora_1760.trc

then the statement
ALTER SESSION
SET tracefile_identifier = 'test';

will change the file name to
ss92001_ora_1760_test.trc
5
© 2005 Julian Dyke
juliandyke.com
DBMS_SYSTEM.KSDWRT

DBMS_SYSTEM
 undocumented package
 installed in all databases
 owned by SYS

To write messages to trace files and/or alert log use
DBMS_SYSTEM.KSDWRT
(
DEST NUMBER,
-- 1 = Trace File, 2 = Alert Log, 3 = Both
TST VARCHAR2
-- Message
);

For example
BEGIN
DBMS_SYSTEM.KSDWRT (1, ‘Output to trace file’);
DBMS_SYSTEM.KSDWRT (2, ‘Output to alert log’);
DBMS_SYSTEM.KSDWRT (3, ’Output to both’);
END;
/
6
© 2005 Julian Dyke
juliandyke.com
BITAND


Initially undocumented built-in function
Performs a bit-wise AND between two operators
SELECT BITAND (42,1) FROM dual;

Can be used as basis for OR and XOR functions
CREATE OR REPLACE FUNCTION bitor (x NUMBER, y NUMBER)
RETURN NUMBER IS
BEGIN
RETURN x + y - BITAND (x,y);
END;
/
CREATE OR REPLACE FUNCTION bitxor (x NUMBER, y NUMBER)
RETURN NUMBER IS
BEGIN
RETURN BITOR (x,y) - BITAND (x,y);
END;
/

7
Beware of overflows
© 2005 Julian Dyke
juliandyke.com
Hexadecimal Format Masks



Introduced in Oracle 8.1.5
Convert decimal numbers to and from hexadecimal
To convert from decimal to hex
SELECT TO_CHAR (1048576,’XXXXXXXX’) FROM dual;

returns 100000

To convert from hex to decimal
SELECT TO_NUMBER (100000,‘XXXXXXXX’) FROM dual;

8
returns 1048576
© 2005 Julian Dyke
juliandyke.com
System Triggers


Introduced in Oracle 8.1.5
Must be created by SYS (as SYSDBA)
CREATE OR REPLACE TRIGGER us01_logon
AFTER LOGON ON us01.SCHEMA
BEGIN
dbms_session.set_sql_trace (TRUE);
END;
CREATE OR REPLACE TRIGGER us01_logoff
BEFORE LOGOFF ON us01.SCHEMA
BEGIN
dbms_session.set_sql_trace (FALSE);
END;
ALTER TRIGGER us01_login ENABLE;
ALTER TRIGGER us01_login DISABLE;
9
© 2005 Julian Dyke
juliandyke.com
DBMS_MONITOR


Introduced in Oracle 10.1
To enable trace in another session use
DBMS_MONITOR.SESSION_TRACE_ENABLE
(
SESSION_ID NUMBER,
-- SID
SERIAL_NUM NUMBER,
-- Serial Number
WAITS BOOLEAN,
-- Include Waits
BINDS BOOLEAN
-- Include Binds
);

Waits (event 10046 level 8) are enabled by default
Binds (event 10046 level 4) are disabled by default

To disable trace in another session use

DBMS_MONITOR.SESSION_TRACE_DISABLE
(
SESSION_ID NUMBER,
-- SID
SERIAL_NUM NUMBER
-- Serial Number
);
10
© 2005 Julian Dyke
juliandyke.com
DBMS_MONITOR


Can be enabled at database level in Oracle 10.2
To enable trace for all database sessions use
DBMS_MONITOR.DATABASE_TRACE_ENABLE
(
WAITS BOOLEAN,
-- Include Waits
BINDS BOOLEAN
-- Include Binds
INSTANCE_NAME VARCHAR2
-- Instance Name
);

To disable trace for all database sessions use
DBMS_MONITOR.DATABASE_TRACE_DISABLE
(
INSTANCE_NAME VARCHAR2
-- Instance Name
);
11
© 2005 Julian Dyke
juliandyke.com
Autonomous Transactions





Introduced in Oracle 8.1.5
Recursive transaction started by main transaction
Can commit or rollback independently of main transaction
Committed data unaffected if main transaction rolls back
Often used for auditing
CREATE OR REPLACE TRIGGER trigger1
BEFORE INSERT ON table1 FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log1 VALUES (:new.col1,:new.col2,SYSDATE);
COMMIT;
END;
/
12
© 2005 Julian Dyke
juliandyke.com
SYS Auditing
13

In Oracle 9.2 and above, operations performed by the SYS
user can be audited.

To enable SYS auditing set AUDIT_SYS_OPERATIONS
parameter to TRUE

Auditing information will be written to text file in directory
specified by AUDIT_DUMP_DEST parameter

Default directory is $ORACLE_HOME/rdbms/audit

Filename is ora_<pid>.aud where pid is the operating system
process ID
© 2005 Julian Dyke
juliandyke.com
Testing
and
Benchmarking
14
© 2005 Julian Dyke
juliandyke.com
Fixed Date




Initialization Parameter
Useful for deterministic testing
In Oracle 8.0 and above can be set dynamically using
ALTER SYSTEM
To set date only use
FIXED_DATE = ‘DD-MON-YY’

Sets time to 00:00:00

To set date and time use
FIXED_DATE = YYYY-MM-DD-HH24:MI:SS
15
© 2005 Julian Dyke
juliandyke.com
Random Functions

To guarantee consistent tests, random functions should
return deterministic results

DBMS_RANDOM package
 Seed can be specified using DBMS_RANDOM.SEED
procedure

SAMPLE clause
 Can be made deterministic by enabling event 10193
 Level specifies seed
ALTER SESSION SET EVENTS
‘10193 trace name context forever, level 42’;
16
© 2005 Julian Dyke
juliandyke.com
Checkpoints and Logfiles

To force a checkpoint
ALTER SYSTEM CHECKPOINT;

To force a log file switch
ALTER SYSTEM SWITCH LOGFILE;

Useful when dumping log files

To force a log file switch and archive the log file
ALTER SYSTEM ARCHIVE LOG CURRENT;

17
Useful for testing archive log creation with
 Physical standby database
 Logical standby database
© 2005 Julian Dyke
juliandyke.com
Flushing the Shared Pool


Introduced in Oracle 8.0
Flushes all unpinned objects from library cache
ALTER SYSTEM FLUSH SHARED_POOL;


18
Useful for deterministic testing but…
After issuing this statement
 All unpinned cursors need reparsing
 All unpinned packages need recompilation
© 2005 Julian Dyke
juliandyke.com
Flushing the Buffer Cache


Introduced in Oracle 10.1
Flushes all unpinned buffers from the buffer cache
ALTER SYSTEM FLUSH BUFFER_CACHE;

In Oracle 9.0.1 and above the following command has the
same effect
ALTER SESSION SET EVENTS
‘IMMEDIATE TRACE NAME FLUSH_CACHE’;


19
Useful for deterministic testing but…
After issuing this statement
 Warm up the cache before testing workloads
© 2005 Julian Dyke
juliandyke.com
Performance
Tuning
20
© 2005 Julian Dyke
juliandyke.com
V$SQL_PLAN
21

Introduced in Oracle 9.0.1

Shows actual execution plan in memory

Enhanced in Oracle 9.2 to include
 Access Predicates (Joins)
 Filter Predicates

Related views include
 V$SQL_PLAN_WORKAREA
 V$SQL_PLAN_STATISTICS
 V$SQL_PLAN_STATISTICS_ALL
© 2005 Julian Dyke
juliandyke.com
V$SQL_PLAN
ADDRESS
HASH_VALUE
CHILD_NUMBER
OPERATION
OPTIONS
OBJECT_NODE
OBJECT#
OBJECT_OWNER
OBJECT_NAME
OPTIMIZER
ID
PARENT_ID
DEPTH
POSITION
COST
CARDINALITY
22
© 2005 Julian Dyke
RAW(4)
NUMBER
NUMBER
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(10)
NUMBER
VARCHAR2(30)
VARCHAR2(64)
VARCHAR2(20)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
BYTES
OTHER_TAG
PARTITION_START
PARTITION_STOP
PARTITION_ID
OTHER
DISTRIBUTION
CPU_COST
IO_COST
TEMP_SPACE
ACCESS_PREDICATES
FILTER_PREDICATES
PROJECTION
TIME
QBLOCK_NAME
REMARKS
NUMBER
VARCHAR(35)
VARCHAR2(5)
VARCHAR2(5)
NUMBER
VARCHAR2(4000)
VARCHAR2(20)
NUMBER
NUMBER
NUMBER
VARCHAR2(4000)
VARCHAR2(4000)
VARCHAR2(4000)
NUMBER
VARCHAR2(31)
VARCHAR2(4000)
juliandyke.com
Optimizer Environment Variables

In Oracle 10.1 and above, optimizer environment variables are
externalized at :




23
instance level
session level
statement level
- V$SYS_OPTIMIZER_ENV
- V$SES_OPTIMIZER_ENV
- V$SQL_OPTIMIZER_ENV
Use the values in these views when determining why
execution plans differ
© 2005 Julian Dyke
juliandyke.com
Optimizer Environment Variables

24
Optimizer Environment Variable values reported by the
dynamic performance views include:
active_instance_count
parallel_dml_mode
bitmap_merge_area_size
parallel_execution_enabled
cpu_count
parallel_query_mode
cursor_sharing
parallel_threads_per_cpu
db_file_multiblock_read_count
pga_aggregate_target
hash_area_size
query_rewrite_enabled
optimizer_dynamic_sampling
query_rewrite_integrity
optimizer_features_enable
skip_unusable_indexes
optimizer_index_caching
sort_area_retained_size
optimizer_index_cost_adj
sort_area_size
optimizer_mode
star_transformation_enabled
optimizer_mode_hinted
statistics_level
parallel_ddl_mode
workarea_size_policy
© 2005 Julian Dyke
juliandyke.com
DBMS_XPLAN


Introduced in Oracle 9.2
Formats PLAN_TABLE contents generated by EXPLAIN PLAN
SELECT * FROM TABLE (dbms_xplan.display);

25
DISPLAY function parameters include
 TABLE_NAME – name of plan table
 STATEMENT_ID – statement ID in plan table
 FORMAT – as below
Value
Description
BASIC
Operation ID, object name and operation/option only
TYPICAL
(Default) Most relevant information including partition
pruning, parallelism and predicates where appropriate
ALL
As TYPICAL plus parallel execution server statements
SERIAL
As TYPICAL without parallel execution server statements
© 2005 Julian Dyke
juliandyke.com
DBMS_XPLAN

For example explain a query
EXPLAIN PLAN FOR
SET STATEMENT_ID = 'STATEMENT1' FOR
SELECT t1.c2, t2.c2
FROM t1, t2
WHERE t1.c1 = t2.c1
AND t1.c2 = 10;

The plan table can be queried using
SELECT * FROM
TABLE
(
dbms_xplan.display ('PLAN_TABLE','STATEMENT1')
);
26
© 2005 Julian Dyke
juliandyke.com
DBMS_XPLAN

Example output with predicates
Id Operation
Name
0 SELECT STATEMENT
*1
HASH JOIN
Rows
Bytes
Cost
10
590
66
10
590
66
*2
TABLE ACCESS FULL
T1
10
60
58
3
TABLE ACCESS FULL
T2
1000
53000
7
Predicate Information (identified by operation id):
1 - access("T1"."C1"="T2"."C1")
2 - filter("T1"."C2"=10)
27
© 2005 Julian Dyke
juliandyke.com
DBMS_XPLAN

Parallel execution queries are automatically formatted e.g.
EXPLAIN PLAN FOR
SELECT /*+ ORDERED PARALLEL (t1 2) USE_MERGE (t1 t2) */
t1.c2, t2.c2
FROM t1, t2
WHERE t1.c1 = t2.c1
AND t1.c2 = 10;

The plan table can be queried using
SELECT * FROM
TABLE (dbms_xplan.display);
28
© 2005 Julian Dyke
juliandyke.com
DBMS_XPLAN

Example output for parallel execution
Id Operation
Name Rows Bytes Cost
TQ
IN-OUT PQ Distrib
0 SELECT STATEMENT
10
590
79
1
MERGE JOIN
10
590
79 78,02
2
SORT JOIN
10
60
33 78,02 PCWP
10
60
29 78,01
*3
*4
5
TABLE ACCESS FULL
T1
SORT JOIN
TABLE ACCESS FULL
1000 53000
T2
1000 53000
P->S QC(RANDOM)
P->P HASH
47 78,02 PCWP
7 78,00
S->P HASH
Predicate Information (identified by operation id):
34-
29
filter("T1"."C2"=10)
access("T1"."C1"="T2"."C1")
filter("T1"."C1"="T2"."C1")
© 2005 Julian Dyke
juliandyke.com
DBMS_XPLAN

Partition pruning information can also be included e.g. for a
range partitioned table
CREATE TABLE t1 (c1 NUMBER,c2 NUMBER,c3 CHAR(50))
PARTITION BY RANGE (c1)
(
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200),
PARTITION p3 VALUES LESS THAN (300),
PARTITION p4 VALUES LESS THAN (400)
);
EXPLAIN PLAN FOR
SELECT c2 FROM t1
WHERE c1 >= 150 AND c1 < 250;
SELECT * FROM TABLE (dbms_xplan.display);
30
© 2005 Julian Dyke
juliandyke.com
DBMS_XPLAN

Example output for partition pruning
Id Operation
Name Rows Bytes Cost
0 SELECT STATEMENT
1
*2
1
26
PStart PStop
2
PARTITION RANGE ITERATOR
TABLE ACCESS FULL
T1
1
26
2
2
3
2
3
Predicate Information (identified by operation id):
2-
31
filter("T1"."C1">=150 AND "T1"."C1"<250)
© 2005 Julian Dyke
juliandyke.com
DBMS_XPLAN

In Oracle 10.1 and above
 New DISPLAY_CURSOR function
 By default displays plan for last statement executed in
session
SQL> SELECT COUNT(*) FROM t1;
SQL > SELECT * FROM TABLE (dbms_xplan.display_cursor);
Id Operation
Name
Rows Cost
0 SELECT STATEMENT
1
2
32
SORT AGGREGATE
TABLE ACCESS FULL
© 2005 Julian Dyke
(%CPU)
160
(100)
51809 160
(2)
Time
1
T1
00:00:02
juliandyke.com
V$SESSION_WAIT_HISTORY

Introduced in Oracle 10.1
SID
SEQ#
EVENT
EVENT
P1TEXT
P1
P2TEXT
P2
P3TEXT
P3
WAIT_TIME
WAIT_COUNT


33
NUMBER
NUMBER
NUMBER
VARCHAR2(64)
VARCHAR2(64)
NUMBER
VARCHAR2(64)
NUMBER
VARCHAR2(64)
NUMBER
NUMBER
NUMBER
Externalises last 10 wait events for each session
Similar information to V$SESSION_WAIT – but much more
user friendly
© 2005 Julian Dyke
juliandyke.com
Administration
34
© 2005 Julian Dyke
juliandyke.com
Kill Session

For example to kill a session for user US01
 Identify the SID and serial number
SELECT sid, serial#
FROM v$session
WHERE username = ‘US01’;

Kill the session using
ALTER SYSTEM KILL SESSION ‘<sid>,<serial#>’;

For example
ALTER SYSTEM KILL SESSION ‘133,523’;

Next command issued by killed session will return error
ORA-00028: your session has been killed
35
© 2005 Julian Dyke
juliandyke.com
Renaming Database Objects

To rename a table:
RENAME oldname TO newname;

To rename an index
ALTER INDEX oldname RENAME TO newname;

In Oracle 9.2 and above to rename a column
ALTER TABLE t1 RENAME COLUMN oldname TO newname;

In Oracle 9.2 and above to rename a constraint
ALTER TABLE t1 RENAME CONSTRAINT oldname TO newname;

In Oracle 10.1 and above to rename a tablespace
ALTER TABLESPACE oldname RENAME TO newname;
36
© 2005 Julian Dyke
juliandyke.com
Dropping Columns
37

Introduced in 8.1.5

Columns can be dropped from a table using the ALTER TABLE
statement

Columns can be
 marked unused immediately and deleted at a later time
 deleted immediately

If the delete operation fails at any point it can be restarted
from the point of failure
© 2005 Julian Dyke
juliandyke.com
Dropping Columns

To drop a column immediately use
ALTER TABLE table1 DROP COLUMN column2;


Columns can also be marked unused immediately
and subsequently dropped
To mark a column unused use
ALTER TABLE table1 SET UNUSED COLUMN column2;

To drop all unused columns from a table use
ALTER TABLE table1 DROP UNUSED COLUMNS;
38
© 2005 Julian Dyke
juliandyke.com
Dropping Columns

If instance is shutdown while column is being dropped, drop
column statement can be continued when instance restarted
ALTER TABLE table1 DROP COLUMNS CONTINUE;
39

DBA_PARTIAL_DROP_TABS describes partially dropped columns

DBA_UNUSED_COL_TABS describes columns marked unused,
but not yet dropped
© 2005 Julian Dyke
juliandyke.com
Default Tablespaces

In Oracle 9.0.1 and above a default temporary tablespace can
be defined
ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE <tablespace>;

In Oracle 10.1 and above a default permanent tablespace can
be defined
ALTER DATABASE
DEFAULT TABLESPACE <tablespace>;
40
© 2005 Julian Dyke
juliandyke.com
Resumable Statements
41

Oracle 9.0.1 and above long running operations encountering
out of space errors can be resumed

Resumable operations include
 Queries
 DML Statements
 SQL*Loader operations
 Import operations
 DDL statements

Out of space errors include
 Tablespace full
 Maximum number of extents reached for object
 Tablespace quota exceeded for a user
© 2005 Julian Dyke
juliandyke.com
Resumable Statements

42
When resumable space allocation is enabled
 Operation suspends if an out of space error occurs
 Details of the error are written to DBA_RESUMABLE
 DBA can optionally be alerted
 DBA can either
 fix the error
 abort the operation
 Operation automatically resumes execution when error is
fixed
 If a further error is detected, operation will suspend again
© 2005 Julian Dyke
juliandyke.com
Resumable Statements
1 – Resumable space allocation is enabled
ALTER SESSION ENABLE RESUMABLE
NAME ‘Batch Update’ TIMEOUT 3600;
2 – Resumable operation starts
INSERT INTO t2
SELECT * FROM t1;
3 – Out of space error occurs
ORA-01653: unable to extend table US01.T2 by 210 in tablespace
‘TS99’
4 – Error is written to alert log
43
© 2005 Julian Dyke
juliandyke.com
Resumable Statements
5 – (Optional) DBA is notified by message generated by
AFTER SUSPEND trigger
6 – DBA queries DBA_RESUMABLE view for details of
suspended operation
7 – DBA fixes error condition e.g.
ALTER TABLESPACE TS99
ADD DATAFILE <filename> SIZE <size>;
8 – Suspended operation resumes automatically
9 – Operation completes successfully
44
© 2005 Julian Dyke
juliandyke.com
Resumable Statements

In Oracle 10.1 and above resumable statements can be
enabled at instance level
ALTER SYSTEM
SET resumable_timeout = <timeout>;

Resumable statements can be disabled at instance level using
ALTER SYSTEM
SET resumable_timeout = 0;

Resumable statements can be enabled at session level using
ALTER SESSION
SET resumable_timeout = <timeout>;

Resumable statements can be disabled at session level using
ALTER SESSION
SET resumable_timeout = 0;
45
© 2005 Julian Dyke
juliandyke.com
Automatic Datafile Deletion

In Oracle 9.0.1 and above, DROP TABLESPACE has been
extended to optionally delete its datafiles
DROP TABLESPACE tablespace_name
INCLUDING CONTENTS AND DATAFILES;

46
The DROP TABLESPACE command automatically deletes
Oracle Managed Files
© 2005 Julian Dyke
juliandyke.com
Database Level Backups

In Oracle 10.1 and above to enable/disable backup mode for
all tablespaces in single statement use:
ALTER DATABASE BEGIN BACKUP;
ALTER DATABASE END BACKUP;

Useful with three-way mirror or snapshot backups

In Oracle 9.2 to disable backup mode for all tablespaces
following a database restart use:
ALTER DATABASE END BACKUP;
47
© 2005 Julian Dyke
juliandyke.com
Tables
48
© 2005 Julian Dyke
juliandyke.com
Row Movement
In Oracle 8.0 updating the partition key columns in a range
partitioned table fails if it would cause the row to be migrated to
another partition

ORA-14402: updating partition key column would cause a partition change
49

In Oracle 8.1.5 and above row movement can be enabled
 a row may be migrated to another partition if its partition key
columns are updated

By default row movement is disabled
© 2005 Julian Dyke
juliandyke.com
Row Movement

Row movement can be enabled when the partitioned table is
created e.g.
CREATE TABLE table1
(
column1 NUMBER,
column2 NUMBER
)
PARTITION BY ….
ENABLE ROW MOVEMENT;

Row movement can be also be enabled and disabled from an
existing table e.g.
ALTER TABLE table1 ENABLE ROW MOVEMENT;
ALTER TABLE table1 DISABLE ROW MOVEMENT;
50
© 2005 Julian Dyke
juliandyke.com
External Tables

External tables
 read-only tables
 definition is stored in data dictionary
 data is stored outside the database in operating system flat
files
 can be queried using SQL
 can be queried in parallel
 can be included in DML statement subqueries

No DML operations are allowed on external table
No indexes can be created on external table

51
© 2005 Julian Dyke
juliandyke.com
External Tables

Example
CREATE DIRECTORY ext_dir AS '/v01/external';
CREATE TABLE transactions
(
account
NUMBER,
value
NUMBER
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’
)
LOCATION (‘transactions.csv’)
);
52
© 2005 Julian Dyke
juliandyke.com
External Tables


The following tests were based on a 1,000,000 row flat file.
Each row contained 74 bytes of data and 4 bytes of separators
Tests were performed in NOARCHIVING and ARCHIVING
modes
Description
53
Time in seconds
NoArchiving
Archiving
Use SQL*Loader with CONVENTIONAL path load to
INSERT rows
318
322
Use SQL*Loader with DIRECT load to INSERT rows
28
285
Use CREATE TABLE AS SELECT to copy data from
external table to internal table
20
290
Use CREATE TABLE AS SELECT to copy data from
internal table to internal table
15
283
© 2005 Julian Dyke
juliandyke.com
Data Segment Compression
54

Introduced in Oracle 9.2

Data is
 compressed when it is written to a block
 decompressed when it is read from the block

Compression is applied at block level

Blocks will only be compressed if
 data is sufficiently large to fill the block
 rows have low enough cardinality

Columns can be reordered within each block to achieve
optimal compression ratios

A segment may contain compressed and uncompressed
blocks
© 2005 Julian Dyke
juliandyke.com
Data Segment Compression

Compression can be specified for new tables
CREATE TABLE t1 (c01 NUMBER,c02 VARCHAR2(30)) COMPRESS;

Compression can also be specified for existing tables
ALTER TABLE t2 COMPRESS;

Existing tables can be compressed using
ALTER TABLE t3 MOVE COMPRESS;

55
Compression can also be specified for
 range and list partitioned tables
 materialized views
 nested tables
© 2005 Julian Dyke
juliandyke.com
Data Segment Compression
56

Only works with direct path inserts

For example
 CREATE TABLE AS SELECT
 INSERT /*+ APPEND */
 ALTER TABLE MOVE
 Materialized View Refresh
 SQL*Loader
 Online Reorganization

Does not work with
 INSERT /*+ NOAPPEND */
 UPDATE
 DELETE
© 2005 Julian Dyke
juliandyke.com
Data Segment Compression
Year
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
Driver
Ayrton Senna
Michael Schumacher
Michael Schumacher
Olivier Panis
Michael Schumacher
Mika Hakkinen
Michael Schumacher
David Coulthard
Michael Schumacher
David Coulthard
Team
McLaren
Benetton
Benetton
Ligier
Ferrari
McLaren
Ferrari
McLaren
Ferrari
McLaren
Monaco Grand Prix
Winners 1993-2002
57
© 2005 Julian Dyke
4
0
2002
1
2
2001
4
0
2000
1
2
1999
4 Mika Hakkinen
1
2
Ligier
1998
1997
Olivier Panis
Row
Data
1996
3
2
3
2
4
Ayrton Senna
4
4 McLaren
3
2 Benetton
2
5 Michael Schumacher
1
3 Ferrari
0
2 David Coulthard
1995
1994
1993
Symbol
Table
juliandyke.com
Data Segment Compression

Compression ratios vary with
 number of rows
 number of columns
 cardinality of rows

For example – loading SALES table from sales history demo
schema
$ORACLE_HOME/demo/schema/sales_history

Table contains 1016271 rows
Block
Size
58
Uncompressed
Size (Blocks)
Compressed Ratio %
Size (Blocks)
2048
18777
13433
71.5
4096
8983
6106
68.0
8192
4398
2850
64.7
16384
2179
1353
62.0
© 2005 Julian Dyke
juliandyke.com
Data Segment Compression


Test1 – Loading the SALES table from a flat file into an empty
table
Compressed
Blocks
Elapsed
TIme (Secs)
CPU Time
(Secs)
No
4398
31.77
4.13
Yes
2850
71.08
43.86
Test2 – Selecting one column from all rows in the compressed
table
SELECT SUM (quantity_sold) FROM sales;
59
Compressed
Blocks
No
4398
3.41
2.77
Yes
2850
3.78
3.53
© 2005 Julian Dyke
Elapsed
TIme (Secs)
CPU Time
(Secs)
juliandyke.com
Indexes
60
© 2005 Julian Dyke
juliandyke.com
Index Leaf Compression

Introduced in Oracle 8.1.5

Compresses leading columns in index

Must be specified when index is created or rebuilt
CREATE INDEX i1 ON t1 (c1, c2, c3) COMPRESS 2;
ALTER INDEX i1 REBUILD ONLINE COMPRESS 1;

61
Benefits
 Reduces number of blocks to hold index
 Reduction in physical I/O for scans
 Improved cache efficiency
 Potential reduction in index height
 CBO more likely to use index
© 2005 Julian Dyke
juliandyke.com
Index Leaf Compression
1600 1400 1300 1200 1000
900
Suffix Slot Array
1500
6
Prefix Slot Array
1700
62
1
4
1100
900 Rome
Suffix Row
1000 Milan
Suffix Row
1100 Italy
Prefix Row
1200 Munich
Suffix Row
1300 Frankfurt
Suffix Row
1400 Berlin
Suffix Row
1500 Germany
CREATE INDEX i1
ON TABLE t1 (country, city)
COMPRESS 1;
Country
City
France
Paris
Germany
Berlin
Germany
Frankfurt
Prefix Row
Germany
Munich
1600 Paris
Suffix Row
Italy
Milan
1700 France
Prefix Row
Italy
Rome
© 2005 Julian Dyke
juliandyke.com
Index Leaf Compression

Number and size of prefixed rows can be verified using
ANALYZE INDEX i1 VALIDATE STRUCTURE;
SELECT * FROM index_stats;

63
Useful INDEX_STATS columns include
 PRE_ROWS
Number of prefix rows
 PRE_ROWS_LEN
Sum of lengths of all prefix rows
 OPT_CMPR_COUNT
Optimal key compression length
 OPT_CMPR_PCTSAVE
Space saved by implementing optimal key length
© 2005 Julian Dyke
juliandyke.com
Index Leaf Compression

Can also be used with IOTs
CREATE TABLE t1
(
c1 NUMBER,
c2 NUMBER,
c3 NUMBER,
c4 NUMBER
CONSTRAINT pk1 PRIMARY KEY (c1,c2,c3)
)
ORGANIZATION INDEX
COMPRESS 2;

To rebuild existing IOT
ALTER TABLE t1 MOVE COMPRESS 1;
64
© 2005 Julian Dyke
juliandyke.com
Index Rebuild Online
65

Introduced in 8.1.5
 create or rebuild index whilst allowing concurrent DML
operations

Works with
 B*tree indexes (non-partitioned and partitioned) – 8.1.5
 IOTs (non-partitioned and partitioned) – 8.1.5
 Reverse key indexes – 9.0.1
 Function-based indexes – 9.0.1
 Compressed indexes – 9.0.1
 IOT Secondary indexes – 9.0.1
© 2005 Julian Dyke
juliandyke.com
Index Rebuild Online

To build an index online use
ALTER INDEX index1 REBUILD ONLINE;
Can
be executed in parallel
Three
phases
prepare phase
 index created
 data dictionary updated
build phase
 index populated
 changes written to journal table
merge phase
rows in journal table are merged
66
© 2005 Julian Dyke
juliandyke.com
ANALYZE INDEX ONLINE

Prior to Oracle 9.0.1, index structure can be analyzed using
ANALYZE INDEX index_name
VALIDATE STRUCTURE;

No DML possible against index while it is being analyzed

In Oracle 9.0.1 and above indexes can be analyzed online
ANALYZE INDEX index_name
VALIDATE STRUCTURE ONLINE;

67
DML statements unaffected
© 2005 Julian Dyke
juliandyke.com
Index Monitoring
68

In Oracle 9.0.1 indexes can be monitored to determine if they
are being used

If index monitoring is enabled for an index, then Oracle
updates a table in the data dictionary when that index is
included in an execution plan by the parser

Indexes are only monitored at parse time

Only SELECT statements and subqueries monitored

Also works for bitmap indexes
© 2005 Julian Dyke
juliandyke.com
Index Monitoring

This example assumes the following definitions
CREATE TABLE table1 (col1 NUMBER, col2 NUMBER);
CREATE INDEX index1 ON table1 (col1);
CREATE INDEX index2 ON table1 (col2);

Enable index monitoring using
ALTER INDEX index1 MONITORING USAGE;
ALTER INDEX index2 MONITORING USAGE;

To check which indexes are being monitored use
SELECT index_name,monitoring
FROM v$object_usage;
69
© 2005 Julian Dyke
INDEX_NAME
MONITORING
INDEX1
YES
INDEX2
YES
juliandyke.com
Index Monitoring

Execute all possible statements against the table
SELECT /*+ INDEX (table1 index1) */ * FROM table1
WHERE col1 = 0;

Check which indexes have been used using
SELECT index_name,used
FROM v$object_usage;

INDEX_NAME
USED
INDEX1
YES
INDEX2
NO
Disable index monitoring using
ALTER INDEX index1 NOMONITORING USAGE;
ALTER INDEX index2 NOMONITORING USAGE;
70
© 2005 Julian Dyke
juliandyke.com
Create Index NOSORT

Specify the NOSORT option to avoid sorting index columns
when creating an index
CREATE INDEX index1 ON table1 (column1) NOSORT

If columns are not in sorted order then index creation will fail
with the following error:
ORA-01409: NOSORT option may not be used;
rows are not in ascending order
71
© 2005 Julian Dyke
juliandyke.com
Create Index Compute Statistics


Introduced in Oracle 8.1.5
To compute statistics when creating an index use:
CREATE INDEX index1 ON table1 (column1) COMPUTE STATISTICS;

To compute statistics when rebuilding an index use:
ALTER INDEX index1 REBUILD COMPUTE STATISTICS;

Statistics will be computed (not estimated)

If the index is composite, statistics only generated for leading
column
If the index is non-partitioned, table, column and index
statistics are gathered
If the index is partitioned only index statistics are gathered


72
© 2005 Julian Dyke
juliandyke.com
Global Index Maintenance

In Oracle 9.0.1 and above UPDATE GLOBAL INDEXES can be
specified for DDL statements on partitioned tables
 Global indexes remain available during the operation
 Updates to the global index are logged

UPDATE GLOBAL INDEXES can be used with the following
partition DDL statements

ADD
SPLIT
DROP
MERGE
MOVE
EXCHANGE
TRUNCATE
COALESCE
For example
ALTER TABLE table1
DROP PARTITION table1_p1
UPDATE GLOBAL INDEXES;
73
© 2005 Julian Dyke
juliandyke.com
Undo
and
Redo
74
© 2005 Julian Dyke
juliandyke.com
Global Temporary Tables
75

Introduced in Oracle 8.1.5

Defined in data dictionary – definition available to all sessions

Data
 only visible to current session
 retained for duration of current transaction or session
 stored in user’s sort space
 can overflow to sort segment in user’s temporary
tablespace

Indexes
 can be created against global temporary tables
 same scope and duration
© 2005 Julian Dyke
juliandyke.com
Global Temporary Tables
76

Can define triggers and views
 Views cannot join permanent and temporary tables

Benefits
 Reduction in DDL
 Reduction in amount of redo generated

DML statements against global temporary tables
 Generate undo
 Generate redo for undo
 Do not generate redo for block changes
© 2005 Julian Dyke
juliandyke.com
Global Temporary Tables

ON COMMIT DELETE ROWS
 rows are only visible to the current transaction
CREATE GLOBAL TEMPORARY TABLE temp1 (column1 NUMBER)
ON COMMIT DELETE ROWS;
INSERT INTO temp1 VALUES (1);
SELECT * FROM temp1;
Column1
1
COMMIT;
SELECT * FROM temp1;
No rows returned
77
© 2005 Julian Dyke
juliandyke.com
Global Temporary Tables

ON COMMIT PRESERVE ROWS
 rows are visible throughout current session
CREATE GLOBAL TEMPORARY TABLE temp2 (column1 NUMBER)
ON COMMIT PRESERVE ROWS;
INSERT INTO temp2 VALUES (1);
SELECT * FROM temp2;
Column1
1
COMMIT;
SELECT * FROM temp2;
Column1
1
78
© 2005 Julian Dyke
juliandyke.com
Guaranteed Undo Retention


Introduced in Oracle 10.1
To specify that unexpired undo should be preserved in all
undo segments even if this makes current operations
requiring undo space fail use
ALTER TABLESPACE tablespace_name
RETENTION GUARANTEE;

Only applies to undo tablespaces
Useful while flashback queries are running

To specify default behaviour use

ALTER TABLESPACE tablespace_name
RETENTION NOGUARANTEE;
79
© 2005 Julian Dyke
juliandyke.com
Enabling NOLOGGING



UNRECOVERABLE clause introduced in Oracle 7.3
NOLOGGING clause introduced in Oracle 8.0
Enabled at object level
ALTER TABLE table NOLOGGING ;

Can be used by
 SQL*Loader direct loads
 CREATE TABLE direct loads
 CREATE INDEX direct loads
 INSERT /*+ APPEND */
 INSERT LOB NOCACHE
CREATE INDEX index1 ON table1(column1) NOLOGGING;


80
Writes reduced redo
Cannot be recovered
© 2005 Julian Dyke
juliandyke.com
Disabling NOLOGGING

If NOLOGGING option is specified
 Limited redo is written
 Changes cannot be recovered
 Changes cannot be transported to standby database

In Oracle 9.2 and above NOLOGGING can be disabled
At database level

ALTER DATABASE FORCE LOGGING ;
ALTER DATABASE NO FORCE LOGGING;

At tablespace level
ALTER TABLESPACE tablespace_name FORCE LOGGING ;
ALTER TABLESPACE tablespace_name NO FORCE LOGGING;
81
© 2005 Julian Dyke
juliandyke.com
PL/SQL
82
© 2005 Julian Dyke
juliandyke.com
Native Dynamic SQL

Prior to Oracle 8.1.5 PL/SQL DDL statements were executed
using DBMS_SQL package
DECLARE
l_cursor INTEGER;
l_result INTEGER;
BEGIN
l_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (l_cursor,
‘ALTER SYSTEM SWITCH LOGFILE’,DBMS_SQL.V7);
l_result := DBMS_SQL.EXECUTE (l_cursor);
DBMS_SQL.CLOSE_CURSOR (l_cursor);
END;
83
© 2005 Julian Dyke
juliandyke.com
Native Dynamic SQL
In Oracle 8.1.5 and above EXECUTE IMMEDIATE can execute DDL
statements

EXECUTE IMMEDIATE 'CREATE TABLE tab1 (c1 NUMBER)';
EXECUTE IMMEDIATE 'ALTER TABLE tab1 ADD (c2 NUMBER)';
EXECUTE IMMEDIATE ‘DROP TABLE tab1’;

EXECUTE IMMEDIATE can also execute DML statements
EXECUTE IMMEDIATE 'INSERT INTO tab1 (c1,c2) VALUES (1,10)';
EXECUTE IMMEDIATE 'UPDATE tab1 SET c1 = 4 WHERE c2 = 10';
EXECUTE IMMEDIATE 'DELETE FROM tab1 WHERE c2 = 10';
84
© 2005 Julian Dyke
juliandyke.com
Native Dynamic SQL

EXECUTE IMMEDIATE can also be used for SELECT
statements

INTO clause is used to define fetch variables

For example
DECLARE
l_c1 NUMBER;
l_str VARCHAR2(1000);
BEGIN
l_str := 'SELECT c0l1 FROM tab1 WHERE c2 = 20';
EXECUTE IMMEDIATE l_str INTO l_c1;
DBMS_OUTPUT.PUT_LINE ('Result is ‘ || l_c1);
END;
85
© 2005 Julian Dyke
juliandyke.com
Native Dynamic SQL

EXECUTE IMMEDIATE can also use bind variables

USING clause is used to specify bind variable positions

For example
DECLARE
l_c1 NUMBER;
l_str VARCHAR2(1000);
BEGIN
l_str := 'SELECT c1 FROM t1 WHERE c2 = :p1';
EXECUTE IMMEDIATE l_str INTO l_c0l1 USING 10;
DBMS_OUTPUT.PUT_LINE ('Result is '||l_c1);
EXECUTE IMMEDIATE l_str INTO l_c0l1 USING 30;
DBMS_OUTPUT.PUT_LINE ('Result is '||l_c1);
END;
86
© 2005 Julian Dyke
juliandyke.com
Bulk Collect
87

Introduced in Oracle 8.1.5

Returns result set in single operation

Can be used with
 SELECT INTO
 FETCH INTO
 RETURNING INTO
© 2005 Julian Dyke
juliandyke.com
Bulk Collect - Example
DECLARE
-- 100000 row table
l_c3 NUMBER;
CURSOR c1 IS SELECT c3 FROM t1;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO l_c3;
-- 3.052 seconds
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
DECLARE
-- 100000 row table
TYPE NUMTYPE IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
l_c3 NUMTYPE;
CURSOR c1 IS SELECT c3 FROM t1;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO l_c3;
-- 0.119 seconds
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
88
© 2005 Julian Dyke
juliandyke.com
Bulk Collect Limit Clause

Bulk collect performance improves as optimum result set size
is approached

Thereafter bulk collect performance degrades as result set
grows

In Oracle 8.1.6 and above the number of rows returned by
FETCH INTO can be restricted using the LIMIT clause
FETCH c1 BULK COLLECT INTO l_c3 LIMIT 1000;
89
© 2005 Julian Dyke
juliandyke.com
FORALL
90

Introduced in Oracle 8.1.5

Sends INSERT, UPDATE or DELETE statements in batches

Can only repeat single DML statement

Works with PL/SQL collections including TABLE, VARRAY,
NESTED TABLE etc.

Much faster than equivalent for-loop

Limited functionality
© 2005 Julian Dyke
juliandyke.com
FORALL Example
DECLARE
TYPE NUMTYPE IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER;
TYPE NAMETYPE IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
l_c1 NUMTYPE;
l_c2 NAMETYPE;
l_c3 NUMTYPE;
BEGIN
FOR i IN 1..100000 LOOP
l_c1(i) := i;
l_c2(i) := LPAD (TO_CHAR (i),30,’0’);
l_c3(i) := MOD (i, 100);
END LOOP;
FOR i IN 1..100000 LOOP
-- FOR Loop – 28 seconds
INSERT INTO t1 VALUES (l_c1 (i), l_c2 (i), l_c3(i));
END LOOP;
FORALL f IN 1..100000 LOOP
-- FORALL Loop – 4 seconds
INSERT INTO t1 VALUES (l_c1 (i), l_c2 (i), l_c3(i));
END;
91
© 2005 Julian Dyke
juliandyke.com
FORALL Performance

Performance of FORALL statement degrades for very large
tables
Rows

92
FOR
FORALL
100000
28
4
1000000
240
360
Times in seconds
© 2005 Julian Dyke
juliandyke.com
Conclusion
93

Every release contains many new features and enhancements
that are not highlighted in the marketing material

Oracle assumes all customers will move on to new versions
when they are released

Study the New Features documentation when you upgrade
© 2005 Julian Dyke
juliandyke.com
Thank you for your interest
For more information and to provide feedback
please contact me
My e-mail address is:
[email protected]
My website address is:
www.juliandyke.com
94
© 2005 Julian Dyke
juliandyke.com