sql - Oracle query with pipelined - performance issue -
i'd create view based on following query:
create or replace view my_view select a.* my_table a, table(my_package.pipelined_function(a.my_date)) p a.my_field1 = p.my_field1 , ... and view should used way:
select * my_view v v.my_date = trunc(sysdate) as can see, request bit tricky since pipelined_function called fields a ! , request slow, suspect because oracle tries compute p possible dates. of course if fix date (for instance my_package.pipelined_function(trunc(sysdate))), answer immediate.
here execution plan (my_table view: fo.v_trading_pool_inout_prd, , pipelined name pool_inout_elig_best_rank @ bottom):
select statement, goal = all_rows 211 2 16576 191 view fo v_trading_pool_inout_candidate 211 2 16576 191 nested loops 130 1 8290 115 view fo v_trading_pool_inout_prd 63 1 8288 56 sort group 63 1 941 56 nested loops outer 62 1 941 56 nested loops outer 61 1 928 55 nested loops 60 1 894 54 nested loops 59 1 884 53 nested loops 58 1 874 52 nested loops outer 57 1 832 51 nested loops outer 56 1 794 50 nested loops outer 55 1 757 49 nested loops outer 54 1 720 48 nested loops outer 53 1 683 47 nested loops outer 52 1 670 46 hash join 51 1 657 45 index full scan fo trading_pool_group_pk 1 113 678 1 hash join right outer 49 18 11718 44 view fo 16 63 4221 13 hash join 16 63 4536 13 hash join outer 13 63 3843 11 filter hash join outer 9 63 3276 8 hash join 3 81 2025 2 index full scan fo trading_pool_group_pk 1 113 678 1 index range scan fo trading_pool_io_eqt_idx1 1 157 2983 1 table access index rowid fo product_blacklist 6 33 891 6 index range scan fo product_blacklist_idx2 2 33 2 table access full fo trading_pool_group_trans 3 6 54 3 view fo index$_join$_024 3 96 1056 2 hash join index fast full scan fo sys_c0018504 1 96 1056 1 index fast full scan fo trading_pool_def_pk 1 96 1056 1 nested loops outer 33 18 10512 31 nested loops outer 30 18 9882 29 filter hash join right outer 29 18 9666 28 table access index rowid fo product_validation 1 13 988 1 index range scan fo product_validation_idx1 1 13 1 nested loops nested loops 28 18 8298 26 nested loops 25 18 7956 24 table access index rowid fo trading_pool_context 1 1 9 1 index unique scan fo trading_pool_ctxt_pk 1 1 1 view fo 24 18 7794 23 sort group 24 18 4950 23 nested loops outer 23 18 4950 23 table access index rowid fo trading_pool_inout_prd 18 18 3510 18 index range scan fo trading_pool_io_eqt_idx1 1 157 1 table access index rowid fo product_blacklist 1 1 80 1 index range scan fo product_blacklist_idx2 1 1 1 index unique scan fo trading_pool_process_type_pk 1 1 1 table access index rowid fo trading_pool_process_type 1 1 19 1 table access index rowid fo trading_pool_rule_def 1 1 12 1 index unique scan fo trading_pool_rule_def_pk 1 1 1 table access index rowid fo trading_pool 1 1 35 1 index range scan fo trading_pool_idx3 1 1 1 table access index rowid cfmlog intranet_users 1 1 13 1 index unique scan cfmlog pk_intranet_users 1 1 1 table access index rowid cfmlog intranet_users 1 1 13 1 index unique scan cfmlog pk_intranet_users 1 1 1 table access index rowid data product_dictionary 1 1 37 1 index range scan data product_dictionary_pk 1 1 1 table access index rowid data product_dictionary 1 1 37 1 index range scan data product_dictionary_pk 1 1 1 table access index rowid data product_dictionary 1 1 37 1 index range scan data product_dictionary_pk 1 1 1 table access index rowid data markets 1 1 38 1 index unique scan data markets_mkt_id_pk 1 1 1 table access index rowid fo trading_pool_def 1 1 42 1 index unique scan fo trading_pool_def_pk 1 1 1 table access index rowid fo strategy 1 1 10 1 index unique scan fo strat_pk 1 1 1 table access index rowid data currency 1 1 10 1 index unique scan data currency_pk 1 1 1 table access index rowid data referential_dictionary 1 1 34 1 index range scan data referential_dictionary_pk 1 1 1 table access index rowid cfmlog intranet_users 1 1 13 1 index unique scan cfmlog pk_intranet_users 1 1 1 collection iterator pickler fetch pool_rule_common pool_inout_elig_best_rank 67 1 2 59 and real query used:
select * fo.v_trading_pool_inout_candidate c c.pool_date = trunc(sysdate) , c.strategy_id = 2; is there way (refactoring ? hints ?) improve performance of view ? lot !
well first thoughts are: have not shown joins cannot see reasons on why have terrible plan. there seem outer joins - maybe in function don't know....
change implicit joins explicit inner joins. joins in clause deprecated.
a pipelined function may not fastest answer in itself. suspect not though need see in it.
Comments
Post a Comment