新闻 - 军事 - 社会 - 军坛 - 体育 - 游戏 - 汽车 - 图库 - 房产 - 旅游 - 博客 - 论坛 - 有料 - 娱乐 - 教育 - 出国 - 科技 - 邮箱 - 搜索
登陆 | 注册

我要注册 新闻中心 网校排名 课程中心 资料中心 出国留学 企业培训

分站导航: 深圳 | 广州 | 东莞 | 佛山 | 珠海 | 上海 | 南京 | 北京 | 更多>>天津杭州苏州
教育联展网新浪微博教育联展网腾讯微博
课程中心深圳教育业培训行业年会教育、考试百科 环球雅思培训保分通过雅思网络课程顶尖名师全国公共英语考试培训托福培训班网络课程听英语四级网络课程试听英语六级网络课程辅导职称英语网上培训包过
新概念英语网络课堂听商务英语在线学习名师成人英语网上培训保过华图网校试听英语口语网上培训试听日语网络课程快乐学习韩语网上培训学习通关

oracle认证

网站首页 > oracle认证考试资料

Oracle索引问题诊断与优化(1)

中华网考试责任编辑:阿原更新时间:2012年1月12日  [点评]

  一、实验

  create table s1 as select * from SH.SALES;

  create table s2 as select * from SH.SALES;

  s1表没有建立索引

  s2表有建立索引

  set timing on;

  select * from s1 where prod_id=1;

  2.45s

  select * from s2 where prod_id=1;

  0.59s

  可见索引对于表查询速度的重要性。

  二、索引性能测试与诊断

  1、查看数据库Index信息:

  SELECT  A.OWNER, A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME, A.INDEX_TYPE,

  B.COLUMN_POSITION, B.COLUMN_NAME, C.TABLESPACE_NAME,

  A.TABLESPACE_NAME, A.UNIQUENESS

  FROM DBA_INDEXES A, DBA_IND_COLUMNS B, DBA_TABLES C

  WHERE A.OWNER = UPPER ('hr')

  AND A.OWNER = B.INDEX_OWNER

  AND A.OWNER = C.OWNER

  AND A.TABLE_NAME LIKE UPPER ('DEPARTMENTS')

  AND A.TABLE_NAME = B.TABLE_NAME

  AND A.TABLE_NAME = C.TABLE_NAME

  AND A.INDEX_NAME = B.INDEX_NAME

  ORDER BY A.OWNER, A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME, B.COLUMN_POSITION

  2、查出没有建立index的表:

  SELECT OWNER, TABLE_NAME

  FROM ALL_TABLES

  WHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP') AND OWNER = UPPER ('scott')

  MINUS

  SELECT OWNER, TABLE_NAME

  FROM ALL_INDEXES

  WHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')

  3、查出建立了过量index的表:

  SELECT   OWNER, TABLE_NAME, COUNT (*) "count"

  FROM ALL_INDEXES

  WHERE OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP') AND OWNER = UPPER ('hr')

  GROUP BY OWNER, TABLE_NAME

  HAVING COUNT (*) > ('4')

  一个表可以有几百个索引,但是对于频繁插入和更新表,索引越多系统CPU,I/O负担就越重;建议每张表不超过5个索引。

  实验:

  create table table1 as select * from SH.SALES;

  create table table2 as select * from SH.SALES;

  table1只在prod_id列建索引

  table2在所有列建索引

  SELECT count(*) FROM table1 where prod_id=30;

  29282

  set timing on;

  update table1 set cust_id=1 where prod_id=30;

  10.56s

  update table2 set cust_id=1 where prod_id=30;

  11.35s

  4、找出全表扫描(Full Scan)的Sid和SQL

  A full table scan occurs when every block is read from a table. Full table scans are often a preferred performance option in batch-style applications, such as decision support. We have seen some excellent run-time improvements in decision support systems that use the parallel query option, which relies on full table scans to operate. However, full table scans at an OLTP site during prime online usage times can create havoc with response times. Full table scans, even on small tables, can degrade response times particularly when the small table drives the query, and this table is not always the most efficient access path.

  The following query reports how many full table scans are taking place:

  SELECT name, value

  FROM v$sysstat

  WHERE name LIKE '%table %'

  ORDER BY name;

  The values relating to the full table scans are:

  table scans (long tables) - a scan of a table that has more than five database blocks

  table scans (short tables) - a count of full table scans with five or fewer blocks

  If the number of long table scans is significant, there is a strong possibility that SQL statements in your application need tuning or indexes need to be added.

  To get an appreciation of how many rows and blocks are being accessed on average for the long full table scans, use this calculation (the sample data comes from an OLTP application):

  Average Long Table Scan Blocks

  = (table scan blocks gotten - (short table scans * 5))

  / long table scans

  = (3,540,450 - (160,618 * 5)) / 661

  = (3,540,450 - (803,090)) / 661

  = 4,141 blocks read per full table scan

  In our example, 4141 average disk reads performed on an OLTP application 661 times in the space of a few short hours is not a healthy situation.

  If you can identify the users who are experiencing the full table scans, you can find out what they were running to cause these scans. Below is a script that allows you to do this:

  REM FILE NAME: fullscan.sql

  REM LOCATION: Database Tuning\File I/O Reports

  REM FUNCTION: Identifies users of full table scans

  REM TESTED ON: 7.3.3.5, 8.0.4.1, 8.1.5, 8.1.7, 9.0.1, 9.2.0.2

  REM PLATFORM: non-specific

  REM REQUIRES: v$session, v$sesstat, v$statname

  REM This view is used by the fscanavg.sql script

  REM

  REM This is a part of the Knowledge Xpert for Oracle Administration REM library.

  REM Copyright (C) 2001 Quest Software

  REM All rights reserved.

  REM

  REM************ Knowledge Xpert for Oracle Administration *************

  DROP VIEW full_table_scans;

  CREATE VIEW full_table_scans

  AS

  SELECT      ss.username

  || '('

  || se.sid

  || ') ' "User Process",

  SUM (DECODE (NAME, 'table scans (short tables)', VALUE)) "Short Scans",

  SUM (DECODE (NAME, 'table scans (long tables)', VALUE)) "Long Scans",

  SUM (DECODE (NAME, 'table scan rows gotten', VALUE)) "Rows Retrieved"

  FROM v$session ss, v$sesstat se, v$statname sn

  WHERE se.statistic# = sn.statistic#

  AND ( NAME LIKE '%table scans (short tables)%'

  OR NAME LIKE '%table scans (long tables)%'

  OR NAME LIKE '%table scan rows gotten%'

  )

  AND se.sid = ss.sid

  AND ss.username IS NOT NULL

  GROUP BY ss.username

  || '('

  || se.sid

  || ') ';

小编推荐


oracle认证考试网络课堂

更多>>

我来说两句

点击查看
 新用户注册

 [Ctrl + Enter]

各地课程推荐

更多>>
  • 深圳
  • 广州
  • 东莞
  • 珠海
  • 佛山
  • 上海
  • 北京
  • 南京
  • 杭州
  • 苏州
  • 天津
  • 石家庄

网友关注

更多>>