select count(*) from F_EVT_SETR_IBPS_RCVSND XX where "XX"."SYS_DATE">=TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "XX"."SYS_DATE"<=TO_DATE('
2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
---------117369
select count(*) from F_EVT_SETR_IBPS_RCVSND XX
---------1823862
select owner,blocks from dba_tables where owner='DWF' and table_name='F_EVT_SETR_IBPS_RCVSND';
--180322
select count(distinct dbms_rowid.rowid_block_number(rowid)) from F_EVT_SETR_IBPS_RCVSND XX
where "XX"."SYS_DATE">=TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "XX"."SYS_DATE"<=TO_DATE('
2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
--19257
数据比例:6.43519082035812
访问块比例:10.6792293785561
-----查看索引是否有效:
SQL> select index_name,status from dba_indexes
where table_name='F_EVT_SETR_IBPS_RCVSND'; 2
INDEX_NAME STATUS
------------------------------ --------
SYS_C0036386 VALID
F_EVT_SETR_IBPS_RCVSND_IDX1 VALID
------查看选择性:
SQL> select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'DWF'
and a.table_name = 'F_EVT_SETR_IBPS_RCVSND'
and a.column_name='SYS_DATE' 2 3 4 5 6 7 8 9 10 11 12
13 ;
COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
SYS_DATE 1823862 1216 .07 HEIGHT BALANCED 254
----------查看统计信息是否过期
SQL> select owner, table_name name, object_type, stale_stats, last_analyzed
from dba_tab_statistics
where table_name in ('F_EVT_SETR_IBPS_RCVSND')
and owner = 'DWF'
and (stale_stats = 'YES' or last_analyzed is null); 2 3 4 5
no rows selected
---查看采样率
SQL> set linesize 200
SQL> SELECT owner,
table_name,
num_rows,
sample_size,
trunc(sample_size / num_rows * 100) ectimate_percent
FROM DBA_TAB_STATISTICS
where
owner = 'DWF'
and table_name='F_EVT_SETR_IBPS_RCVSND'; 2 3 4 5 6 7 8 9
OWNER TABLE_NAME NUM_ROWS SAMPLE_SIZE ECTIMATE_PERCENT
------------------------------ ------------------------------ ---------- ----------- ----------------
DWF F_EVT_SETR_IBPS_RCVSND 1823862 1823862 100
---对比执行计划
SQL> explain plan for select * from F_EVT_SETR_IBPS_RCVSND XX where "XX"."SYS_DATE">=TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "XX"."SYS_DATE"<=TO_DATE('
2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
2 3 4 ;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1166731381
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122K| 74M| 48895 (1)| 00:09:47 |
|* 1 | TABLE ACCESS FULL| F_EVT_SETR_IBPS_RCVSND | 122K| 74M| 48895 (1)| 00:09:47 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("XX"."SYS_DATE">=TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "XX"."SYS_DATE"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
15 rows selected.
SQL> explain plan for select /*+ index (XX F_EVT_SETR_IBPS_RCVSND_IDX1)*/ * from F_EVT_SETR_IBPS_RCVSND XX where "XX"."SYS_DATE">=TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "XX"."SYS_DATE"<=TO_DATE('
2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 2 3 ;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 577873514
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 122K| 74M| 58832 (1)| 00:11:46 |
| 1 | TABLE ACCESS BY INDEX ROWID| F_EVT_SETR_IBPS_RCVSND | 122K| 74M| 58832 (1)| 00:11:46 |
|* 2 | INDEX RANGE SCAN | F_EVT_SETR_IBPS_RCVSND_IDX1 | 122K| | 327 (1)| 00:00:04 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("XX"."SYS_DATE">=TO_DATE(' 2014-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"XX"."SYS_DATE"<=TO_DATE(' 2014-03-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
15 rows selected.