Well I am very lucky today.
After cloning a 8.1.7 db I found a query would return diff results with
and without cbo or by changing the sequence of expr in the where clause.
Can anyone help me understand the 10053 trace output before apply
8.1.7.4 patch and remove this bug.
Case 1: with cbo and all analyzed, return zero rows. Wrong Result.
SELECT "USER_OR_TEAM_ID",
"ID_TYPE",
"ITEM_TYPE",
"ITEM1_ID",
"ITEM2_ID",
"ITEM3_ID",
"ITEM4_ID",
"ITEM5_ID",
"ITEM6_ID",
"PRV",
"GRANT_DENY_FLAG"
FROM acl
WHERE (
(id_type = 1 AND user_or_team_id IN (SELECT user_id FROM
my_user_id))
or
(id_type = 2 AND user_or_team_id IN (SELECT team_id FROM
my_team))
)
Case 2: with cbo and all analyzed but changing the order of expr in the
where clause, returns 16 rows. Correct result.
SELECT "USER_OR_TEAM_ID",
"ID_TYPE",
"ITEM_TYPE",
"ITEM1_ID",
"ITEM2_ID",
"ITEM3_ID",
"ITEM4_ID",
"ITEM5_ID",
"ITEM6_ID",
"PRV",
"GRANT_DENY_FLAG"
FROM acl
WHERE (
(id_type = 2 AND user_or_team_id IN (SELECT team_id FROM
my_team))
or
(id_type = 1 AND user_or_team_id IN (SELECT user_id FROM
my_user_id))
)
Case 3: with rule hint, return 16 rows. Correct result.
SELECT /*+ RULE */ "USER_OR_TEAM_ID",
"ID_TYPE",
"ITEM_TYPE",
"ITEM1_ID",
"ITEM2_ID",
"ITEM3_ID",
"ITEM4_ID",
"ITEM5_ID",
"ITEM6_ID",
"PRV",
"GRANT_DENY_FLAG"
FROM acl
WHERE (
(id_type = 1 AND user_or_team_id IN (SELECT user_id FROM
my_user_id))
or
(id_type = 2 AND user_or_team_id IN (SELECT team_id FROM
my_team))
)
Below is the 10053 trace output for the explain plan
explain plan for SELECT "USER_OR_TEAM_ID",
"ID_TYPE",
"ITEM_TYPE",
"ITEM1_ID",
"ITEM2_ID",
"ITEM3_ID",
"ITEM4_ID",
"ITEM5_ID",
"ITEM6_ID",
"PRV",
"GRANT_DENY_FLAG"
FROM acl
WHERE (
(id_type = 1 AND user_or_team_id IN (SELECT user_id FROM
my_user_id))
or
(id_type = 2 AND user_or_team_id IN (SELECT team_id FROM
my_team))
);
***********************
Dump file E:\oracle\admin\SMPS\udump\ORA00336.TRC
Thu Apr 17 11:40:55 2003
ORACLE V8.1.7.0.0 - Production vsnsta=0
vsnsql=e vsnxtr=3
Windows 2000 Version 5.0 Service Pack 3, CPU type 586
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
Windows 2000 Version 5.0 Service Pack 3, CPU type 586
Instance name: smps
Redo thread mounted by this instance: 1
Oracle process number: 9
Windows thread id: 336, image: ORACLE.EXE
- 2003-04-17 11:40:55.281
- SESSION ID:(9.53) 2003-04-17 11:40:54.984
QUERY
explain plan for SELECT "USER_OR_TEAM_ID",
"ID_TYPE",
"ITEM_TYPE",
"ITEM1_ID",
"ITEM2_ID",
"ITEM3_ID",
"ITEM4_ID",
"ITEM5_ID",
"ITEM6_ID",
"PRV",
"GRANT_DENY_FLAG"
FROM acl
WHERE (
(id_type = 1 AND user_or_team_id IN (SELECT user_id FROM
my_user_id))
or
(id_type = 2 AND user_or_team_id IN (SELECT team_id FROM
my_team))
)
PARAMETERS USED BY THE OPTIMIZER
OPTIMIZER_FEATURES_ENABLE = 8.1.7
OPTIMIZER_MODE/GOAL = Choose
OPTIMIZER_PERCENT_PARALLEL = 0
HASH_AREA_SIZE = 393216
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = FALSE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = FALSE
_PUSH_JOIN_PREDICATE = FALSE
PARALLEL_BROADCAST_ENABLED = FALSE
OPTIMIZER_MAX_PERMUTATIONS = 80000
OPTIMIZER_INDEX_CACHING = 90
OPTIMIZER_INDEX_COST_ADJ = 25
QUERY_REWRITE_ENABLED = FALSE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = FALSE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = FALSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = FALSE
_PUSH_JOIN_UNION_VIEW = FALSE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = FALSE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = TRUE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = FALSE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
DB_FILE_MULTIBLOCK_READ_COUNT = 16
SORT_AREA_SIZE = 393216
BASE STATISTICAL INFORMATION
Table stats Table: AI_USERS2TEAMS Alias: AI_USERS2TEAMS
TOTAL :: CDN: 102 NBLKS: 1 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 13
Table stats Table: AI_USERS Alias: AI_USERS
TOTAL :: CDN: 87 NBLKS: 2 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 112
-- Index stats
INDEX#: 7922 COL#: 2
TOTAL :: LVLS: 0 #LB: 1 #DK: 87 LB/K: 1 DB/K: 1 CLUF: 25
INDEX#: 7921 COL#: 1
TOTAL :: LVLS: 0 #LB: 1 #DK: 87 LB/K: 1 DB/K: 1 CLUF: 2
INDEX#: 7923 COL#: 4
TOTAL :: LVLS: 0 #LB: 1 #DK: 87 LB/K: 1 DB/K: 1 CLUF: 19
SINGLE TABLE ACCESS PATH
TABLE: AI_USERS ORIG CDN: 87 CMPTD CDN: 1
Access path: tsc Resc: 1 Resp: 1
BEST_CST: 1.00 PATH: 2 Degree: 1
SINGLE TABLE ACCESS PATH
Column: TEAM_ID Col#: 2 Table: AI_USERS2TEAMS Alias:
AI_USERS2TEAMS
NDV: 14 NULLS: 0 DENS: 7.1429e-002 LO: 519349491
HI: 2141177496
TABLE: AI_USERS2TEAMS ORIG CDN: 102 CMPTD CDN: 8
Access path: tsc Resc: 1 Resp: 1
BEST_CST: 1.00 PATH: 2 Degree: 1
OPTIMIZER STATISTICS AND COMPUTATIONS
GENERAL PLANS
Join order[1]: AI_USERS [AI_USERS] AI_USERS2TEAMS [AI_USERS2TEAMS]
Now joining: AI_USERS2TEAMS [AI_USERS2TEAMS] *******
NL Join
Outer table: cost: 1 cdn: 1 rcz: 15 resp: 1
Inner table: AI_USERS2TEAMS
Access path: tsc Resc: 1
Join resc: 2 Resp: 2
Column: USER_ID Col#: 1 Table: AI_USERS2TEAMS Alias:
AI_USERS2TEAMS
NDV: 78 NULLS: 0 DENS: 1.2821e-002 LO: 17967661 HI:
771958357
Column: USER_ID Col#: 1 Table: AI_USERS Alias: AI_USERS
NDV: 87 NULLS: 0 DENS: 1.1494e-002 LO: 17967658 HI:
771958357
Join cardinality: 1 = outer (1) * inner (8) * sel (1.2500e-001)
[flag=0]
Best NL cost: 2 resp: 2
SM Join
Outer table:
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Inner table: AI_USERS2TEAMS
resc: 1 cdn: 8 rcz: 13 deg: 1 resp: 1
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 1 Row size: 27 Rows: 1
Initial runs: 1 Merge passes: 1 Cost / pass:
2
Total sort cost: 2
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 1 Row size: 25 Rows: 8
Initial runs: 1 Merge passes: 1 Cost / pass:
2
Total sort cost: 2
Merge join Cost: 5 Resp: 5
SM Join (with index on outer)
Access path: index (no sta/stp keys)
INDEX#: 7921 TABLE: AI_USERS
CST: 3 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000
Outer table:
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Inner table: AI_USERS2TEAMS
resc: 1 cdn: 8 rcz: 13 deg: 1 resp: 1
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 1 Row size: 25 Rows: 8
Initial runs: 1 Merge passes: 1 Cost / pass:
2
Total sort cost: 2
Merge join Cost: 4 Resp: 4
HA Join
Outer table:
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Inner table: AI_USERS2TEAMS
resc: 1 cdn: 8 rcz: 13 deg: 1 resp: 1
Hash join one ptn: 1 Deg: 1
hash_area: 48 buildfrag: 49 probefrag: 1
ppasses: 2
Hash join Resc: 3 Resp: 3
Join result: cost: 2 cdn: 1 rcz: 28
Best so far: TABLE#: 0 CST: 1 CDN: 1 BYTES:
15
Best so far: TABLE#: 1 CST: 2 CDN: 1 BYTES:
28
Join order[2]: AI_USERS2TEAMS [AI_USERS2TEAMS] AI_USERS [AI_USERS]
Now joining: AI_USERS [AI_USERS] *******
NL Join
Outer table: cost: 1 cdn: 8 rcz: 13 resp: 1
Inner table: AI_USERS
Access path: tsc Resc: 1
Join resc: 9 Resp: 9
OPTIMIZER PERCENT INDEX CACHING = 90
Access path: index (unique)
INDEX#: 7921 TABLE: AI_USERS
CST: 1 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000
Join resc: 3 resp:3
OPTIMIZER PERCENT INDEX CACHING = 90
Access path: index (eq-unique)
INDEX#: 7921 TABLE: AI_USERS
CST: 1 IXSEL: 0.0000e+000 TBSEL: 0.0000e+000
Join resc: 3 resp:3
Join cardinality: 1 = outer (8) * inner (1) * sel (1.2500e-001)
[flag=0]
Best NL cost: 3 resp: 3
Final:
CST: 2 CDN: 1 RSC: 2 RSP: 2 BYTES: 28
QBCCFRW set
PARAMETERS USED BY THE OPTIMIZER
OPTIMIZER_FEATURES_ENABLE = 8.1.7
OPTIMIZER_MODE/GOAL = Choose
OPTIMIZER_PERCENT_PARALLEL = 0
HASH_AREA_SIZE = 393216
HASH_JOIN_ENABLED = TRUE
HASH_MULTIBLOCK_IO_COUNT = 0
OPTIMIZER_SEARCH_LIMIT = 5
PARTITION_VIEW_ENABLED = FALSE
_ALWAYS_STAR_TRANSFORMATION = FALSE
_B_TREE_BITMAP_PLANS = FALSE
STAR_TRANSFORMATION_ENABLED = FALSE
_COMPLEX_VIEW_MERGING = FALSE
_PUSH_JOIN_PREDICATE = FALSE
PARALLEL_BROADCAST_ENABLED = FALSE
OPTIMIZER_MAX_PERMUTATIONS = 80000
OPTIMIZER_INDEX_CACHING = 90
OPTIMIZER_INDEX_COST_ADJ = 25
QUERY_REWRITE_ENABLED = FALSE
QUERY_REWRITE_INTEGRITY = ENFORCED
_INDEX_JOIN_ENABLED = FALSE
_SORT_ELIMINATION_COST_RATIO = 0
_OR_EXPAND_NVL_PREDICATE = TRUE
_NEW_INITIAL_JOIN_ORDERS = FALSE
_OPTIMIZER_MODE_FORCE = TRUE
_OPTIMIZER_UNDO_CHANGES = FALSE
_UNNEST_SUBQUERY = FALSE
_PUSH_JOIN_UNION_VIEW = FALSE
_FAST_FULL_SCAN_ENABLED = TRUE
_OPTIM_ENHANCE_NNULL_DETECTION = TRUE
_ORDERED_NESTED_LOOP = FALSE
_NESTED_LOOP_FUDGE = 100
_NO_OR_EXPANSION = FALSE
_QUERY_COST_REWRITE = TRUE
QUERY_REWRITE_EXPRESSION = TRUE
_IMPROVED_ROW_LENGTH_ENABLED = TRUE
_USE_NOSEGMENT_INDEXES = FALSE
_ENABLE_TYPE_DEP_SELECTIVITY = TRUE
_IMPROVED_OUTERJOIN_CARD = TRUE
_OPTIMIZER_ADJUST_FOR_NULLS = TRUE
_OPTIMIZER_CHOOSE_PERMUTATION = 0
_USE_COLUMN_STATS_FOR_FUNCTION = TRUE
_SUBQUERY_PRUNING_ENABLED = TRUE
_SUBQUERY_PRUNING_REDUCTION_FACTOR = 50
_SUBQUERY_PRUNING_COST_FACTOR = 20
_LIKE_WITH_BIND_AS_EQUALITY = FALSE
_TABLE_SCAN_COST_PLUS_ONE = FALSE
_SORTMERGE_INEQUALITY_JOIN_OFF = FALSE
_DEFAULT_NON_EQUALITY_SEL_CHECK = TRUE
_ONESIDE_COLSTAT_FOR_EQUIJOINS = TRUE
DB_FILE_MULTIBLOCK_READ_COUNT = 16
SORT_AREA_SIZE = 393216
BASE STATISTICAL INFORMATION
Table stats Table: ACL Alias: ACL
TOTAL :: CDN: 2571 NBLKS: 20 TABLE_SCAN_CST: 3 AVG_ROW_LEN: 38
-- Index stats
INDEX#: 8213 COL#: 1 2 3 4
TOTAL :: LVLS: 1 #LB: 12 #DK: 1688 LB/K: 1 DB/K: 1 CLUF: 786
Table stats Table: AI_USERS Alias: AI_USERS
TOTAL :: CDN: 87 NBLKS: 2 TABLE_SCAN_CST: 1 AVG_ROW_LEN: 112
-- Index stats
INDEX#: 7922 COL#: 2
TOTAL :: LVLS: 0 #LB: 1 #DK: 87 LB/K: 1 DB/K: 1 CLUF: 25
INDEX#: 7921 COL#: 1
TOTAL :: LVLS: 0 #LB: 1 #DK: 87 LB/K: 1 DB/K: 1 CLUF: 2
INDEX#: 7923 COL#: 4
TOTAL :: LVLS: 0 #LB: 1 #DK: 87 LB/K: 1 DB/K: 1 CLUF: 19
SINGLE TABLE ACCESS PATH
TABLE: AI_USERS ORIG CDN: 87 CMPTD CDN: 1
Access path: tsc Resc: 1 Resp: 1
BEST_CST: 1.00 PATH: 2 Degree: 1
SINGLE TABLE ACCESS PATH
Column: ID_TYPE Col#: 2 Table: ACL Alias: ACL
NDV: 1 NULLS: 0 DENS: 1.0000e+000 LO: 2 HI: 2
TABLE: ACL ORIG CDN: 2571 CMPTD CDN: 2571
Access path: tsc Resc: 3 Resp: 3
BEST_CST: 3.00 PATH: 2 Degree: 1
OPTIMIZER STATISTICS AND COMPUTATIONS
GENERAL PLANS
Join order[1]: AI_USERS [AI_USERS] ACL [ACL]
Now joining: ACL [ACL] *******
NL Join
Outer table: cost: 1 cdn: 1 rcz: 15 resp: 1
Inner table: ACL
Access path: tsc Resc: 3
Join resc: 4 Resp: 4
OPTIMIZER PERCENT INDEX CACHING = 90
Column: USER_OR_TE Col#: 1 Table: ACL Alias: ACL
NDV: 15 NULLS: 0 DENS: 6.6667e-002 LO: 519349491
HI: 2141177496
Column: USER_ID Col#: 1 Table: AI_USERS Alias: AI_USERS
NDV: 87 NULLS: 0 DENS: 1.1494e-002 LO: 17967658 HI:
771958357
Access path: index (scan)
INDEX#: 8213 TABLE: ACL
CST: 54 IXSEL: 6.6667e-002 TBSEL: 6.6667e-002
Join resc: 15 resp:15
Join cardinality: 171 = outer (1) * inner (2571) * sel (6.6667e-002)
[flag=0]
Best NL cost: 4 resp: 4
SM Join
Outer table:
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Inner table: ACL
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 1 Row size: 27 Rows: 1
Initial runs: 1 Merge passes: 1 Cost / pass:
2
Total sort cost: 2
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 12 Row size: 38 Rows: 2571
Initial runs: 1 Merge passes: 1 Cost / pass:
15
Total sort cost: 14
Merge join Cost: 19 Resp: 19
SM Join (with index on outer)
Access path: index (no sta/stp keys)
INDEX#: 7921 TABLE: AI_USERS
CST: 3 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000
Outer table:
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Inner table: ACL
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 12 Row size: 38 Rows: 2571
Initial runs: 1 Merge passes: 1 Cost / pass:
15
Total sort cost: 14
Merge join Cost: 18 Resp: 18
HA Join
Outer table:
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Inner table: ACL
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
Hash join one ptn: 2 Deg: 1
hash_area: 48 buildfrag: 49 probefrag: 12
ppasses: 2
Hash join Resc: 6 Resp: 6
Join result: cost: 4 cdn: 171 rcz: 40
Best so far: TABLE#: 0 CST: 1 CDN: 1 BYTES:
15
Best so far: TABLE#: 1 CST: 4 CDN: 171 BYTES:
6840
Join order[2]: ACL [ACL] AI_USERS [AI_USERS]
Now joining: AI_USERS [AI_USERS] *******
NL Join
Outer table: cost: 3 cdn: 2571 rcz: 25 resp: 3
Inner table: AI_USERS
Access path: tsc Resc: 1
Join resc: 2574 Resp: 2574
OPTIMIZER PERCENT INDEX CACHING = 90
Access path: index (unique)
INDEX#: 7921 TABLE: AI_USERS
CST: 1 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000
Join resc: 646 resp:646
OPTIMIZER PERCENT INDEX CACHING = 90
Access path: index (eq-unique)
INDEX#: 7921 TABLE: AI_USERS
CST: 1 IXSEL: 0.0000e+000 TBSEL: 0.0000e+000
Join resc: 646 resp:646
Join cardinality: 171 = outer (2571) * inner (1) * sel (6.6667e-002)
[flag=0]
Best NL cost: 646 resp: 646
SM Join
Outer table:
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
Inner table: AI_USERS
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 12 Row size: 38 Rows: 2571
Initial runs: 1 Merge passes: 1 Cost / pass:
15
Total sort cost: 14
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 1 Row size: 27 Rows: 1
Initial runs: 1 Merge passes: 1 Cost / pass:
2
Total sort cost: 2
Merge join Cost: 19 Resp: 19
HA Join
Outer table:
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
Inner table: AI_USERS
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Hash join one ptn: 2 Deg: 1 (sides swapped)
hash_area: 48 buildfrag: 49 probefrag: 12
ppasses: 2
Hash join Resc: 6 Resp: 6
******** Next OR predicate ********
******** OR-branching ********
Column: USER_OR_TE Col#: 1 Table: ACL Alias: ACL
NDV: 15 NULLS: 0 DENS: 6.6667e-002 LO: 519349491
HI: 2141177496
Column: ID_TYPE Col#: 2 Table: ACL Alias: ACL
NDV: 1 NULLS: 0 DENS: 1.0000e+000 LO: 2 HI: 2
Column: ID_TYPE Col#: 2 Table: ACL Alias: ACL
NDV: 1 NULLS: 0 DENS: 1.0000e+000 LO: 2 HI: 2
Column: USER_OR_TE Col#: 1 Table: ACL Alias: ACL
NDV: 15 NULLS: 0 DENS: 6.6667e-002 LO: 519349491
HI: 2141177496
Column: ID_TYPE Col#: 2 Table: ACL Alias: ACL
NDV: 1 NULLS: 0 DENS: 1.0000e+000 LO: 2 HI: 2
Column: USER_OR_TE Col#: 1 Table: ACL Alias: ACL
NDV: 15 NULLS: 0 DENS: 6.6667e-002 LO: 519349491
HI: 2141177496
Column: USER_ID Col#: 1 Table: AI_USERS Alias: AI_USERS
NDV: 87 NULLS: 0 DENS: 1.1494e-002 LO: 17967658 HI:
771958357
SINGLE TABLE ACCESS PATH
TABLE: AI_USERS ORIG CDN: 87 CMPTD CDN: 1
Access path: tsc Resc: 1 Resp: 1
BEST_CST: 1.00 PATH: 2 Degree: 1
SINGLE TABLE ACCESS PATH
TABLE: ACL ORIG CDN: 2571 CMPTD CDN: 2571
Access path: tsc Resc: 3 Resp: 3
BEST_CST: 3.00 PATH: 2 Degree: 1
GENERAL PLANS
Join order[1]: AI_USERS [AI_USERS] ACL [ACL]
Now joining: ACL [ACL] *******
NL Join
Outer table: cost: 1 cdn: 1 rcz: 15 resp: 1
Inner table: ACL
Access path: tsc Resc: 3
Join resc: 4 Resp: 4
OPTIMIZER PERCENT INDEX CACHING = 90
Access path: index (scan)
INDEX#: 8213 TABLE: ACL
CST: 54 IXSEL: 6.6667e-002 TBSEL: 6.6667e-002
Join resc: 15 resp:15
Join cardinality: 171 = outer (1) * inner (2571) * sel (6.6667e-002)
[flag=0]
Best NL cost: 4 resp: 4
SM Join
Outer table:
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Inner table: ACL
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 1 Row size: 27 Rows: 1
Initial runs: 1 Merge passes: 1 Cost / pass:
2
Total sort cost: 2
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 12 Row size: 38 Rows: 2571
Initial runs: 1 Merge passes: 1 Cost / pass:
15
Total sort cost: 14
Merge join Cost: 19 Resp: 19
SM Join (with index on outer)
Access path: index (no sta/stp keys)
INDEX#: 7921 TABLE: AI_USERS
CST: 3 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000
Outer table:
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Inner table: ACL
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 12 Row size: 38 Rows: 2571
Initial runs: 1 Merge passes: 1 Cost / pass:
15
Total sort cost: 14
Merge join Cost: 18 Resp: 18
HA Join
Outer table:
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Inner table: ACL
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
Hash join one ptn: 2 Deg: 1
hash_area: 48 buildfrag: 49 probefrag: 12
ppasses: 2
Hash join Resc: 6 Resp: 6
Join result: cost: 4 cdn: 171 rcz: 40
Best so far: TABLE#: 0 CST: 1 CDN: 1 BYTES:
15
Best so far: TABLE#: 1 CST: 4 CDN: 171 BYTES:
6840
Join order[2]: ACL [ACL] AI_USERS [AI_USERS]
Now joining: AI_USERS [AI_USERS] *******
NL Join
Outer table: cost: 3 cdn: 2571 rcz: 25 resp: 3
Inner table: AI_USERS
Access path: tsc Resc: 1
Join resc: 2574 Resp: 2574
OPTIMIZER PERCENT INDEX CACHING = 90
Access path: index (unique)
INDEX#: 7921 TABLE: AI_USERS
CST: 1 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000
Join resc: 646 resp:646
OPTIMIZER PERCENT INDEX CACHING = 90
Access path: index (eq-unique)
INDEX#: 7921 TABLE: AI_USERS
CST: 1 IXSEL: 0.0000e+000 TBSEL: 0.0000e+000
Join resc: 646 resp:646
Join cardinality: 171 = outer (2571) * inner (1) * sel (6.6667e-002)
[flag=0]
Best NL cost: 646 resp: 646
SM Join
Outer table:
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
Inner table: AI_USERS
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 12 Row size: 38 Rows: 2571
Initial runs: 1 Merge passes: 1 Cost / pass:
15
Total sort cost: 14
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 1 Row size: 27 Rows: 1
Initial runs: 1 Merge passes: 1 Cost / pass:
2
Total sort cost: 2
Merge join Cost: 19 Resp: 19
HA Join
Outer table:
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
Inner table: AI_USERS
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Hash join one ptn: 2 Deg: 1 (sides swapped)
hash_area: 48 buildfrag: 49 probefrag: 12
ppasses: 2
Hash join Resc: 6 Resp: 6
******** OR-branching ********
Column: USER_OR_TE Col#: 1 Table: ACL Alias: ACL
NDV: 15 NULLS: 0 DENS: 6.6667e-002 LO: 519349491
HI: 2141177496
Column: ID_TYPE Col#: 2 Table: ACL Alias: ACL
NDV: 1 NULLS: 0 DENS: 1.0000e+000 LO: 2 HI: 2
Column: ID_TYPE Col#: 2 Table: ACL Alias: ACL
NDV: 1 NULLS: 0 DENS: 1.0000e+000 LO: 2 HI: 2
Column: USER_OR_TE Col#: 1 Table: ACL Alias: ACL
NDV: 15 NULLS: 0 DENS: 6.6667e-002 LO: 519349491
HI: 2141177496
Column: ID_TYPE Col#: 2 Table: ACL Alias: ACL
NDV: 1 NULLS: 0 DENS: 1.0000e+000 LO: 2 HI: 2
Column: USER_OR_TE Col#: 1 Table: ACL Alias: ACL
NDV: 15 NULLS: 0 DENS: 6.6667e-002 LO: 519349491
HI: 2141177496
Column: USER_ID Col#: 1 Table: AI_USERS Alias: AI_USERS
NDV: 87 NULLS: 0 DENS: 1.1494e-002 LO: 17967658 HI:
771958357
SINGLE TABLE ACCESS PATH
TABLE: AI_USERS ORIG CDN: 87 CMPTD CDN: 1
Access path: tsc Resc: 1 Resp: 1
BEST_CST: 1.00 PATH: 2 Degree: 1
SINGLE TABLE ACCESS PATH
TABLE: ACL ORIG CDN: 2571 CMPTD CDN: 2571
Access path: tsc Resc: 3 Resp: 3
BEST_CST: 3.00 PATH: 2 Degree: 1
GENERAL PLANS
Join order[1]: AI_USERS [AI_USERS] ACL [ACL]
Now joining: ACL [ACL] *******
NL Join
Outer table: cost: 1 cdn: 1 rcz: 15 resp: 1
Inner table: ACL
Access path: tsc Resc: 3
Join resc: 4 Resp: 4
OPTIMIZER PERCENT INDEX CACHING = 90
Access path: index (scan)
INDEX#: 8213 TABLE: ACL
CST: 54 IXSEL: 6.6667e-002 TBSEL: 6.6667e-002
Join resc: 15 resp:15
Join cardinality: 171 = outer (1) * inner (2571) * sel (6.6667e-002)
[flag=0]
Best NL cost: 4 resp: 4
SM Join
Outer table:
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Inner table: ACL
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 1 Row size: 27 Rows: 1
Initial runs: 1 Merge passes: 1 Cost / pass:
2
Total sort cost: 2
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 12 Row size: 38 Rows: 2571
Initial runs: 1 Merge passes: 1 Cost / pass:
15
Total sort cost: 14
Merge join Cost: 19 Resp: 19
SM Join (with index on outer)
Access path: index (no sta/stp keys)
INDEX#: 7921 TABLE: AI_USERS
CST: 3 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000
Outer table:
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Inner table: ACL
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 12 Row size: 38 Rows: 2571
Initial runs: 1 Merge passes: 1 Cost / pass:
15
Total sort cost: 14
Merge join Cost: 18 Resp: 18
HA Join
Outer table:
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Inner table: ACL
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
Hash join one ptn: 2 Deg: 1
hash_area: 48 buildfrag: 49 probefrag: 12
ppasses: 2
Hash join Resc: 6 Resp: 6
Join result: cost: 4 cdn: 171 rcz: 40
Best so far: TABLE#: 0 CST: 1 CDN: 1 BYTES:
15
Best so far: TABLE#: 1 CST: 4 CDN: 171 BYTES:
6840
Join order[2]: ACL [ACL] AI_USERS [AI_USERS]
Now joining: AI_USERS [AI_USERS] *******
NL Join
Outer table: cost: 3 cdn: 2571 rcz: 25 resp: 3
Inner table: AI_USERS
Access path: tsc Resc: 1
Join resc: 2574 Resp: 2574
OPTIMIZER PERCENT INDEX CACHING = 90
Access path: index (unique)
INDEX#: 7921 TABLE: AI_USERS
CST: 1 IXSEL: 1.0000e+000 TBSEL: 1.0000e+000
Join resc: 646 resp:646
OPTIMIZER PERCENT INDEX CACHING = 90
Access path: index (eq-unique)
INDEX#: 7921 TABLE: AI_USERS
CST: 1 IXSEL: 0.0000e+000 TBSEL: 0.0000e+000
Join resc: 646 resp:646
Join cardinality: 171 = outer (2571) * inner (1) * sel (6.6667e-002)
[flag=0]
Best NL cost: 646 resp: 646
SM Join
Outer table:
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
Inner table: AI_USERS
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 12 Row size: 38 Rows: 2571
Initial runs: 1 Merge passes: 1 Cost / pass:
15
Total sort cost: 14
SORT resource Sort statistics
Sort width: 5 Area size: 135168 Degree: 1
Blocks to Sort: 1 Row size: 27 Rows: 1
Initial runs: 1 Merge passes: 1 Cost / pass:
2
Total sort cost: 2
Merge join Cost: 19 Resp: 19
HA Join
Outer table:
resc: 3 cdn: 2571 rcz: 25 deg: 1 resp: 3
Inner table: AI_USERS
resc: 1 cdn: 1 rcz: 15 deg: 1 resp: 1
Hash join one ptn: 2 Deg: 1 (sides swapped)
hash_area: 48 buildfrag: 49 probefrag: 12
ppasses: 2
Hash join Resc: 6 Resp: 6
Final:
CST: 4 CDN: 171 RSC: 4 RSP: 4 BYTES: 6840
PLAN
Cost of plan: 4
Operation...........Object name.....Options.........Id...Pid..
SELECT STATEMENT 0
NESTED LOOPS 1
TABLE ACCESS AI_USERS FULL 2 1
TABLE ACCESS ACL FULL 3 1
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mandar A. Ghosalkar
INET: mghosalk_at_byer.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Apr 17 2003 - 14:47:05 CDT
