1 / 94

Forgotten Features

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.

nenet
Télécharger la présentation

Forgotten Features

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Forgotten Features Julian Dyke Independent Consultant Web Version juliandyke.com ©2005 Julian Dyke

  2. Introduction Forgotten Features Tracing and Auditing Testing and Benchmarking Administration Execution Plans Tables Indexes SQL PL/SQL Conclusion Agenda

  3. 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

  4. Tracing andAuditing

  5. 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

  6. 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;/

  7. 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

  8. 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

  9. 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;

  10. 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 );

  11. 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 );

  12. 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;/

  13. 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

  14. Testing andBenchmarking

  15. 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

  16. 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’;

  17. 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

  18. 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

  19. 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

  20. PerformanceTuning

  21. 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

  22. 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

  23. 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

  24. Optimizer Environment Variables • Optimizer Environment Variable values reported by the dynamic performance views include:

  25. 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

  26. 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'));

  27. DBMS_XPLAN • Example output with predicates Predicate Information (identified by operation id): 1 - access("T1"."C1"="T2"."C1")2 - filter("T1"."C2"=10)

  28. 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);

  29. 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")

  30. 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);

  31. DBMS_XPLAN • Example output for partition pruning Predicate Information (identified by operation id): 2 - filter("T1"."C1">=150 AND "T1"."C1"<250)

  32. 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);

  33. 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

  34. Administration

  35. 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

  36. 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;

  37. 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

  38. 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;

  39. 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

  40. 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>;

  41. 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

  42. 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

  43. 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

  44. 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

  45. 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;

  46. 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

  47. 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;

  48. Tables

  49. 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

  50. 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;

More Related