Hi --
Following is the SQL statement which is causing trouble in the program
SELECT fnd_number.number_to_canonical(/*+ RULE*/
nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) *
FEED.scale),0) ) FROM /* Person */
pay_run_result_values TARGET
,pay_balance_feeds_f FEED
,pay_run_results RR
,pay_assignment_actions ASSACT
,pay_assignment_actions BAL_ASSACT
,pay_payroll_actions PACT
,pay_payroll_actions BACT
,per_assignments_f ASS
where BAL_ASSACT.assignment_action_id = 156422 -- bind variable
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and FEED.balance_type_id = 95 + decode(TARGET.INPUT_VALUE_ID,
null, 0, 0) -- 95 is a bind variable
and FEED.input_value_id = TARGET.input_value_id
and nvl(TARGET.result_value,'0') != '0'
and TARGET.run_result_id = RR.run_result_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and ASSACT.payroll_action_id = PACT.payroll_action_id
and PACT.effective_date between FEED.effective_start_date and
FEED.effective_end_date
and RR.status in ('P','PA')
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and ASSACT.assignment_id = ASS.assignment_id
/* This may break under the cost-based optimizer, since the result
depends on the correct plan! */
and ASS.person_id = (select person_id
from per_assignments_f START_ASS
where START_ASS.assignment_id =
BAL_ASSACT.assignment_id
and rownum = 1)
and PACT.effective_date between ASS.effective_start_date and
ASS.effective_end_date
/* Period To Date */
and PACT.effective_date >= ( select start_date
from per_time_periods PTP
where BACT.time_period_id = PTP.time_period_id)
--
--
--
I substituted values for tow bind variables and ran the SQL under
SQL*PLUS by turning on autotrace, and following are the
results..........
--
*
*
--
FND_NUMBER.NUMBER_TO_CANONICAL(/*+RULE*/NVL(SUM(FND_NUMBER.CANONICAL_TO_NUMBER(T
--------------------------------------------------------------------------------
2296.22
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=24 Card=1 Bytes=146)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 NESTED LOOPS (Cost=24 Card=1 Bytes=146)
4 3 NESTED LOOPS (Cost=22 Card=1 Bytes=118)
5 4 NESTED LOOPS (Cost=11 Card=11 Bytes=1155)
6 5 NESTED LOOPS (Cost=9 Card=1 Bytes=92)
7 6 NESTED LOOPS (Cost=7 Card=1 Bytes=80)
8 7 NESTED LOOPS (Cost=5 Card=1 Bytes=60)
9 8 NESTED LOOPS (Cost=3 Card=1 Bytes=29)
10 9 TABLE ACCESS (BY INDEX ROWID) OF
'PAY_ASSIGNMENT_ACTIONS' (Cost=2 Card=1 Bytes=20)
11 10 INDEX (UNIQUE SCAN) OF
'PAY_ASSIGNMENT_ACTIONS_PK' (UNIQUE) (Cost=1 Card=1)
12 9 TABLE ACCESS (BY INDEX ROWID) OF
'PAY_PAYROLL_ACTIONS' (Cost=1 Card=91281 Bytes=821529)
13 12 INDEX (UNIQUE SCAN) OF
'PAY_PAYROLL_ACTIONS_PK' (UNIQUE)
14 8 TABLE ACCESS (BY INDEX ROWID) OF
'PER_ALL_ASSIGNMENTS_F' (Cost=2 Card=186 Bytes=5766)
15 14 INDEX (RANGE SCAN) OF
'PER_ASSIGNMENTS_F_N12' (NON-UNIQUE) (Cost=1 Card=186)
16 15 COUNT (STOPKEY)
17 16 TABLE ACCESS (BY INDEX ROWID) OF
'PER_ALL_ASSIGNMENTS_F' (Cost=3 Card=1 Bytes=15)
18 17 INDEX (RANGE SCAN) OF
'PER_ASSIGNMENTS_F_PK' (UNIQUE) (Cost=2 Card=1)
19 7 TABLE ACCESS (BY INDEX ROWID) OF
'PAY_ASSIGNMENT_ACTIONS' (Cost=2 Card=91700 Bytes=1834000)
20 19 INDEX (RANGE SCAN) OF
'PAY_ASSIGNMENT_ACTIONS_N1' (NON-UNIQUE) (Cost=1 Card=91700)
21 6 TABLE ACCESS (BY INDEX ROWID) OF
'PAY_RUN_RESULTS' (Cost=2 Card=80149 Bytes=961788)
22 21 INDEX (RANGE SCAN) OF
'PAY_RUN_RESULTS_N50'(NON-UNIQUE) (Cost=1 Card=80149)
23 5 INDEX (RANGE SCAN) OF
'PAY_RUN_RESULT_VALUES_N99' (NON-UNIQUE) (Cost=2 Card=1057677
Bytes=13749801)
24 4 TABLE ACCESS (BY INDEX ROWID) OF
'PAY_PAYROLL_ACTIONS' (Cost=1 Card=91281 Bytes=1186653)
25 24 INDEX (UNIQUE SCAN) OF
'PAY_PAYROLL_ACTIONS_PK'(UNIQUE)
26 3 TABLE ACCESS (BY INDEX ROWID) OF
'PAY_BALANCE_FEEDS_F' (Cost=2 Card=20300 Bytes=568400)
27 26 INDEX (RANGE SCAN) OF 'PAY_BALANCE_FEEDS_F_N50'
(NON-UNIQUE) (Cost=1 Card=20300)
28 2 TABLE ACCESS (BY INDEX ROWID) OF 'PER_TIME_PERIODS'
(Cost=2 Card=1 Bytes=12)
29 28 INDEX (UNIQUE SCAN) OF 'PER_TIME_PERIODS_PK'
(UNIQUE) (Cost=1 Card=2)
--
--
Statistics
----------------------------------------------------------
46 recursive calls
0 db block gets
1181 consistent gets
22 physical reads
0 redo size
341 bytes sent via SQL*Net to client
417 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
-********************************************************************************
I also have the execution plan from TKPROF output, and it looks like
the plan from TKPROF is little bit different than the plan in
AUTOTRACE
following the execution plan from TKPROF .........
call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 13910 139.38 1517.04 0 0 0
0
Execute 13910 7.43 8.87 0 0 0
0
Fetch 13910 254.43 315.83 1126 35943544 0
13910
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 41730 401.24 1841.74 1126 35943544 0
13910
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 183 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
0 FILTER
1 NESTED LOOPS
216 NESTED LOOPS
106 NESTED LOOPS
106 NESTED LOOPS
10 NESTED LOOPS
2 NESTED LOOPS
2 NESTED LOOPS
2 TABLE ACCESS BY INDEX ROWID PAY_ASSIGNMENT_ACTIONS
2 INDEX UNIQUE SCAN (object id 42799)
2 TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_ACTIONS
2 INDEX UNIQUE SCAN (object id 42886)
2 TABLE ACCESS BY INDEX ROWID PER_ALL_ASSIGNMENTS_F
2 INDEX RANGE SCAN (object id 44006)
10 TABLE ACCESS BY INDEX ROWID PAY_ASSIGNMENT_ACTIONS
20 INDEX RANGE SCAN (object id 42785)
114 TABLE ACCESS BY INDEX ROWID PAY_RUN_RESULTS
114 INDEX RANGE SCAN (object id 43276)
210 TABLE ACCESS BY INDEX ROWID PAY_PAYROLL_ACTIONS
210 INDEX UNIQUE SCAN (object id 42886)
320 TABLE ACCESS BY INDEX ROWID PAY_RUN_RESULT_VALUES
896 INDEX RANGE SCAN (object id 43289)
215 TABLE ACCESS BY INDEX ROWID PAY_BALANCE_FEEDS_F
215 INDEX RANGE SCAN (object id 42933)
0 TABLE ACCESS BY INDEX ROWID PER_TIME_PERIODS
0 INDEX UNIQUE SCAN (object id 43989)
2 COUNT STOPKEY
1 TABLE ACCESS BY INDEX ROWID PER_ALL_ASSIGNMENTS_F
1 INDEX RANGE SCAN (object id 44034)
---------------------------------------------------------------------
Thanks for your time and help.
Anil
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<1018103981.5662.2.nnrp-10.9e984b29_at_news.demon.co.uk>...
> Are you aware that any SQL statement that
> executes inside a PL/SQL block is counted
> as a recursive SQL statement ?
>
> --
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar - UK, April 3rd - 5th
> http://www.jlcomp.demon.co.uk/seminar.html
>
> Host to The Co-Operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> Author of:
> Practical Oracle 8i: Building Efficient Databases
>
>
> anil chada wrote in message
> <36503db6.0204031125.769d8588_at_posting.google.com>...
>
> >if oracle is spending most of the time parsing recursive calls, then
> >can anyone provide me with some ideas about,
> >how to isolate the problem and fix it (apart from increasing share
> >pool size)?
> >
> >
> >Any input is greatly appreciated.
> >
> >Thanks
> >Anil
Received on Mon Apr 08 2002 - 10:59:41 CDT
