Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 一个sql加了索引依然还是走全表，怎么回事？ ...
发新帖
knowwinter

0

积分

1

好友

4

主题

[RAC性能调优] 一个sql加了索引依然还是走全表，怎么回事？

1#
发表于 2013-4-19 11:11:32 | 查看: 4701| 回复: 19
select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,'ADVANCED +PEEKED_BINDS'));
Enter value for sql_id: 5712nr1b46uuu
old   1: select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,'ADVANCED +PEEKED_BINDS'))
new   1: select plan_table_output from table (dbms_xplan.display_awr('5712nr1b46uuu',null,null,'ADVANCED +PEEKED_BINDS'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5712nr1b46uuu
--------------------
select transactio0_.ID as ID18_, transactio0_.ACTION_SEQ as ACTION2_18_,
transactio0_.CREDIT_RATIO_SEQ as CREDIT3_18_,
transactio0_.DEBIT_RATIO_SEQ as DEBIT4_18_, transactio0_.DEBIT_SEQ as
DEBIT5_18_, transactio0_.CREDIT_SEQ as CREDIT6_18_, transactio0_.ORDER_ID
as ORDER7_18_, transactio0_.SERVICE_FLAG as SERVICE8_18_,
transactio0_.SERVICE_DATE as SERVICE9_18_, transactio0_.SERVICE_PERSON as
SERVICE10_18_, transactio0_.AMOUNT as AMOUNT18_, transactio0_.CURRENCY as
CURRENCY18_, transactio0_.EXCHANGE_RATE as EXCHANGE13_18_,
transactio0_.REVERSE_FLAG as REVERSE14_18_, transactio0_.RELATING_SEQ as

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
RELATING15_18_, transactio0_.TRANS_ID as TRANS16_18_,
transactio0_.TRANS_STATUS as TRANS17_18_, transactio0_.BEGIN_DATE as
BEGIN18_18_, transactio0_.END_DATE as END19_18_, transactio0_.DEBIT_FEE
as DEBIT20_18_, transactio0_.CREDIT_FEE as CREDIT21_18_,
transactio0_.SELLER_URL as SELLER22_18_, transactio0_.OPER_SOURCE as
OPER23_18_, transactio0_.COMMODITY_INFO as COMMODITY24_18_,
transactio0_.SELLER_PARAM as SELLER25_18_, transactio0_.DES_FLAG as
DES26_18_, transactio0_.MAC as MAC18_, transactio0_.VERSION as
VERSION18_, transactio0_.CREATE_USER as CREATE29_18_,
transactio0_.CREATE_TIME as CREATE30_18_, transactio0_.UPDATE_USER as
UPDATE31_18_, transactio0_.UPDATE_USER_SYSTEM as UPDATE32_18_,

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
transactio0_.UPDATE_TIME as UPDATE33_18_, transactio0_.TRADE_TYPE as
TRADE34_18_, transactio0_.BANK_SEQ as BANK35_18_, transactio0_.CARD_NO as
CARD36_18_, transactio0_.BANK_NAME as BANK37_18_,
transactio0_.BANK_PROV_SEQ as BANK38_18_, transactio0_.BANK_CITY_SEQ as
BANK39_18_, transactio0_.PERSON_NAME as PERSON40_18_,
transactio0_.PERSON_MAIL as PERSON41_18_, transactio0_.PERSON_PHONE as
PERSON42_18_, transactio0_.PERSON_MOBILE as PERSON43_18_,
transactio0_.REMARK as REMARK18_, transactio0_.ACCOUNT_BANK_SEQ as
ACCOUNT45_18_, transactio0_.VERIFY_CODE as VERIFY46_18_,
transactio0_.BARCODE as BARCODE18_, transactio0_.AUDIT_TRANS as
AUDIT48_18_, transactio0_.encryptCode as encrypt49_18_,

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
transactio0_.PURPOSE as PURPOSE18_, transactio0_.INSIDE_STATUS as
INSIDE51_18_, transactio0_.BANK_RESPONSE as BANK52_18_,
transactio0_.DOWNLOADSTATE as DOWNLOA53_18_, transactio0_.OPERATOR_SEQ as
OPERATOR54_18_, transactio0_.BANK_INFO_SEQ as BANK55_18_,
transactio0_.VOUCHER_CODE as VOUCHER56_18_, transactio0_.VNV_ACCOUNT_ID
as VNV57_18_, transactio0_.BATCH_NO as BATCH58_18_ from TRANSACTION
transactio0_ where transactio0_.END_DATE>=:1 and
transactio0_.END_DATE<=:2 and transactio0_.ACTION_SEQ<>5

Plan hash value: 4237979237


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--

| Id  | Operationpay   | Namepay | Rows  | Bytes | Cost (%CPU)| Time
|

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

|   0 | SELECT STATEMENT   |paypay |pay |pay |  8092 (100)|
|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   1 |  FILTER pay   |paypay |pay |pay |pay      |
|

|   2 |   TABLE ACCESS FULL| TRANSACTION |   966 |   611K|  8092   (1)| 00:01:38
|

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



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / TRANSACTIO0_@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TRANSACTIO0_"@"SEL$1")
      END_OUTLINE_DATA
  */


71 rows selected.

就一个简单的单表查询，原先这查询走的是全表，我在end_date上建了索引，用explain plan看他走了索引 ，因为这个SQL都是在半夜跑的 白天在v$sqlarea里抓不到这个sql，于是我第二天再看了awr GETS依然跟原先一样高，用awrsqrpt对这个sql做了报告 看出依然走的还是全表，我用sql profile想去固定他 但是没有用，这到底怎么搞啊，白天在v$sqlarea中看不到这个sql，那是不是应该说这个sql已经被踢出共享内存了？但为什么晚上这个sql再跑的时候的sql id 和plan hash value还是一样的 那应该说明这个SQL和执行计划还在共享内存里咯？
Ask600--专注于Oracle数据库技术http://www.ask600.com
2#
发表于 2013-4-19 11:14:32

set linesize 140 pagesize 2000
跑下面的脚本


set lines 150 pages 150
col BEGIN_INTERVAL_TIME for a23
col PLAN_HASH_VALUE for 9999999999
col date_time for a30
col snap_id heading 'SnapId'
col executions_delta heading "No. of exec"
col sql_profile heading "SQL|Profile" for a7
col date_time heading 'Date time'

col avg_lio heading 'LIO/exec' for 99999999999.99
col avg_cputime heading 'CPUTIM/exec' for 9999999.99
col avg_etime heading 'ETIME/exec' for 9999999.99
col avg_pio heading 'PIO/exec' for 9999999.99
col avg_row heading 'ROWs/exec' for 9999999.99
SELECT distinct
s.snap_id ,
PLAN_HASH_VALUE,
to_char(s.BEGIN_INTERVAL_TIME,'mm/dd/yy_hh24mi')|| to_char(s.END_INTERVAL_TIME,'_hh24mi') Date_Time,
SQL.executions_delta,
SQL.buffer_gets_delta/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_lio,
--SQL.ccwait_delta,
(SQL.cpu_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_cputime ,
(SQL.elapsed_time_delta/1000000)/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_etime,
SQL.DISK_READS_DELTA/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_pio,
SQL.rows_processed_total/decode(nvl(SQL.executions_delta,0),0,1,SQL.executions_delta) avg_row
--,SQL.sql_profile
FROM
dba_hist_sqlstat SQL,
dba_hist_snapshot s
WHERE
SQL.instance_number =(select instance_number from v$instance)
and SQL.dbid =(select dbid from v$database)
and s.snap_id = SQL.snap_id
AND sql_id in
('&SQLID') order by s.snap_id
/


xp_awr.sql


select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,
'ADVANCED +PEEKED_BINDS'));











回复 只看该作者 道具 举报

3#
发表于 2013-4-19 11:23:39
跑了 结果是这个
Enter value for sqlid: 5712nr1b46uuu
old  21: ('&SQLID') order by s.snap_id
new  21: ('5712nr1b46uuu') order by s.snap_id

    SnapId PLAN_HASH_VALUE Date timepaypaypay  No. of execpay     LIO/exec CPUTIM/exec  ETIME/execpay PIO/exec   ROWs/exec
---------- --------------- ------------------------------ ----------- --------------- ----------- ----------- ----------- -----------
     20064pay4237979237 04/12/13_0100_0200paypaypay 2974pay     57252.48pay     1.07pay 1.20pay      .00      350.86
     20065pay4237979237 04/12/13_0200_0300paypaypay  626pay     57363.51pay     1.06pay 1.14pay      .00     2018.53
     20088pay4237979237 04/13/13_0100_0200paypaypay 2967pay     57255.38pay     1.03pay 1.09pay      .00      146.90
     20089pay4237979237 04/13/13_0200_0300paypaypay  633pay     57475.02pay     1.03pay 1.10pay      .00      836.02
     20112pay4237979237 04/14/13_0100_0200paypaypay 2933pay     57275.47pay      .64pay  .78pay    11.51      132.95
     20113pay4237979237 04/14/13_0200_0300paypaypay  657pay     57382.21pay      .62pay  .62pay      .00      726.74
     20136pay4237979237 04/15/13_0100_0200paypaypay 2132pay     57284.28pay      .95pay10.35pay   616.50      455.36
     20137pay4237979237 04/15/13_0200_0301paypaypay  476pay     57726.54pay      .93pay10.45pay   650.67     2498.42
     20160pay4237979237 04/16/13_0100_0200paypaypay 2933pay     57394.42pay     1.05pay 1.20pay      .99      551.81
     20161pay4237979237 04/16/13_0200_0300paypaypay  647pay     57502.74pay     1.05pay 1.16pay      .00     3054.34
     20184pay4237979237 04/17/13_0100_0200paypaypay 2859pay     57456.96pay      .71pay 1.00pay    19.21      608.79
     20185pay4237979237 04/17/13_0200_0300paypaypay  652pay     57565.15pay      .70pay  .70pay      .32     3279.45
     20208pay4237979237 04/18/13_0100_0200paypaypay 2893pay     57488.15pay      .68pay  .78pay     6.92      434.85
     20209pay4237979237 04/18/13_0200_0300paypaypay  678pay     57592.82pay      .67pay  .67pay      .00     2291.13
     20232pay4237979237 04/19/13_0101_0200paypaypay 2881pay     57531.07pay     1.03pay 1.13pay      .53      177.94
     20233pay4237979237 04/19/13_0200_0300paypaypay  714pay     57631.60pay     1.03pay 1.09pay      .00      896.23

16 rows selected.

SQL> select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,
  2  'ADVANCED +PEEKED_BINDS'));
Enter value for sql_id: 5712nr1b46uuu
old   1: select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,
new   1: select plan_table_output from table (dbms_xplan.display_awr('5712nr1b46uuu',null,null,

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5712nr1b46uuu
--------------------
select transactio0_.ID as ID18_, transactio0_.ACTION_SEQ as ACTION2_18_,
transactio0_.CREDIT_RATIO_SEQ as CREDIT3_18_,
transactio0_.DEBIT_RATIO_SEQ as DEBIT4_18_, transactio0_.DEBIT_SEQ as
DEBIT5_18_, transactio0_.CREDIT_SEQ as CREDIT6_18_, transactio0_.ORDER_ID
as ORDER7_18_, transactio0_.SERVICE_FLAG as SERVICE8_18_,
transactio0_.SERVICE_DATE as SERVICE9_18_, transactio0_.SERVICE_PERSON as
SERVICE10_18_, transactio0_.AMOUNT as AMOUNT18_, transactio0_.CURRENCY as
CURRENCY18_, transactio0_.EXCHANGE_RATE as EXCHANGE13_18_,
transactio0_.REVERSE_FLAG as REVERSE14_18_, transactio0_.RELATING_SEQ as
RELATING15_18_, transactio0_.TRANS_ID as TRANS16_18_,
transactio0_.TRANS_STATUS as TRANS17_18_, transactio0_.BEGIN_DATE as
BEGIN18_18_, transactio0_.END_DATE as END19_18_, transactio0_.DEBIT_FEE
as DEBIT20_18_, transactio0_.CREDIT_FEE as CREDIT21_18_,
transactio0_.SELLER_URL as SELLER22_18_, transactio0_.OPER_SOURCE as
OPER23_18_, transactio0_.COMMODITY_INFO as COMMODITY24_18_,
transactio0_.SELLER_PARAM as SELLER25_18_, transactio0_.DES_FLAG as
DES26_18_, transactio0_.MAC as MAC18_, transactio0_.VERSION as
VERSION18_, transactio0_.CREATE_USER as CREATE29_18_,
transactio0_.CREATE_TIME as CREATE30_18_, transactio0_.UPDATE_USER as
UPDATE31_18_, transactio0_.UPDATE_USER_SYSTEM as UPDATE32_18_,
transactio0_.UPDATE_TIME as UPDATE33_18_, transactio0_.TRADE_TYPE as
TRADE34_18_, transactio0_.BANK_SEQ as BANK35_18_, transactio0_.CARD_NO as
CARD36_18_, transactio0_.BANK_NAME as BANK37_18_,
transactio0_.BANK_PROV_SEQ as BANK38_18_, transactio0_.BANK_CITY_SEQ as
BANK39_18_, transactio0_.PERSON_NAME as PERSON40_18_,
transactio0_.PERSON_MAIL as PERSON41_18_, transactio0_.PERSON_PHONE as
PERSON42_18_, transactio0_.PERSON_MOBILE as PERSON43_18_,
transactio0_.REMARK as REMARK18_, transactio0_.ACCOUNT_BANK_SEQ as
ACCOUNT45_18_, transactio0_.VERIFY_CODE as VERIFY46_18_,
transactio0_.BARCODE as BARCODE18_, transactio0_.AUDIT_TRANS as
AUDIT48_18_, transactio0_.encryptCode as encrypt49_18_,
transactio0_.PURPOSE as PURPOSE18_, transactio0_.INSIDE_STATUS as
INSIDE51_18_, transactio0_.BANK_RESPONSE as BANK52_18_,
transactio0_.DOWNLOADSTATE as DOWNLOA53_18_, transactio0_.OPERATOR_SEQ as
OPERATOR54_18_, transactio0_.BANK_INFO_SEQ as BANK55_18_,
transactio0_.VOUCHER_CODE as VOUCHER56_18_, transactio0_.VNV_ACCOUNT_ID
as VNV57_18_, transactio0_.BATCH_NO as BATCH58_18_ from TRANSACTION
transactio0_ where transactio0_.END_DATE>=:1 and
transactio0_.END_DATE<=:2 and transactio0_.ACTION_SEQ<>5

Plan hash value: 4237979237

----------------------------------------------------------------------------------
| Id  | Operationpay   | Namepay | Rows  | Bytes | Cost (%CPU)| Timepay |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |paypay |pay |pay |  8092 (100)| pay |
|   1 |  FILTER pay   |paypay |pay |pay |pay      | pay |
|   2 |   TABLE ACCESS FULL| TRANSACTION |   966 |   611K|  8092   (1)| 00:01:38 |
----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / TRANSACTIO0_@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TRANSACTIO0_"@"SEL$1")
      END_OUTLINE_DATA
  */


71 rows selected.

回复 只看该作者 道具 举报

4#
发表于 2013-4-19 11:24:55
Enter value for sqlid: 5712nr1b46uuu
old  21: ('&SQLID') order by s.snap_id
new  21: ('5712nr1b46uuu') order by s.snap_id

    SnapId PLAN_HASH_VALUE Date time  No. of exec     LIO/exec CPUTIM/exec  ETIME/exec PIO/exec   ROWs/exec
---------- --------------- ------------------------------ ----------- --------------- ----------- ----------- ----------- -----------
     200644237979237 04/12/13_0100_0200 2974     57252.48     1.07 1.20      .00      350.86
     200654237979237 04/12/13_0200_0300  626     57363.51     1.06 1.14      .00     2018.53
     200884237979237 04/13/13_0100_0200 2967     57255.38     1.03 1.09      .00      146.90
     200894237979237 04/13/13_0200_0300  633     57475.02     1.03 1.10      .00      836.02
     201124237979237 04/14/13_0100_0200 2933     57275.47      .64  .78    11.51      132.95
     201134237979237 04/14/13_0200_0300  657     57382.21      .62  .62      .00      726.74
     201364237979237 04/15/13_0100_0200 2132     57284.28      .9510.35   616.50      455.36
     201374237979237 04/15/13_0200_0301  476     57726.54      .9310.45   650.67     2498.42
     201604237979237 04/16/13_0100_0200 2933     57394.42     1.05 1.20      .99      551.81
     201614237979237 04/16/13_0200_0300  647     57502.74     1.05 1.16      .00     3054.34
     201844237979237 04/17/13_0100_0200 2859     57456.96      .71 1.00    19.21      608.79
     201854237979237 04/17/13_0200_0300  652     57565.15      .70  .70      .32     3279.45
     202084237979237 04/18/13_0100_0200 2893     57488.15      .68  .78     6.92      434.85
     202094237979237 04/18/13_0200_0300  678     57592.82      .67  .67      .00     2291.13
     202324237979237 04/19/13_0101_0200 2881     57531.07     1.03 1.13      .53      177.94
     202334237979237 04/19/13_0200_0300  714     57631.60     1.03 1.09      .00      896.23

16 rows selected.

SQL> select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,
  2  'ADVANCED +PEEKED_BINDS'));
Enter value for sql_id: 5712nr1b46uuu
old   1: select plan_table_output from table (dbms_xplan.display_awr('&sql_id',null,null,
new   1: select plan_table_output from table (dbms_xplan.display_awr('5712nr1b46uuu',null,null,

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5712nr1b46uuu
--------------------
select transactio0_.ID as ID18_, transactio0_.ACTION_SEQ as ACTION2_18_,
transactio0_.CREDIT_RATIO_SEQ as CREDIT3_18_,
transactio0_.DEBIT_RATIO_SEQ as DEBIT4_18_, transactio0_.DEBIT_SEQ as
DEBIT5_18_, transactio0_.CREDIT_SEQ as CREDIT6_18_, transactio0_.ORDER_ID
as ORDER7_18_, transactio0_.SERVICE_FLAG as SERVICE8_18_,
transactio0_.SERVICE_DATE as SERVICE9_18_, transactio0_.SERVICE_PERSON as
SERVICE10_18_, transactio0_.AMOUNT as AMOUNT18_, transactio0_.CURRENCY as
CURRENCY18_, transactio0_.EXCHANGE_RATE as EXCHANGE13_18_,
transactio0_.REVERSE_FLAG as REVERSE14_18_, transactio0_.RELATING_SEQ as
RELATING15_18_, transactio0_.TRANS_ID as TRANS16_18_,
transactio0_.TRANS_STATUS as TRANS17_18_, transactio0_.BEGIN_DATE as
BEGIN18_18_, transactio0_.END_DATE as END19_18_, transactio0_.DEBIT_FEE
as DEBIT20_18_, transactio0_.CREDIT_FEE as CREDIT21_18_,
transactio0_.SELLER_URL as SELLER22_18_, transactio0_.OPER_SOURCE as
OPER23_18_, transactio0_.COMMODITY_INFO as COMMODITY24_18_,
transactio0_.SELLER_PARAM as SELLER25_18_, transactio0_.DES_FLAG as
DES26_18_, transactio0_.MAC as MAC18_, transactio0_.VERSION as
VERSION18_, transactio0_.CREATE_USER as CREATE29_18_,
transactio0_.CREATE_TIME as CREATE30_18_, transactio0_.UPDATE_USER as
UPDATE31_18_, transactio0_.UPDATE_USER_SYSTEM as UPDATE32_18_,
transactio0_.UPDATE_TIME as UPDATE33_18_, transactio0_.TRADE_TYPE as
TRADE34_18_, transactio0_.BANK_SEQ as BANK35_18_, transactio0_.CARD_NO as
CARD36_18_, transactio0_.BANK_NAME as BANK37_18_,
transactio0_.BANK_PROV_SEQ as BANK38_18_, transactio0_.BANK_CITY_SEQ as
BANK39_18_, transactio0_.PERSON_NAME as PERSON40_18_,
transactio0_.PERSON_MAIL as PERSON41_18_, transactio0_.PERSON_PHONE as
PERSON42_18_, transactio0_.PERSON_MOBILE as PERSON43_18_,
transactio0_.REMARK as REMARK18_, transactio0_.ACCOUNT_BANK_SEQ as
ACCOUNT45_18_, transactio0_.VERIFY_CODE as VERIFY46_18_,
transactio0_.BARCODE as BARCODE18_, transactio0_.AUDIT_TRANS as
AUDIT48_18_, transactio0_.encryptCode as encrypt49_18_,
transactio0_.PURPOSE as PURPOSE18_, transactio0_.INSIDE_STATUS as
INSIDE51_18_, transactio0_.BANK_RESPONSE as BANK52_18_,
transactio0_.DOWNLOADSTATE as DOWNLOA53_18_, transactio0_.OPERATOR_SEQ as
OPERATOR54_18_, transactio0_.BANK_INFO_SEQ as BANK55_18_,
transactio0_.VOUCHER_CODE as VOUCHER56_18_, transactio0_.VNV_ACCOUNT_ID
as VNV57_18_, transactio0_.BATCH_NO as BATCH58_18_ from TRANSACTION
transactio0_ where transactio0_.END_DATE>=:1 and
transactio0_.END_DATE<=:2 and transactio0_.ACTION_SEQ<>5

Plan hash value: 4237979237

----------------------------------------------------------------------------------
| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   | | | |  8092 (100)|  |
|   1 |  FILTER    | | | |      |  |
|   2 |   TABLE ACCESS FULL| TRANSACTION |   966 |   611K|  8092   (1)| 00:01:38 |
----------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / TRANSACTIO0_@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "TRANSACTIO0_"@"SEL$1")
      END_OUTLINE_DATA
  */


71 rows selected.

回复 只看该作者 道具 举报

5#
发表于 2013-4-19 11:34:44
全表扫描也只有  57252.48  多的逻辑读 这说明 该表并不大，  全表扫描的成本也仅为 8092
单次执行大约耗时 1秒

给出你 加的索引的ddl 以及 使用索引时的 执行计划

回复 只看该作者 道具 举报

6#
发表于 2013-4-19 11:52:57
select dbms_metadata.get_ddl('INDEX','IDX_TRANSACTION__END_DATE','VNV') from dual;

DBMS_METADATA.GET_DDL('INDEX','IDX_TRANSACTION__END_DATE','VNV')
--------------------------------------------------------------------------------

  CREATE INDEX "VNV"."IDX_TRANSACTION__END_DATE" ON "VNV"."TRANSACTION" ("END_DATE")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "VNVBANKDATA"


SELECT STATEMENT, GOAL = HINT: ALL_ROWS                        1880        1536        986112        17684982        1877        0                HINT: ALL_ROWS        322                23        2013/4/19 12:03:36       
FILTER                                                                1        TO_DATE(:1)<=TO_DATE(:2)                322                        2013/4/19 12:03:36       
  TABLE ACCESS BY INDEX ROWID        VNV        TRANSACTION        1880        1536        986112        17684982        1877        2        "TRANSACTIO0_"."ACTION_SEQ"<>5        ANALYZED        322                23        2013/4/19 12:03:36       
   INDEX RANGE SCAN        VNV        IDX_TRANSACTION__END_DATE        10        2767                624814        10        3                ANALYZED        322                1        2013/4/19 12:03:36        1

回复 只看该作者 道具 举报

7#
发表于 2013-4-19 11:55:42
下面的 sql统计信息 没法看。。。


很简单的事情


set autotrace traceonly

SQL里加HINT ，直接贴出来 这有多难？

回复 只看该作者 道具 举报

8#
发表于 2013-4-19 11:56:59
这个执行计划是我用pl/sql里的explain plan 出来的  这个确实会走索引 但是实际运行中他走的还是全表 这就让我很郁闷了 由于这个SQL带了绑定变量所以不能在sqlplus 里用autotrace 所以他其实并没有执行  但我用字面量带进去在sqlplus里执行 他也是用的索引  可就是等晚上程序跑的时候他还是用的全表  搞不懂为什么

回复 只看该作者 道具 举报

9#
发表于 2013-4-19 11:59:27
直接在sqlplus里跑这个会包SP2-0552: Bind variable "2" not declared.  

回复 只看该作者 道具 举报

10#
发表于 2013-4-19 12:05:33
我用pl/sql里的explain plan 出来的  这个确实会走索引 ==>这没有意义

用explain plan for 语句;

@?/rdbms/admin/utlxplp

回复 只看该作者 道具 举报

11#
发表于 2013-4-19 12:07:05
select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3144977033

----------------------------------------------------------------------------------------------------------
| Id  | Operation     | Name | Rows  | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     | |  1536 |   963K|  1880   (1)| 00:00:23 |
|*  1 |  FILTER      | | | |      |  |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TRANSACTION |  1536 |   963K|  1880   (1)| 00:00:23 |
|*  3 |    INDEX RANGE SCAN     | IDX_TRANSACTION__END_DATE |  2767 | |    10   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(:1)<=TO_DATE(:2))
   2 - filter("TRANSACTIO0_"."ACTION_SEQ"<>5)
   3 - access("TRANSACTIO0_"."END_DATE">=:1 AND "TRANSACTIO0_"."END_DATE"<=:2)

Note
-----
   - SQL profile "coe_5712nr1b46uuu_4237979237" used for this statement

21 rows selected.

回复 只看该作者 道具 举报

12#
发表于 2013-4-19 12:07:40
set autotrace traceonly 要带值去执行。你的统计信息准不。

回复 只看该作者 道具 举报

13#
发表于 2013-4-19 12:09:33
我就不明白为什么实际的执行结果跟explain出来的不一样

回复 只看该作者 道具 举报

14#
发表于 2013-4-19 12:10:25
用索引的成本更低一些，  你可以尝试 flush shared_pool 让它重新硬解析 再看看

回复 只看该作者 道具 举报

15#
发表于 2013-4-19 12:38:12
flush shared_pool 不是风险很大吗？他会把所有的都清出去，有没有其他办法

回复 只看该作者 道具 举报

16#
发表于 2013-4-19 12:43:43
http://www.askmaclean.com/archiv ... 96%B9%E6%B3%95.html

回复 只看该作者 道具 举报

17#
发表于 2013-4-19 12:55:55
kevinlin.ora 发表于 2013-4-19 12:43
http://www.askmaclean.com/archives/oracle%E4%B8%AD%E6%B8%85%E9%99%A4%E6%B8%B8%E6%A0%87%E7%BC%93%E5%A ...

我10.2.0.1的没purge，grant 那个表我也试过 但结果还是一样  表的统计信息我之前用dbms_stats包收集的，不知道有没有区别，我就是很奇怪，那个SQL只在半夜出现，白天的时候我在v$sqlarea中找不到这个sql，在shared_pool中的sql是不是都应该在v$sqlarea中可以找到？不在v$sqlarea中的sql是不是就已经被T出内存了？再执行是不是就应该重新解析生成执行计划？

回复 只看该作者 道具 举报

18#
发表于 2013-4-19 13:08:30
END_DATE的值分布均匀吗？绑定变量会有value窥视，重新分析一下表，收集END_DATE柱状图试试。

回复 只看该作者 道具 举报

19#
发表于 2013-4-19 13:13:30
猜测这个SQL每天夜间是多次运行，而且白天是被flush掉的，那么查到的夜间的sql plan是由第一次硬解析产生，而第一次硬解析产生的执行计划与第一次的绑定变量窥视有关。人为设置或将查得的绑定变量值作为调试该SQL的plan，可能产生区别，根据时间范围的不同，执行计划出现改变是很有可能的。

回复 只看该作者 道具 举报

20#
发表于 2013-4-19 13:35:24
被FLUSH掉的SQL 再重新执行时sql id和plan hash value还会和之前的一模一样的吗？重新解析后sql id还会是一样的？那个SQL的sql id 和plan hash value 每次都是一样的

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-16 11:39 , Processed in 0.059305 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569