随笔分类 -  Perf Tun

摘要:1、我们研究了一下这几个SQL,发现其中几个SQL都是对一张300万条记录的表做全表扫描,而从SQL来看,每次返回的记录只有几条,这种情况下,完全是可以通过索引来访问的.添加了一个复合索引后,这几个TOP SQL的性能有了明显的改善,SQL的成本下降了99%,过了几分钟CPU也出现了久违的IDLE,系统性能得到了明显的改善.启示 阅读全文
posted @ 2013-06-24 17:52 ArcerZhang 阅读(176) 评论(0) 推荐(0) 编辑
摘要: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 阅读全文
posted @ 2013-05-10 14:08 ArcerZhang 阅读(290) 评论(0) 推荐(0) 编辑
摘要: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 ... 阅读全文
posted @ 2013-05-10 13:58 ArcerZhang 阅读(216) 评论(0) 推荐(0) 编辑
摘要: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 阅读全文
posted @ 2013-05-10 11:21 ArcerZhang 阅读(211) 评论(0) 推荐(0) 编辑
摘要: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 阅读全文
posted @ 2013-05-09 19:18 ArcerZhang 阅读(458) 评论(0) 推荐(0) 编辑
摘要: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 阅读全文
posted @ 2013-05-09 16:56 ArcerZhang 阅读(205) 评论(0) 推荐(0) 编辑
摘要: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... 阅读全文
posted @ 2013-05-09 16:46 ArcerZhang 阅读(270) 评论(0) 推荐(0) 编辑
摘要: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. 阅读全文
posted @ 2013-05-09 13:34 ArcerZhang 阅读(239) 评论(0) 推荐(0) 编辑
摘要: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 阅读全文
posted @ 2013-05-09 11:47 ArcerZhang 阅读(182) 评论(0) 推荐(0) 编辑
摘要:本章主要就是讲解,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最最普通的表集群表(将几个相互关联的表,一起放在一个块里面,读写的时候只进 阅读全文
posted @ 2013-05-08 19:12 ArcerZhang 阅读(198) 评论(0) 推荐(0) 编辑
摘要: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 阅读全文
posted @ 2013-05-08 18:37 ArcerZhang 阅读(296) 评论(0) 推荐(0) 编辑
摘要: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 阅读全文
posted @ 2013-05-08 16:56 ArcerZhang 阅读(187) 评论(0) 推荐(0) 编辑
摘要: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 阅读全文
posted @ 2013-05-08 14:44 ArcerZhang 阅读(365) 评论(0) 推荐(0) 编辑
摘要: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 阅读全文
posted @ 2013-05-08 11:33 ArcerZhang 阅读(192) 评论(0) 推荐(0) 编辑
摘要: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 信息,都 阅读全文
posted @ 2013-05-07 18:48 ArcerZhang 阅读(156) 评论(0) 推荐(0) 编辑
摘要: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 阅读全文
posted @ 2013-05-07 17:52 ArcerZhang 阅读(231) 评论(0) 推荐(0) 编辑
摘要:Skewed Data分析上述SQL语句,如果查询的数据很多,多到接近于整张表的数据了,那这个时候查询最优执行计划,就是全表扫描(full scan);如果查询的数据不是很多,此时就最好按照索引查询.数据的分布不同,最优的执行计划也不相同. 阅读全文
posted @ 2013-05-07 16:54 ArcerZhang 阅读(129) 评论(0) 推荐(0) 编辑
摘要:总结Table statistics ---> DBA_TABLES;获取Table statistics,通过DBA_TABLES 数据字典SQL> cldesc DBA_TABLES; Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) TABLE... 阅读全文
posted @ 2013-05-07 15:32 ArcerZhang 阅读(278) 评论(0) 推荐(0) 编辑
摘要: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 阅读全文
posted @ 2013-05-07 13:27 ArcerZhang 阅读(123) 评论(0) 推荐(0) 编辑
摘要: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 阅读全文
posted @ 2013-05-07 13:15 ArcerZhang 阅读(202) 评论(1) 推荐(0) 编辑

点击右上角即可分享
微信分享提示