1 / 39

Monitoring and Managing Storage

Monitoring and Managing Storage. Objectives. After completing this lesson, you should be able to: Tune redo writing and archiving operations Issue statements that can be suspended when encountering space condition errors

pilar
Download Presentation

Monitoring and Managing Storage

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. Monitoring and Managing Storage

  2. Objectives • After completing this lesson, you should be able to: • Tune redo writing and archiving operations • Issue statements that can be suspended when encountering space condition errors • Reduce space-related error conditions through proactively managing tablespace space usage • Reclaim wasted space from tables and indexes using the segment-shrink functionality • Estimate the size of new tables and indexes • Use different storage options to improve performance of queries • Rebuild indexes online

  3. Online Redo Log File Configuration • Size redo log files to minimize contention. • Provide enough groups to prevent waiting. • Store redo log files on separate, fast devices. • Monitor the redo log file configuration with: • V$LOGFILE • V$LOG • V$INSTANCE_RECOVERY

  4. Redo Logfile Sizing Advisor

  5. Increasing the Performance of Archiving • Allow the LGWR process to write to a disk different from the one the ARCn process is reading. • Share the archiving work during a temporary increase in workload: • Increase the number of archive processes. • Change archiving speed: • LOG_ARCHIVE_MAX_PROCESSES • LOG_ARCHIVE_DEST_n ALTER SYSTEM ARCHIVE LOG ALL TO <log_archive_dest>

  6. Resumable Statements • A resumable statement: • Allows you to suspend large operations instead of receiving an error • Gives you a chance to fix the problem while the operation is suspended, rather than starting over • Is suspended for the following conditions: • Out of space • Maximum extents reached • Space quota exceeded

  7. Using Resumable Space Allocation • Queries, DML operations, and certain DDL operations can be resumed if they encounter an out-of-space error. • A resumable statement can be issued through SQL, PL/SQL, SQL*Loader, or the Oracle Call Interface (OCI). • To run a resumable statement, you must first enable resumable statements for your session. ALTER SESSION ENABLE RESUMABLE; INSERT INTO sales_new SELECT * FROM sh.sales; ALTER SESSION DISABLE RESUMABLE;

  8. Resuming Suspended Statements SQL statement EMPLOYEES table Used block Free space Suspended Continue SQL operation EMPLOYEES table AFTER SUSPENDtrigger Abort

  9. Proactive Tablespace Monitoring Overview • Server-generated alerts inform you that: • Tablespaces are running low on available space • Segments are running out of space • Data gathering and reporting provides: • Historical tablespace disk space usage • Segment growth trend analysis

  10. Tablespace Space Usage Monitoring Check every 10 min. • Read-onlyand offline tablespaces: Do not set up alerts. • Temporary tablespace: Threshold corresponds to space currently used by sessions. • Undo tablespace: Threshold corresponds to space used by active and unexpired extents. • Tablespaces that can extend automatically: Threshold is based on the maximum file size. 97% Critical Cleared Alert 85% Warning Cleared Alert MMON

  11. Edit Tablespace Page

  12. Segment Advisor Overview

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

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

  15. Database Control and Segment Shrink

  16. Accessing the Segment Advisor

  17. Segment Advisor

  18. Shrinking Segments Using SQL ALTER … SHRINK SPACE [COMPACT] [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

  19. 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 the HWM is adjusted.

  20. Segment Resource Estimation

  21. Growth Trend Report • Used by the Segment Advisor • Space usage statistics are collected into AWR

  22. Monitoring Index Space • To collect usage statistics regarding an index: • To view statistics collected: • Rebuild indexes with wastage greater than 20%: • To coalesce indexes (alternative to REBUILD): SQL> EXECUTE dbms_stats.gather_index_stats - > ('OE','CUSTOMERS_PK'); SQL> SELECT name, 2 (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100ASwastage 3 FROM index_stats; SQL> ALTER INDEX oe.customers_pk REBUILD; SQL> ALTER INDEX oe.customers_pk COALESCE;

  23. Monitoring Index Space Usage Predicted Actual

  24. Deciding Whether to Rebuildor Coalesce an Index Rebuild Coalesce Quickly moves index to Cannot move index to another tablespace another tablespace Higher costs: Requires more Lower costs: Does not disk space require more disk space Creates new tree, shrinks Coalesces leaf blocks within height if applicable same branch of tree Enables you to quickly change Quickly frees up index storage and tablespace leaf blocks for use parameters without having todrop the original index

  25. Identifying Unused Indexes • Use index monitoring to determine whether an index is being used: 1. Enable index monitoring, and clear out any existing index use information. 2. Continue normal database operations. 3. Query the V$OBJECT_USAGE view for usage information. 4. Disable index monitoring at the end of evaluation. SQL> ALTER INDEX emp_job_ix MONITORING USAGE; SQL> SELECT index_name, used FROM V$OBJECT_USAGE; SQL> ALTER INDEX emp_job_ix NOMONITORING USAGE;

  26. Index-Organized Tables Regular table access IOT access Table accessby ROWID Non-key columns Key column Row header

  27. Index-Organized Tablesand Heap Tables • Compared to heap tables, IOTs have: • Faster key-based access to table data • Do not duplicate the storage of primary key values • Require less storage • Use secondary indexes and logical rowids • Have higher availability, as table reorganization does not invalidate secondary indexes • IOTs have the following restrictions: • Must have a primary key that is not DEFERRABLE • Cannot be clustered • Cannot use composite partitioning • Cannot contain a column of type ROWID or LONG

  28. Creating Index-Organized Tables SQL> CREATE TABLE country 2 ( country_id CHAR(2) 3 CONSTRAINT country_id_nn NOT NULL, 4 country_name VARCHAR2(40), 5 currency_name VARCHAR2(25), 6 currency_symbol VARCHAR2(3), 7 map BLOB, 8 flag BLOB, 9 CONSTRAINT country_c_id_pk 10 PRIMARY KEY (country_id)) 11 ORGANIZATION INDEX 12 TABLESPACE indx 13 PCTTHRESHOLD 20 14 OVERFLOW TABLESPACE users;

  29. IOT Row Overflow indx tablespace users tablespace Segment = COUNTRY_C_ID_PK IOT_type = IOT Segment_type = INDEX Index_type = IOT - TOP Segment = SYS_IOT_OVER_n IOT_type = IOT_OVERFLOW Segment_type = TABLE Remaining part of the row Rows within PCTTHRESHOLD

  30. Querying DBA_TABLES for IOT Information SQL> SELECT table_name, iot_name, iot_type 2 FROM DBA_TABLES; TABLE_NAME IOT_NAME IOT_TYPE ----------------- -------- ------------ COUNTRY IOT SYS_IOT_OVER_2268 COUNTRY IOT_OVERFLOW

  31. Querying DBA_INDEXESand DBA_SEGMENTS for IOT information SQL> SELECT index_name, index_type, 2 tablespace_name, table_name 2 FROM DBA_INDEXES; INDEX_NAME INDEX_TYPE TABLESPACE TABLE_NAME --------------- ---------- ---------- ---------- COUNTRY_C_ID_PK IOT - TOP INDX COUNTRY SQL> SELECT segment_name, tablespace_name, 2 segment_type 3 FROM DBA_SEGMENTS; SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE ----------------- --------------- ------------ SYS_IOT_OVER_2268 USER TABLE COUNTRY_C_ID_PK INDX INDEX

  32. Using a Mapping Table SQL> CREATE TABLE country 2 ( country_id CHAR(2) 3 CONSTRAINT country_id_nn NOT NULL 4 , country_name VARCHAR2(40) 5 , currency_name VARCHAR2(25) 6 , currency_symbol VARCHAR2(3) 7 , CONSTRAINT country_c_id_pk 8 PRIMARY KEY (country_id)) 9 ORGANIZATION INDEX 10 MAPPING TABLE TABLESPACE users;

  33. Maintaining a Mapping Table • Collect statistics on a mapping table by analyzing the IOT. • Query the DBA_INDEXES view to determine the percentage accuracy of the mapping table. • Rebuild the mapping table if required, using the ALTER TABLE command. • Use the COMPRESS clause of CREATETABLE or ALTERTABLE for the mapping table. SQL> SELECT index_name, pct_direct_access 2 FROM DBA_INDEXES 3 WHERE pct_direct_access IS NOT NULL;

  34. Clusters Cluster Key (ORD_NO) 101 ORD_DT CUST_CD 05-JAN-97 R01 PROD QTY A4102 20 A5675 19 W0824 10 102 ORD_DT CUST_CD 07-JAN-97 N45 PROD QTY A2091 11 G7830 20 N9587 26 ORD_NO PROD QTY ... ----- ------ ------ 101 A4102 20 102 A2091 11 102 G7830 20 102 N9587 26 101 A5675 19 101 W0824 10 ORD_NO ORD_DT CUST_CD ------ ------ ------ 101 05-JAN-97 R01 102 07-JAN-97 N45 Unclustered orders and order_item tables Clustered orders and order_item tables

  35. Cluster Types Index cluster Hash cluster Sorted hash cluster Hash function Hash function

  36. Situations Where Clusters Are Useful

  37. Sorted Hash Cluster: Example CREATE CLUSTER calls_cluster ( origin_number NUMBER , call_timestamp NUMBER SORT , call_duration NUMBER SORT) HASHKEYS 10000 SINGLE TABLE HASH IS origin_number SIZE 50; Cluster key Sort key CREATE TABLE calls ( origin_number NUMBER , call_timestamp NUMBER , call_duration NUMBER , other_info VARCHAR2(30)) CLUSTER calls_cluster( origin_number,call_timestamp,call_duration );

  38. Summary • In this lesson, you should have learned how to: • Tune redo writing and archiving operations • Issue statements that can be suspended when encountering space condition errors • Reduce space-related error conditions through proactively managing tablespace space usage • Reclaim wasted space from tables and indexes using the segment shrink functionality • Estimate the size of new tables and indexes • Use sorted hash clusters • Rebuild indexes online

  39. Practice 13 Overview:Managing Storage • This practice covers the following topics: • Monitoring index space usage and rebuilding indexes if needed • Using the Segment Advisor to shrink the space allocated to a table • Using the Segment Space Estimator to determine the amount of space required to create a new table.

More Related