Select vs Assign
It isn’t unusual to see PL/SQL that contains select columns into variables from some_table
This is normal and expected when the data you need is in a table.
However, many years ago, Oracle provided a workaround for using SELECT statements in a somewhat unorthodox way via the DUAL table.
Some examples:
select sysdate into vDate from dual;
select user into vUser from dual;
select sys_context('userenv','sid') into vSID from dual;
While such usage can be useful at times, it’s a bit of a performance hog.
In general, do not use select .. into when you could use a direct variable assignment instead.
Here a PL/SQL block is using select into to assign a date to a variable:
declare vDate date; begin select sysdate into vDate from dual; end; /
The same assignment can be performed directly:
declare vDate date; begin vDate := sysdate; end; /
Do you think it makes any difference which method is used?
If you think they’re equivalent, you may want to keep reading.
Testing
We’ll use two SQL scripts: select.sql and assign.sql.
Each script will assign sysdate to vDate 1M times in a loop.
We’ll use two different forms of monitoring for the tests:
- perf
- Oracle Trace
Additionally, we’ll run the scripts without any monitoring, just so we can see the timing data.
We’ll set timing via set timing on in SQLPlus.
If you’re unfamiliar with perf, this is a good place to start: perf. I show all scripts in their entirety at the end of this article.
Timings
Before doing any kind of tracing, I’ll first run the test scripts to get timing information.
First I’ll run select.sql, then assign.sql.
Each script will make 1M variable assignments.
select.sql
SQL# @select USERNAME SID SPID -------------------- ---------- ------------------------ JKSTILL 51 13280 1 row selected. Testing speed of 'select into var' Press ENTER when ready Working... PL/SQL procedure successfully completed. Elapsed: 00:00:07.99
Assigning sysdate into vDate 1M times took 7.99 seconds.
assign.sql
SQL# @assign USERNAME SID SPID -------------------- ---------- ------------------------ JKSTILL 51 13280 1 row selected. Elapsed: 00:00:00.02 Testing speed of 'var := something' Press ENTER when ready Working... PL/SQL procedure successfully completed. Elapsed: 00:00:00.33
Directly assigning via vDate := sysdate 1M times took significantly less time at 0.33 seconds.
It should be clear that you should never use select from dual when you’re able to use a direct variable assignment.
Now, let’s dig a little deeper and get a better understanding of why there’s such a large difference between the two methods of assigning a value to a variable.
Testing with Perf
We can use perf to count the operations performed by the server.
The record.sh script is used to start the recording on the server.
Testing Method
This is a fairly simple manual testing method.
Each of the SQL scripts will pause until I press ENTER.
While the SQL script is paused, I switch to the ssh session where I’m logged into the database server as root.
Then I start the perf recording via ./record.sh PID
Switching back to the SQLPlus session, I press ENTER.
Then I switch back to the server, and press CTL-C when the SQLPlus job is done.
Though somewhat crude, this method is sufficient for these tests.
I performed each test in this way, resulting in two files:
- data.assign
- data.select
These files were renamed from the default perf.data following each test.
In the previous tests you may have noticed that the SPID was reported. This refers to the server PID for the Oracle process started on behalf of the SQLPlus session. It’s this PID that’s used with the record.sh script.
./record.sh 13280
I ran the same SQL scripts while recording each with perf.
perf report
Now to create a report from each file.
I used the following command to create a nice execution tree of each data file, along with counts for each function called.
perf report --stdio -g count -i perf.data.select
Although the output is fairly interesting, we won’t be delving into it today. What’s most interesting at this time is the number of operations performed, expressed as counters in perf.
We aren’t looking at timing, just how much work had to be done on the server for each test script.
For that, we just need one line from each file:
$ grep 'Event count' perf.rpt.* perf.rpt.assign:# Event count (approx.): 223223223 perf.rpt.select:# Event count (approx.): 7292292285
Well, that’s interesting. The number of calls when running the select.sql script is 32x that of the assign.sql script.
Testing with Oracle Trace
There are a number of methods to start a trace on an Oracle Session.
Here I’ll be using the old standby, alter session set events '10046 trace name context forever, level 12' simply because I have a script for it, and the name is easy to remember.
I once again ran the same two test SQL scripts, but this time by first setting the tracefile_identifier and enabling the trace.
Running select.sql with 10046 trace
alter session set tracefile_identifier = 'SELECT'; select value from v$diag where name = 'Default Trace File'; @@10046 @@select exit
Running assign.sql with 10046 trace
alter session set tracefile_identifier = 'ASSIGN'; select value from v$diag where name = 'Default Trace File'; @@10046 @@assign exit
Then I copied the tracefiles from the server.
Analysis
We can learn a bit just by checking the sizes of the files:
$ wc cdb1_ora*.trc 3000405 6001742 256119114 cdb1_ora_5689_SELECT.trc 159 850 9088 cdb1_ora_6414_ASSIGN.trc 3000564 6002592 256128202 total
There is a striking disparity in the size of those files. The overhead of using Oracle Trace caused the execution time of select.sql to balloon from eight seconds to 55 seconds.
Here’s a simple profile of each trace file:
$ ./profiler-2.pl cdb1_ora_5689_SELECT.trc Response Time Component Duration Pct # Calls Dur/Call ---------------------------------------- --------- ------ --------- ------------ CPU service 45.13s 80.9% 12 3.761055s SQL*Net message from client 6.12s 11.0% 7 0.874465s unaccounted-for 4.52s 8.1% 1 4.515813s library cache lock 0.00s 0.0% 1 0.000958s library cache pin 0.00s 0.0% 1 0.000514s PGA memory operation 0.00s 0.0% 29 0.000009s SQL*Net message to client 0.00s 0.0% 7 0.000001s ---------------------------------------- --------- ------ --------- ------------ Total response time 55.77s 100.0% $ ./profiler-2.pl cdb1_ora_6414_ASSIGN.trc Response Time Component Duration Pct # Calls Dur/Call ---------------------------------------- --------- ------ --------- ------------ SQL*Net message from client 6.63s 95.5% 7 0.946822s CPU service 0.31s 4.4% 12 0.025454s unaccounted-for 0.01s 0.1% 1 0.009777s PGA memory operation 0.00s 0.0% 2 0.000009s SQL*Net message to client 0.00s 0.0% 7 0.000001s ---------------------------------------- --------- ------ --------- ------------ Total response time 6.94s 100.0%
It would seem the results are a bit skewed by the overhead of the trace, as there are 45 seconds of CPU used (recall that without tracing, the script took eight seconds).
Using standard linux tools, we can get a better idea of why the select.sql takes so much more time than assign.sql.
select.sql trace
awk '{ print $1 }' cdb1_ora_5689_SELECT.trc | sort | uniq -c | sort -n | tail -20
3 select
3 toid
3 value=###
3 value=4294951004
5 =====================
5 END
5 PARSING
7 PARSE
11 kxsbbbfp=7f7826769da0
12 STAT
14 BINDS
14 Bind#0
14 oacdty=02
14 oacflg=00
45 WAIT
58 ***
67
1000013 FETCH
1000016 EXEC
1000021 CLOSE
assign.sql trace
awk '{ print $1 }' cdb1_ora_6414_ASSIGN.trc | sort | uniq -c | sort -n | tail -20
3 BINDS
3 Bind#0
3 Bind#1
3 Dump
3 Dumping
3 END
3 PARSING
3 oacdty=02
3 oacdty=123
3 oacflg=00
3 oacflg=01
3 toid
3 value=###
5 EXEC
5 PARSE
9
9 STAT
10 CLOSE
11 ***
16 WAIT
The last three lines of the report for select.sql tell the story; when assigning variable via select into from dual, Oracle had to create, fetch and close a cursor 1M times.
That overhead can be avoided simply by assigning variables directly, as seen in assign.sql.
Any blog that uses perf for analysis would be incomplete without the requisite flame graphs.
Flame graph for select.sql
What’s of interest in these flame graphs is the amount of work being done once the script enters the plsql_run section.
The select.sql script has quite a bit of code being executed; not only is there a large stack of code being executed, it is very wide, which in a flame graph indicates a lot of work being performed.
Flame graph for assign.sql
Now take a look at the flame graph for assign.sql. There’s much less above the plsql_run section. Not only is the stack shorter, it’s much narrower, and therefore faster.
Conclusion
It’s good to periodically test your assumptions.
You probably wouldn’t notice the difference in singleton events that happen too quickly for a human to perceive the difference in timing. But when scaled up, such as I have done here, the differences are easy to see.
Will using a direct assignment make a noticeable difference in a PL/SQL program that does it only once? Probably not. But what if that PL/SQL program is called frequently?
What if there are several PL/SQL programs doing this? Maybe some of them doing so in a loop?
Not only would the difference in performance be discernable, but this activity would consume extra resources; leaving them unavailable for other processes.
Over time, small changes can add up to significant performance improvements.
The next time you’re writing some PL/SQL, be sure to look at it with a critical eye toward the impact it will have on system performance.
Scripts
Below please find all the scripts I used:
get-curr-ospid.sql
-- get-curr-ospid.sql
--
-- get the server OS Pid for the current session
-- Jared Still jkstill@gmail.com still@pythian.com
col username format a20
select
s.username,
s.sid,
p.spid
from v$session s, v$process p
where s.sid = sys_context('userenv','sid')
and p.addr = s.paddr
order by username, sid
/
select.sql
@get-curr-ospid prompt prompt Testing speed of 'select into var' prompt prompt Press ENTER when ready prompt accept dummy prompt Working... prompt set timing on declare vDate date; begin for i in 1..1e6 loop select sysdate into vDate from dual; end loop; end; /
assign.sql
@get-curr-ospid prompt prompt Testing speed of 'var := something' prompt prompt Press ENTER when ready prompt accept dummy prompt Working... prompt set timing on declare vDate date; begin for i in 1..1e6 loop vDate := sysdate; end loop; end; /
record.sh
#!/usr/bin/env bash
declare PID=$1
: ${PID:?Please supply PID}
[[ "$PID" =~ ^[0-9]+$ ]] || {
echo
echo $PID is not numeric
echo
exit 1
}
echo Recording:
perf record -F 999 -T -g --timestamp-filename -p $PID
10046.sql
-- level 4 is bind values -- level 8 is waits -- level 12 is both -- -- see 10046_off.sql to end tracing alter session set events '10046 trace name context forever, level 12'; --sys.dbms_system.set_ev(sid(n), serial(n), 10046, 8, '');
10046_off.sql
alter session set events '10046 trace name context off' /
You can find all the relevant files on github: select-vs-assign.
Thank you for reading and please feel free to leave any thoughts or questions in the comments.
