ITPub博客

Some pitfalls are discussed in the following sections:Hit ratios Wait events with timed statistics Comparing Oracle statistics with other factors Wait events without timed statistics Idle wait events Computed statistics [@more@]Interpreting StatisticsWhen initially examining performance data, you can formulate potential theories by examining your statistics. One way to ensure that your interpretation of the statistics is correct is to perform cross-checks with other data. This establishes whethe

尽管oracle已经不建议使用SORT_AREA_SIZE参数了，oracle建议通过设置PGA_AGGREGATE_TARGET来激活自动工作区管理。不过在doc上看到了，随手记录一下！[@more@]Specifying Memory for SQL Work Area with SORT_AREA_SIZEThe memory for the SQL work area can also be controlled with the SORT_AREA_SIZE initialization parameter.Note: Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL wor

创建同样大小的表空间，永久表空间要比临时标空间慢很多，原因就像doc所言创建数据文件时block需要被初始化为"empty",其实我觉得就是初始化成oracle可以识别的以db_block_size为大小的block格式吧；而临时文件不需要被初始化![@more@]During tablespace creation, the datafiles that make up the tablespace are initialized with special "empty" block images. TEMPFILES are not initialized.SQL> set timing onSQL> create tablespace test datafile 'C:APPWCHORADATADB11TEST01.DBF' SIZE 1G;表空间已创建

需要注意的一句话是：Usually, the optimizer does not consider the order in which tables appear in the FROM clause when choosing an execution plan.[@more@]Choosing Execution Plans for Joins with the RBONote: The following considerations apply to both the cost-based and rule-based approaches:The optimizer first determines whether joining two or more of the tables definitely results in a row source containing at most one row. The optimizer recognizes such situations based on UNIQUE and PRIMARY KEY c

下面列出来的这些条件都会执行Full Table Scan，因此我们在使用时要特别注意！[@more@]RBO Path 15: Full Table ScanThis access path is available for any SQL statement, regardless of its WHERE clause conditions, except when its FROM clause contains SAMPLE or SAMPLE BLOCK.Note that the full table scan is the lowest ranked access path on the list. This means that the RBO always chooses an access path that uses an index if one is available, even if a full table scan might execute faster.The following cond

If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on each index to retrieve the rowids of the rows that satisfy each condition. Oracle then merges the sets of rowids to obtain a set of rowids of rows that satisfy all conditions. Oracle then accesses the table using these rowids.Oracle can merge up to five indexes. If the WHERE clause uses columns of more than five single-column indexes, then Oracle merges five of them,

当我第一次看到hint相关内容中讲到AND_EQUAL时（The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.）感觉没有读懂，不知道这里提到的merge到底指的什么意思，但是当看到RBO Path 9: Single-Column Indexes章节中的解释时对AND_EQUAL已经非常清楚了，原来是对单列index scan找到满足条件的rowid的merge（If the WHERE clauses uses columns of many single-column indexes, then Oracle executes the statement by performing a range scan on

RBO Path 1: Single Row by RowidThis access path is available only if the statement's WHERE clause identifies the selected rows by rowid or with the CURRENT OF CURSOR embedded SQL syntax supported by the Oracle precompilers. To execute the statement, Oracle accesses the table by rowid.[@more@]验证1：This access path is available only if the statement's WHERE clause identifies the selected rows by rowidSQL> select tt.* , rowid from tt;ID NA ROWID---------- -- ------------------1 a AAAEG4AAEAAAA

doc上的原话，随手记录下！Note that the full table scan is the lowest ranked access path on the list. This means that the RBO always chooses an access path that uses an index if one is available, even if a full table scan might execute faster.从上面这段话我们知道full table scan 是最低等级的访问路径，那意味着下面列出来的15种访问路径的数字越小等级越高，也就是说使用访问路径的等级越高rbo认为效率越高，当然实际情况可能并非如此。[@more@]Understanding Access Paths for the RBOUsing the RBO, the optimizer

都是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

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

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

在性能优化向导中经常会出现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

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

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

tanfufa 上传了一些不错的东西，记录一下！[@more@]http://www.itpub.net/thread-976360-1-1.html

之前没有想过这个问题，第一次看到这样的说法是在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

看到经常有人问此问题！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

那么究竟什么是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

从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 (