• 博客访问: 4965734
  • 博文数量: 710
  • 用 户 组: 普通用户
  • 注册时间: 2007-12-07 15:08
  • 认证徽章:
个人简介

了解并联系warehouse: http://blog.itpub.net/19602/viewspace-1059211/

文章分类

全部博文(710)

文章存档

2018年(7)

2017年(7)

2016年(20)

2015年(20)

2014年(45)

2013年(68)

2012年(69)

2011年(87)

2010年(68)

2009年(103)

2008年(140)

2007年(65)

2006年(7)

2005年(3)

2004年(1)

发布时间:2008-04-28 15:02:10

都是doc上的原话,记录一下!FIRST_ROWS(n)提示告诉优化器要执行的sql语句的优化目标是获得最快的响应时间,但是下面这些情况该提示不起作用:The optimizer ignores this hint in DELETE and UPDATE statement blocks and in SELECT statement blocks that contain any of the following syntax:Set operators (UNION, INTERSECT, MINUS, UNION ALL) GROUP BY clause FOR UPDATE clause Aggregate functions DISTINCT operator ORDER BY clauses, when there is no index on the ordering columns These statements cannot be optimized ......【阅读全文】

阅读(3677) | 评论(0) | 转发(0)

发布时间:2008-04-28 13:11:31

doc上介绍hint不能被传播到view内部,除非在创建view时把hint写在view的内部或者使用global hint![@more@]By default, hints do not propagate inside a complex view. For example, if you specify a hint in a query that selects against a complex view, then that hint is not honored, because it is not pushed inside the view.Note: If the view is a single-table, then the hint is not propagated.Unless the hints are inside the base view, they might not be honored from a query against the view.--==================......【阅读全文】

阅读(2708) | 评论(0) | 转发(0)

发布时间:2008-04-28 12:19:16

doc上的这段话对Optimizer Hints介绍的非常清楚,记录一下!当然更重要的是能够熟练的使用各种hint来改变sql的执行计划从而优化sql这里对下面一段做一点解释:You can use hints to specify the following:The optimization approach for a SQL statement --The optimization approach 指的是cbo和rbo The goal of the cost-based optimizer for a SQL statement --The goal of the cost-based optimizer 指的是语句追求的是最大吞吐量( all_rows)还是最快响应时间(first_rows(n)) The access path for a table accessed by the statement ......【阅读全文】

阅读(3820) | 评论(0) | 转发(0)

发布时间:2008-04-25 22:56:18

在性能优化向导中经常会出现Cardinality,Cardinality到底是指什么,看看doc的准确解释:Cardinality is the number of unique values in a column in relation to the number of rows in the table[@more@]CREATE_BITMAP_AREA_SIZEPropertyDescriptionParameter typeIntegerDefault value8388608 (8 MB)ModifiableNoRange of valuesOperating system-dependentBasicNoNote:Oracle does not recommend using the CREATE_BITMAP_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends ......【阅读全文】

阅读(3697) | 评论(0) | 转发(0)

发布时间:2008-04-24 12:16:50

By placing a constraint in the enabled novalidated state, you enable the constraint without locking the table.如doc所言,把constriant由状态disable变成enable时在表上会产生lock,期间所有的DML, queries, or DDL都被阻塞(If you change a constraint from disabled to enabled, then the table must be locked. No new DML, queries, or DDL can occur, because there is no mechanism to ensure that operations on the table conform to the constraint during the enable operation.)。阻塞的原因是:because there is no ......【阅读全文】

阅读(3068) | 评论(0) | 转发(0)

发布时间:2008-04-23 23:42:03

primary key和unique约束是要依赖index的,下面通过试验来看看他们之间的依赖关系![@more@]SQL> select * from tt;ID NA---------- --1 a2 b3 c4 d5 e6 f1000 h已选择7行。SQL> alter table tt add constraint pk_tt primary key (id);表已更改。SQL> select index_name,table_name,uniqueness,status from user_indexes where table_name='TT';INDEX_NAME TABLE_NAME UNIQUENES STATUS------------------------------ ------------------------------ --------- --------PK_TT TT UNIQUE VALIDSQL> alter table tt add const......【阅读全文】

阅读(5159) | 评论(0) | 转发(0)

发布时间:2008-04-23 18:41:09

tanfufa 上传了一些不错的东西,记录一下![@more@]http://www.itpub.net/thread-976360-1-1.html......【阅读全文】

阅读(2436) | 评论(0) | 转发(0)

发布时间:2008-04-23 16:50:44

之前没有想过这个问题,第一次看到这样的说法是在piner的面试题中。当时觉得有点意思,这几天细读doc,上面也提到了这样的说法:Consider an index on a table on which a lot of DML has been performed. Because of the DML, the size of the index can increase to the point where each block is only 50% full, or even less. If the index refers to most of the columns in the table, then the index could actually be larger than the table.[@more@]doc:Re-creating IndexesYou might want to re-create an index to compact ......【阅读全文】

阅读(2753) | 评论(0) | 转发(0)

发布时间:2008-04-22 18:30:49

看到经常有人问此问题!doc的原话,看到了随手记录下![@more@]A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:CREATE INDEX comp_ind ON table1(x, y, z); x, xy, and xyz combinations of columns are leading portions of the index yz, y, and z combinations of columns are not leading portions of the index ......【阅读全文】

阅读(4578) | 评论(0) | 转发(1)

发布时间:2008-04-20 22:46:05

那么究竟什么是DETERMINISTIC Functions,看看doc的解释!DETERMINISTIC FunctionsAny user-written function used in a function-based index must have been declared with the DETERMINISTIC keyword to indicate that the function will always return the same output return value for any given set of input argument values, now and in the future.[@more@]SQL> desc tt 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- ID ......【阅读全文】

阅读(3461) | 评论(0) | 转发(0)

发布时间:2008-04-20 21:56:18

从10g开始CBO下group by之后不能保证数据是按照分组字段排序的![@more@]大致步骤:SQL> select id , avg(age) from tt1 group by id; ID AVG(AGE)---------- ---------- 6 0 4 0 3 0 0 0 10 1执行计划----------------------------------------------------------Plan hash value: 112617873---------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (......【阅读全文】

阅读(3636) | 评论(0) | 转发(0)

发布时间:2008-04-19 19:17:38

在CBO下,oracle会把不含通配符的like表达式转为为等式,前提是like前面的字段类型必须是可变宽度字符类型的。当遇到char类型的字段时它不会这样做![@more@]doc描述:The optimizer simplifies conditions that use the LIKE comparison operator to compare an expression with no wildcard characters into an equivalent condition that uses an equality operator instead.In the following example, the optimizer simplifies the first condition into the second:last_name LIKE 'SMITH' is transformed intolast_name = 'SMIT......【阅读全文】

阅读(3398) | 评论(0) | 转发(0)

发布时间:2008-04-17 12:13:29

可以通过dbms_stats提供的set_table_stats来修改表的统计信息从而使cbo产生我们希望的执行计划![@more@]SQL> edit已写入 file afiedt.buf 1 DECLARE 2 num_rows NUMBER; 3 num_blocks NUMBER; 4 avg_row_len NUMBER; 5 BEGIN 6 -- retrieve the values of table statistics on OE.ORDERS 7 -- statistics table name: OE.SAVESTATS statistics ID: TEST1 8 DBMS_STATS.GET_TABLE_STATS('XYS','TT',null, 9 'MY_STAT_TAB','TEST_20080416', 10 num_rows,num_blocks,avg_row_len); 11 -- ......【阅读全文】

阅读(2932) | 评论(0) | 转发(0)

发布时间:2008-04-17 11:35:06

通过dbms_stats提供的几个procedure可以监视stale statistics的对象以便之后对这些对象搜集statistics。[@more@]SQL> exec dbms_stats.alter_schema_tab_monitoring('xys');PL/SQL 过程已成功完成。SQL> select * from tt;ID N---------- -1 a2 A3 B4 b5 cSQL> update tt set name='m';已更新5行。SQL> commit;提交完成。--user_tab_modifications中没有记录,oracle flush sga到user_tab_modifications可能需要一段时间,不过我们可以通过DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO来手动flushSQL> select * from user_t......【阅读全文】

阅读(3465) | 评论(0) | 转发(0)

发布时间:2008-04-17 10:55:51

利用dbms_stats的procedure gather_schema_stats 和gather_database_stats通过使用参数options 和objlist可以列出那些没有统计信息或者统计信息stale的对象。[@more@]其中options的值可以是:ValueMeaningGATHER STALEGathers statistics on tables with stale statistics.GATHERGathers statistics on all tables. (default)GATHER EMPTYGathers statistics only on tables without statistics.LIST STALECreates a list of tables with stale statistics.LIST EMPTYCreates a list of tables that do not have statistics.GATHER AUTOGa......【阅读全文】

阅读(3282) | 评论(0) | 转发(0)

发布时间:2008-04-16 22:29:54

在创建函数index之后系统会自动产生虚拟列,而这一列也是隐藏的,在创建index时系统自动使用了compute statistics选项为创建的index搜集了statistics,然而并不会为生成的虚拟列搜集statistics,这时如果需要为虚拟列搜集statistics就需要重新分析表或者单独为虚拟列搜集statistics。[@more@]SQL> create table tt(id int , name char(1));表已创建。SQL> insert into tt values(1 , 'a');已创建 1 行。SQL> insert into tt values(2 , 'A');已创建 1 行。SQL> insert into tt values(3 , 'B');已创建 1 行。SQL> insert i......【阅读全文】

阅读(2859) | 评论(0) | 转发(0)

发布时间:2008-04-16 15:22:40

dbms_stats package中提供了过程export_X_stats和import_X_stats(其中X代表column,table,index,schema,database)可以实现统计信息的export和import。通过这种方法我们可以把生产环境的统计信息导入到测试库中模拟生产库。[@more@]SQL> exec dbms_stats.create_stat_table('XYS','MY_STAT_TAB');PL/SQL 过程已成功完成。SQL> DESC MY_STAT_TAB; 名称 是否为空? 类型 ----------------------------------------- -------- ---------------------------- STATID ......【阅读全文】

阅读(4173) | 评论(0) | 转发(0)

发布时间:2008-04-16 10:29:06

doc上的原话,随手记录一下。 [@more@]Oracle Corporation strongly recommends that you use the DBMS_STATS package rather than ANALYZE to collect optimizer statistics. That package lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. Further, the cost-based optimizer will eventually use only statistics that have been collected by DBMS_STATS. See Oracle9i Supplied PL/SQL Packages and Types Reference for mor......【阅读全文】

阅读(2958) | 评论(0) | 转发(0)

发布时间:2008-04-16 10:06:24

对象被分析之后,所有和该对象相关的被parse过的sql都会失效,下次需要执行相同的sql时需要再次parse,因此当系统执行过搜集statistics的操作后面临的可能是在短时间内产生很多hard parse。 其实非常容易理解,因为有了新的statistics,oracle在下次执行相同的sql时会使用新的statistics产生新的执行计划。[@more@]When you generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. Oracle also invalidates any currentl......【阅读全文】

阅读(2669) | 评论(0) | 转发(0)

发布时间:2008-04-15 23:36:45

Use the DBMS_STATS package to generate statistics.Statistics generated include the following:[@more@]Use the DBMS_STATS package to generate statistics.Statistics generated include the following:Table statistics Number of rows Number of blocks Average row length Column statistics Number of distinct values (NDV) in column Number of nulls in column Data distribution (histogram) Index statistics Number of leaf blocks Levels Clustering factor System statistics I/O performance and utilization CPU perf......【阅读全文】

阅读(2522) | 评论(0) | 转发(0)

发布时间:2008-04-15 23:10:41

user_tab_cols differs from "USER_TAB_COLUMNS" in that hidden columns are not filtered out。10.2版本中user_tab_cols比user_tab_columns多出下面几列:HIDDEN_COLUMN VARCHAR2(3)VIRTUAL_COLUMN VARCHAR2(3)SEGMENT_COLUMN_ID NUMBERINTERNAL_COLUMN_ID NOT NULL NUMBERQUALIFIED_COL_NAME VARCHAR2(4000)[@more@]简单的测试过程如下:SQL> desc tt名称 是否为空? 类型----------------------------------------- -------- ----------------------------ID NOT NULL NUMBER(38)NAME VARCHAR2(10)AGE NUMBER(38)COL_HIDD......【阅读全文】

阅读(7362) | 评论(0) | 转发(0)

发布时间:2008-04-14 11:55:11

index和index之间是如何进行hash join的,因为index中包括了rowid,所以通过rowid可以连接。Index JoinsAn index join is a hash join of several indexes that together contain all the table columns that are referenced in the query. If an index join is used, then no table access is needed, because all the relevant column values can be retrieved from the indexes. An index join cannot be used to eliminate a sort operation. The index join is available only with the CBO.Index Join HintsYou can specify an inde......【阅读全文】

阅读(2680) | 评论(0) | 转发(0)

发布时间:2008-04-13 16:00:06

其实Fast Full Index Scans是针对full table scan而言的,Fast Full Index Scans的一些特点也和full table scan相似(如:多块读):Fast Full Index Scans的一些特点:1.Fast full scan is available only with the CBO2.at least one column in the index key has the NOT NULL constraint3. A fast full scan accesses the data in the index itself, without accessing the table4.It cannot be used to eliminate a sort operation, because the data is not ordered by the index key.(粗略的试验了一下发现也是排好序了,按照index_k......【阅读全文】

阅读(3498) | 评论(0) | 转发(0)

发布时间:2008-04-13 13:52:57

唯一index和非唯一index的index entry中尽管都存在rowid,但是其实有一点细微的差别,在唯一index中index_column就是index_column, 数据通过index_column排序;而在非唯一index中index_column其实是index_key+rowid,如果index_key相同,那么会使用rowid排序。[@more@]Data is returned in the ascending order of index columns. Multiple rows with identical values are sorted in ascending order by rowid.--=======================================================SQL> select /*+ index(a idx_tt) */ a.*,rowid from tt a wh......【阅读全文】

阅读(2718) | 评论(0) | 转发(0)

发布时间:2008-04-09 11:28:23

理解oracle lock的概念,有助于理解多用户的并发执行。[@more@]测试过程1:--===========================================================================1.测试表t是一个存在200w左右数据的大表,数据完全来自dba_objectssession1:22:52:28 SQL> select count(object_id) from t;COUNT(OBJECT_ID)---------------- 2019072已用时间: 00: 00: 01.2022:21:46 SQL> create index idx_t on t(object_id);执行上面create index语句,保证能持续一段时间,以便在session2,3,4中进行其他操作session:检测在t上都加了什么类型的......【阅读全文】

阅读(3316) | 评论(0) | 转发(0)
给主人留下些什么吧!~~
留言热议
请登录后留言。

登录 注册