[bbk3154] 第63集 - Chapter 15-Application Tuning(03)
Query dba_tables for IOT
DBA_TABLES->IOT
SQL> SELECT table_name,iot_name,iot_type FROM dba_tables WHERE table_name LIKE '%IOT%'; TABLE_NAME IOT_NAME IOT_TYPE ------------------------------ ------------------------------ ------------ SYS_IOT_OVER_12334 AQ$_ALERT_QT_G IOT_OVERFLOW SYS_IOT_OVER_12537 AQ$_AQ$_MEM_MC_G IOT_OVERFLOW SYS_IOT_OVER_12573 AQ$_AQ_PROP_TABLE_G IOT_OVERFLOW SYS_IOT_OVER_12752 AQ$_KUPC$DATAPUMP_QUETAB_G IOT_OVERFLOW SYS_IOT_OVER_5146 RULE_SET_IOT$ IOT_OVERFLOW SYS_IOT_OVER_5140 RULE_SET_PR$ IOT_OVERFLOW SYS_IOT_OVER_5150 RULE_SET_ROP$ IOT_OVERFLOW SYS_IOT_OVER_5387 CHNF$_CLAUSES IOT_OVERFLOW SYS_IOT_OVER_5422 CHNF$_GROUP_FILTER_IOT IOT_OVERFLOW SYS_IOT_OVER_5690 RECENT_RESOURCE_INCARNATIONS$ IOT_OVERFLOW RULE_SET_IOT$ IOT TABLE_NAME IOT_NAME IOT_TYPE ------------------------------ ------------------------------ ------------ CHNF$_GROUP_FILTER_IOT IOT SYS_IOT_OVER_12214 AQ$_SCHEDULER$_EVENT_QTAB_G IOT_OVERFLOW SYS_IOT_OVER_12248 AQ$_SCHEDULER$_REMDB_JOBQTAB_G IOT_OVERFLOW SYS_IOT_OVER_12278 AQ$_SCHEDULER_FILEWATCHER_QT_G IOT_OVERFLOW SYS_IOT_OVER_12875 AQ$_SYS$SERVICE_METRICS_TAB_G IOT_OVERFLOW SYS_IOT_OVER_13393 AQ$_WM$EVENT_QUEUE_TABLE_G IOT_OVERFLOW SYS_IOT_OVER_55269 EXF$ATTRLIST IOT_OVERFLOW SYS_IOT_OVER_55279 EXF$IDXSECOBJ IOT_OVERFLOW SYS_IOT_OVER_57275 RLM$COLLGRPBYSPEC IOT_OVERFLOW SYS_IOT_OVER_57272 RLM$EQUALSPEC IOT_OVERFLOW SYS_IOT_OVER_57254 RLM$ERRCODE IOT_OVERFLOW TABLE_NAME IOT_NAME IOT_TYPE ------------------------------ ------------------------------ ------------ SYS_IOT_OVER_57295 RLM$JOBQUEUE IOT_OVERFLOW SYS_IOT_OVER_57269 RLM$PRIMEVTTYPEMAP IOT_OVERFLOW SYS_IOT_OVER_57262 RLM$RULESET IOT_OVERFLOW SYS_IOT_OVER_55480 DR$PARALLEL IOT_OVERFLOW SYS_IOT_OVER_55433 DR$SQE IOT_OVERFLOW SYS_IOT_OVER_57304 RLM4J$ATTRALIASES IOT_OVERFLOW SYS_IOT_OVER_57298 RLM4J$EVTSTRUCTS IOT_OVERFLOW SYS_IOT_OVER_57301 RLM4J$RULESET IOT_OVERFLOW SYS_IOT_OVER_70061 AQ$_MGMT_LOADER_QTABLE_G IOT_OVERFLOW SYS_IOT_OVER_66894 AQ$_MGMT_NOTIFY_QTABLE_G IOT_OVERFLOW SYS_IOT_OVER_67131 MGMT_METRICS_RAW IOT_OVERFLOW TABLE_NAME IOT_NAME IOT_TYPE ------------------------------ ------------------------------ ------------ SYS_IOT_OVER_67302 MGMT_TARGET_CREDENTIALS IOT_OVERFLOW SYS_IOT_OVER_67248 MGMT_TASK_WORKER_COUNTS IOT_OVERFLOW SYS_IOT_OVER_67755 MGMT_POLICY_ASSOC IOT_OVERFLOW SYS_IOT_OVER_67759 MGMT_POLICY_ASSOC_CFG_PARAMS IOT_OVERFLOW SYS_IOT_OVER_67749 MGMT_POLICY_BIND_VARS IOT_OVERFLOW SYS_IOT_OVER_67313 MGMT_JOB_CREDENTIALS IOT_OVERFLOW SYS_IOT_OVER_67308 MGMT_ENTERPRISE_CREDENTIALS IOT_OVERFLOW SYS_IOT_OVER_67877 MGMT_LICENSE_CONFIRMATION IOT_OVERFLOW SYS_IOT_OVER_67134 MGMT_CURRENT_METRICS IOT_OVERFLOW SYS_IOT_OVER_69647 MGMT_DB_INIT_PARAMS_ECM IOT_OVERFLOW SYS_IOT_OVER_67764 MGMT_SNAPSHOT_METRIC_MAP IOT_OVERFLOW TABLE_NAME IOT_NAME IOT_TYPE ------------------------------ ------------------------------ ------------ SYS_IOT_OVER_67305 MGMT_HOST_CREDENTIALS IOT_OVERFLOW SYS_IOT_OVER_67245 MGMT_COLLECTION_TASK_CONTEXT IOT_OVERFLOW SYS_IOT_OVER_67232 MGMT_COLL_ITEM_PROPERTIES IOT_OVERFLOW SYS_IOT_OVER_70800 WWV_FLOW_JOB_BIND_VALUES IOT_OVERFLOW SYS_IOT_OVER_74062 AQ$_ORDERS_QUEUETABLE_G IOT_OVERFLOW SYS_IOT_OVER_74080 AQ$_STREAMS_QUEUE_TABLE_G IOT_OVERFLOW SYS_IOT_OVER_76689 IOT1 IOT_OVERFLOW SYS_IOT_OVER_76692 IOT2 IOT_OVERFLOW IOT1 IOT IOT2 IOT TRANSIENT_IOT$ 55 rows selected.
DBA_INDEXES->IOT
SQL> SELECT index_name,index_type,tablespace_name,table_name FROM dba_indexes WHERE index_name LIKE '%IOT%'; INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_NAME ------------------------------ --------------------------- ------------------------------ ------------------------------ IOT_PK2 IOT - TOP TAB_LAB IOT2 IOT_PK1 IOT - TOP TAB_LAB IOT1 SYS_IOT_TOP_74380 IOT - TOP EXAMPLE DR$SUP_TEXT_IDX$N SYS_IOT_TOP_74375 IOT - TOP EXAMPLE DR$SUP_TEXT_IDX$K SYS_IOT_TOP_74075 IOT - TOP EXAMPLE AQ$_STREAMS_QUEUE_TABLE_T SYS_IOT_TOP_74083 IOT - TOP EXAMPLE AQ$_STREAMS_QUEUE_TABLE_I SYS_IOT_TOP_74077 IOT - TOP EXAMPLE AQ$_STREAMS_QUEUE_TABLE_H SYS_IOT_TOP_74080 IOT - TOP EXAMPLE AQ$_STREAMS_QUEUE_TABLE_G SYS_IOT_TOP_74085 IOT - TOP EXAMPLE AQ$_STREAMS_QUEUE_TABLE_C SYS_IOT_TOP_74057 IOT - TOP EXAMPLE AQ$_ORDERS_QUEUETABLE_T SYS_IOT_TOP_74065 IOT - TOP EXAMPLE AQ$_ORDERS_QUEUETABLE_I INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_NAME ------------------------------ --------------------------- ------------------------------ ------------------------------ SYS_IOT_TOP_74059 IOT - TOP EXAMPLE AQ$_ORDERS_QUEUETABLE_H SYS_IOT_TOP_74062 IOT - TOP EXAMPLE AQ$_ORDERS_QUEUETABLE_G SYS_IOT_TOP_70800 IOT - TOP SYSAUX WWV_FLOW_JOB_BIND_VALUES SYS_IOT_TOP_66889 IOT - TOP SYSAUX AQ$_MGMT_NOTIFY_QTABLE_T SYS_IOT_TOP_66897 IOT - TOP SYSAUX AQ$_MGMT_NOTIFY_QTABLE_I SYS_IOT_TOP_66891 IOT - TOP SYSAUX AQ$_MGMT_NOTIFY_QTABLE_H SYS_IOT_TOP_66894 IOT - TOP SYSAUX AQ$_MGMT_NOTIFY_QTABLE_G SYS_IOT_TOP_70056 IOT - TOP SYSAUX AQ$_MGMT_LOADER_QTABLE_T SYS_IOT_TOP_70064 IOT - TOP SYSAUX AQ$_MGMT_LOADER_QTABLE_I SYS_IOT_TOP_70058 IOT - TOP SYSAUX AQ$_MGMT_LOADER_QTABLE_H SYS_IOT_TOP_70061 IOT - TOP SYSAUX AQ$_MGMT_LOADER_QTABLE_G INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_NAME ------------------------------ --------------------------- ------------------------------ ------------------------------ SYS_IOT_TOP_55445 IOT - TOP SYSAUX DR$THS_FPHRASE SYS_IOT_TOP_55460 IOT - TOP SYSAUX DR$STOPWORD SYS_IOT_TOP_55433 IOT - TOP SYSAUX DR$SQE SYS_IOT_TOP_55470 IOT - TOP SYSAUX DR$PENDING SYS_IOT_TOP_55405 IOT - TOP SYSAUX DR$PARAMETER SYS_IOT_TOP_55480 IOT - TOP SYSAUX DR$PARALLEL SYS_IOT_TOP_55473 IOT - TOP SYSAUX DR$ONLINE_PENDING SYS_IOT_TOP_55498 IOT - TOP SYSAUX DR$INDEX_CDI_COLUMN SYS_IOT_TOP_55486 IOT - TOP SYSAUX DR$DBO SYS_IOT_TOP_57257 IOT - TOP SYSAUX RLM$RULESETSTCODE SYS_IOT_TOP_57288 IOT - TOP SYSAUX RLM$INCRRRSCHACT INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_NAME ------------------------------ --------------------------- ------------------------------ ------------------------------ SYS_IOT_TOP_13388 IOT - TOP SYSAUX AQ$_WM$EVENT_QUEUE_TABLE_T SYS_IOT_TOP_13396 IOT - TOP SYSAUX AQ$_WM$EVENT_QUEUE_TABLE_I SYS_IOT_TOP_13390 IOT - TOP SYSAUX AQ$_WM$EVENT_QUEUE_TABLE_H SYS_IOT_TOP_13393 IOT - TOP SYSAUX AQ$_WM$EVENT_QUEUE_TABLE_G SYS_IOT_TOP_12870 IOT - TOP SYSAUX AQ$_SYS$SERVICE_METRICS_TAB_T SYS_IOT_TOP_12878 IOT - TOP SYSAUX AQ$_SYS$SERVICE_METRICS_TAB_I SYS_IOT_TOP_12872 IOT - TOP SYSAUX AQ$_SYS$SERVICE_METRICS_TAB_H SYS_IOT_TOP_12875 IOT - TOP SYSAUX AQ$_SYS$SERVICE_METRICS_TAB_G SYS_IOT_TOP_12273 IOT - TOP SYSTEM AQ$_SCHEDULER_FILEWATCHER_QT_T SYS_IOT_TOP_12281 IOT - TOP SYSTEM AQ$_SCHEDULER_FILEWATCHER_QT_I SYS_IOT_TOP_12275 IOT - TOP SYSTEM AQ$_SCHEDULER_FILEWATCHER_QT_H INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_NAME ------------------------------ --------------------------- ------------------------------ ------------------------------ SYS_IOT_TOP_12278 IOT - TOP SYSTEM AQ$_SCHEDULER_FILEWATCHER_QT_G SYS_IOT_TOP_12243 IOT - TOP SYSTEM AQ$_SCHEDULER$_REMDB_JOBQTAB_T SYS_IOT_TOP_12251 IOT - TOP SYSTEM AQ$_SCHEDULER$_REMDB_JOBQTAB_I SYS_IOT_TOP_12245 IOT - TOP SYSTEM AQ$_SCHEDULER$_REMDB_JOBQTAB_H SYS_IOT_TOP_12248 IOT - TOP SYSTEM AQ$_SCHEDULER$_REMDB_JOBQTAB_G SYS_IOT_TOP_12209 IOT - TOP SYSTEM AQ$_SCHEDULER$_EVENT_QTAB_T SYS_IOT_TOP_12217 IOT - TOP SYSTEM AQ$_SCHEDULER$_EVENT_QTAB_I SYS_IOT_TOP_12211 IOT - TOP SYSTEM AQ$_SCHEDULER$_EVENT_QTAB_H SYS_IOT_TOP_12214 IOT - TOP SYSTEM AQ$_SCHEDULER$_EVENT_QTAB_G SYS_IOT_TOP_5422 IOT - TOP SYSAUX CHNF$_GROUP_FILTER_IOT SYS_IOT_TOP_5387 IOT - TOP SYSAUX CHNF$_CLAUSES INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_NAME ------------------------------ --------------------------- ------------------------------ ------------------------------ SYS_IOT_TOP_5150 IOT - TOP SYSAUX RULE_SET_ROP$ SYS_IOT_TOP_5140 IOT - TOP SYSAUX RULE_SET_PR$ SYS_IOT_TOP_5146 IOT - TOP SYSAUX RULE_SET_IOT$ I_RULE_SET_IOT NORMAL SYSAUX RULE_SET_IOT$ SYS_IOT_TOP_12747 IOT - TOP SYSTEM AQ$_KUPC$DATAPUMP_QUETAB_T SYS_IOT_TOP_12755 IOT - TOP SYSTEM AQ$_KUPC$DATAPUMP_QUETAB_I SYS_IOT_TOP_12749 IOT - TOP SYSTEM AQ$_KUPC$DATAPUMP_QUETAB_H SYS_IOT_TOP_12752 IOT - TOP SYSTEM AQ$_KUPC$DATAPUMP_QUETAB_G SYS_IOT_TOP_12568 IOT - TOP SYSTEM AQ$_AQ_PROP_TABLE_T SYS_IOT_TOP_12576 IOT - TOP SYSTEM AQ$_AQ_PROP_TABLE_I SYS_IOT_TOP_12570 IOT - TOP SYSTEM AQ$_AQ_PROP_TABLE_H INDEX_NAME INDEX_TYPE TABLESPACE_NAME TABLE_NAME ------------------------------ --------------------------- ------------------------------ ------------------------------ SYS_IOT_TOP_12573 IOT - TOP SYSTEM AQ$_AQ_PROP_TABLE_G SYS_IOT_TOP_12532 IOT - TOP SYSTEM AQ$_AQ$_MEM_MC_T SYS_IOT_TOP_12540 IOT - TOP SYSTEM AQ$_AQ$_MEM_MC_I SYS_IOT_TOP_12534 IOT - TOP SYSTEM AQ$_AQ$_MEM_MC_H SYS_IOT_TOP_12537 IOT - TOP SYSTEM AQ$_AQ$_MEM_MC_G SYS_IOT_TOP_12329 IOT - TOP SYSAUX AQ$_ALERT_QT_T SYS_IOT_TOP_12337 IOT - TOP SYSAUX AQ$_ALERT_QT_I SYS_IOT_TOP_12331 IOT - TOP SYSAUX AQ$_ALERT_QT_H SYS_IOT_TOP_12334 IOT - TOP SYSAUX AQ$_ALERT_QT_G 75 rows selected.
DBA_SEGMENTS->IOT
SQL> SELECT segment_name,tablespace_name,segment_type FROM dba_segments WHERE tablespace_name = 'TAB_LAB'; SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE ------------------------------ ------------------------------ ------------------ SYS_IOT_OVER_76692 TAB_LAB TABLE SYS_IOT_OVER_76689 TAB_LAB TABLE IOT_PK2 TAB_LAB INDEX BIN$28vX5Fcm2mLgQKjAyAFLIg==$0 TAB_LAB INDEX IOT_PK1 TAB_LAB INDEX
IOT表的诞生，本身就是基于快速查询而设计的.通常情况下，不建议在IOT表上再创建第二个索引. 而且这种查询通常情况下都是基于主键查询的，而不是基于非主键来查询的.
Using a Mapping Table
Mapping Table是一个普通的heap表，建立Mapping Table就是将此表的物理rowid与IOT表的逻辑rowid对应起来.
SQL>CREATE TABLE country ( country_id CHAR(2) CONSTRAINT country_id_nn NOT NULL, country_name VARCHAR2(40), country_name VARCHAR2(25), country_symbol VARCHAR2(3), CONSTRAINT country_c_id_pk PRIMARY KEY(country_id) ) ORGANIZATION INDEX MAPPING TABLE TABLESPACE users;
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.
SQL>SELECT index_name,pct_direct_access FROM dba_indexes WHERE pct_direct_access IS NOT NULL;
- Rebuild the mapping table if required,using the ALTER TABLE command.
- Use the MINMIZE RECORDS_PER_BLOCK clause of ALTER TABLE for the mapping table.
The ANALYZE Statement
Use the ANALYZE statement to:
- VALIDATE STRUCTURE
- LIST CHAINED ROWS
- Collect statistics not used by the optimizer,such as information on freee list blocks.
- Sample a number(instead of a percentage) of rows
SQL>ANALYZE TABLE hr.employees VALIDATE STRUCTURE;
注意：虽然现在oracle在推荐使用dbms_stats包，但是ANALYZE之所以还存在使用，就是因为其功能涵盖了dbms_stats尚未拥有的.biru
比如:
-
- To use the VALIDATE OR LIST CHAINED ROWS clauses.
- To collect information on freelist blocks.
OLTP Systems
- High-throughput,insert- and update-intensive
- Large,continuously growing data volume
- Concurrent access by many users
- Tuining goals:
- -Availability
- -Speed
- -Concurrency
- -Recoverability
OLTP Requirements
- Explicit extent allocation
- Indexes:
- -Not too many(B-tree better than bitmap)
- -Reverse key for sequence columns
- -Rebuilt regularly
- Clusters for tables in join queries:
- -Index clusters for growing tables
- -Hash clusters for stable tables
- Materialized views
- Index-organized tables.(快速查询)
OLTP Application Issues
- Use declarative constraints instead of application code.
- Make sure that code is shared.
- Use bind variables rather than literals for optimally shared SQL.
- Use the CURSOR_SHARING parameter.
DSS/Data warehourse
- Queries on large amounts of data
- Heavy use of full table scans
- Tuning goals:
- -Fast response time
- -Focus on SQL statement tuning
- The Parallel Query feature is designed for data warehouse enviroment.
Data Warehouse Requirements
Storge allocation
- Set the block size and DB_FILE_MULTIBLOCK_READ_COUNT carefully.
- Make sure that extent sizes are multiples of this parameter value.确保extent尺寸的大小应当是BLOCK的整数倍
- Run dbms_stats regularly.
DB_FILE_MULTIPLE_READ_COUNT含义：一次性I/O读多少块数据到内存中.
Further Requirements
- Evalute the need for indexes:
- -Use bitmap indexes when possible
- -Use index-organized tables for(range) retrieval by primary keys.
- -Generate histograms for indexed columns that are not distributed uniformly.
- Clustering:Consider hash clusters for performance access.
DW Application Issues
- Parsing time is less important
- The execution plan must be optimal:(最佳的)
- -Use the parallel query feature
- -Tune carefully,using hints if appropriate.
- -Test on realistic amounts of data
- -Consider using PL/SQL functions to code logic into queries.
- Bind variables are problematic.(使用bind variable一般就是减少parse time，在这里一般是不需要的)
Hybird System
除非一般公司预算不足，否则一般情况下会将系统分为OLTP和OLAP系统
| OLTP | Data warehourse |
| Performs index searches | More full table scans |
| Uses B-tree indexes | Uses bitmap indexes |
| Use reverse key indexes | Use index-organized tables |
| CURSOR_SHARING set to Similar can assist performance | CURSOR_SHARING should be left on Exact |
| Should not use Parallel Query | Employes Parallel Query for large operations |
| PCTFREE accroding to expected update activity | PCTFREE can be set to 0 |
| Shared code and bind variables | Literal variables and hints |
| Uses ANALYZE indexes | Generates histograms |
Summary
In this lesson,you should have learned how to do the following:
- Explain the role of the DBA in tuning applications
- Move tables using the ALTER TABLE command
- Redefine a table online
- Create different types of indexes
- Build and manage index-organized tables
- Explain and plan OLTP,DSS,and hybird system
