Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

TABLE ACCESS BY INDEX ROWID cardinality > 1

640834Aug 18 2008 — edited Apr 28 2009
Hi,

Is it a wrong cardinality estimation in step 4 of the plan?
It has estimated 949 (table rows) Should it be 1?

SELECT /*comment*/
P.NOMBREPERSONACOMPLETO
FROM
EFECTOS E,
PERSONAS P
WHERE P.CODIGODEEMPRESA = E.CODIGODEEMPRESA
AND P.CODIGOPERSONAL = E.CODIGOPERSONAL
and e.numefectofactor =25970

============
Plan Table
============
---------------------------------------------------+------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------+------------------------
| 0 | SELECT STATEMENT | | | | 2 |
| 1 | NESTED LOOPS | | 1 | 39 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID | EFECTOS | 1 | 13 | 1 |
| 3 | INDEX UNIQUE SCAN | EFECTOS_PK | 1 | | 0 |
| 4 | TABLE ACCESS BY INDEX ROWID | PERSONAS | 979 | 25K | 1 |
| 5 | INDEX UNIQUE SCAN | PK_PERSONAS| 1 | | 0 |
---------------------------------------------------+------------------------

Any clue? Thanks!!

pd.: I paste the 10053 trace.

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/oracle/product/10.2.0
System name: SunOS
...
Release: 5.10
Version: Generic_120011-14
Machine: sun4u
...
******************************************
Current SQL statement for this session:
SELECT /*comment*/
P.NOMBREPERSONACOMPLETO
FROM
EFECTOS E,
PERSONAS P
WHERE P.CODIGODEEMPRESA = E.CODIGODEEMPRESA
AND P.CODIGOPERSONAL = E.CODIGOPERSONAL
and e.numefectofactor =25970
*******************************************
Legend
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
JPPD - join predicate push-down
FPD - filter push-down
PM - predicate move-around
CVM - complex view merging
SPJ - select-project-join
SJC - set join conversion
SU - subquery unnesting
OBYE - order by elimination
ST - star transformation
qb - query block
LB - leaf blocks
DK - distinct keys
LB/K - average number of leaf blocks per key
DB/K - average number of data blocks per key
CLUF - clustering factor
NDV - number of distinct values
Resp - response cost
Card - cardinality
Resc - resource cost
NL - nested loops (join)
SM - sort merge (join)
HA - hash (join)
CPUCSPEED - CPU Speed
IOTFRSPEED - I/O transfer speed
IOSEEKTIM - I/O seek time
SREADTIM - average single block read time
MREADTIM - average multiblock read time
MBRC - average multiblock read count
MAXTHR - maximum I/O system throughput
SLAVETHR - average slave I/O throughput
dmeth - distribution method
1: no partitioning required
2: value partitioned
4: right is random (round-robin)
512: left is random (round-robin)
8: broadcast right and partition left
16: broadcast left and partition right
32: partition left using partitioning of right
64: partition right using partitioning of left
128: use hash partitioning dimension
256: use range partitioning dimension
2048: use list partitioning dimension
1024: run the join in serial
0: invalid distribution method
sel - selectivity
ptn - partition
*******************************************
Peeked values of the binds in SQL statement
*******************************************
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
*************************************
PARAMETERS WITH ALTERED VALUES
******************************
sqlstat_enabled = true
statistics_level = all
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
fix 4663698 = enabled
fix 4545833 = enabled
fix 3499674 = disabled
fix 4584065 = enabled
fix 4602374 = enabled
fix 4569940 = enabled
fix 4631959 = enabled
fix 4519340 = enabled
fix 4550003 = enabled
fix 4488689 = enabled
fix 3118776 = enabled
fix 4519016 = enabled
fix 4487253 = enabled
fix 4556762 = 15
fix 4728348 = enabled
fix 4723244 = enabled
fix 4554846 = enabled
fix 4175830 = enabled
fix 4722900 = enabled
fix 5094217 = enabled
fix 4904890 = enabled
fix 4483286 = disabled
fix 4969880 = disabled
fix 4711525 = enabled
fix 4717546 = enabled
fix 4904838 = enabled
fix 5005866 = enabled
fix 4600710 = enabled
fix 5129233 = enabled
fix 5195882 = enabled
fix 5084239 = enabled
fix 4595987 = enabled
fix 4134994 = enabled
fix 5104624 = enabled
fix 4908162 = enabled
fix 5015557 = enabled
*************************************
PARAMETERS WITH DEFAULT VALUES
******************************
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
queryrewrite_fudge = 90
optimizer_features_enable = 10.2.0.3
optimizersearch_limit = 5
cpu_count = 4
active_instance_count = 1
parallel_threads_per_cpu = 2
hash_area_size = 131072
bitmap_merge_area_size = 1048576
sort_area_size = 65536
sort_area_retained_size = 0
sortelimination_cost_ratio = 0
optimizerblock_size = 8192
sortmultiblock_read_count = 2
hashmultiblock_io_count = 0
dbfile_optimizer_read_count = 16
optimizermax_permutations = 2000
pga_aggregate_target = 512000 KB
pgamax_size = 204800 KB
queryrewrite_maxdisjunct = 257
smmauto_min_io_size = 56 KB
smmauto_max_io_size = 248 KB
smmmin_size = 512 KB
smmmax_size = 102400 KB
smmpx_max_size = 256000 KB
cputo_io = 0
optimizerundo_cost_change = 10.2.0.3
parallel_query_mode = enabled
parallel_dml_mode = disabled
parallel_ddl_mode = enabled
optimizer_mode = all_rows
optimizerpercent_parallel = 101
alwaysanti_join = choose
alwayssemi_join = choose
optimizermode_force = true
partitionview_enabled = true
alwaysstar_transformation = false
queryrewrite_or_error = false
hashjoin_enabled = true
cursor_sharing = exact
btree_bitmap_plans = true
star_transformation_enabled = false
optimizercost_model = choose
newsort_cost_estimate = true
complexview_merging = true
unnestsubquery = true
eliminatecommon_subexpr = true
predmove_around = true
convertset_to_join = false
pushjoin_predicate = true
pushjoin_union_view = true
fastfull_scan_enabled = true
optimenhance_nnull_detection = true
parallelbroadcast_enabled = true
pxbroadcast_fudge_factor = 100
orderednested_loop = true
noor_expansion = false
optimizer_index_cost_adj = 100
optimizer_index_caching = 0
systemindex_caching = 0
disabledatalayer_sampling = false
query_rewrite_enabled = true
query_rewrite_integrity = enforced
querycost_rewrite = true
queryrewrite_2 = true
queryrewrite_1 = true
queryrewrite_expression = true
queryrewrite_jgmigrate = true
queryrewrite_fpc = true
queryrewrite_drj = true
fullpwise_join_enabled = true
partialpwise_join_enabled = true
leftnested_loops_random = true
improvedrow_length_enabled = true
indexjoin_enabled = true
enabletype_dep_selectivity = true
improvedouterjoin_card = true
optimizeradjust_for_nulls = true
optimizerdegree = 0
usecolumn_stats_for_function = true
subquerypruning_enabled = true
subquerypruning_mv_enabled = false
orexpand_nvl_predicate = true
likewith_bind_as_equality = false
tablescan_cost_plus_one = true
costequality_semi_join = true
defaultnon_equality_sel_check = true
newinitial_join_orders = true
onesidecolstat_for_equijoins = true
optimpeek_user_binds = true
minimalstats_aggregation = true
forcetemptables_for_gsets = false
workarea_size_policy = auto
smmauto_cost_enabled = true
gsanti_semi_join_allowed = true
optimnew_default_join_sel = true
optimizer_dynamic_sampling = 2
prerewrite_push_pred = true
optimizernew_join_card_computation = true
unionrewrite_for_gs = yes_gset_mvs
generalizedpruning_enabled = true
optimadjust_for_part_skews = true
forcedatefold_trunc = false
optimizersystem_stats_usage = true
skip_unusable_indexes = true
removeaggr_subquery = true
optimizerpush_down_distinct = 0
dmlmonitoring_enabled = true
optimizerundo_changes = false
predicateelimination_enabled = true
nestedloop_fudge = 100
projectview_columns = true
localcommunication_costing_enabled = true
localcommunication_ratio = 50
queryrewrite_vop_cleanup = true
slavemapping_enabled = true
optimizercost_based_transformation = linear
optimizermjc_enabled = true
rightouter_hash_enable = true
sprpush_pred_refspr = true
optimizercache_stats = false
optimizercbqt_factor = 50
optimizersqu_bottomup = true
ficarea_size = 131072
optimizerskip_scan_enabled = true
optimizercost_filter_pred = false
optimizersortmerge_join_enabled = true
optimizerjoin_sel_sanity_check = true
mmvquery_rewrite_enabled = true
btmmv_query_rewrite_enabled = true
addstale_mv_to_dependency_list = true
distinctview_unnesting = false
optimizerdim_subq_join_sel = true
optimizerdisable_strans_sanity_checks = 0
optimizercompute_index_stats = true
pushjoin_union_view2 = true
optimizerignore_hints = false
optimizerrandom_plan = 0
queryrewrite_setopgrw_enable = true
optimizercorrect_sq_selectivity = true
disablefunction_based_index = false
optimizerjoin_order_control = 3
optimizercartesian_enabled = true
optimizerstarplan_enabled = true
extendedpruning_enabled = true
optimizerpush_pred_cost_based = true
sqlmodel_unfold_forloops = run_time
enabledml_lock_escalation = false
bloomfilter_enabled = true
updatebji_ipdml_enabled = 0
optimizerextended_cursor_sharing = udo
dmmax_shared_pool_pct = 1
optimizercost_hjsmj_multimatch = true
optimizertransitivity_retain = true
pxpwg_enabled = true
optimizer_secure_view_merging = true
optimizerjoin_elimination_enabled = true
flashback_table_rpi = non_fbt
optimizercbqt_no_size_restriction = true
optimizerenhanced_filter_push = true
optimizerfilter_pred_pullup = true
rowsrctrace_level = 0
simpleview_merging = true
optimizerrownum_pred_based_fkr = true
optimizerbetter_inlist_costing = all
optimizerself_induced_cache_cost = false
optimizermin_cache_blocks = 10
optimizeror_expansion = depth
optimizerorder_by_elimination_enabled = true
optimizerouter_to_anti_enabled = true
selfjoinmv_duplicates = true
dimensionskip_null = true
forcerewrite_enable = false
optimizerstar_tran_in_with_clause = true
optimizercomplex_pred_selectivity = true
optimizerconnect_by_cost_based = true
gbyhash_aggregation_enabled = true
globalindexpnum_filter_enabled = true
fixcontrol_key = 0
optimizerskip_scan_guess = false
enablerow_shipping = false
rowshipping_threshold = 80
rowshipping_explain = false
optimizerrownum_bind_default = 10
firstk_rows_dynamic_proration = true
optimizernative_full_outer_join = off
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
fix 4663698 = enabled
fix 4545833 = enabled
fix 3499674 = disabled
fix 4584065 = enabled
fix 4602374 = enabled
fix 4569940 = enabled
fix 4631959 = enabled
fix 4519340 = enabled
fix 4550003 = enabled
fix 4488689 = enabled
fix 3118776 = enabled
fix 4519016 = enabled
fix 4487253 = enabled
fix 4556762 = 15
fix 4728348 = enabled
fix 4723244 = enabled
fix 4554846 = enabled
fix 4175830 = enabled
fix 4722900 = enabled
fix 5094217 = enabled
fix 4904890 = enabled
fix 4483286 = disabled
fix 4969880 = disabled
fix 4711525 = enabled
fix 4717546 = enabled
fix 4904838 = enabled
fix 5005866 = enabled
fix 4600710 = enabled
fix 5129233 = enabled
fix 5195882 = enabled
fix 5084239 = enabled
fix 4595987 = enabled
fix 4134994 = enabled
fix 5104624 = enabled
fix 4908162 = enabled
fix 5015557 = enabled
***************************************
PARAMETERS IN OPT_PARAM HINT
****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
****************
QUERY BLOCK TEXT
****************
SELECT /*comment*/
P.NOMBREPERSONACOMPLETO
FROM
EFECTOS E,
PERSONAS P
WHERE P.CODIGODEEMPRESA = E.CODIGODEEMPRESA
AND P.CODIGOPERSONAL = E.CODIGOPERSONAL
and e.numefectofactor =25970
*********************
QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
fro(0): flg=0 objn=72866 hint_alias="E"@"SEL$1"
fro(1): flg=0 objn=73042 hint_alias="P"@"SEL$1"
*****************************
SYSTEM STATISTICS INFORMATION
*****************************
Using NOWORKLOAD Stats
CPUSPEED: 243 millions instruction/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: PERSONAS Alias: P
#Rows: 979 #Blks: 13 AvgRowLen: 75.00
Column (#1): CODIGODEEMPRESA(VARCHAR2)
AvgLen: 6.00 NDV: 5 Nulls: 0 Density: 0.2
Column (#2): CODIGOPERSONAL(NUMBER)
AvgLen: 5.00 NDV: 929 Nulls: 0 Density: 0.0010764 Min: 0 Max: 900000000
Index Stats::
Index: IDX_PERSONA_IDIOMA Col#: 4
LVLS: 1 #LB: 3 #DK: 11 LB/K: 1.00 DB/K: 2.00 CLUF: 26.00
Index: PERSONAS_AU Col#: 18 19
LVLS: 0 #LB: 1 #DK: 1 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Index: PERSONAS_IDX_003 Col#: 8 12
LVLS: 1 #LB: 8 #DK: 974 LB/K: 1.00 DB/K: 1.00 CLUF: 283.00
Index: PERSONAS_IDX_005 Col#: 1 12
LVLS: 1 #LB: 5 #DK: 874 LB/K: 1.00 DB/K: 1.00 CLUF: 544.00
Index: PK_PERSONAS Col#: 1 2
LVLS: 1 #LB: 4 #DK: 979 LB/K: 1.00 DB/K: 1.00 CLUF: 564.00
***********************
Table Stats::
Table: EFECTOS Alias: E
#Rows: 104 #Blks: 5 AvgRowLen: 94.00
Column (#2): CODIGODEEMPRESA(VARCHAR2)
AvgLen: 5.00 NDV: 1 Nulls: 0 Density: 1
Column (#8): CODIGOPERSONAL(NUMBER)
AvgLen: 4.00 NDV: 19 Nulls: 0 Density: 0.052632 Min: 1 Max: 1666
Index Stats::
Index: EFECTOS_AU Col#: 22 23
LVLS: 0 #LB: 0 #DK: 0 LB/K: 0.00 DB/K: 0.00 CLUF: 0.00
Index: EFECTOS_CODIGOCONTRATO_IDX Col#: 4
LVLS: 0 #LB: 1 #DK: 20 LB/K: 1.00 DB/K: 1.00 CLUF: 26.00
Index: EFECTOS_FECHAVENCIMIENTO_IDX Col#: 14
LVLS: 0 #LB: 1 #DK: 69 LB/K: 1.00 DB/K: 1.00 CLUF: 40.00
Index: EFECTOS_NUMEFECTOCEDENTE_IDX Col#: 10
LVLS: 0 #LB: 1 #DK: 101 LB/K: 1.00 DB/K: 1.00 CLUF: 54.00
Index: EFECTOS_PK Col#: 1
LVLS: 0 #LB: 1 #DK: 104 LB/K: 1.00 DB/K: 1.00 CLUF: 32.00
***************************************
1-ROW TABLES: EFECTOS[E]#0 PERSONAS[P]#1
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): NUMEFECTOFACTOR(NUMBER)
AvgLen: 4.00 NDV: 104 Nulls: 0 Density: 0.0096154 Min: 1 Max: 1062
Using prorated density: 0.0048077 of col #1 as selectivity of out-of-range value pred
Table: EFECTOS Alias: E
Card: Original: 104 Rounded: 1 Computed: 0.50 Non Adjusted: 0.50
Access Path: TableScan
Cost: 3.02 Resp: 3.02 Degree: 0
Cost_io: 3.00 Cost_cpu: 56547
Resp_io: 3.00 Resp_cpu: 56547
Using prorated density: 0.0048077 of col #1 as selectivity of out-of-range value pred
Access Path: index (UniqueScan)
Index: EFECTOS_PK
resc_io: 1.00 resc_cpu: 8461
ix_sel: 0.0096154 ix_sel_with_filters: 0.0096154
Cost: 1.00 Resp: 1.00 Degree: 1
Using prorated density: 0.0048077 of col #1 as selectivity of out-of-range value pred
Access Path: index (AllEqUnique)
Index: EFECTOS_PK
resc_io: 1.00 resc_cpu: 8461
ix_sel: 0.0048077 ix_sel_with_filters: 0.0048077
Cost: 1.00 Resp: 1.00 Degree: 1
One row Card: 1.00
Best:: AccessPath: IndexUnique Index: EFECTOS_PK
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 1.00 Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
Table: PERSONAS Alias: P
Card: Original: 979 Rounded: 979 Computed: 979.00 Non Adjusted: 979.00
Access Path: TableScan
Cost: 5.16 Resp: 5.16 Degree: 0
Cost_io: 5.00 Cost_cpu: 454809
Resp_io: 5.00 Resp_cpu: 454809
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: PERSONAS_IDX_005
resc_io: 6.00 resc_cpu: 238529
ix_sel: 1 ix_sel_with_filters: 1
Cost: 6.08 Resp: 6.08 Degree: 0
Access Path: index (FullScan)
Index: PK_PERSONAS
resc_io: 5.00 resc_cpu: 231407
ix_sel: 1 ix_sel_with_filters: 1
Cost: 5.08 Resp: 5.08 Degree: 0
Access Path: index (FullScan)
Index: PK_PERSONAS
resc_io: 5.00 resc_cpu: 231407
ix_sel: 1 ix_sel_with_filters: 1
Cost: 5.08 Resp: 5.08 Degree: 0
Access Path: index (FullScan)
Index: PERSONAS_IDX_005
resc_io: 6.00 resc_cpu: 238529
ix_sel: 1 ix_sel_with_filters: 1
Cost: 6.08 Resp: 6.08 Degree: 0
******** Cost index join ********
Index join: Considering index join to index PK_PERSONAS
Index join: Joining index PERSONAS_IDX_005
Ix HA Join
Outer table:
resc: 6.35 card 979.00 bytes: 21 deg: 1 resp: 6.35
Inner table: <no name>
resc: 7.60 card: 979.00 bytes: 31 deg: 1 resp: 7.60
using dmeth: 2 #groups: 1
Cost per ptn: 0.58 #ptns: 1
hash_area: 0 (max=128) Hash join: Resc: 14.54 Resp: 14.54 [multiMatchCost=0.00]
******** Index join cost ********
Cost: 14.54
******** End index join costing ********
Best:: AccessPath: TableScan
Cost: 5.16 Degree: 1 Resp: 5.16 Card: 979.00 Bytes: 0
Concatenated index card: 979 #cols: 2 Table: EFECTOS
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
***********************
Join order[1]: EFECTOS[E]#0 PERSONAS[P]#1
***************
Now joining: PERSONAS[P]#1
***************
NL Join
Outer table: Card: 1.00 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 13
Inner table: PERSONAS Alias: P
Access Path: TableScan
NL Join: Cost: 6.16 Resp: 6.16 Degree: 0
Cost_io: 6.00 Cost_cpu: 463270
Resp_io: 6.00 Resp_cpu: 463270
Access Path: index (UniqueScan)
Index: PK_PERSONAS
resc_io: 1.00 resc_cpu: 9391
ix_sel: 0.0010215 ix_sel_with_filters: 0.0010215
NL Join (ordered): Cost: 2.01 Resp: 2.01 Degree: 1
Cost_io: 2.00 Cost_cpu: 17853
Resp_io: 2.00 Resp_cpu: 17853
Access Path: index (RangeScan)
Index: PERSONAS_IDX_005
resc_io: 110.00 resc_cpu: 895928
ix_sel: 0.2 ix_sel_with_filters: 0.2
NL Join (ordered): Cost: 111.31 Resp: 111.31 Degree: 1
Cost_io: 111.00 Cost_cpu: 904390
Resp_io: 111.00 Resp_cpu: 904390
Access Path: index (AllEqUnique)
Index: PK_PERSONAS
resc_io: 1.00 resc_cpu: 9391
ix_sel: 0.0010215 ix_sel_with_filters: 0.0010215
NL Join (ordered): Cost: 2.01 Resp: 2.01 Degree: 1
Cost_io: 2.00 Cost_cpu: 17853
Resp_io: 2.00 Resp_cpu: 17853
****** trying bitmap/domain indexes ******
Best NL cost: 2.01
resc: 2.01 resc_io: 2.00 resc_cpu: 17853
resp: 2.01 resp_io: 2.00 resp_cpu: 17853
Join Card: 1.00 = outer (1.00) * inner (1.00) * sel (0.0010256)
Join Card - Rounded: 1 Computed: 1.00
SM Join
Outer table:
resc: 1.00 card 1.00 bytes: 13 deg: 1 resp: 1.00
Inner table: PERSONAS Alias: P
resc: 5.16 card: 979.00 bytes: 26 deg: 1 resp: 5.16
using dmeth: 2 #groups: 1
SORT resource Sort statistics
Sort width: 598 Area size: 524288 Max Area size: 104857600
Degree: 1
Blocks to Sort: 5 Row size: 39 Total Rows: 979
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 3356193
Total Temp space used: 0
SM join: Resc: 7.31 Resp: 7.31 [multiMatchCost=0.00]
SM cost: 7.31
resc: 7.31 resc_io: 6.00 resc_cpu: 3819463
resp: 7.31 resp_io: 6.00 resp_cpu: 3819463
HA Join
Outer table:
resc: 1.00 card 1.00 bytes: 13 deg: 1 resp: 1.00
Inner table: PERSONAS Alias: P
resc: 5.16 card: 979.00 bytes: 26 deg: 1 resp: 5.16
using dmeth: 2 #groups: 1
Cost per ptn: 0.53 #ptns: 1
hash_area: 0 (max=128) Hash join: Resc: 6.69 Resp: 6.69 [multiMatchCost=0.00]
HA cost: 6.69
resc: 6.69 resc_io: 6.00 resc_cpu: 2020305
resp: 6.69 resp_io: 6.00 resp_cpu: 2020305
Best:: JoinMethod: NestedLoop
Cost: 2.01 Degree: 1 Resp: 2.01 Card: 1.00 Bytes: 39
***********************
Best so far: Table#: 0 cost: 1.0029 card: 1.0000 bytes: 13
Table#: 1 cost: 2.0061 card: 1.0000 bytes: 39
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
*********************************
Number of join permutations tried: 1
*********************************
(newjo-save) [1 0 ]
Final - All Rows Plan: Best join order: 1
Cost: 2.0061 Degree: 1 Card: 1.0000 Bytes: 39
Resc: 2.0061 Resc_io: 2.0000 Resc_cpu: 17853
Resp: 2.0061 Resp_io: 2.0000 Resc_cpu: 17853
kkoipt: Query block SEL$1 (#0)
******* UNPARSED QUERY IS *******
SELECT "P"."NOMBREPERSONACOMPLETO" "NOMBREPERSONACOMPLETO" FROM "FACTORING"."EFECTOS" "E","GESAFIN"."PERSONAS" "P" WHERE "E"."NUMEFECTOFACTOR"=25970 AND "P"."CODIGOPERSONAL"="E"."CODIGOPERSONAL" AND "P"."CODIGODEEMPRESA"="E"."CODIGODEEMPRESA"
kkoqbc-end
: call(in-use=73376, alloc=82568), compile(in-use=47528, alloc=50360)
apadrv-end: call(in-use=73376, alloc=82568), compile(in-use=48384, alloc=50360)
=====================
PARSING IN CURSOR #1 len=74 dep=1 uid=0 oct=6 lid=0 tim=11120908968272 hv=2492450042 ad='df8424e0'
update object_usage set flags=flags+1 where obj#=:1 and bitand(flags, 1)=0
END OF STMT
PARSE #1:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=11120908968262
EXEC #1:c=0,e=396,p=0,cr=3,cu=0,mis=0,r=0,dep=1,og=4,tim=11120908968865
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE OBJECT_USAGE (cr=3 pr=0 pw=0 time=195 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=569 op='TABLE ACCESS BY INDEX ROWID OBJECT_USAGE (cr=3 pr=0 pw=0 time=183 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=570 op='INDEX RANGE SCAN I_STATS_OBJ# (cr=2 pr=0 pw=0 time=122 us)'
=====================
PARSING IN CURSOR #1 len=74 dep=1 uid=0 oct=6 lid=0 tim=11120908969447 hv=2492450042 ad='df8424e0'
update object_usage set flags=flags+1 where obj#=:1 and bitand(flags, 1)=0
END OF STMT
PARSE #1:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=11120908969440
EXEC #1:c=0,e=231,p=0,cr=4,cu=0,mis=0,r=0,dep=1,og=4,tim=11120908969855
STAT #1 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE OBJECT_USAGE (cr=4 pr=0 pw=0 time=137 us)'
STAT #1 id=2 cnt=0 pid=1 pos=1 obj=569 op='TABLE ACCESS BY INDEX ROWID OBJECT_USAGE (cr=4 pr=0 pw=0 time=131 us)'
STAT #1 id=3 cnt=1 pid=2 pos=1 obj=570 op='INDEX RANGE SCAN I_STATS_OBJ# (cr=2 pr=0 pw=0 time=46 us)'

sql_id=6hjw42ynhzwsr.
Current SQL statement for this session:
SELECT /*comment*/
P.NOMBREPERSONACOMPLETO
FROM
EFECTOS E,
PERSONAS P
WHERE P.CODIGODEEMPRESA = E.CODIGODEEMPRESA
AND P.CODIGOPERSONAL = E.CODIGOPERSONAL
and e.numefectofactor =25970

============
Plan Table
============
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | NESTED LOOPS | | 1 | 39 | 2 | 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID | EFECTOS | 1 | 13 | 1 | 00:00:01 |
| 3 | INDEX UNIQUE SCAN | EFECTOS_PK | 1 | | 0 | |
| 4 | TABLE ACCESS BY INDEX ROWID | PERSONAS | 979 | 25K | 1 | 00:00:01 |
| 5 | INDEX UNIQUE SCAN | PK_PERSONAS| 1 | | 0 | |
--------------------------------------------------------------------------------------
Predicate Information:
----------------------
3 - access("E"."NUMEFECTOFACTOR"=25970)
5 - access("P"."CODIGODEEMPRESA"="E"."CODIGODEEMPRESA" AND "P"."CODIGOPERSONAL"="E"."CODIGOPERSONAL")

Content of other_xml column
===========================
db_version : 10.2.0.3
parse_schema : EXPLOTACION
plan_hash : 3718558659
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EFECTOS"."NUMEFECTOFACTOR"))
INDEX_RS_ASC(@"SEL$1" "P"@"SEL$1" ("PERSONAS"."CODIGODEEMPRESA" "PERSONAS"."CODIGOPERSONAL"))
LEADING(@"SEL$1" "E"@"SEL$1" "P"@"SEL$1")
USE_NL(@"SEL$1" "P"@"SEL$1")
END_OUTLINE_DATA
*/

Optimizer environment:
optimizer_mode_hinted = false
optimizer_features_hinted = 0.0.0
parallel_execution_enabled = true
parallel_query_forced_dop = 0
parallel_dml_forced_dop = 0
parallel_ddl_forced_degree = 0
parallel_ddl_forced_instances = 0
queryrewrite_fudge = 90
optimizer_features_enable = 10.2.0.3
optimizersearch_limit = 5
cpu_count = 4
active_instance_count = 1
parallel_threads_per_cpu = 2
hash_area_size = 131072
bitmap_merge_area_size = 1048576
sort_area_size = 65536
sort_area_retained_size = 0
sortelimination_cost_ratio = 0
optimizerblock_size = 8192
sortmultiblock_read_count = 2
hashmultiblock_io_count = 0
dbfile_optimizer_read_count = 16
optimizermax_permutations = 2000
pga_aggregate_target = 512000 KB
pgamax_size = 204800 KB
queryrewrite_maxdisjunct = 257
smmauto_min_io_size = 56 KB
smmauto_max_io_size = 248 KB
smmmin_size = 512 KB
smmmax_size = 102400 KB
smmpx_max_size = 256000 KB
cputo_io = 0
optimizerundo_cost_change = 10.2.0.3
parallel_query_mode = enabled
parallel_dml_mode = disabled
parallel_ddl_mode = enabled
optimizer_mode = all_rows
sqlstat_enabled = true
optimizerpercent_parallel = 101
alwaysanti_join = choose
alwayssemi_join = choose
optimizermode_force = true
partitionview_enabled = true
alwaysstar_transformation = false
queryrewrite_or_error = false
hashjoin_enabled = true
cursor_sharing = exact
btree_bitmap_plans = true
star_transformation_enabled = false
optimizercost_model = choose
newsort_cost_estimate = true
complexview_merging = true
unnestsubquery = true
eliminatecommon_subexpr = true
predmove_around = true
convertset_to_join = false
pushjoin_predicate = true
pushjoin_union_view = true
fastfull_scan_enabled = true
optimenhance_nnull_detection = true
parallelbroadcast_enabled = true
pxbroadcast_fudge_factor = 100
orderednested_loop = true
noor_expansion = false
optimizer_index_cost_adj = 100
optimizer_index_caching = 0
systemindex_caching = 0
disabledatalayer_sampling = false
query_rewrite_enabled = true
query_rewrite_integrity = enforced
querycost_rewrite = true
queryrewrite_2 = true
queryrewrite_1 = true
queryrewrite_expression = true
queryrewrite_jgmigrate = true
queryrewrite_fpc = true
queryrewrite_drj = true
fullpwise_join_enabled = true
partialpwise_join_enabled = true
leftnested_loops_random = true
improvedrow_length_enabled = true
indexjoin_enabled = true
enabletype_dep_selectivity = true
improvedouterjoin_card = true
optimizeradjust_for_nulls = true
optimizerdegree = 0
usecolumn_stats_for_function = true
subquerypruning_enabled = true
subquerypruning_mv_enabled = false
orexpand_nvl_predicate = true
likewith_bind_as_equality = false
tablescan_cost_plus_one = true
costequality_semi_join = true
defaultnon_equality_sel_check = true
newinitial_join_orders = true
onesidecolstat_for_equijoins = true
optimpeek_user_binds = true
minimalstats_aggregation = true
forcetemptables_for_gsets = false
workarea_size_policy = auto
smmauto_cost_enabled = true
gsanti_semi_join_allowed = true
optimnew_default_join_sel = true
optimizer_dynamic_sampling = 2
prerewrite_push_pred = true
optimizernew_join_card_computation = true
unionrewrite_for_gs = yes_gset_mvs
generalizedpruning_enabled = true
optimadjust_for_part_skews = true
forcedatefold_trunc = false
statistics_level = all
optimizersystem_stats_usage = true
skip_unusable_indexes = true
removeaggr_subquery = true
optimizerpush_down_distinct = 0
dmlmonitoring_enabled = true
optimizerundo_changes = false
predicateelimination_enabled = true
nestedloop_fudge = 100
projectview_columns = true
localcommunication_costing_enabled = true
localcommunication_ratio = 50
queryrewrite_vop_cleanup = true
slavemapping_enabled = true
optimizercost_based_transformation = linear
optimizermjc_enabled = true
rightouter_hash_enable = true
sprpush_pred_refspr = true
optimizercache_stats = false
optimizercbqt_factor = 50
optimizersqu_bottomup = true
ficarea_size = 131072
optimizerskip_scan_enabled = true
optimizercost_filter_pred = false
optimizersortmerge_join_enabled = true
optimizerjoin_sel_sanity_check = true
mmvquery_rewrite_enabled = true
btmmv_query_rewrite_enabled = true
addstale_mv_to_dependency_list = true
distinctview_unnesting = false
optimizerdim_subq_join_sel = true
optimizerdisable_strans_sanity_checks = 0
optimizercompute_index_stats = true
pushjoin_union_view2 = true
optimizerignore_hints = false
optimizerrandom_plan = 0
queryrewrite_setopgrw_enable = true
optimizercorrect_sq_selectivity = true
disablefunction_based_index = false
optimizerjoin_order_control = 3
optimizercartesian_enabled = true
optimizerstarplan_enabled = true
extendedpruning_enabled = true
optimizerpush_pred_cost_based = true
sqlmodel_unfold_forloops = run_time
enabledml_lock_escalation = false
bloomfilter_enabled = true
updatebji_ipdml_enabled = 0
optimizerextended_cursor_sharing = udo
dmmax_shared_pool_pct = 1
optimizercost_hjsmj_multimatch = true
optimizertransitivity_retain = true
pxpwg_enabled = true
optimizer_secure_view_merging = true
optimizerjoin_elimination_enabled = true
flashback_table_rpi = non_fbt
optimizercbqt_no_size_restriction = true
optimizerenhanced_filter_push = true
optimizerfilter_pred_pullup = true
rowsrctrace_level = 0
simpleview_merging = true
optimizerrownum_pred_based_fkr = true
optimizerbetter_inlist_costing = all
optimizerself_induced_cache_cost = false
optimizermin_cache_blocks = 10
optimizeror_expansion = depth
optimizerorder_by_elimination_enabled = true
optimizerouter_to_anti_enabled = true
selfjoinmv_duplicates = true
dimensionskip_null = true
forcerewrite_enable = false
optimizerstar_tran_in_with_clause = true
optimizercomplex_pred_selectivity = true
optimizerconnect_by_cost_based = true
gbyhash_aggregation_enabled = true
globalindexpnum_filter_enabled = true
fixcontrol_key = 0
optimizerskip_scan_guess = false
enablerow_shipping = false
rowshipping_threshold = 80
rowshipping_explain = false
optimizerrownum_bind_default = 10
firstk_rows_dynamic_proration = true
optimizernative_full_outer_join = off
*********************************
Bug Fix Control Environment
***************************
fix 4611850 = enabled
fix 4663804 = enabled
fix 4663698 = enabled
fix 4545833 = enabled
fix 3499674 = disabled
fix 4584065 = enabled
fix 4602374 = enabled
fix 4569940 = enabled
fix 4631959 = enabled
fix 4519340 = enabled
fix 4550003 = enabled
fix 4488689 = enabled
fix 3118776 = enabled
fix 4519016 = enabled
fix 4487253 = enabled
fix 4556762 = 15
fix 4728348 = enabled
fix 4723244 = enabled
fix 4554846 = enabled
fix 4175830 = enabled
fix 4722900 = enabled
fix 5094217 = enabled
fix 4904890 = enabled
fix 4483286 = disabled
fix 4969880 = disabled
fix 4711525 = enabled
fix 4717546 = enabled
fix 4904838 = enabled
fix 5005866 = enabled
fix 4600710 = enabled
fix 5129233 = enabled
fix 5195882 = enabled
fix 5084239 = enabled
fix 4595987 = enabled
fix 4134994 = enabled
fix 5104624 = enabled
fix 4908162 = enabled
fix 5015557 = enabled
Query Block Registry:
*********************
SEL$1 0x7af17688 (PARSER) [FINAL]
Optimizer State Dump: call(in-use=82616, alloc=98936), compile(in-use=79768, alloc=125832)
=====================
PARSING IN CURSOR #2 len=193 dep=0 uid=71 oct=3 lid=71 tim=11120908987319 hv=2836394775 ad='b9a115f8'
SELECT /*comment*/
P.NOMBREPERSONACOMPLETO
FROM
EFECTOS E,
PERSONAS P
WHERE P.CODIGODEEMPRESA = E.CODIGODEEMPRESA
AND P.CODIGOPERSONAL = E.CODIGOPERSONAL
and e.numefectofactor =25970
END OF STMT
PARSE #2:c=40000,e=42209,p=0,cr=7,cu=0,mis=1,r=0,dep=0,og=1,tim=11120908987306
EXEC #2:c=0,e=166,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=11120908987786
WAIT #2: nam='SQL*Net message to client' ela= 6 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=11120908987902
FETCH #2:c=0,e=82,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,tim=11120908988103
WAIT #2: nam='SQL*Net message from client' ela= 5414 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=11120908993759
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='NESTED LOOPS (cr=1 pr=0 pw=0 time=73 us)'
STAT #2 id=2 cnt=0 pid=1 pos=1 obj=72866 op='TABLE ACCESS BY INDEX ROWID EFECTOS (cr=1 pr=0 pw=0 time=66 us)'
STAT #2 id=3 cnt=0 pid=2 pos=1 obj=72868 op='INDEX UNIQUE SCAN EFECTOS_PK (cr=1 pr=0 pw=0 time=53 us)'
STAT #2 id=4 cnt=0 pid=1 pos=2 obj=73042 op='TABLE ACCESS BY INDEX ROWID PERSONAS (cr=0 pr=0 pw=0 time=0 us)'
STAT #2 id=5 cnt=0 pid=4 pos=1 obj=74242 op='INDEX UNIQUE SCAN PK_PERSONAS (cr=0 pr=0 pw=0 time=0 us)'
WAIT #0: nam='SQL*Net message to client' ela= 9 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=11120908994496
WAIT #0: nam='SQL*Net message from client' ela= 2740220 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=11120911734811
=====================

Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 26 2009
Added on Aug 18 2008
6 comments
3,928 views