Database Reference
In-Depth Information
To remove any side effects from the initial hard parsing of queries, we'll run the following script three times, but for
now ignore its output. We'll run the script again in a fresh session so as to see the effects on memory usage in a controlled
environment. We'll use the sort area sizes of 64KB, 1MB, and 1GB in turn, so save this script as
run_query.sql
(this script assumes the username and password are
eoda/foo
, you'll have to modify it for your environment):
connect eoda/foo
set serveroutput off
set echo on
column sid new_val SID
select sid from v$mystat where rownum = 1;
alter session set workarea_size_policy=manual;
alter session set sort_area_size = &1;
prompt run @reset_stat &SID and @watch_stat in another session here!
pause
set termout off
select * from t order by 1, 2, 3, 4;
set termout on
prompt run @watch_stat in another session here!
pause
and then run:
@run_query 65536
@run_query 1048576
@run_query 1073741820
Just ignore the output for now; we are just warming up the shared pool and getting everything “even.”
■
When we process SQL in the database, we must first parse the SQL statement. There are two types of parses.
The first is a hard parse, which is what happens the first time a query is parsed by the database instance and includes
query plan generation and optimization. The second is a soft parse, which can skip many of the steps a hard parse must
do, because it can reuse the result of that work. We hard parsed the previous queries so as to not measure the work
performed by that operation in the following section.
Note
I suggest logging out of that SQL*Plus session and logging back in before continuing, in order to get a consistent
environment, or one in which no work has been done yet. Now, we will want to be able to measure the session
memory of the session running the big
ORDER BY
queries from a second separate session. If we used the same session,
our query to see how much memory we are using for sorting might itself influence the very numbers we are looking at.
To measure the memory from this second session, we'll use a small SQL*Plus script I developed for this. It is actually a
pair of scripts; you'll be told when to run them by the
run_query.sql
script. The one that resets a small table and sets
a SQL*Plus variable to the SID we want to watch is called
reset_stat.sql
:
drop table sess_stats;
create table sess_stats
( name varchar2(64), value number, diff number );
variable sid number
exec :sid := &1
