Running select.sql with 10046 trace alter session set tracefile_identifier = 'SELECT' I once again ran the same two test SQL scripts, but this time by first setting the tracefile_identifier and enabling the trace. 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. There are a number of methods to start a trace on an Oracle Session. The number of calls when running the select.sql script is 32x that of the assign.sql script. We aren’t looking at timing, just how much work had to be done on the server for each test script.įor that, we just need one line from each file: $ grep 'Event count' perf.rpt.* What’s most interesting at this time is the number of operations performed, expressed as counters in perf. Perf report -stdio -g count -i Īlthough the output is fairly interesting, we won’t be delving into it today. I used the following command to create a nice execution tree of each data file, along with counts for each function called. I ran the same SQL scripts while recording each with perf. It’s this PID that’s used with the record.sh script. This refers to the server PID for the Oracle process started on behalf of the SQLPlus session. In the previous tests you may have noticed that the SPID was reported. These files were renamed from the default perf.data following each test. I performed each test in this way, resulting in two files: Though somewhat crude, this method is sufficient for these tests. Then I switch back to the server, and press CTL-C when the SQLPlus job is done. Switching back to the SQLPlus session, 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. This is a fairly simple manual testing method.Įach of the SQL scripts will pause until I press ENTER. The record.sh script is used to start the recording on the server. We can use perf to count the operations performed by the server. 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. It should be clear that you should never use select from dual when you’re able to use a direct variable assignment. assign.sql SQL# SID SPIDĭirectly assigning via vDate := sysdate 1M times took significantly less time at 0.33 seconds. select.sql SQL# SID SPIDĪssigning sysdate into vDate 1M times took 7.99 seconds. Timingsīefore doing any kind of tracing, I’ll first run the test scripts to get timing information.įirst I’ll run select.sql, then assign.sql.Įach script will make 1M variable assignments. I show all scripts in their entirety at the end of this article. If you’re unfamiliar with perf, this is a good place to start: perf. We’ll set timing via set timing on in SQLPlus. We’ll use two different forms of monitoring for the tests:Īdditionally, we’ll run the scripts without any monitoring, just so we can see the timing data. We’ll use two SQL scripts: select.sql and assign.sql.Įach script will assign sysdate to vDate 1M times in a loop. If you think they’re equivalent, you may want to keep reading. The same assignment can be performed directly: declareĭo you think it makes any difference which method is used? Here a PL/SQL block is using select into to assign a date to a variable: declare into when you could use a direct variable assignment instead. While such usage can be useful at times, it’s a bit of a performance hog. Select sys_context('userenv','sid') into vSID from dual Some examples: select sysdate into vDate from dual However, many years ago, Oracle provided a workaround for using SELECT statements in a somewhat unorthodox way via the DUAL table. This is normal and expected when the data you need is in a table. It isn’t unusual to see PL/SQL that contains select columns into variables from some_table
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |