ITPub博客

从下面的doc理解，如果单一的应用程序跑在oracle数据库服务器上，那么（On an Oracle data server system, where there is generally only one application running, the server runs database activity in user space. Activities required to service database requests (such as scheduling, synchronization, I/O, memory management, and process/thread creation and tear down) run in kernel mode. In a system where all CPU is fully utilized, a healthy Oracle system runs between 65% and 95% in user space.）oracle对系统

http://space.itpub.net/756652/viewspace-244138转，不错的文章！[@more@]http://space.itpub.net/756652/viewspace-244138

http://www.itpub.net/thread-148366-1-2.html记录一下![@more@]http://www.itpub.net/thread-148366-1-2.html

什么是Scalability，看看他的形容词的意思：scalable adj.可攀登的可升级的，这里Scalability我通常理解为系统的可攀升能力。doc的原意是：Scalability is a system's ability to process more workload, with a proportional increase in system resource usage. In other words, in a scalable system, if you double the workload, then the system would use twice as many system resources. This sounds obvious, but due to conflicts within the system, the resource usage might exceed twice the original workload.[@more@]Un

不知道该起个什么样的标题来记录这句话，看到这句话时感觉写的非常好，想记录一下：Performance problems are usually the result of contention for, or exhaustion of, some system resource性能问题通常是一些系统资源被争夺或者是被耗尽的结果！[@more@]Performance problems are usually the result of contention for, or exhaustion of, some system resource --===========================================从另外的角度来讲：性能问题来至于：Performance problems generally result from either a lack of throughput, unaccept

What Is Disk Contention?Disk contention occurs when multiple processes try to access the same disk simultaneously. Most disks have limits on both the number of accesses (I/O operations each second) and the amount of data they can transfer each second (I/O data rate, or throughput). When these limits are reached, processes must wait to access the disk.[@more@]What Is Disk Contention?Disk contention occurs when multiple processes try to access the same disk simultaneously. Most disks have limits o

http://www.itpub.net/thread-877110-1-5.html收藏记录一下！[@more@]http://www.itpub.net/thread-877110-1-5.html

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