|
List Info Thread: Re: Views based on function returning table
[1]
|
||||||||||||
|
about | contact Other archives ( Real Estate discussion Medical topics ) |
||||||||||||
Re: Views based on function returning table |
|
List Info Thread: Re: Views based on function returning table
[1]
|
||||||||||||
|
about | contact Other archives ( Real Estate discussion Medical topics ) |
||||||||||||
gmail.com>:
> I am saying this based on my understanding is that
nowadays the SQL and
> PL/SQL engines are co-mingled (for want of a better
word) and there are no
> costly context switches (process + memory space)
anymore when SQL calls
> PL/SQL and PL/SQL passes result sets back.
Context switches do exist. And overhead as well. There are
however
some explicit means to overcome that as well as implicit
conversion
for very limited constructs.
See example. So I have table big with 975145 records in it.
Lets look
at execution time as well as statistics 'session logical
reads'.
SQL> select value from v$statname s, v$mystat m
2 where s.STATISTIC# = m.STATISTIC#
3 and name = 'session logical reads';
VALUE
----------
1049691
So now we'll go through a simple cursor fetching row by row
(slow by slow 
SQL> declare
2 cursor c is select * from big;
3 r c%rowtype;
4 begin
5 open c;
6 loop
7 fetch c into r;
8 if c%notfound then exit; end if;
9 end loop;
10 close c;
11 end;
12 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:18.46
SQL> select value from v$statname s, v$mystat m
2 where s.STATISTIC# = m.STATISTIC#
3 and name = 'session logical reads';
VALUE
----------
2024847
So row by rows takes 18.46 secs and 975156 logical reads.
Now let's look at bulk collect fetching 100 rows each time.
SQL> declare
2 cursor c is select * from big;
3 subtype r is c%rowtype;
4 type arr_t is table of r index by pls_integer;
5 arr arr_t;
6 begin
7 open c;
8 loop
9 FETCH c BULK COLLECT INTO arr LIMIT 100;
10 EXIT WHEN arr.COUNT = 0;
11 end loop;
12 close c;
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.34
SQL> select value from v$statname s, v$mystat m
2 where s.STATISTIC# = m.STATISTIC#
3 and name = 'session logical reads';
VALUE
----------
2050020
Bulk collect with limit 100 rows took 2.34 secs and 25173
logical reads.
Can we improve it with fetching more rows each time? Let's
try with 10K
SQL> declare
2 cursor c is select * from big;
3 subtype r is c%rowtype;
4 type arr_t is table of r index by pls_integer;
5 arr arr_t;
6 begin
7 open c;
8 loop
9 FETCH c BULK COLLECT INTO arr LIMIT 10000;
10 EXIT WHEN arr.COUNT = 0;
11 end loop;
12 close c;
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.32
SQL> select value from v$statname s, v$mystat m
2 where s.STATISTIC# = m.STATISTIC#
3 and name = 'session logical reads';
VALUE
----------
2065704
So now it needs 2.32 secs and 15684 logical reads.
As we can see the improvement was very small if any.
Now for the implicit fetching of more rows each time (cursor
for loop)
SQL> declare
2 cursor c is select * from big;
3 r c%rowtype;
4 begin
5 for r in c loop
6 null;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.54
SQL> select value from v$statname s, v$mystat m
2 where s.STATISTIC# = m.STATISTIC#
3 and name = 'session logical reads';
VALUE
----------
2090877
So cursor for loop needed 02.54 secs and the same 25173
logical reads.
So cursor for loops and only cursor for loops are internally
optimized
to fetch 100 rows each time. But switches between pl/sql and
sql do
cost also in 10g and I'm quite sure in 11g as well (there
are probably
some new internal optimizations, don't know).
Gints Plivna