Re: Session PGA memory max exceeded 2 GB and crashes.
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: Norman Dunbar <norman.dunbar@xxxxxxxxxxxxxxxxxxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 20 Dec 2006 13:31:29 -0800 (PST)
Norman
See this AskTom thread - the number of executions of the user function depends
on the SQL execution plan:
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6978972926020.
I reproduced (on Oracle XE, using SQL Developer:
======
script
======
select * from v$version;
CREATE OR REPLACE FUNCTION TESTUSER.TEST_FUNCTION
(param IN VARCHAR2)
RETURN NUMBER AS
BEGIN
counter_pkg.increment_counter;
RETURN 190;
END;
/
drop index th_amt_i;
begin counter_pkg.reset_counter; end;
/
select * from trade_history where amount > test_function('X');
begin
counter_pkg.print_counter('FTS access');
counter_pkg.reset_counter;
end;
/
select * from trade_history where amount > (select test_function('X') from
dual);
begin
counter_pkg.print_counter('FTS access with subquery');
counter_pkg.reset_counter;
end;
/
create index th_amt_i on trade_history(amount);
select count(*) from trade_history;
select * from trade_history where amount > test_function('X');
begin
counter_pkg.print_counter('Index range scan');
counter_pkg.reset_counter;
end;
/
=================
script output
=================
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
5 rows selected
FUNCTION TESTUSER.TEST_FUNCTION Compiled.
drop index th_amt_i succeeded.
anonymous block completed
TRADE_DATE AMOUNT TICKER
------------------------- ---------------------- ------
12-SEP-06 199 ORCL
11-SEP-06 198 ORCL
10-SEP-06 197 ORCL
09-SEP-06 196 ORCL
08-SEP-06 195 ORCL
07-SEP-06 194 ORCL
06-SEP-06 193 ORCL
05-SEP-06 192 ORCL
04-SEP-06 191 ORCL
9 rows selected
anonymous block completed
FTS access: execution count = 365
TRADE_DATE AMOUNT TICKER
------------------------- ---------------------- ------
12-SEP-06 199 ORCL
11-SEP-06 198 ORCL
10-SEP-06 197 ORCL
09-SEP-06 196 ORCL
08-SEP-06 195 ORCL
07-SEP-06 194 ORCL
06-SEP-06 193 ORCL
05-SEP-06 192 ORCL
04-SEP-06 191 ORCL
9 rows selected
anonymous block completed
FTS access with subquery: execution count = 1
create index succeeded.
COUNT(*)
----------------------
365
1 rows selected
TRADE_DATE AMOUNT TICKER
------------------------- ---------------------- ------
04-SEP-06 191 ORCL
05-SEP-06 192 ORCL
06-SEP-06 193 ORCL
07-SEP-06 194 ORCL
08-SEP-06 195 ORCL
09-SEP-06 196 ORCL
10-SEP-06 197 ORCL
11-SEP-06 198 ORCL
12-SEP-06 199 ORCL
9 rows selected
anonymous block completed
Index range scan: execution count = 1
-------------
That still doesn't explain how your call stack was blown, of course...
Regards
Nigel
--
//www.freelists.org/webpage/oracle-l
Other related posts:
