Oracle 性能 视图

(2)-V$SQL_PLAN

　　本视图提供了一种方式检查那些执行过的并且仍在缓存中的 cursor的执行计划。

　　通常，本视图提供的信息与打印出的 EXPLAIN PLAN非常相似，不过， EXPLAIN PLAN显示的是理论上的计划，并不一定在执行的时候就会被使用，但 V$SQL_PLAN中包括的是实际被使用的计划。获自 EXPLAIN PLAN语句的执行计划跟具体执行的计划可以不同，因为 cursor可能被不同的 session参数值编译 (如， HASH_AREA_SIZE)。

V$SQL_PLAN 中数据可以：

l         确认当前的执行计划

l         鉴别创建表索引效果

l         寻找 cursor包括的存取路径 (例如，全表查询或范围索引查询 )

l         鉴别索引的选择是否最优

l         决定是否最优化选择的详细执行计划 (如， nested loops join)如开发者所愿。

　　本视图同时也可被用于当成一种关键机制在计划对比中。计划对比通常用于下列各项发生改变时：

l         删除和新建索引

l         在数据库对象上执行分析语句

l         修改初始参数值

l         从 rule-based切换至 cost-based优化方式

l         升级应用程序或数据库到新版本之后

　　如果之前的计划仍然在 (例如，从 V$SQL_PLAN选择出记录并保存到 oracle表中供参考 )，那么就有可能去鉴别一条 SQL语句在执行计划改变后性能方面有什么变化。

注意：

Oracle公司强烈推荐你使用 DBMS_STATS包而非 ANALYZE收集优化统计。该包可以让你平行地搜集统计项，收集分区对象 (partitioned objects)的全集统计，并且通过其它方式更好的调整你的统计收集方式。此处， cost-based优化器将最终使用被 DBMS_STATS收集的统计项。浏览 Oracle9i Supplied PL/SQL包和类型参考以获得关于此包的更多信息。

不过，你必须使用 ANALYZE语句而非 DBMS_STATS进行统计收集，不涉及 cost-based优化器，就像：

·使用 VALIDATE或 LIST CHAINED ROWS子句

·在 freelist blocks上收集信息。

V$SQL_PLAN 中的常用列：

除了一些新加列，本视图几乎包括所有的 PLAN_TABLE列，那些同样存在于 PLAN_TABLE中的列拥有相同的值：

l         ADDRESS：当前 cursor父句柄位置

l         HASH_VALUE：在 library cache中父语句的 HASH值。

ADDRESS和 HASH_VALUE这两列可以被用于连接 v$sqlarea查询 cursor-specific 信息。

l         CHILD_NUMBER：使用这个执行计划的子 cursor数

列 ADDRESS,HASH_VALUE以及 CHILD_NUMBER可被用于连接 v$sql查询子 cursor信息。

l         OPERATION: 在各步骤执行内部操作的名称，例如： TABLE ACCESS

l         OPTIONS: 描述列 OPERATION在操作上的变种，例如： FULL

l         OBJECT_NODE: 用于访问对象的数据库链接 database link 的名称对于使用并行执行的本地查询该列能够描述操作中输出的次序。

l         OBJECT#: 表或索引对象数量

l         OBJECT_OWNER: 对于包含有表或索引的架构 schema 给出其所有者的名称

l         OBJECT_NAME: 表或索引名

l         OPTIMIZER: 执行计划中首列的默认优化模式；例如， CHOOSE。比如业务是个存储数据库，它将告知是否对象是最优化的。

l         ID: 在执行计划中分派到每一步的序号。

l         PARENT_ID: 对 ID 步骤的输出进行操作的下一个执行步骤的 ID。

l         DEPTH: 业务树深度 (或级 )。

l         POSITION: 对于具有相同 PARENT_ID 的操作其相应的处理次序。

l         COST: cost-based方式优化的操作开销的评估，如果语句使用 rule-based方式，本列将为空。

l         CARDINALITY: 根据 cost-based方式操作所访问的行数的评估。

l         BYTES: 根据 cost-based方式操作产生的字节的评估，。

l         OTHER_TAG: 其它列的内容说明。

l         PARTITION_START: 范围存取分区中的开始分区。

l         PARTITION_STOP: 范围存取分区中的停止分区。

l         PARTITION_ID: 计算 PARTITION_START和 PARTITION_STOP这对列值的步数

l         OTHER: 其它信息即执行步骤细节，供用户参考。

l         DISTRIBUTION: 为了并行查询，存储用于从生产服务器到消费服务器分配列的方法

l         CPU_COST: 根据 cost-based方式 CPU操作开销的评估。如果语句使用 rule-based方式，本列为空。

l         IO_COST: 根据 cost-based方式 I/O操作开销的评估。如果语句使用 rule-based方式，本列为空。

l         TEMP_SPACE: cost-based方式操作 (sort or hash-join)的临时空间占用评估。如果语句使用 rule-based方式，本列为空。

l         ACCESS_PREDICATES: 指明以便在存取结构中定位列，例如，在范围索引查询中的开始或者结束位置。

l         FILTER_PREDICATES: 在生成数据之前即指明过滤列。

CONNECT BY操作产生 DEPTH列替换 LEVEL伪列，有时被用于在 SQL脚本中帮助 indent PLAN_TABLE数据

V$SQL_PLAN 中的连接列

　　列 ADDRESS,HASH_VALUE和 CHILD_NUMBER被用于连接 V$SQL或 V$SQLAREA来获取 cursor-specific信息，例如， BUFFER_GET,或连接 V$SQLTEXT获取完整的 SQL语句。

Column View                                                            Joined                 Column(s)

ADDRESS, HASH_VALUE                                    V$SQLAREA    ADDRESS, HASH_VALUE

ADDRESS,HASH_VALUE,CHILD_NUMBER      V$SQL       ADDRESS,HASH_VALUE,CHILD_NUMBER

ADDRESS, HASH_VALUE                                    V$SQLTEXT      ADDRESS, HASH_VALUE

确认 SQL 语句的优化计划

　　下列语句显示一条指定 SQL语句的执行计划。查看一条 SQL语句的执行计划是调整优化 SQL语句的第一步。这条被查询到执行计划的 SQL语句是通过语句的 HASH_VALUE和 ADDRESS列识别。分两步执行：

1.SELECT sql_text, address, hash_value FROM v$sql

WHERE sql_text like '%TAG%';

SQL_TEXT   ADDRESS HASH_VALUE

-------- -------- ----------

          82157784 1224822469

2.SELECT operation, options, object_name, cost FROM v$sql_plan

WHERE address = '82157784' AND hash_value = 1224822469;

OPERATION            OPTIONS       OBJECT_NAME        COST

-------------------- ------------- ------------------ ----

SELECT STATEMENT                                         5

SORT

    AGGREGATE

      HASH JOIN                                          5

      TABLE ACCESS   FULL          DEPARTMENTS           2

       TABLE ACCESS   FULL          EMPLOYEES             2

 

转载:学习Oracle动态性能表-(4)-V$SESSTAT

　　按照 OracleOnlineBook中的描述， v$sesstat存储 session从 login到 logout的详细资源使用统计。

　　类似于 v$sysstat,该视图存储下列类别的统计：

l         事件发生次数的统计，如用户提交数。

l         数据产生，存取或者操作的 total列 (如： redo size)

l         执行操作所花费的时间累积，例如 session CPU占用 (如果 TIMED_STATISTICS值为 true)

注意：

如果初始参数 STATISTICS_LEVEL被设置为 TYPICAL或 ALL，时间统计被数据库自动收集如果 STATISTICS_LEVEL被设置为 BASIC，你必须设置 TIMED_STATISTICS值为 TRUE以打开收集功能。

如果你已设置了 DB_CACHE_ADVICE,TIMED_STATISTICS或 TIMED_OS_STATISTICS，或在初始参数文件或使用 ALTER_SYSTEM或 ALTER SESSION，那么你所设定的值的值将覆盖 STATISTICS_LEVEL的值。

v$sysstat和 v$sesstat差别如下：

n         v$sesstat只保存 session数据，而 v$sysstat则保存所有 sessions的累积值。

n         v$sesstat只是暂存数据， session退出后数据即清空。 v$sysstat则是累积的，只有当实例被 shutdown才会清空。

n         v$sesstat不包括统计项名称，如果要获得统计项名称则必须与 v$sysstat或 v$statname连接查询获得。

v$sesstat可被用于找出如下类型 session：

n         高资源占用

n         高平均资源占用比 (登陆后资源使用率 )

n         默认资源占用比 (两快照之间 )

在 V$SESSTAT 中使用统计

　　多数 v$sesstat中的统计参考是 v$sysstat描述的子集，包括 session logical reads, CPU used by this session, db block changes, redo size, physical writes, parse count (hard), parse count (total), sorts (memory), and sorts (disk).

V$SESSTAT 常用列说明

n         SID： session唯一 ID

n         STATISTIC#：资源唯一 ID

n         VALUE：资源使用

示例 1 ：下列找出当前 session 中最高的 logical 和 Physical I/O 比率 .

　　下列 SQL语句显示了所有连接到数据库的 session逻辑、物理读比率 (每秒 )。 logical和 physical I/O比率是通过自登陆后的时间消耗计算得出。对于 sessions连接到数据库这种长周期操作而言也许不够精确，不过做个示例却足够了。

先获得 session逻辑读和物理读统计项的 STATISTIC#值：

SELECT name , statistic#

FROM V$STATNAME

WHERE name IN ( 'session logical reads' , 'physical reads' ) ;

NAME                           STATISTIC#

------------------------------ ----------

session logical reads                   9

physical reads                         40

通过上面获得的 STATISTIC#值执行下列语句：

SELECT ses.sid

     , DECODE(ses.action,NULL,'online','batch')          "User"

     , MAX(DECODE(sta.statistic#,9,sta.value,0))

       /greatest(3600*24*(sysdate-ses.logon_time),1)     "Log IO/s"

     , MAX(DECODE(sta.statistic#,40,sta.value,0))

       /greatest(3600*24*(sysdate-ses.logon_time),1)     "Phy IO/s"

     , 60*24*(sysdate-ses.logon_time)                    "Minutes"

FROM V$SESSION ses

    , V$SESSTAT sta

WHERE ses.status     = 'ACTIVE'

AND sta.sid        = ses.sid

AND sta.statistic# IN (9,40)

GROUP BY ses.sid, ses.action, ses.logon_time

ORDER BY

        SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )

      / greatest(3600*24*(sysdate-ses.logon_time),1) DESC;

SID User   Log IO/s Phy IO/s Minutes

----- ------ -------- -------- -------

1951 batch       291    257.3       1

470 online    6,161     62.9       0

730 batch     7,568     43.2     197

2153 online    1,482     98.9      10

2386 batch     7,620     35.6      35

1815 batch     7,503     35.5     26

1965 online    4,879     42.9      19

1668 online    4,318     44.5       1

1142 online      955     69.2      35

1855 batch       573     70.5       8

1971 online    1,138     56.6       1

1323 online    3,263     32.4       5

1479 batch     2,857     35.1       3

421 online    1,322     46.8      15

2405 online      258     50.4       8

示例 2 ：又例如通过 v$sesstat 和 v$statname 连接查询某个 SID 各项信息。

select a.*,b. name

from v$sesstat a,v$statname b

where a.sid= 10 and a.statistic#=b.statistic#;

(2)-v$mystat

　　本视图是 v$sesstat的一个子集，返回当前 session的统计项。当通过触发器审计 session资源使用，可以使用 v$mystat来捕获资源使用，这将比直接扫描 v$sesstat的列要节省资源的多。