Managing Schema Objects

1. Managing Schema Objects

2. Objectives • After completing this lesson, you should be able to do the following: • Configure automatic segment-space management • Estimate table and index size • Perform online redefinition of tables • Enable automatic statistics collection • Use the Segment Advisor • Enable resumable space allocation

3. Using Automatic Segment-Space Management • Bitmaps are used to manage the free space within segments • Benefits provided by this capability include: • Ease of use • Better space utilization • Better concurrency handling • Better performance

4. Automatic Segment-Space Managementat Work BMB S E G M E N T … BMB BMB BMB BMB … BMB BMB BMB BMB … BMB … BMB BMB … … … … … DATA … Block … { Extent

5. Creating an Automatic Segment-SpaceManagement Segment • Segments are declared at the tablespace level. • Tablespace must be permanent and locally managed. • SEGMENTSPACEMANAGEMENT is the attribute used for tablespace creation, which cannot be subsequently altered. • Automatic space management segments are specified with the AUTO keyword. • For free list segments, use the default value of MANUAL. • Specifications of PCTUSED, FREELIST, and FREELISTGROUPS are ignored at table creation.

6. Using Enterprise Manager to Specify Automatic Segment-Space Management

7. Using SQL to Create an Automatic Space Management Segment SQL> CREATE TABLESPACE sample 2 EXTENT MANAGEMENT LOCAL 3 SEGMENT SPACE MANAGEMENT AUTO; SQL> CREATE TABLE students 2 (name VARCHAR2(30), hobbies CLOB) 3 TABLESPACE sample;

8. Granting Object Privileges on Behalf of the Object Owner • GRANTANYOBJECTPRIVILEGE system privilege allows you to grant and revoke any object privilege on behalf of the object owner. • You can grant access to objects in any schema without connecting to the schema. • Part of the Oracle-supplied DBA role • When you use this privilege to grant a privilege, the object owner is defined as the grantor, unless you have the object privilege you are granting.

9. Online Redefinition of Tables • Occasionally, you need to reorganize a large heavily used table. • Previously, these redefinitions forced the table to be unavailable for the duration of the operation. • Table redefinitions can be performed online beginning with Oracle9i.

10. Online Table Redefinition: Features • A nonpartitioned table can be converted into a partitioned table and vice versa. • The organization of a table can be changed from a heap-based to index-organized table (IOT), and vice versa. • Columns can be dropped. • New columns can be added to a table. • Parallel support can be added or removed. • Storage parameters can be modified. • A column can be renamed. • A table can be moved to a new tablespace.

11. Online Table Redefinition Transform Result table Source table Track DML operations Transform updates Store DML changes

12. Using the DBMS_REDEFITION Package 1. Decide on the method of redefinition. 2. Determine if the table is a candidate for online redefinition with the CAN_REDEF_TABLE procedure. 3. Create an empty interim table with the desired characteristics. 4. Start the redefinition process with the START_REDEF_TABLE procedure. 5. Use the COPY_TABLE_DEPENDENTS procedure to automatically create dependent objects. 6. Finish the redefinition process with the FINISH_REDEF_TABLE procedure.

13. Online Table Redefinition: Synchronizing the Interim Table • Use the SYNC_INTERIM_TABLE procedure to periodically synchronize the interim table with the original one. • Synchronization is recommended if there is significant DML activity between the start and finish of the redefinition.

14. Online Table Redefinition: Terminating the Redefinition Process • Use the ABORT_REDEF_TABLE procedure to terminate the redefinition. • Use if an error occurs during the process. • After this procedure executes, drop the interim table and its associated objects.

15. Online Table Redefinition: Limitations • If you are using the primary key (or pseudo-primary key) method, the table to be redefined must have the same primary key or pseudo-primary key columns. • You cannot use the rowid method of redefinition for index-organized tables (IOTs). • The following are not supported: • User-defined data types • BFILE columns • Tables with materialized view logs • Horizontal subsetting, vertical subsetting, and column transformations

16. Online Table Redefinition: Limitations • LONG columns must be converted to CLOBS; LONG RAW columns must be converted to BLOBS. • New columns being added must not be declared as NOTNULL until the redefinition is complete. • A subset of rows cannot be redefined. • The following tables cannot be redefined: • Tables in the SYS or SYSTEM schema • An overflow table of an IOT • Materialized view container tables • Advanced queuing tables • Temporary tables • Clustered tables

17. Resumable Space Allocation: Overview • Resumable space allocationprovides: • The ability to suspend and resume execution of large database operations in the event of repairable failure • Support for errors related to space limits and out-of-space conditions • An opportunity for the DBA to take corrective steps to resolve the error condition • Suspended statements that automatically continue operation

18. Resumable Space Allocation: Life Cycle • Resumable space allocation is enabled using the ALTERSESSION command. • A statement is suspended when one of the following conditions occurs: • Out of space condition • Maximum number of extents reached condition • Space quota exceeded condition • When a statement is suspended: • The error is reported in the alert log • A system event trigger, after suspension, can be executed • When the error condition disappears, the suspended statement automatically resumes.

19. Resumable Space Allocation: Operations • Queries: SELECT statements that run out of temporary space. • Data manipulation language commands: INSERT, UPDATE, DELETE • Import/Export when invoked with the resumable space allocation option. • SQL*Loader when invoked with the resumable space allocation option. • Various data definition language commands

20. Enabling Resumable Space Allocation Systemwide • Enable with the RESUMABLE_TIMEOUT initialization parameter • Specify a timeout interval with the RESUMABLE_TIMEOUT initialization parameter • Disabled by default • Can be changed dynamically • Within a session, users can enable resumable space allocation.

21. Enabling Session Resumable Space Allocation • Enable resumable space allocation: • Disable resumable space allocation: • Change the session name: • Change the timeout period: SQL> ALTER SESSION ENABLE RESUMABLE 2 TIMEOUT 600 NAME 'Starting Point'; SQL> ALTER SESSION DISABLE RESUMABLE; SQL> ALTER SESSION ENABLE RESUMABLE NAME 2 'new name'; SQL> ALTER SESSION ENABLE RESUMABLE TIMEOUT 2 3600;

22. Using the DBMS_RESUMABLE Package • DBMS_RESUMABLE procedures: • ABORT(sessionID) • GET_SESSION_TIMEOUT(sessionID) • SET_SESSION_TIMEOUT(sessionID,timeout) • GET_TIMEOUT() • SET_TIMEOUT(timeout) • SPACE_ERROR_INFO(error_type, object_type,object_owner, table_space_name,object_name, sub_object_name)

23. Creating an AFTERSUSPEND System Event • Automatically generated when a statement encounters a correctable error: • SQL statements that are executed in an AFTERSUSPEND trigger are always nonresumable. CREATE OR REPLACE TRIGGERres_default after suspendon database DECLARE BEGIN /* send an email to notify DBA */ COMMIT; END;

24. SESSION_ID Session identifier of the statement INSTANCE_ID Instance number of the statement SQL_TEXT First 1,000 characters of the statement NAME The name given to the statement STATUS RUNNING, SUSPENDED, COMPLETED, ABORTED, TIMEOUT ERROR_NUMBER Error code of the last correctable error (1) ERROR_MSG Error message corresponding to (1) START_TIME Start time of the statement SUSPEND_TIME Last time the statement was suspended RESUME_TIME Last time the statement was resumed Obtaining Information About the Statusof Resumable Statements • DBA_RESUMABLE dictionary view:

25. Data Segment Compression: Overview • Applies to heap-organized tables only • Compresses data inside blocks • Useful for data warehouse environment: • Disk use reduction • Memory use reduction • Query execution speedup • Limited update activity • Optimized for direct-load scenarios

26. Data Segment Compression at Work 188670 C 563.7 188670 I 1648.7 188670 P 571.95 188670 S 140.25 188670 T 633.35 189450 Z 5055.5 189450 U 2714.6 189450 A 4199.1 189450 K 6296.9 189450 R 94.65 188670 189450 C 563.7, I 1648.7, P 571.95, S 140.25, T 633.35, Z 5055.5, U 2714.6, A 4199.1, K 6296.9, R 94.65 Block before compression Block after compression

27. Creating Compressed Segments • You can compress: • Entire tables • Specific partitions of a partitioned table • Use the new COMPRESS attribute at: • Tablespace level • Table level • Partition level • Ideal for rolling window operations

28. Creating Compressed Segments: Example CREATE TABLE customers_comp COMPRESS AS SELECT * FROM sh.customers; CREATE TABLESPACE sampleDATAFILE 'sample01.dbf' SIZE 20M DEFAULT COMPRESS; CREATE TABLE costs( prod_id,time_id,unit_cost,unit_price) PARTITION BY RANGE (time_id) ( PARTITION c2001 VALUES LESS THAN ('2002') TABLESPACE SAMPLE, PARTITION c2002 VALUES LESS THAN ('2003') TABLESPACE SAMPLE NOCOMPRESS);

29. Compressing Segments • Immediately move a segment to a compressed or noncompressed form: • Change a segment’s compressed scheme for future utilization: • Operations are not possible for partitioned tables having bitmap indexes (ORA-14646). ALTER TABLE customers MOVE COMPRESS; ALTER TABLE customers COMPRESS;

30. Achieving a Better Compression Ratio • Find one row ordering with the maximum number of repeated values per block. • With only one low-cardinality column: • Low cardinality can be determined by querying: • DBA_TAB_COL_STATISTICS • DBA_PART_COL_STATISTICS • DBA_SUBPART_COL_STATISTICS CREATE TABLE compressed COMPRESS AS SELECT * FROM to_be_compressed ORDER BY low_cardinality_column;

31. Achieving a Better Compression Ratio • In presence of multiple low cardinality columns: • Determine the C1 column with the lowest cardinality • Determine the C2 column with the lowest cardinality of other columns for fixed values of C1 • Determine the C3 column with the lowest cardinality when the first two are fixed • By continuing this process, you can determine some column sequence C1, C2, …, Cn • Create the table using the sequence found: CREATE TABLE compressed COMPRESS AS SELECT * FROM to_be_compressed ORDER BY C1, C2, …, Cn;

32. Determining Whether a Table Is Using Compression • For partitioned tables: • COMPRESSION column inside DBA_TAB_PARTITIONS, or DBA_TAB_SUBPARTITOINS • DEF_COMPRESSION inside DBA_PART_TABLES • For a particular segment: SELECT d.segment_name, d.partition_name, DECODE(BITAND(s.spare1,2048),2048, 'ENABLED', 'DISABLED') FROM sys.seg$ s, dba_segments d, sys.ts$ t WHERE d.HEADER_FILE = s.FILE# and d.HEADER_BLOCK = s.BLOCK# and t.ts# = s.ts# and t.name = 'SYSTEM' and d.segment_name='TPART' and d.owner='SYSTEM';

33. Segment Advisor: Overview • Determines the list of objects that are good candidates for shrinking • Recommendations are based on: • Sampled analysis • Historical information • Future growth trends • Accessible from Database Control: • Advisor Central page • Tablespaces page • Schema object pages

34. Invoking the Segment Advisor

35. Viewing the Growth Trend Report • Used by the Segment Advisor • Space usage statistics are collected in the AWR.

36. Segment Resource Estimation

37. Shrinking Segments: Overview Data Unusedspace Unusedspace Data HWM Shrinkoperation Reclaimed space HWM

38. Shrinking Segments: Considerations • Online and in-place operation • Applicable only to segments residing in ASSM tablespaces • Candidate segment types: • Heap-organized tables and index-organized tables • Indexes • Partitions and subpartitions • Materialized views and materialized view logs • Indexes are maintained. • Triggers are not fired.

39. Shrinking Segments by Using SQL ALTER … SHRINK SPACE [CASCADE] TABLE INDEX MATERIALIZED VIEW MATERIALIZED VIEW LOG MODIFY PARTITION MODIFY SUBPARTITION ALTER TABLE employees ENABLE ROW MOVEMENT; 1 ALTER TABLE employees SHRINK SPACE CASCADE; 2

40. Segment Shrink: Basic Execution ALTER TABLE employees SHRINK SPACE COMPACT; HWM 1 HWM ALTER TABLE employees SHRINK SPACE; 2 HWM

41. Segment Shrink:Execution Considerations • Use compaction only: • To avoid unnecessary cursor invalidation • During peak hours • DML operations and queries can be issued during compaction. • DML operations are blocked when HWM is adjusted.

42. Using EM to Shrink Segments

43. Migrating to the Cost-Based Optimizer • Oracle Database 10g supports only the cost-based optimizer. • The cost-based optimizer relies on accurate statistics to determine the optimal access path for a query. • Plan stability can be maintained by using stored outlines to capture, save, and reuse the execution plans for all of the queries of a given application.

44. Automatic Optimizer Statistics Collection: Overview • Oracle8i provides the DBMS_STATS package: • DBA determines how to gather statistics. • DBA determines when to gather statistics. • Oracle9i determines how to gather statistics: • Statistics can be gathered using a single command. • DBA determines when to gather statistics. • Oracle Database 10g fully automates statistics gathering: • DBA no longer has to gather statistics. • Table monitoring is used by default.

45. Automatically Collecting Statistics • STATISTICS_LEVEL = TYPICAL | ALL • Statistics gathered by the predefined GATHER_STATS_JOB job • This job implicitly determines: • Database objects with missing or stale statistics • Appropriate sampling percentage necessary to gather good statistics on those objects • Appropriate columns that require histograms and the size of those histograms • Degree of parallelism for statistics gathering • Prioritization of objects on which to collect statistics

46. GATHER_STATS_JOB STATISTICS_LEVEL = TYPICAL or ALL MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW WEEKEND_WINDOW 10 p.m.–6 a.m. Mon to Fri 12 a.m. Sat to 12 a.m. Mon GATHER_STATS_JOB AUTO_TASKS_JOB_CLASS AUTO_TASKS_CONSUMER_GROUP

47. Changing the GATHER_STATS_JOB Schedule

48. Locking Statistics • Prevents automatic gathering • Mainly used for volatile tables: • Lock without statistics implies dynamic sampling • Lock with statistics for representative values EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('owner name', 'table name'); EXECUTE DBMS_STATS.LOCK_SCHEMA_STATS ('owner name'); SELECT stattype_locked FROM dba_tab_statistics;

49. Using the DBMS_STATS Package • New FORCE argument • Override statistics locking EXECUTE DBMS_STATS.DELETE_*_STATS(…,- FORCE=>TRUE); EXECUTE DBMS_STATS.IMPORT_*_STATS(…,-FORCE=>TRUE); EXECUTE DBMS_STATS.RESTORE_*_STATS(…,- FORCE=>TRUE); EXECUTE DBMS_STATS.SET_*_STATS(…,-FORCE=>TRUE);

50. Automatic Statistics Collection: Considerations • You should continue to gather statistics manually in the following cases: • After bulk operations • When using external tables • To collect system statistics • To collect statistics on fixed objects