2011年6月11日星期六

Expert Oracle Database Architecture读书笔记之Setting Up Environment

Setting Up login File
  SQL*Plus allows us to setup a login.sql file, a script that is executed each and every time we 
start SQL*Plus. Further, it allows us to set an environment variable, SQLPATH, so that it can find this 
login.sql script, no matter what directory it is in. 

define _editor=vi 
set serveroutput on size 1000000 
set trimspool on 
set long 5000 
set linesize 100 
set pagesize 9999 
column plan_plus_exp format a80 
column global_name new_value gname 
set termout off 
define gname=idle 
column global_name new_value gname 
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0, length(global_name), 
dot-1) ) global_name 
from (select global_name, instr(global_name,'.') dot from global_name ); 
set sqlprompt '&gname> ' 
set termout on 

Setting up Autotrace in SQL*Plus
Initial Setup 
This is what I like to do to get AUTOTRACE working: 
•  cd [ORACLE_HOME]/rdbms/admin 
•  log into SQL*Plus as SYSTEM 
•  run @utlxplan 
•  run CREATE PUBLIC SYNONYM PLAN_TABLE FOR PLAN_TABLE; 
•  run GRANT ALL ON PLAN_TABLE TO PUBLIC; 
You can replace the GRANT TO PUBLIC with some user if you want. By making it public, you let anyone trace using SQL*Plus (not a bad thing, in my opinion). This prevents every user from having to install their own plan table. The alternative is for you to run @utlxplan in every schema from which you want to use AUTOTRACE. 
The next step is creating and granting the PLUSTRACE role: 
•  cd [ORACLE_HOME]/sqlplus/admin 
•  log into SQL*Plus as SYS or AS SYSDBA 
•  run @plustrce 
•  run GRANT PLUSTRACE TO PUBLIC; 
Again, you can replace PUBLIC in the GRANT command with some user if you want.

Controlling the Report 
•  SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the default. 
•  SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer execution path. 
•  SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL statement execution statistics. 
•  SET AUTOTRACE ON - The AUTOTRACE report includes both the optimizer execution path and the 
SQL statement execution statistics. 
•  SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. 

Database Statistic Name
Description
recursive calls
Number of recursive calls generated at both the user and system level. Oracle maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.
db block gets
Number of times a CURRENT block was requested.
consistent gets
Number of times a consistent read was requested for a block.
physical reads
Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
redo size
Total amount of redo generated in bytes.
bytes sent via SQL*Net to client
Total number of bytes sent to the client from the foreground processes.
bytes received via SQL*Net from client
Total number of bytes received from the client over Oracle Net.
SQL*Net roundtrips to/from client
Total number of Oracle Net messages sent to and received from the client.
sorts (memory)
Number of sort operations that were performed completely in memory and did not require any disk writes.
sorts (disk)
Number of sort operations that required at least one disk write.
rows processed
Number of rows processed during the operation.

针对以上3个概念进行的说明解释及关系如下:
1、DB Block Gets(当前请求的块数目)
当前模式块意思就是在操作中正好提取的块数目,而不是在一致性读的情况下而产生的块数。正常的情况下,一个查询提取的块是在查询开始的那个时间点上存在的数据块,当前块是在这个时刻存在的数据块,而不是在这个时间点之前或者之后的数据块数目。

2、Consistent Gets(数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块)
这里的概念是在处理你这个操作的时候需要在一致性读状态上处理多少个块,这些块产生的主要原因是因为由于在你查询的过程中,由于其他会话对数据块进行操作,而对所要查询的块有了修改,但是由于我们的查询是在这些修改之前调用的,所以需要对回滚段中的数据块的前映像进行查询,以保证数据的一致性。这样就产生了一致性读。

3、Physical Reads(物理读)
就是从磁盘上读取数据块的数量,其产生的主要原因是:
1、 在数据库高速缓存中不存在这些块
2、 全表扫描
3、 磁盘排序

它们三者之间的关系大致可概括为:
逻辑读指的是Oracle从内存读到的数据块数量。一般来说是'consistent gets' + 'db block gets'。当在内存中找不到所需的数据块的话就需要从磁盘中获取,于是就产生了'phsical reads'。



Explain plan

  An explain plan is a representation of the access path that is taken when a query is executed within Oracle.

 Query processing can be divided into 7 phases:
 [1] Syntactic - checks the syntax of the query
 [2] Semantic - checks that all objects exist and are accessible
 [3] View Merging - rewrites query as join on base tables as opposed to using views
 [4] Statement Transformation - rewrites query transforming some complex constructs into simpler ones
       where appropriate (e.g. subquery unnesting, in/or transformation)
 [5] Optimization - determines the optimal access path for the query to take. With the Rule Based
      Optimizer (RBO) it uses a set of heuristics to determine access path. With the Cost Based
      Optimizer (CBO) we use statistics to analyze the relative costs of accessing objects.
 [6] QEP Generation
 [7] QEP Execution
 (QEP = Query Evaluation Plan)

 Steps [1]-[6] are handled by the parser.
 Step [7] is the execution of the statement.

 Explain Plan一般按缩进长度来判断,缩进最大的最先执行,如果有2行缩进一样,那么就先执行上面的。

OPERATION and OPTIONS Values Produced by EXPLAIN PLAN

Operation
Option
Description
AND-EQUAL
.
Operation accepting multiple sets of rowids, returning the intersection of the sets, eliminating duplicates. Used for the single-column indexes access path.
BITMAP
CONVERSION
TO ROWIDS converts bitmap representations to actual rowids that can be used to access the table.
FROM ROWIDS converts the rowids to a bitmap representation.
COUNT returns the number of rowids if the actual values are not needed.
BITMAP
INDEX
SINGLE VALUE looks up the bitmap for a single key value in the index.
RANGE SCAN retrieves bitmaps for a key value range.
FULL SCAN performs a full scan of a bitmap index if there is no start or stop key.
BITMAP
MERGE
Merges several bitmaps resulting from a range scan into one bitmap.
BITMAP
MINUS
Subtracts bits of one bitmap from another. Row source is used for negated predicates. Can be used only if there are nonnegated predicates yielding a bitmap from which the subtraction can take place. An example appears in "Viewing Bitmap Indexes with EXPLAIN PLAN".
BITMAP
OR
Computes the bitwise OR of two bitmaps.
BITMAP
AND
Computes the bitwise AND of two bitmaps.
BITMAP
KEY ITERATION
Takes each row from a table row source and finds the corresponding bitmap from a bitmap index. This set of bitmaps are then merged into one bitmap in a following BITMAP MERGE operation.
CONNECT BY
.
Retrieves rows in hierarchical order for a query containing a CONNECT BY clause.
CONCATENATION
.
Operation accepting multiple sets of rows returning the union-all of the sets.
COUNT
.
Operation counting the number of rows selected from a table.
COUNT
STOPKEY
Count operation where the number of rows returned is limited by the ROWNUM expression in the WHERE clause.
DOMAIN INDEX
.
Retrieval of one or more rowids from a domain index. The options column contain information supplied by a user-defined domain index cost function, if any.
FILTER
.
Operation accepting a set of rows, eliminates some of them, and returns the rest.
FIRST ROW
.
Retrieval of only the first row selected by a query.
FOR UPDATE
.
Operation retrieving and locking the rows selected by a query containing a FOR UPDATE clause.
HASH
GROUP BY
Operation hashing a set of rows into groups for a query with a GROUP BY clause.
HASH JOIN
(These are join operations.)
.
Operation joining two sets of rows and returning the result. This join method is useful for joining large data sets of data (DSS, Batch). The join condition is an efficient way of accessing the second table.
Query optimizer uses the smaller of the two tables/data sources to build a hash table on the join key in memory. Then it scans the larger table, probing the hash table to find the joined rows.
HASH JOIN
ANTI
Hash (left) antijoin
HASH JOIN
SEMI
Hash (left) semijoin
HASH JOIN
RIGHT ANTI
Hash right antijoin
HASH JOIN
RIGHT SEMI
Hash right semijoin
HASH JOIN
OUTER
Hash (left) outer join
HASH JOIN
RIGHT OUTER
Hash right outer join
INDEX
(These are access methods.)
UNIQUE SCAN
Retrieval of a single rowid from an index.
INDEX
RANGE SCAN
Retrieval of one or more rowids from an index. Indexed values are scanned in ascending order.
INDEX
RANGE SCAN DESCENDING
Retrieval of one or more rowids from an index. Indexed values are scanned in descending order.
INDEX
FULL SCAN
Retrieval of all rowids from an index when there is no start or stop key. Indexed values are scanned in ascending order.
INDEX
FULL SCAN DESCENDING
Retrieval of all rowids from an index when there is no start or stop key. Indexed values are scanned in descending order.
INDEX
FAST FULL SCAN
Retrieval of all rowids (and column values) using multiblock reads. No sorting order can be defined. Compares to a full table scan on only the indexed columns. Only available with the cost based optimizer.
INDEX
SKIP SCAN
Retrieval of rowids from a concatenated index without using the leading column(s) in the index. Introduced in Oracle9i. Only available with the cost based optimizer.
INLIST ITERATOR
.
Iterates over the next operation in the plan for each value in the IN-list predicate.
INTERSECTION
.
Operation accepting two sets of rows and returning the intersection of the sets, eliminating duplicates.
MERGE JOIN
(These are join operations.)
.
Operation accepting two sets of rows, each sorted by a specific value, combining each row from one set with the matching rows from the other, and returning the result.
MERGE JOIN
OUTER
Merge join operation to perform an outer join statement.
MERGE JOIN
ANTI
Merge antijoin.
MERGE JOIN
SEMI
Merge semijoin.
MERGE JOIN
CARTESIAN
Can result from 1 or more of the tables not having any join conditions to any other tables in the statement. Can occur even with a join and it may not be flagged as CARTESIAN in the plan.
CONNECT BY
.
Retrieval of rows in hierarchical order for a query containing a CONNECT BY clause.
MAT_VIEW REWITE ACCESS
(These are access methods.)
FULL
Retrieval of all rows from a materialized view.
MAT_VIEW REWITE ACCESS
SAMPLE
Retrieval of sampled rows from a materialized view.
MAT_VIEW REWITE ACCESS
CLUSTER
Retrieval of rows from a materialized view based on a value of an indexed cluster key.
MAT_VIEW REWITE ACCESS
HASH
Retrieval of rows from materialized view based on hash cluster key value.
MAT_VIEW REWITE ACCESS
BY ROWID RANGE
Retrieval of rows from a materialized view based on a rowid range.
MAT_VIEW REWITE ACCESS
SAMPLE BY ROWID RANGE
Retrieval of sampled rows from a materialized view based on a rowid range.
MAT_VIEW REWITE ACCESS
BY USER ROWID
If the materialized view rows are located using user-supplied rowids.
MAT_VIEW REWITE ACCESS
BY INDEX ROWID
If the materialized view is nonpartitioned and rows are located using index(es).
MAT_VIEW REWITE ACCESS
BY GLOBAL INDEX ROWID
If the materialized view is partitioned and rows are located using only global indexes.
MAT_VIEW REWITE ACCESS
BY LOCAL INDEX ROWID
If the materialized view is partitioned and rows are located using one or more local indexes and possibly some global indexes.
Partition Boundaries:
The partition boundaries might have been computed by:
A previous PARTITION step, in which case the PARTITION_START and PARTITION_STOP column values replicate the values present in the PARTITION step, and the PARTITION_ID contains the ID of the PARTITION step. Possible values forPARTITION_START and PARTITION_STOP are NUMBER(n), KEYINVALID.
The MAT_VIEW REWRITE ACCESS or INDEX step itself, in which case the PARTITION_ID contains the ID of the step. Possible values for PARTITION_START and PARTITION_STOP are NUMBER(n), KEYROW REMOVE_LOCATION (MAT_VIEW REWRITEACCESS only), and INVALID.
MINUS
.
Operation accepting two sets of rows and returning rows appearing in the first set but not in the second, eliminating duplicates.
NESTED LOOPS
(These are join operations.)
.
Operation accepting two sets of rows, an outer set and an inner set. Oracle compares each row of the outer set with each row of the inner set, returning rows that satisfy a condition. This join method is useful for joining small subsets of data (OLTP). The join condition is an efficient way of accessing the second table.
NESTED LOOPS
OUTER
Nested loops operation to perform an outer join statement.
PARTITION
.
Iterates over the next operation in the plan for each partition in the range given by the PARTITION_START and PARTITION_STOP columns. PARTITION describes partition boundaries applicable to a single partitioned object (table or index) or to a set of equi-partitioned objects (a partitioned table and its local indexes). The partition boundaries are provided by the values of PARTITION_START and PARTITION_STOP of the PARTITION. Refer to Table 19-1 for valid values of partition start/stop.
PARTITION
SINGLE
Access one partition.
PARTITION
ITERATOR
Access many partitions (a subset).
PARTITION
ALL
Access all partitions.
PARTITION
INLIST
Similar to iterator, but based on an IN-list predicate.
PARTITION
INVALID
Indicates that the partition set to be accessed is empty.
PX ITERATOR
BLOCK,CHUNK
Implements the division of an object into block or chunk ranges among a set of parallel slaves
PXCOORDINATOR
.
Implements the Query Coordinator which controls, schedules, and executes the parallel plan below it using parallel query slaves. It also represents a serialization point, as the end of the part of the plan executed in parallel and always has aPX SEND QC operation below it.
PX PARTITION
.
Same semantics as the regular PARTITION operation except that it appears in a parallel plan
PX RECEIVE
.
Shows the consumer/receiver slave node reading repartitioned data from a send/producer (QC or slave) executing on a PX SEND node. This information was formerly displayed into the DISTRIBUTION column. See Table 19-2.
PX SEND
QC(RANDOM), HASHRANGE
Implements the distribution method taking place between two parallel set of slaves. Shows the boundary between two slave sets and how data is repartitioned on the send/producer side (QC or side. This information was formerly displayed into the DISTRIBUTION column. See Table 19-2.
REMOTE
.
Retrieval of data from a remote database.
SEQUENCE
.
Operation involving accessing values of a sequence.
SORT
AGGREGATE
Retrieval of a single row that is the result of applying a group function to a group of selected rows.
SORT
UNIQUE
Operation sorting a set of rows to eliminate duplicates.
SORT
GROUP BY
Operation sorting a set of rows into groups for a query with a GROUP BY clause.
SORT
JOIN
Operation sorting a set of rows before a merge-join.
SORT
ORDER BY
Operation sorting a set of rows for a query with an ORDER BY clause.
TABLE ACCESS
(These are access methods.)
FULL
Retrieval of all rows from a table.
TABLE ACCESS
SAMPLE
Retrieval of sampled rows from a table.
TABLE ACCESS
CLUSTER
Retrieval of rows from a table based on a value of an indexed cluster key.
TABLE ACCESS
HASH
Retrieval of rows from table based on hash cluster key value.
TABLE ACCESS
BY ROWID RANGE
Retrieval of rows from a table based on a rowid range.
TABLE ACCESS
SAMPLE BY ROWID RANGE
Retrieval of sampled rows from a table based on a rowid range.
TABLE ACCESS
BY USER ROWID
If the table rows are located using user-supplied rowids.
TABLE ACCESS
BY INDEX ROWID
If the table is nonpartitioned and rows are located using index(es).
TABLE ACCESS
BY GLOBAL INDEX ROWID
If the table is partitioned and rows are located using only global indexes.
TABLE ACCESS
BY LOCAL INDEX ROWID
If the table is partitioned and rows are located using one or more local indexes and possibly some global indexes.
Partition Boundaries:
The partition boundaries might have been computed by:
A previous PARTITION step, in which case the PARTITION_START and PARTITION_STOP column values replicate the values present in the PARTITION step, and the PARTITION_ID contains the ID of the PARTITION step. Possible values forPARTITION_START and PARTITION_STOP are NUMBER(n), KEYINVALID.
The TABLE ACCESS or INDEX step itself, in which case the PARTITION_ID contains the ID of the step. Possible values for PARTITION_START and PARTITION_STOP are NUMBER(n), KEYROW REMOVE_LOCATION (TABLE ACCESS only), and INVALID.
UNION
.
Operation accepting two sets of rows and returns the union of the sets, eliminating duplicates.
VIEW
.
Operation performing a view's query and then returning the resulting rows to another operation.






没有评论:

发表评论