- 最后登录
- 2023-8-16
- 在线时间
- 1686 小时
- 威望
- 2135
- 金钱
- 50532
- 注册时间
- 2011-10-12
- 阅读权限
- 200
- 帖子
- 5207
- 精华
- 39
- 积分
- 2135
- UID
- 2
|
4#
发表于 2013-7-25 22:41:36
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 2657 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: -1 blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: CPS_EFFECT_TEMP_LOG Alias: C (Using composite stats)
#Rows: 14300372 #Blks: 761619 AvgRowLen: 360.00
Index Stats::
Index: IDX_CPS_ETL_SD_DESC Col#: 68
LVLS: 3 #LB: 104916 #DK: 3603712 LB/K: 1.00 DB/K: 2.00 CLUF: 8927884.00
Index: IDX_CPS_ETL_WID_ON_PN_CT Col#: 17 15 9 18 20
USING COMPOSITE STATS
LVLS: 2 #LB: 136367 #DK: 14554833 LB/K: 1.00 DB/K: 1.00 CLUF: 7977146.00
Index: IND_CETL_CID_ED Col#: 9 69
USING COMPOSITE STATS
LVLS: 3 #LB: 128228 #DK: 2370283 LB/K: 1.00 DB/K: 1.00 CLUF: 2017374.00
Index: IND_CPS_ETL_WID_SD Col#: 15 28
USING COMPOSITE STATS
PARTITION [0] LVLS: 2 #LB: 19441 #DK: 1158406 LB/K: 1.00 DB/K: 1.00 CLUF: 1104556.00
PARTITION [1] LVLS: 2 #LB: 12400 #DK: 594897 LB/K: 1.00 DB/K: 1.00 CLUF: 645889.00
PARTITION [2] LVLS: 2 #LB: 12238 #DK: 716163 LB/K: 1.00 DB/K: 1.00 CLUF: 829822.00
PARTITION [3] LVLS: 2 #LB: 14494 #DK: 780384 LB/K: 1.00 DB/K: 1.00 CLUF: 927927.00
PARTITION [4] LVLS: 2 #LB: 14421 #DK: 876665 LB/K: 1.00 DB/K: 1.00 CLUF: 935147.00
PARTITION [5] LVLS: 2 #LB: 28617 #DK: 1444643 LB/K: 1.00 DB/K: 1.00 CLUF: 1538202.00
PARTITION [6] LVLS: 2 #LB: 29002 #DK: 1382720 LB/K: 1.00 DB/K: 1.00 CLUF: 1537306.00
PARTITION [7] LVLS: 2 #LB: 16095 #DK: 905870 LB/K: 1.00 DB/K: 1.00 CLUF: 934340.00
PARTITION [8] LVLS: 2 #LB: 7706 #DK: 437691 LB/K: 1.00 DB/K: 1.00 CLUF: 511919.00
PARTITION [9] LVLS: 2 #LB: 7594 #DK: 417511 LB/K: 1.00 DB/K: 1.00 CLUF: 487428.00
(NOT ANALYZED)
LVLS: 2 #LB: 169658 #DK: 9269238 LB/K: 1.00 DB/K: 1.00 CLUF: 10255135.00
Index: PK_CPS_EFFECT_TEMP_LOG Col#: 1
LVLS: 2 #LB: 73569 #DK: 13901786 LB/K: 1.00 DB/K: 1.00 CLUF: 3271299.00
Access path analysis for CPS_EFFECT_TEMP_LOG
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for CPS_EFFECT_TEMP_LOG[C]
Table: CPS_EFFECT_TEMP_LOG Alias: C
Card: Original: 14300372.000000 Rounded: 8 Computed: 7.75 Non Adjusted: 7.75
Access Path: TableScan
Cost: 206784.98 Resp: 206784.98 Degree: 0
Cost_io: 206274.00 Cost_cpu: 16292112239
Resp_io: 206274.00 Resp_cpu: 16292112239
kkofmx: index filter:SYS_OP_UNDESCEND(SYS_OP_DESCEND("C"."END_DATE"))>='2013-07-24 10:52:05'
kkofmx: index filter:SYS_OP_UNDESCEND(SYS_OP_DESCEND("C"."END_DATE"))<='2013-07-24 10:55:05'
Using prorated density: 0.000000 of col #69 as selectvity of out-of-range/non-existent value pred
Access Path: index (FullScan)
Index: IND_CETL_CID_ED
resc_io: 128232.00 resc_cpu: 3887031044
ix_sel: 1.000000 ix_sel_with_filters: 0.000000
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 50.00
***** End Logdef Adjustment ******
***** Logdef predicate Adjustment ******
Final IO cst 0.00 , CPU cst 100.00
***** End Logdef Adjustment ******
Cost: 128400.55 Resp: 128400.55 Degree: 1
Best:: AccessPath: TableScan
Cost: 206784.98 Degree: 1 Resp: 206784.98 Card: 7.75 Bytes: 0
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: CPS_EFFECT_TEMP_LOG[C]#0
***********************
Best so far: Table#: 0 cost: 206784.9785 card: 7.7531 bytes: 704
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
Trying or-Expansion on query block SEL$335DD26A (#0)
Transfer Optimizer annotations for query block SEL$335DD26A (#0)
id=0 fptrnum predicate=ROWNUM<=3000
id=0 frofand predicate="C"."END_DATE"<='2013-07-24 10:55:05' AND "C"."END_DATE">='2013-07-24 10:52:05'
Final cost for query block SEL$335DD26A (#0) - All Rows Plan:
Best join order: 1
Cost: 206784.9785 Degree: 1 Card: 8.0000 Bytes: 704
Resc: 206784.9785 Resc_io: 206274.0000 Resc_cpu: 16292112239
Resp: 206784.9785 Resp_io: 206274.0000 Resc_cpu: 16292112239
kkoqbc-subheap (delete addr=0x2b6d4408d978, in-use=16832, alloc=21632)
kkoqbc-end:
:
call(in-use=82784, alloc=114552), compile(in-use=115976, alloc=119424), execution(in-use=5584, alloc=8088)
kkoqbc: finish optimizing query block SEL$335DD26A (#0)
kkoqbc: optimizing query block SEL$1 (#0)
:
call(in-use=82784, alloc=114552), compile(in-use=115976, alloc=119424), execution(in-use=5584, alloc=8088)
kkoqbc-subheap (create addr=0x2b6d4408d978)
****************
QUERY BLOCK TEXT
****************
select *
from (select A.*, ROWNUM RN
from (select c.advertiser_id,
c.campaign_id,
c.order_no,
c.product_no,
c.product_name,
c.act
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=1 objn=0 hint_alias="from$_subquery$_001"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEEDNW: 2657 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: -1 blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: from$_subquery$_001 Alias: from$_subquery$_001 (NOT ANALYZED)
#Rows: 0 #Blks: 0 AvgRowLen: 0.00
Access path analysis for from$_subquery$_001
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: from$_subquery$_001[from$_subquery$_001]#0
***********************
Best so far: Table#: 0 cost: 206784.9785 card: 8.0000 bytes: 5472
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofand predicate="from$_subquery$_001"."RN">=1
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1
Cost: 206784.9785 Degree: 1 Card: 8.0000 Bytes: 5472
Resc: 206784.9785 Resc_io: 206274.0000 Resc_cpu: 16292112239
Resp: 206784.9785 Resp_io: 206274.0000 Resc_cpu: 16292112239
kkoqbc-subheap (delete addr=0x2b6d4408d978, in-use=13288, alloc=21264)
kkoqbc-end:
:
call(in-use=89360, alloc=114552), compile(in-use=116304, alloc=119424), execution(in-use=5584, alloc=8088)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
:
call(in-use=89360, alloc=114552), compile(in-use=119640, alloc=123568), execution(in-use=5584, alloc=8088)
Starting SQL statement dump
user_id=97 user_name=YIQIFA_BS_PRO module=SQL*Plus action=
sql_id=b3b0uhr6j4vn6 plan_hash_value=662673090 problem_type=3
----- Current SQL Statement for this session (sql_id=b3b0uhr6j4vn6) -----
select *
from (select A.*, ROWNUM RN
from (select c.advertiser_id,
c.campaign_id,
c.order_no,
c.product_no,
c.product_name,
c.action_price,
c.total_amount,
c.product_type,
c.commision_type,
c.data_type,
c.website_id
from cps_effect_temp_log c
where 1 = 1
AND c.end_Date >= '2013-07-24 10:52:05'
AND c.end_Date <= '2013-07-24 10:55:05') A
where ROWNUM <= 3000)
where RN >= 1
sql_text_length=699
sql=select *
from (select A.*, ROWNUM RN
from (select c.advertiser_id,
c.campaign_id,
c.order_no,
c.product_no,
c.product_name,
c.act
sql=ion_price,
c.total_amount,
c.product_type,
c.commision_type,
c.data_type,
c.website_id
from cps_effect_temp_log c
sql= where 1 = 1
AND c.end_Date >= '2013-07-24 10:52:05'
AND c.end_Date <= '2013-07-24 10:55:05') A
where ROWNUM <= 3000)
where RN >= 1
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
---------------------------------------------------+-----------------------------------+---------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time | Pstart| Pstop |
---------------------------------------------------+-----------------------------------+---------------+
| 0 | SELECT STATEMENT | | | | 202K | | | |
| 1 | VIEW | | 8 | 5472 | 202K | 00:41:22 | | |
| 2 | COUNT STOPKEY | | | | | | | |
| 3 | PARTITION LIST ALL | | 8 | 704 | 202K | 00:41:22 | 1 | 111 |
| 4 | TABLE ACCESS FULL | CPS_EFFECT_TEMP_LOG| 8 | 704 | 202K | 00:41:22 | 1 | 111 |
---------------------------------------------------+-----------------------------------+---------------+
Predicate Information:
----------------------
1 - filter("RN">=1)
2 - filter(ROWNUM<=3000)
4 - filter(("C"."END_DATE"<='2013-07-24 10:55:05' AND "C"."END_DATE">='2013-07-24 10:52:05'))
Content of other_xml column
===========================
nodeid/pflags: 4 17nodeid/pflags: 3 17 db_version : 11.2.0.1
parse_schema : YIQIFA_BS_PRO
plan_hash : 662673090
plan_hash_2 : 1220428092
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
FULL(@"SEL$335DD26A" "C"@"SEL$3")
END_OUTLINE_DATA
*/
从哪里看出 全表扫描的cost明显大于索引？ |
|
发表于 2013-7-24 13:55:42
|
查看: 5493|
回复: 18
|手机版|Archiver|Oracle数据库数据恢复、性能优化
IP卡
狗仔卡
分享
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
显身卡
发表于 2013-7-26 11:10:01