Forgotten Features Julian Dyke Independent Consultant Web Version
Forgotten Features Julian Dyke Independent Consultant Web Version © 2005 Julian Dyke juliandyke. com
Agenda u u u 2 © 2005 Julian Dyke Introduction Forgotten Features u Tracing and Auditing u Testing and Benchmarking u Administration u Execution Plans u Tables u Indexes u SQL u PL/SQL Conclusion juliandyke. com
Criteria for an Oracle feature 3 u Easy to understand u Easy to implement u Works in first release u Documented in first release u Compatible with other features u Improves Productivity or Manageability u Saves resources / money u Intellectually stimulating © 2005 Julian Dyke juliandyke. com
Tracing and Auditing 4 © 2005 Julian Dyke juliandyke. com
TRACEFILE_IDENTIFIER u u Initialisation Parameter Introduced in Oracle 8. 1. 7 tracefile_identifier = '<identifier>' u e. g. in Oracle 9. 2 if a trace file is called ss 92001_ora_1760. trc u then the statement ALTER SESSION SET tracefile_identifier = 'test'; u will change the file name to ss 92001_ora_1760_test. trc 5 © 2005 Julian Dyke juliandyke. com
DBMS_SYSTEM. KSDWRT u DBMS_SYSTEM u undocumented package u installed in all databases u owned by SYS u 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 VARCHAR 2 -- Message ); u 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 u u Initially undocumented built-in function Performs a bit-wise AND between two operators SELECT BITAND (42, 1) FROM dual; u 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; / u 7 Beware of overflows © 2005 Julian Dyke juliandyke. com
Hexadecimal Format Masks u u u Introduced in Oracle 8. 1. 5 Convert decimal numbers to and from hexadecimal To convert from decimal to hex SELECT TO_CHAR (1048576, ’XXXX’) FROM dual; u returns 100000 u To convert from hex to decimal SELECT TO_NUMBER (100000, ‘XXXX’) FROM dual; u 8 returns 1048576 © 2005 Julian Dyke juliandyke. com
System Triggers u u Introduced in Oracle 8. 1. 5 Must be created by SYS (as SYSDBA) CREATE OR REPLACE TRIGGER us 01_logon AFTER LOGON ON us 01. SCHEMA BEGIN dbms_session. set_sql_trace (TRUE); END; CREATE OR REPLACE TRIGGER us 01_logoff BEFORE LOGOFF ON us 01. SCHEMA BEGIN dbms_session. set_sql_trace (FALSE); END; ALTER TRIGGER us 01_login ENABLE; ALTER TRIGGER us 01_login DISABLE; 9 © 2005 Julian Dyke juliandyke. com
DBMS_MONITOR u u 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 ); u Waits (event 10046 level 8) are enabled by default Binds (event 10046 level 4) are disabled by default u To disable trace in another session use u DBMS_MONITOR. SESSION_TRACE_DISABLE ( SESSION_ID NUMBER, -- SID SERIAL_NUM NUMBER -- Serial Number ); 10 © 2005 Julian Dyke juliandyke. com
DBMS_MONITOR u u 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 VARCHAR 2 -- Instance Name ); u To disable trace for all database sessions use DBMS_MONITOR. DATABASE_TRACE_DISABLE ( INSTANCE_NAME VARCHAR 2 -- Instance Name ); 11 © 2005 Julian Dyke juliandyke. com
Autonomous Transactions u u u 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 trigger 1 BEFORE INSERT ON table 1 FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO log 1 VALUES (: new. col 1, : new. col 2, SYSDATE); COMMIT; END; / 12 © 2005 Julian Dyke juliandyke. com
SYS Auditing 13 u In Oracle 9. 2 and above, operations performed by the SYS user can be audited. u To enable SYS auditing set AUDIT_SYS_OPERATIONS parameter to TRUE u Auditing information will be written to text file in directory specified by AUDIT_DUMP_DEST parameter u Default directory is $ORACLE_HOME/rdbms/audit u 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 u u 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’ u Sets time to 00: 00 u To set date and time use FIXED_DATE = YYYY-MM-DD-HH 24: MI: SS 15 © 2005 Julian Dyke juliandyke. com
Random Functions u To guarantee consistent tests, random functions should return deterministic results u DBMS_RANDOM package u Seed can be specified using DBMS_RANDOM. SEED procedure u SAMPLE clause u Can be made deterministic by enabling event 10193 u Level specifies seed ALTER SESSION SET EVENTS ‘ 10193 trace name context forever, level 42’; 16 © 2005 Julian Dyke juliandyke. com
Checkpoints and Logfiles u To force a checkpoint ALTER SYSTEM CHECKPOINT; u To force a log file switch ALTER SYSTEM SWITCH LOGFILE; u Useful when dumping log files u To force a log file switch and archive the log file ALTER SYSTEM ARCHIVE LOG CURRENT; u 17 Useful for testing archive log creation with u Physical standby database u Logical standby database © 2005 Julian Dyke juliandyke. com
Flushing the Shared Pool u u Introduced in Oracle 8. 0 Flushes all unpinned objects from library cache ALTER SYSTEM FLUSH SHARED_POOL; u u 18 Useful for deterministic testing but… After issuing this statement u All unpinned cursors need reparsing u All unpinned packages need recompilation © 2005 Julian Dyke juliandyke. com
Flushing the Buffer Cache u u Introduced in Oracle 10. 1 Flushes all unpinned buffers from the buffer cache ALTER SYSTEM FLUSH BUFFER_CACHE; u In Oracle 9. 0. 1 and above the following command has the same effect ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME FLUSH_CACHE’; u u 19 Useful for deterministic testing but… After issuing this statement u Warm up the cache before testing workloads © 2005 Julian Dyke juliandyke. com
Performance Tuning 20 © 2005 Julian Dyke juliandyke. com
V$SQL_PLAN 21 u Introduced in Oracle 9. 0. 1 u Shows actual execution plan in memory u Enhanced in Oracle 9. 2 to include u Access Predicates (Joins) u Filter Predicates u Related views include u V$SQL_PLAN_WORKAREA u 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 VARCHAR 2(30) VARCHAR 2(10) NUMBER VARCHAR 2(30) VARCHAR 2(64) VARCHAR 2(20) 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) VARCHAR 2(5) NUMBER VARCHAR 2(4000) VARCHAR 2(20) NUMBER VARCHAR 2(4000) NUMBER VARCHAR 2(31) VARCHAR 2(4000) juliandyke. com
Optimizer Environment Variables u In Oracle 10. 1 and above, optimizer environment variables are externalized at : u u 23 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 © 2005 Julian Dyke juliandyke. com
Optimizer Environment Variables u 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 u u Introduced in Oracle 9. 2 Formats PLAN_TABLE contents generated by EXPLAIN PLAN SELECT * FROM TABLE (dbms_xplan. display); u 25 DISPLAY function parameters include u TABLE_NAME – name of plan table u STATEMENT_ID – statement ID in plan table u 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 u For example explain a query EXPLAIN PLAN FOR SET STATEMENT_ID = 'STATEMENT 1' FOR SELECT t 1. c 2, t 2. c 2 FROM t 1, t 2 WHERE t 1. c 1 = t 2. c 1 AND t 1. c 2 = 10; u The plan table can be queried using SELECT * FROM TABLE ( dbms_xplan. display ('PLAN_TABLE', 'STATEMENT 1') ); 26 © 2005 Julian Dyke juliandyke. com
DBMS_XPLAN u Example output with predicates Id Operation Name 0 SELECT STATEMENT *1 HASH JOIN Rows Bytes Cost 10 590 66 *2 TABLE ACCESS FULL T 1 10 60 58 3 TABLE ACCESS FULL T 2 1000 53000 7 Predicate Information (identified by operation id): 1 - access("T 1". "C 1"="T 2". "C 1") 2 - filter("T 1". "C 2"=10) 27 © 2005 Julian Dyke juliandyke. com
DBMS_XPLAN u Parallel execution queries are automatically formatted e. g. EXPLAIN PLAN FOR SELECT /*+ ORDERED PARALLEL (t 1 2) USE_MERGE (t 1 t 2) */ t 1. c 2, t 2. c 2 FROM t 1, t 2 WHERE t 1. c 1 = t 2. c 1 AND t 1. c 2 = 10; u The plan table can be queried using SELECT * FROM TABLE (dbms_xplan. display); 28 © 2005 Julian Dyke juliandyke. com
DBMS_XPLAN u 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 T 1 SORT JOIN TABLE ACCESS FULL 1000 53000 T 2 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("T 1". "C 2"=10) access("T 1". "C 1"="T 2". "C 1") filter("T 1". "C 1"="T 2". "C 1") © 2005 Julian Dyke juliandyke. com
DBMS_XPLAN u Partition pruning information can also be included e. g. for a range partitioned table CREATE TABLE t 1 (c 1 NUMBER, c 2 NUMBER, c 3 CHAR(50)) PARTITION BY RANGE (c 1) ( PARTITION p 1 VALUES LESS THAN (100), PARTITION p 2 VALUES LESS THAN (200), PARTITION p 3 VALUES LESS THAN (300), PARTITION p 4 VALUES LESS THAN (400) ); EXPLAIN PLAN FOR SELECT c 2 FROM t 1 WHERE c 1 >= 150 AND c 1 < 250; SELECT * FROM TABLE (dbms_xplan. display); 30 © 2005 Julian Dyke juliandyke. com
DBMS_XPLAN u Example output for partition pruning Id Operation Name Rows Bytes Cost PStart PStop 0 SELECT STATEMENT 1 *2 1 26 2 PARTITION RANGE ITERATOR TABLE ACCESS FULL T 1 1 26 2 2 3 Predicate Information (identified by operation id): 2 - 31 filter("T 1". "C 1">=150 AND "T 1". "C 1"<250) © 2005 Julian Dyke juliandyke. com
DBMS_XPLAN u In Oracle 10. 1 and above u New DISPLAY_CURSOR function u By default displays plan for last statement executed in session SQL> SELECT COUNT(*) FROM t 1; 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 T 1 00: 02 juliandyke. com
V$SESSION_WAIT_HISTORY u Introduced in Oracle 10. 1 SID SEQ# EVENT P 1 TEXT P 1 P 2 TEXT P 2 P 3 TEXT P 3 WAIT_TIME WAIT_COUNT u u 33 NUMBER VARCHAR 2(64) 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 u For example to kill a session for user US 01 u Identify the SID and serial number SELECT sid, serial# FROM v$session WHERE username = ‘US 01’; u Kill the session using ALTER SYSTEM KILL SESSION ‘<sid>, <serial#>’; u For example ALTER SYSTEM KILL SESSION ‘ 133, 523’; u 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 u To rename a table: RENAME oldname TO newname; u To rename an index ALTER INDEX oldname RENAME TO newname; u In Oracle 9. 2 and above to rename a column ALTER TABLE t 1 RENAME COLUMN oldname TO newname; u In Oracle 9. 2 and above to rename a constraint ALTER TABLE t 1 RENAME CONSTRAINT oldname TO newname; u 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 u Introduced in 8. 1. 5 u Columns can be dropped from a table using the ALTER TABLE statement u Columns can be u marked unused immediately and deleted at a later time u deleted immediately u If the delete operation fails at any point it can be restarted from the point of failure © 2005 Julian Dyke juliandyke. com
Dropping Columns u To drop a column immediately use ALTER TABLE table 1 DROP COLUMN column 2; u u Columns can also be marked unused immediately and subsequently dropped To mark a column unused use ALTER TABLE table 1 SET UNUSED COLUMN column 2; u To drop all unused columns from a table use ALTER TABLE table 1 DROP UNUSED COLUMNS; 38 © 2005 Julian Dyke juliandyke. com
Dropping Columns u If instance is shutdown while column is being dropped, drop column statement can be continued when instance restarted ALTER TABLE table 1 DROP COLUMNS CONTINUE; 39 u DBA_PARTIAL_DROP_TABS describes partially dropped columns u DBA_UNUSED_COL_TABS describes columns marked unused, but not yet dropped © 2005 Julian Dyke juliandyke. com
Default Tablespaces u In Oracle 9. 0. 1 and above a default temporary tablespace can be defined ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <tablespace>; u 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 u Oracle 9. 0. 1 and above long running operations encountering out of space errors can be resumed u Resumable operations include u Queries u DML Statements u SQL*Loader operations u Import operations u DDL statements u Out of space errors include u Tablespace full u Maximum number of extents reached for object u Tablespace quota exceeded for a user © 2005 Julian Dyke juliandyke. com
Resumable Statements u 42 When resumable space allocation is enabled u Operation suspends if an out of space error occurs u Details of the error are written to DBA_RESUMABLE u DBA can optionally be alerted u DBA can either u fix the error u abort the operation u Operation automatically resumes execution when error is fixed u 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 t 2 SELECT * FROM t 1; 3 – Out of space error occurs ORA-01653: unable to extend table US 01. T 2 by 210 in tablespace ‘TS 99’ 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 TS 99 ADD DATAFILE <filename> SIZE <size>; 8 – Suspended operation resumes automatically 9 – Operation completes successfully 44 © 2005 Julian Dyke juliandyke. com
Resumable Statements u In Oracle 10. 1 and above resumable statements can be enabled at instance level ALTER SYSTEM SET resumable_timeout = <timeout>; u Resumable statements can be disabled at instance level using ALTER SYSTEM SET resumable_timeout = 0; u Resumable statements can be enabled at session level using ALTER SESSION SET resumable_timeout = <timeout>; u Resumable statements can be disabled at session level using ALTER SESSION SET resumable_timeout = 0; 45 © 2005 Julian Dyke juliandyke. com
Automatic Datafile Deletion u 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; u 46 The DROP TABLESPACE command automatically deletes Oracle Managed Files © 2005 Julian Dyke juliandyke. com
Database Level Backups u 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; u Useful with three-way mirror or snapshot backups u 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 u ORA-14402: updating partition key column would cause a partition change 49 u In Oracle 8. 1. 5 and above row movement can be enabled u a row may be migrated to another partition if its partition key columns are updated u By default row movement is disabled © 2005 Julian Dyke juliandyke. com
Row Movement u Row movement can be enabled when the partitioned table is created e. g. CREATE TABLE table 1 ( column 1 NUMBER, column 2 NUMBER ) PARTITION BY …. ENABLE ROW MOVEMENT; u Row movement can be also be enabled and disabled from an existing table e. g. ALTER TABLE table 1 ENABLE ROW MOVEMENT; ALTER TABLE table 1 DISABLE ROW MOVEMENT; 50 © 2005 Julian Dyke juliandyke. com
External Tables u External tables u read-only tables u definition is stored in data dictionary u data is stored outside the database in operating system flat files u can be queried using SQL u can be queried in parallel u can be included in DML statement subqueries u No DML operations are allowed on external table No indexes can be created on external table u 51 © 2005 Julian Dyke juliandyke. com
External Tables u Example CREATE DIRECTORY ext_dir AS '/v 01/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 u u The following tests were based on a 1, 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 No. 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 u Introduced in Oracle 9. 2 u Data is u compressed when it is written to a block u decompressed when it is read from the block u Compression is applied at block level u Blocks will only be compressed if u data is sufficiently large to fill the block u rows have low enough cardinality u Columns can be reordered within each block to achieve optimal compression ratios u A segment may contain compressed and uncompressed blocks © 2005 Julian Dyke juliandyke. com
Data Segment Compression u Compression can be specified for new tables CREATE TABLE t 1 (c 01 NUMBER, c 02 VARCHAR 2(30)) COMPRESS; u Compression can also be specified for existing tables ALTER TABLE t 2 COMPRESS; u Existing tables can be compressed using ALTER TABLE t 3 MOVE COMPRESS; u 55 Compression can also be specified for u range and list partitioned tables u materialized views u nested tables © 2005 Julian Dyke juliandyke. com
Data Segment Compression 56 u Only works with direct path inserts u For example u CREATE TABLE AS SELECT u INSERT /*+ APPEND */ u ALTER TABLE MOVE u Materialized View Refresh u SQL*Loader u Online Reorganization u Does not work with u INSERT /*+ NOAPPEND */ u UPDATE u 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 Olivier Panis Michael Schumacher Mika Hakkinen Michael Schumacher David Coulthard Team Mc. Laren Benetton Ligier Ferrari Mc. Laren Monaco Grand Prix Winners 1993 -2002 57 © 2005 Julian Dyke 4 0 2002 1 2 2001 4 0 1 2 2000 1999 4 Mika Hakkinen 1 2 Ligier 1998 1997 Olivier Panis Row Data 1996 3 2 4 Ayrton Senna 4 4 Mc. Laren 3 2 Benetton 2 1 5 Michael Schumacher 3 Ferrari 0 2 David Coulthard 1995 1994 1993 Symbol Table juliandyke. com
Data Segment Compression u Compression ratios vary with u number of rows u number of columns u cardinality of rows u For example – loading SALES table from sales history demo schema $ORACLE_HOME/demo/schema/sales_history u 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 u u Test 1 – 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 Test 2 – 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 u Introduced in Oracle 8. 1. 5 u Compresses leading columns in index u Must be specified when index is created or rebuilt CREATE INDEX i 1 ON t 1 (c 1, c 2, c 3) COMPRESS 2; ALTER INDEX i 1 REBUILD ONLINE COMPRESS 1; u 61 Benefits u Reduces number of blocks to hold index u Reduction in physical I/O for scans u Improved cache efficiency u Potential reduction in index height u 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 i 1 ON TABLE t 1 (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 u Number and size of prefixed rows can be verified using ANALYZE INDEX i 1 VALIDATE STRUCTURE; SELECT * FROM index_stats; u 63 Useful INDEX_STATS columns include u PRE_ROWS Number of prefix rows u PRE_ROWS_LEN Sum of lengths of all prefix rows u OPT_CMPR_COUNT Optimal key compression length u OPT_CMPR_PCTSAVE Space saved by implementing optimal key length © 2005 Julian Dyke juliandyke. com
Index Leaf Compression u Can also be used with IOTs CREATE TABLE t 1 ( c 1 NUMBER, c 2 NUMBER, c 3 NUMBER, c 4 NUMBER CONSTRAINT pk 1 PRIMARY KEY (c 1, c 2, c 3) ) ORGANIZATION INDEX COMPRESS 2; u To rebuild existing IOT ALTER TABLE t 1 MOVE COMPRESS 1; 64 © 2005 Julian Dyke juliandyke. com
Index Rebuild Online 65 u Introduced in 8. 1. 5 u create or rebuild index whilst allowing concurrent DML operations u Works with u B*tree indexes (non-partitioned and partitioned) – 8. 1. 5 u IOTs (non-partitioned and partitioned) – 8. 1. 5 u Reverse key indexes – 9. 0. 1 u Function-based indexes – 9. 0. 1 u Compressed indexes – 9. 0. 1 u IOT Secondary indexes – 9. 0. 1 © 2005 Julian Dyke juliandyke. com
Index Rebuild Online u To build an index online use ALTER INDEX index 1 REBUILD ONLINE; u. Can be executed in parallel u. Three phases uprepare phase u index created u data dictionary updated ubuild phase u index populated u changes written to journal table umerge phase urows in journal table are merged 66 © 2005 Julian Dyke juliandyke. com
ANALYZE INDEX ONLINE u Prior to Oracle 9. 0. 1, index structure can be analyzed using ANALYZE INDEX index_name VALIDATE STRUCTURE; u No DML possible against index while it is being analyzed u In Oracle 9. 0. 1 and above indexes can be analyzed online ANALYZE INDEX index_name VALIDATE STRUCTURE ONLINE; u 67 DML statements unaffected © 2005 Julian Dyke juliandyke. com
Index Monitoring 68 u In Oracle 9. 0. 1 indexes can be monitored to determine if they are being used u 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 u Indexes are only monitored at parse time u Only SELECT statements and subqueries monitored u Also works for bitmap indexes © 2005 Julian Dyke juliandyke. com
Index Monitoring u This example assumes the following definitions CREATE TABLE table 1 (col 1 NUMBER, col 2 NUMBER); CREATE INDEX index 1 ON table 1 (col 1); CREATE INDEX index 2 ON table 1 (col 2); u Enable index monitoring using ALTER INDEX index 1 MONITORING USAGE; ALTER INDEX index 2 MONITORING USAGE; u To check which indexes are being monitored use SELECT index_name, monitoring FROM v$object_usage; 69 © 2005 Julian Dyke INDEX_NAME MONITORING INDEX 1 YES INDEX 2 YES juliandyke. com
Index Monitoring u Execute all possible statements against the table SELECT /*+ INDEX (table 1 index 1) */ * FROM table 1 WHERE col 1 = 0; u Check which indexes have been used using SELECT index_name, used FROM v$object_usage; u INDEX_NAME USED INDEX 1 YES INDEX 2 NO Disable index monitoring using ALTER INDEX index 1 NOMONITORING USAGE; ALTER INDEX index 2 NOMONITORING USAGE; 70 © 2005 Julian Dyke juliandyke. com
Create Index NOSORT u Specify the NOSORT option to avoid sorting index columns when creating an index CREATE INDEX index 1 ON table 1 (column 1) NOSORT u 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 u u Introduced in Oracle 8. 1. 5 To compute statistics when creating an index use: CREATE INDEX index 1 ON table 1 (column 1) COMPUTE STATISTICS; u To compute statistics when rebuilding an index use: ALTER INDEX index 1 REBUILD COMPUTE STATISTICS; u Statistics will be computed (not estimated) u 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 u u 72 © 2005 Julian Dyke juliandyke. com
Global Index Maintenance u In Oracle 9. 0. 1 and above UPDATE GLOBAL INDEXES can be specified for DDL statements on partitioned tables u Global indexes remain available during the operation u Updates to the global index are logged u UPDATE GLOBAL INDEXES can be used with the following partition DDL statements u ADD SPLIT DROP MERGE MOVE EXCHANGE TRUNCATE COALESCE For example ALTER TABLE table 1 DROP PARTITION table 1_p 1 UPDATE GLOBAL INDEXES; 73 © 2005 Julian Dyke juliandyke. com
Undo and Redo 74 © 2005 Julian Dyke juliandyke. com
Global Temporary Tables 75 u Introduced in Oracle 8. 1. 5 u Defined in data dictionary – definition available to all sessions u Data u only visible to current session u retained for duration of current transaction or session u stored in user’s sort space u can overflow to sort segment in user’s temporary tablespace u Indexes u can be created against global temporary tables u same scope and duration © 2005 Julian Dyke juliandyke. com
Global Temporary Tables 76 u Can define triggers and views u Views cannot join permanent and temporary tables u Benefits u Reduction in DDL u Reduction in amount of redo generated u DML statements against global temporary tables u Generate undo u Generate redo for undo u Do not generate redo for block changes © 2005 Julian Dyke juliandyke. com
Global Temporary Tables u ON COMMIT DELETE ROWS u rows are only visible to the current transaction CREATE GLOBAL TEMPORARY TABLE temp 1 (column 1 NUMBER) ON COMMIT DELETE ROWS; INSERT INTO temp 1 VALUES (1); SELECT * FROM temp 1; Column 1 1 COMMIT; SELECT * FROM temp 1; No rows returned 77 © 2005 Julian Dyke juliandyke. com
Global Temporary Tables u ON COMMIT PRESERVE ROWS u rows are visible throughout current session CREATE GLOBAL TEMPORARY TABLE temp 2 (column 1 NUMBER) ON COMMIT PRESERVE ROWS; INSERT INTO temp 2 VALUES (1); SELECT * FROM temp 2; Column 1 1 COMMIT; SELECT * FROM temp 2; Column 1 1 78 © 2005 Julian Dyke juliandyke. com
Guaranteed Undo Retention u u 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; u Only applies to undo tablespaces Useful while flashback queries are running u To specify default behaviour use u ALTER TABLESPACE tablespace_name RETENTION NOGUARANTEE; 79 © 2005 Julian Dyke juliandyke. com
Enabling NOLOGGING u u u UNRECOVERABLE clause introduced in Oracle 7. 3 NOLOGGING clause introduced in Oracle 8. 0 Enabled at object level ALTER TABLE table NOLOGGING ; u Can be used by u SQL*Loader direct loads u CREATE TABLE direct loads u CREATE INDEX direct loads u INSERT /*+ APPEND */ u INSERT LOB NOCACHE CREATE INDEX index 1 ON table 1(column 1) NOLOGGING; u u 80 Writes reduced redo Cannot be recovered © 2005 Julian Dyke juliandyke. com
Disabling NOLOGGING u If NOLOGGING option is specified u Limited redo is written u Changes cannot be recovered u Changes cannot be transported to standby database u In Oracle 9. 2 and above NOLOGGING can be disabled At database level u ALTER DATABASE FORCE LOGGING ; ALTER DATABASE NO FORCE LOGGING; u 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 u 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. V 7); 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 u EXECUTE IMMEDIATE 'CREATE TABLE tab 1 (c 1 NUMBER)'; EXECUTE IMMEDIATE 'ALTER TABLE tab 1 ADD (c 2 NUMBER)'; EXECUTE IMMEDIATE ‘DROP TABLE tab 1’; u EXECUTE IMMEDIATE can also execute DML statements EXECUTE IMMEDIATE 'INSERT INTO tab 1 (c 1, c 2) VALUES (1, 10)'; EXECUTE IMMEDIATE 'UPDATE tab 1 SET c 1 = 4 WHERE c 2 = 10'; EXECUTE IMMEDIATE 'DELETE FROM tab 1 WHERE c 2 = 10'; 84 © 2005 Julian Dyke juliandyke. com
Native Dynamic SQL u EXECUTE IMMEDIATE can also be used for SELECT statements u INTO clause is used to define fetch variables u For example DECLARE l_c 1 NUMBER; l_str VARCHAR 2(1000); BEGIN l_str : = 'SELECT c 0 l 1 FROM tab 1 WHERE c 2 = 20'; EXECUTE IMMEDIATE l_str INTO l_c 1; DBMS_OUTPUT. PUT_LINE ('Result is ‘ || l_c 1); END; 85 © 2005 Julian Dyke juliandyke. com
Native Dynamic SQL u EXECUTE IMMEDIATE can also use bind variables u USING clause is used to specify bind variable positions u For example DECLARE l_c 1 NUMBER; l_str VARCHAR 2(1000); BEGIN l_str : = 'SELECT c 1 FROM t 1 WHERE c 2 = : p 1'; EXECUTE IMMEDIATE l_str INTO l_c 0 l 1 USING 10; DBMS_OUTPUT. PUT_LINE ('Result is '||l_c 1); EXECUTE IMMEDIATE l_str INTO l_c 0 l 1 USING 30; DBMS_OUTPUT. PUT_LINE ('Result is '||l_c 1); END; 86 © 2005 Julian Dyke juliandyke. com
Bulk Collect 87 u Introduced in Oracle 8. 1. 5 u Returns result set in single operation u Can be used with u SELECT INTO u FETCH INTO u RETURNING INTO © 2005 Julian Dyke juliandyke. com
Bulk Collect - Example DECLARE -- 100000 row table l_c 3 NUMBER; CURSOR c 1 IS SELECT c 3 FROM t 1; BEGIN OPEN c 1; LOOP FETCH c 1 INTO l_c 3; -- 3. 052 seconds EXIT WHEN c 1%NOTFOUND; END LOOP; CLOSE c 1; END; DECLARE -- 100000 row table TYPE NUMTYPE IS TABLE OF NUMBER(6) INDEX BY BINARY_INTEGER; l_c 3 NUMTYPE; CURSOR c 1 IS SELECT c 3 FROM t 1; BEGIN OPEN c 1; LOOP FETCH c 1 BULK COLLECT INTO l_c 3; -- 0. 119 seconds EXIT WHEN c 1%NOTFOUND; END LOOP; CLOSE c 1; END; 88 © 2005 Julian Dyke juliandyke. com
Bulk Collect Limit Clause u Bulk collect performance improves as optimum result set size is approached u Thereafter bulk collect performance degrades as result set grows u In Oracle 8. 1. 6 and above the number of rows returned by FETCH INTO can be restricted using the LIMIT clause FETCH c 1 BULK COLLECT INTO l_c 3 LIMIT 1000; 89 © 2005 Julian Dyke juliandyke. com
FORALL 90 u Introduced in Oracle 8. 1. 5 u Sends INSERT, UPDATE or DELETE statements in batches u Can only repeat single DML statement u Works with PL/SQL collections including TABLE, VARRAY, NESTED TABLE etc. u Much faster than equivalent for-loop u 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 VARCHAR 2(30) INDEX BY BINARY_INTEGER; l_c 1 NUMTYPE; l_c 2 NAMETYPE; l_c 3 NUMTYPE; BEGIN FOR i IN 1. . 100000 LOOP l_c 1(i) : = i; l_c 2(i) : = LPAD (TO_CHAR (i), 30, ’ 0’); l_c 3(i) : = MOD (i, 100); END LOOP; FOR i IN 1. . 100000 LOOP -- FOR Loop – 28 seconds INSERT INTO t 1 VALUES (l_c 1 (i), l_c 2 (i), l_c 3(i)); END LOOP; FORALL f IN 1. . 100000 LOOP -- FORALL Loop – 4 seconds INSERT INTO t 1 VALUES (l_c 1 (i), l_c 2 (i), l_c 3(i)); END; 91 © 2005 Julian Dyke juliandyke. com
FORALL Performance u Performance of FORALL statement degrades for very large tables Rows u 92 FORALL 100000 28 4 1000000 240 360 Times in seconds © 2005 Julian Dyke juliandyke. com
Conclusion 93 u Every release contains many new features and enhancements that are not highlighted in the marketing material u Oracle assumes all customers will move on to new versions when they are released u 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: info@juliandyke. com My website address is: www. juliandyke. com 94 © 2005 Julian Dyke juliandyke. com
- Slides: 94