forgotten features l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Forgotten Features PowerPoint Presentation
Download Presentation
Forgotten Features

Loading in 2 Seconds...

play fullscreen
1 / 94

Forgotten Features - PowerPoint PPT Presentation


  • 184 Views
  • Uploaded on

Forgotten Features. Julian Dyke Independent Consultant. Web Version. juliandyke.com. © 2005 Julian Dyke. Introduction Forgotten Features Tracing and Auditing Testing and Benchmarking Administration Execution Plans Tables Indexes SQL PL/SQL Conclusion. Agenda.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Forgotten Features' - nenet


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
forgotten features

Forgotten Features

Julian Dyke

Independent Consultant

Web Version

juliandyke.com

©2005 Julian Dyke

agenda
Introduction

Forgotten Features

Tracing and Auditing

Testing and Benchmarking

Administration

Execution Plans

Tables

Indexes

SQL

PL/SQL

Conclusion

Agenda
criteria for an oracle feature
Criteria for an Oracle feature
  • 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
tracefile identifier
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

dbms system ksdwrt
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;/

bitand
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 ISBEGIN RETURN x + y - BITAND (x,y);END;/

CREATE OR REPLACE FUNCTION bitxor (x NUMBER, y NUMBER) RETURN NUMBER ISBEGIN RETURN BITOR (x,y) - BITAND (x,y);END;/

  • Beware of overflows
hexadecimal format masks
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;

  • returns 1048576
system triggers
System Triggers
  • Introduced in Oracle 8.1.5
  • Must be created by SYS (as SYSDBA)

CREATE OR REPLACE TRIGGER us01_logonAFTER LOGON ON us01.SCHEMABEGINdbms_session.set_sql_trace (TRUE);END;

CREATE OR REPLACE TRIGGER us01_logoffBEFORE LOGOFF ON us01.SCHEMABEGINdbms_session.set_sql_trace (FALSE);END;

ALTER TRIGGER us01_login ENABLE;

ALTER TRIGGER us01_login DISABLE;

dbms monitor
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 );

dbms monitor11
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 );

autonomous transactions
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 trigger1BEFORE INSERT ON table1 FOR EACH ROWDECLARE PRAGMA AUTONOMOUS_TRANSACTION;BEGIN INSERT INTO log1 VALUES (:new.col1,:new.col2,SYSDATE); COMMIT;END;/

sys auditing
SYS Auditing
  • 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>.audwhere pid is the operating system process ID
fixed date
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

random functions
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’;

checkpoints and logfiles
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;

  • Useful for testing archive log creation with
    • Physical standby database
    • Logical standby database
flushing the shared pool
Flushing the Shared Pool
  • Introduced in Oracle 8.0
  • Flushes all unpinned objects from library cache

ALTER SYSTEM FLUSH SHARED_POOL;

  • Useful for deterministic testing but…
  • After issuing this statement
    • All unpinned cursors need reparsing
    • All unpinned packages need recompilation
flushing the buffer cache
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’;

  • Useful for deterministic testing but…
  • After issuing this statement
    • Warm up the cache before testing workloads
v sql plan
V$SQL_PLAN
  • 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
v sql plan22

BYTES NUMBEROTHER_TAG VARCHAR(35)PARTITION_START VARCHAR2(5)PARTITION_STOP VARCHAR2(5)PARTITION_ID NUMBEROTHER VARCHAR2(4000)DISTRIBUTION VARCHAR2(20)CPU_COST NUMBERIO_COST NUMBERTEMP_SPACE NUMBERACCESS_PREDICATES VARCHAR2(4000)FILTER_PREDICATES VARCHAR2(4000)PROJECTION VARCHAR2(4000)TIME NUMBERQBLOCK_NAME VARCHAR2(31)REMARKS VARCHAR2(4000)BYTES NUMBEROTHER_TAG VARCHAR(35)PARTITION_START VARCHAR2(5)PARTITION_STOP VARCHAR2(5)PARTITION_ID NUMBEROTHER VARCHAR2(4000)DISTRIBUTION VARCHAR2(20)CPU_COST NUMBERIO_COST NUMBERTEMP_SPACE NUMBERACCESS_PREDICATES VARCHAR2(4000)FILTER_PREDICATES VARCHAR2(4000)PROJECTION VARCHAR2(4000)TIME NUMBERQBLOCK_NAME VARCHAR2(31)REMARKS VARCHAR2(4000)

V$SQL_PLAN

ADDRESS RAW(4)HASH_VALUE NUMBERCHILD_NUMBER NUMBEROPERATION VARCHAR2(30)OPTIONS VARCHAR2(30)OBJECT_NODE VARCHAR2(10)OBJECT# NUMBEROBJECT_OWNER VARCHAR2(30)OBJECT_NAME VARCHAR2(64)OPTIMIZER VARCHAR2(20)ID NUMBERPARENT_ID NUMBERDEPTH NUMBERPOSITION NUMBERCOST NUMBERCARDINALITY NUMBER

optimizer environment variables
Optimizer Environment Variables
  • In Oracle 10.1 and above, optimizer environment variables are externalized at :
    • instance level - V$SYS_OPTIMIZER_ENV
    • session level - V$SES_OPTIMIZER_ENV
    • statement level - V$SQL_OPTIMIZER_ENV
  • Use the values in these views when determining why execution plans differ
optimizer environment variables24
Optimizer Environment Variables
  • Optimizer Environment Variable values reported by the dynamic performance views include:
dbms xplan
DBMS_XPLAN
  • Introduced in Oracle 9.2
  • Formats PLAN_TABLE contents generated by EXPLAIN PLAN

SELECT * FROM TABLE (dbms_xplan.display);

  • DISPLAY function parameters include
    • TABLE_NAME – name of plan table
    • STATEMENT_ID – statement ID in plan table
    • FORMAT – as below
dbms xplan26
DBMS_XPLAN
  • For example explain a query

EXPLAIN PLAN FOR SET STATEMENT_ID = 'STATEMENT1' FORSELECT t1.c2, t2.c2FROM t1, t2WHERE t1.c1 = t2.c1AND t1.c2 = 10;

  • The plan table can be queried using

SELECT * FROM TABLE ( dbms_xplan.display ('PLAN_TABLE','STATEMENT1'));

dbms xplan27
DBMS_XPLAN
  • Example output with predicates

Predicate Information (identified by operation id):

1 - access("T1"."C1"="T2"."C1")2 - filter("T1"."C2"=10)

dbms xplan28
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.c2FROM t1, t2WHERE t1.c1 = t2.c1AND t1.c2 = 10;

  • The plan table can be queried using

SELECT * FROM TABLE (dbms_xplan.display);

dbms xplan29
DBMS_XPLAN
  • Example output for parallel execution

Predicate Information (identified by operation id):

3 - filter("T1"."C2"=10)4 - access("T1"."C1"="T2"."C1") filter("T1"."C1"="T2"."C1")

dbms xplan30
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);

dbms xplan31
DBMS_XPLAN
  • Example output for partition pruning

Predicate Information (identified by operation id):

2 - filter("T1"."C1">=150 AND "T1"."C1"<250)

dbms xplan32
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);

v session wait history
V$SESSION_WAIT_HISTORY
  • Introduced in Oracle 10.1

SID NUMBERSEQ# NUMBEREVENT NUMBEREVENT VARCHAR2(64)P1TEXT VARCHAR2(64)P1 NUMBERP2TEXT VARCHAR2(64)P2 NUMBERP3TEXT VARCHAR2(64)P3 NUMBERWAIT_TIME NUMBERWAIT_COUNT NUMBER

  • Externalises last 10 wait events for each session
  • Similar information to V$SESSION_WAIT – but much more user friendly
kill session
Kill Session
  • For example to kill a session for user US01
    • Identify the SID and serial number

SELECT sid, serial# FROM v$sessionWHERE 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

renaming database objects
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;

dropping columns
Dropping Columns
  • 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
dropping columns38
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;

dropping columns39
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;

  • DBA_PARTIAL_DROP_TABS describes partially dropped columns
  • DBA_UNUSED_COL_TABS describes columns marked unused, but not yet dropped
default tablespaces
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>;

resumable statements
Resumable Statements
  • 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
resumable statements42
Resumable Statements
  • 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
resumable statements43
Resumable Statements

1 – Resumable space allocation is enabled

ALTER SESSION ENABLE RESUMABLENAME ‘Batch Update’ TIMEOUT 3600;

2 – Resumable operation starts

INSERT INTO t2SELECT * 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

resumable statements44
Resumable Statements

5 – (Optional) DBA is notified by message generated by AFTER SUSPEND trigger6 – DBA queries DBA_RESUMABLE view for details of suspended operation7 – DBA fixes error condition e.g.

ALTER TABLESPACE TS99ADD DATAFILE <filename> SIZE <size>;

8 – Suspended operation resumes automatically

9 – Operation completes successfully

resumable statements45
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;

automatic datafile deletion
Automatic Datafile Deletion
  • In Oracle 9.0.1 and above, DROP TABLESPACE has been extended to optionally delete its datafiles

DROP TABLESPACE tablespace_nameINCLUDING CONTENTS AND DATAFILES;

  • The DROP TABLESPACE command automatically deletes Oracle Managed Files
database level backups
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;

row movement
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

  • 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
row movement50
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;

external tables
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
external tables52
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’));

external tables53
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
data segment compression
Data Segment Compression
  • 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
data segment compression55
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;

  • Compression can also be specified for
    • range and list partitioned tables
    • materialized views
    • nested tables
data segment compression56
Data Segment Compression
  • 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
data segment compression57

2002

2001

2000

1999

Row Data

4

Mika Hakkinen

1998

1997

1996

Olivier Panis

Ligier

1995

1994

Ayrton Senna

4

1993

4

McLaren

4

SymbolTable

2

Benetton

3

5

2

Michael Schumacher

3

Ferrari

4

1

3

1

3

1

4

2

2

0

2

2

2

0

1

2

David Coulthard

0

Data Segment Compression

Monaco Grand PrixWinners 1993-2002

data segment compression58
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
data segment compression59
Data Segment Compression
  • Test1 – Loading the SALES table from a flat file into an empty table
  • Test2 – Selecting one column from all rows in the compressed table

SELECT SUM (quantity_sold) FROM sales;

index leaf compression
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;

  • 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
index leaf compression62

Suffix Slot Array

1600

1400

1300

1200

1000

900

6

1500

1

1100

1700

4

Prefix Slot Array

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

Prefix Row

1600

Paris

Suffix Row

1700

France

Prefix Row

Index Leaf Compression

CREATE INDEX i1ON TABLE t1 (country, city)COMPRESS 1;

index leaf compression63
Index Leaf Compression
  • Number and size of prefixed rows can be verified using

ANALYZE INDEX i1 VALIDATE STRUCTURE;SELECT * FROM index_stats;

  • 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

index leaf compression64
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 INDEXCOMPRESS 2;

  • To rebuild existing IOT

ALTER TABLE t1 MOVE COMPRESS 1;

index rebuild online
Index Rebuild Online
  • 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
index rebuild online66
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
analyze index online
ANALYZE INDEX ONLINE
  • Prior to Oracle 9.0.1, index structure can be analyzed using

ANALYZE INDEX index_nameVALIDATE 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_nameVALIDATE STRUCTURE ONLINE;

  • DML statements unaffected
index monitoring
Index Monitoring
  • 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
index monitoring69
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;

index monitoring70
Index Monitoring
  • Execute all possible statements against the table

SELECT /*+ INDEX (table1 index1) */ * FROM table1WHERE col1 = 0;

  • Check which indexes have been used using

SELECT index_name,used FROM v$object_usage;

  • Disable index monitoring using

ALTER INDEX index1 NOMONITORING USAGE;ALTER INDEX index2 NOMONITORING USAGE;

create index nosort
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

create index compute statistics
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
global index maintenance
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
  • For example

ALTER TABLE table1DROP PARTITION table1_p1UPDATE GLOBAL INDEXES;

global temporary tables
Global Temporary Tables
  • 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
global temporary tables76
Global Temporary Tables
  • 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
global temporary tables77
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;

Column11

COMMIT;

SELECT * FROM temp1;

No rows returned

global temporary tables78
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;

Column11

COMMIT;

SELECT * FROM temp2;

Column11

guaranteed undo retention
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_nameRETENTION GUARANTEE;

  • Only applies to undo tablespaces
  • Useful while flashback queries are running
  • To specify default behaviour use

ALTER TABLESPACE tablespace_nameRETENTION NOGUARANTEE;

enabling nologging
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;

  • Writes reduced redo
  • Cannot be recovered
disabling nologging
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;

native dynamic sql
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;

native dynamic sql84
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';

native dynamic sql85
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;

native dynamic sql86
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;

bulk collect
Bulk Collect
  • Introduced in Oracle 8.1.5
  • Returns result set in single operation
  • Can be used with
    • SELECT INTO
    • FETCH INTO
    • RETURNING INTO
bulk collect example
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;

bulk collect limit clause
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;

forall
FORALL
  • 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
forall example
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;

forall performance
FORALL Performance
  • Performance of FORALL statement degrades for very large tables
  • Times in seconds
conclusion
Conclusion
  • 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
thank you for your interest
Thank you for your interest

For more information and to provide feedback please contact me

My e-mail address is:

info@juliandyke.com

My website address is:

www.juliandyke.com