如何查看oracle索引使用率,困惑很长时间 没用oracle索引应该删除,但怎样才能查出索引的使用情况呢? 我在一本介绍不太详细的书上看到,可以通过user_indexes 表查到,但我看了,那上没有,可能还是不会查,哪位了解,指导一下 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 :) tgm78 , 我看到你在这个论坛为别人解决了很多问题,拜托帮我查查这个问题。现在我做的项目数据库访问速度很慢(不是所有表,复杂的表索引加的不好),所以我想删除不常用的索引,这样可以节省很多反复尝试的时间。 lz坦白的说,我没有见过可以体现索引使用率的方法你的情况,我建议查找造成数据库缓慢的sql,然后再对这些sql进行优化。使得他们有好的执行计划为好。 还有一种比较常见的情况。就是sql使用了索引,但是执行计划并不好。原因在于索引为什么没有使用上。大多数情况是sql写的有问题,比较常见的是:sql语句有隐式数据类型的转换。。lz我只能提示你到这一步了。具体问题具体分析。问题也可以从多种方面进行分析,不必非要从一条路去罗马。呵呵 可我没用过 sql执行计划, 怎样用呢??? SQL> @?/rdbms/admin/utlxplan.sql;Table created.SQL> alter session set "_always_semi_join"=off;Session altered.SQL> explain plan for 2 select q.id as questionid, q.text as questiontext, q.is_deleted, q.create_date, q.created_by, q.last_update_date, q.last_updated_by, q.image_url, q.image_location, q.category_id, q.allow_html, q.site_id, qbq.question_bank_id, qbq.question_id as qstidfrom ilearn.question q,ilearn.question_bank_question qbq,ilearn.response_type rtwhere exists (select 'x' from ilearn.response_value where response_value.response_type_id = rt.id and response_value.is_deleted != 'y') and q.id = qbq.question_id and rt.question_id = q.id and rt.is_deleted != 'y' and q.is_deleted != 'y' and qbq.question_bank_id = :1 3 4 5 6 7 8 9 10 11 12 13 /Explained.SQL> @?/rdbms/admin/utlxpls.sql;PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost |--------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 6 | 810 | 142 ||* 1 | FILTER | | | | ||* 2 | HASH JOIN | | 6 | 810 | 130 || 3 | NESTED LOOPS | | 118 | 14278 | 86 ||* 4 | TABLE ACCESS FULL | QUESTION | 51988 | 5533K| 86 ||* 5 | INDEX UNIQUE SCAN | QUESTION_BANK_QUESTION_PK | 1 | 12 | ||* 6 | TABLE ACCESS FULL | RESPONSE_TYPE | 2599 | 36386 | 43 ||* 7 | TABLE ACCESS BY INDEX ROWID| RESPONSE_VALUE | 4 | 32 | 2 ||* 8 | INDEX RANGE SCAN | RESPONSE_VALUE_RTYPE_INDEX | 4 | | 1 |--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter( EXISTS (SELECT /*+ */ 0 FROM "ILEARN"."RESPONSE_VALUE" "RESPONSE_VALUE" WHERE "RESPONSE_VALUE"."RESPONSE_TYPE_ID"=:B1 AND "RESPONSE_VALUE"."IS_DELETED"<>'y')) 2 - access("SYS_ALIAS_1"."QUESTION_ID"="Q"."ID") 4 - filter("Q"."IS_DELETED"<>'y') 5 - access("QBQ"."QUESTION_BANK_ID"=TO_NUMBER(:Z) AND "Q"."ID"="QBQ"."QUESTION_ID") 6 - filter("SYS_ALIAS_1"."IS_DELETED"<>'y') 7 - filter("RESPONSE_VALUE"."IS_DELETED"<>'y') 8 - access("RESPONSE_VALUE"."RESPONSE_TYPE_ID"=:B1)Note: cpu costing is off28 rows selected.示例如上 上述不是个好的执行计划,因为一上来就是table full(全表扫描) oracle9i图形界面下创建对象后如何授权?如何建表 如何编写sql语句? 怎样使用ADO.net访问oracle数据库 跪求jdbc driver 安装Oracle 9i是开始的一个错误 如何在oracle安装 jvm? 请教下载oracle8i的网址 傻透了,一时糊涂我把oracle的用户删掉了,怎么恢复数据?,大家帮我啊!! 急救:嵌套表出错 服务器Oracle连接问题 oracle连接问题! 这个rollup要怎么写?
坦白的说,我没有见过可以体现索引使用率的方法你的情况,我建议查找造成数据库缓慢的sql,然后再对这些sql进行优化。使得他们有好的执行计划为好。
原因在于索引为什么没有使用上。
大多数情况是sql写的有问题,比较常见的是:sql语句有隐式数据类型的转换。。lz我只能提示你到这一步了。具体问题具体分析。问题也可以从多种方面进行分析,不必非要从一条路去罗马。呵呵
SQL> alter session set "_always_semi_join"=off;Session altered.SQL> explain plan for
2 select q.id as questionid, q.text as questiontext, q.is_deleted, q.create_date, q.created_by, q.last_update_date, q.last_updated_by, q.image_url, q.image_location, q.category_id, q.allow_html, q.site_id, qbq.question_bank_id, qbq.question_id as qstid
from ilearn.question q,ilearn.question_bank_question qbq,ilearn.response_type rt
where exists
(select 'x'
from ilearn.response_value
where response_value.response_type_id = rt.id and response_value.is_deleted != 'y') and
q.id = qbq.question_id and
rt.question_id = q.id and
rt.is_deleted != 'y' and
q.is_deleted != 'y' and
qbq.question_bank_id = :1 3 4 5 6 7 8 9 10 11 12
13 /Explained.SQL> @?/rdbms/admin/utlxpls.sql;PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 810 | 142 |
|* 1 | FILTER | | | | |
|* 2 | HASH JOIN | | 6 | 810 | 130 |
| 3 | NESTED LOOPS | | 118 | 14278 | 86 |
|* 4 | TABLE ACCESS FULL | QUESTION | 51988 | 5533K| 86 |
|* 5 | INDEX UNIQUE SCAN | QUESTION_BANK_QUESTION_PK | 1 | 12 | |
|* 6 | TABLE ACCESS FULL | RESPONSE_TYPE | 2599 | 36386 | 43 |
|* 7 | TABLE ACCESS BY INDEX ROWID| RESPONSE_VALUE | 4 | 32 | 2 |
|* 8 | INDEX RANGE SCAN | RESPONSE_VALUE_RTYPE_INDEX | 4 | | 1 |
--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter( EXISTS (SELECT /*+ */ 0 FROM "ILEARN"."RESPONSE_VALUE" "RESPONSE_VALUE"
WHERE "RESPONSE_VALUE"."RESPONSE_TYPE_ID"=:B1 AND "RESPONSE_VALUE"."IS_DELETED"<>'y'))
2 - access("SYS_ALIAS_1"."QUESTION_ID"="Q"."ID")
4 - filter("Q"."IS_DELETED"<>'y')
5 - access("QBQ"."QUESTION_BANK_ID"=TO_NUMBER(:Z) AND "Q"."ID"="QBQ"."QUESTION_ID")
6 - filter("SYS_ALIAS_1"."IS_DELETED"<>'y')
7 - filter("RESPONSE_VALUE"."IS_DELETED"<>'y')
8 - access("RESPONSE_VALUE"."RESPONSE_TYPE_ID"=:B1)Note: cpu costing is off28 rows selected.
示例如上