随笔分类 - Perf Tun
摘要:1、我们研究了一下这几个SQL,发现其中几个SQL都是对一张300万条记录的表做全表扫描,而从SQL来看,每次返回的记录只有几条,这种情况下,完全是可以通过索引来访问的.添加了一个复合索引后,这几个TOP SQL的性能有了明显的改善,SQL的成本下降了99%,过了几分钟CPU也出现了久违的IDLE,系统性能得到了明显的改善.启示
阅读全文
摘要:Table Lock ModesThese table lock modes are automatically assigned by the Oracle server:Row Exclusive(RX):INSERT,UPDATE,DELETERow Share(RS):SELECT ... FOR UPDATEManually Locking a TableManually acquired in LOCK TABLE StatementSQL>LOCK TABLE HR.employees IN share MODE;Share(S)No DML operations allo
阅读全文
摘要:DML LocksA DML transaction gets at least two blocks:A shared table lockAn exclusive row lockA shared table lock主要是阻止DDL语句对当前表进行修改.An exclusive row lock主要是阻止别的transaction修改同一条记录.如何查看锁的信息?有三个重要的动态性能视图:v$transaction:记录了当前活动的transaction.desc v$transactionSQL> desc v$transaction; Name ...
阅读全文
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Define levels of lockingIdentify causes of contentionPrevent locking problemsUse Oracle utilities to detect lock contentionResolve contention in an emergencyResovle deadlock conditionsModes of LockingOracle Database uses t
阅读全文
摘要:Partition table 三种基本类型:ranage、hash、list partition table,另外composite partition就是对以上三种类型的组合类型.Composite PartitioningIdeal for both historical data and data placementProvides high availability and manageability,like range partitioningImproves performance for parallel DML and supports partition-wise joi
阅读全文
摘要:Hash Partitioning OverviewEasy to ImplementEnables better performance for PDML and partion-wise joinsPDML->P DML并发的修改数据partition-wise join只能的Join,只需要有用的Partition 进行Join,没用的partition就不参加JoinInserts rows into partitions automatically based on the hash of the partion keySupports(hash) local indexesp
阅读全文
摘要:Partitioning MethodsThe following partitioning methods are available:RangeHashListComposite按年度分区,2009年、2010年、...每年一张表;自oracle 9i以后,每个表空间可以指定不同的block_size,但是在分区表中,每个不同的分区必须使用相同的db_block_size;CREATE TABLE sales( acct_no NUMBER(5), person VARCHAR2(30), sales_amount...
阅读全文
摘要:Hash Clustered TablesHash Clustered Table的设计目的就是减少I/O的次数,如果设计不当(一般因为size大小设置问题) 导致overflow blocks ,无疑会增大I/O的次数,造成事倍功半.create hash clustered table and show it space info[oracle@arcerzhang usefull_scripts]$ lltotal 4-rw-r--r-- 1 oracle oinstall 3642 May 9 12:45 show_space.sql[oracle@arcerzhang useful.
阅读全文
摘要:Data Access MethodsTo enhance performance ,you can use the following data access methods:ClustersIndexesB-tree(nomal or reverse key)BitmapFunction basedIndex-organized tablesMaterialized viewsClustersCluster TypesHash Clustered TablesExample创建Index ClusterCREATE CLUSTER mycluster(deptno number(2)) s
阅读全文
摘要:本章主要就是讲解,Oracle提供了哪些手段让我们的访问速度更快!ObjectivesAfter completing this lesson,you should be able to do the following:Compare and evalate the different storage structuessExamine different data access methodsImplement different partitionion methodsData Storage Structures最最普通的表集群表(将几个相互关联的表,一起放在一个块里面,读写的时候只进
阅读全文
摘要:Index ReorganizationIndexes on volatile tables are a performance problem.Only entirely empty index blocks go to the free list.If a block contains only one entry,it must be maintained.You may need to rebuild indexes.Monitoring Indexes SpaceTo collect usage statistics regarding an index:(搜集索引的相关数据)SQL
阅读全文
摘要:Database Block SizeMinimize block visits by:Using a large block sizePacking rows tightlyPreventing row migrationPacking rows tightly与Preventing row migraion是一对矛盾体.如果packing rows tightly过量,就会导致row migration.DB_BLOCK_SIZE ParameterThe database block size:Is defined by the DB_BLOCK_SIZE parameter.Is se
阅读全文
摘要:Locally Managed ExtentsCreate a locally managed tablespace:(以后所有的表空间管理方式都需要使用Locally managed method)SQL>CREATE TABLESPACE user_data_1DATAFILE '/u01/oradata/sid_name/1m_1.dbf'SIZE 100MEXTENT MANAGEMENT LOCALUNIFORM SIZE 2M;With the Oracle database the default extent management is local.Pro
阅读全文
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Use automatic segment space managementUse manual segment space managementDescribe the use of Oracle block parametersRecover space from sparsely populated segmentsDescribe and detect chaining and migration of Oracle blocksP
阅读全文
摘要:Using System StatisticsSystem statistics enable the CBO to use CPU and I/O characteristics.System statistics must be gathered on a regular basis;this does not invalidate cached plans.Gathering system statistics equals analyzing system activity for a specified period of time.所有的System Statistics 信息,都
阅读全文
摘要:Generating HistogramsHistogram statistics are generated by:SQL>EXECUTE dbms_stats.gather_table_stats('HR','EMPLOYEES',METHOD_OPT => 'FOR COLUMNS SIZE 10 salary') ;Histograms are specified using the METHOD_OPT argument of the DBMS_STATS gathering procedure.Oracle recomme
阅读全文
摘要:Skewed Data分析上述SQL语句,如果查询的数据很多,多到接近于整张表的数据了,那这个时候查询最优执行计划,就是全表扫描(full scan);如果查询的数据不是很多,此时就最好按照索引查询.数据的分布不同,最优的执行计划也不相同.
阅读全文
摘要:总结Table statistics ---> DBA_TABLES;获取Table statistics,通过DBA_TABLES 数据字典SQL> cldesc DBA_TABLES; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) TABLE...
阅读全文
摘要:When to Gather Statistics在10g、11g里面,搜集statistics信息分自动和手工方式两种.Table StatisticsNumber of rowsNumber of blocks and empty blocksAverage available free spaceNumber of chained or migrated rowsAverage row lengthLast analyze date and smaple sizeData dictionary view:dba_tables查看表的统计信息,主要依据数据字典dba_tablesIndex
阅读全文
摘要:OjbectivesAfter completing this lesson,you should be able to do the followingUnderstanding Statistics管理statistic的package:dbms_stats.Managing StatisticsUse the dbms_stats packages:gather_table_statsgather_index_statsgather_schema_statsgahter_database_statsgather_stable_stats创建表、插入数据SQL> create tab
阅读全文