Welcome, Guest
Username: Password: Remember me

TOPIC: top candidates for switching literals to binds

top candidates for switching literals to binds 2 years 2 days ago #357

Hi Cary,

What would be the command for mrskew to process all database requests from a trace by replacing literals with variables (let's say N for any numbers and 'C' for any strings) and have a count on them after this replacement. I am interested in finding the most impact in eliminating hard parsing by switching literal usage into bindings for an OLTP system during a monitored period of time through tracing.

Thank you,
Eugen
The administrator has disabled public write access.

Re:top candidates for switching literals to binds 2 years 1 day ago #358

Eugen,

The root of your question is, "How can I find the SQL statements that will yield the best performance improvement if I switch from using literals to using placeholder variables?"

As you saw on my face in the classroom, this began as a very difficult question in my mind. I didn't know how to answer it. My first impulse, which was to show you how our Method R Profiler aggregates distinct SQL statements that differ only in their literals, was, I think, reasonable, because the Profiler is unique among tools in performing that aggregation for you. Continuing that line of thinking, I mulled over the idea that perhaps you could concatenate all your trace files together and run the Profiler on that, and then you'd be able to see a profile for each of your literal-converted-to-placeholder statements.

...But that would be a very perverted use of the Profiler, and the output you'd get would be a mess. You'd see gazillions of seconds of unaccounted-for time, both positive and negative, because the concatenation of several files would mean that there's no longer a consistent, monitonically ascending clock progressing through the input trace file, which the Method R Profiler requires to make sense of its input. The Profiler would run, though, and there might be some interesting data that would surface as a result, although it would take me several minutes of studying and explaining to begin to understand it all.

Then I discovered the reason that I found the question to be so difficult. It is because your question is backwards from the question I'm accustomed to asking, which is, "What can I do to make a system faster for the business's highest priority user experiences?" Your question is, in its essence, "I have this proposed remedy action; where best can I use it?" My question sometimes leads to the verdict that you should switch from literals to placeholder variables, but your question pre-supposes that switching from literals to placeholder variables is a good idea, and your search is to find the best places where that remedy will make the most difference.

Because I approach problems downward from the performance impact (instead of upward from the proposed remedy), the tools I've helped design are oriented in the downward direction, too (from behavior observable by users downward into the technical data). This is why using the Profiler to answer your question might work, but it would be messy. It's a tool designed to do a job that sounds kind of like what you need, but it's really built to do the reverse of what you're asking.

Now let's explore your question by rephrasing your original question a little bit: "If using literals instead of placeholder variables is a problem, then how would this problem manifest itself in our users' experiences?" The answer is a little more complicated than most people expect.

Here is how a program looks that doesn't use placeholders:
for each value V to process {
    sql = sprintf("... where thing='%s' ...", V);
    c = prepare(sql);
    s = execute(c);
  }
Here is how its call sequence (which you can see in its trace data) might look (I say "might," because it depends on your Oracle Database version and what kind of code you use between your application and the database [e.g., JDBC, Perl DBI, ODBC, etc.]):
loop {
    SQL*Net message from client
    /* perhaps some 'latch: shared pool' calls */
    /* perhaps some 'latch: library cache' calls */
    PARSE 
    SQL*Net message to client
    EXEC
  }
The profile (created either by mrskew or the Method R Profiler) resulting from this will show lots of PARSE calls and certainly there's CPU time consumed by each one.

How will changing your code to use placeholders affect the profile? Well, imagine that you rewrite your code to look like this:
for each value V to process {
    sql = "... where thing=:t ...";  /* use a placeholder here */
    c = prepare(sql);
    s = execute(c, V);               /* and bind its value here */
  }
If this is how you rewrite your application, then you're going to earn only a minuscule performance improvement. The only calls you'll potentially eliminate are the 'latch: shared pool' calls. You'll still have the same number of PARSE, EXEC, and FETCH calls, and still the same number of network I/O calls, and probably exactly the same number of 'latch: library cache' calls, too.
loop {
    SQL*Net message from client
    /* perhaps some 'latch: library cache' calls */
    PARSE 
    SQL*Net message to client
    EXEC
  }
The improvement you really want will come from rewriting the code to make the prepare() call a loop invariant, like this:
sql = "... where thing=:t ...";    /* still uses a placeholder */
  c = prepare(sql);                  /* now outside the loop */
  for each value V to process {
    s = execute(c, V);               /* and bind its value here */
  }
Now your call sequence will look like this:
SQL*Net message from client
  /* perhaps some 'latch: shared pool' calls */
  /* perhaps some 'latch: library cache' calls */
  PARSE 
  loop {
    SQL*Net message from client
    SQL*Net message to client
    EXEC
  }
This is a huge improvement over the previous versions, because the latch activity and the PARSE call will be executed only once. As a result, your process will run more quickly, and the whole system will scale better because processes using this code won't need so many serialized latch acquisitions or so much CPU.

So, then... How to initiate the hunt for where opportunities like this are hiding? I would begin with the standard Method R way: trace user experiences and look at their profiles. When you see the pattern of lots of PARSE calls for a single experience, or—heaven forbid—time spent awaiting acquisitions of parse-related latches, then go into the source code with the knife.

Once you have some trace files (and from talking with you, I got the idea that you already have some), I would experiment with mrskew, too. For example:
mrskew --group='$sqlid' --name=PARSE
This will determine which SQLIDs are associated with the most PARSE call time. It's a broad first step in your hunt for PARSE time. Next:
mrskew --group='substr($sql,0,100)' --name=PARSE
This query is interesting because it'll group by the first 100 characters of your SQL statements. Search for the substring length ('100' is only a first guess) that will group statements so that the literal values fall beyond the substring you're grouping by. This will give you an estimation of the SQL sharing opportunity that the Method R Profiler could quantify for you exactly. Work with the --name argument to seek for latch activity, too:
mrskew --group='substr($sql,0,100)' --name=latch
If you find lots of parse latch calls for a given statement, then you'll have found some very valuable source code to go fix.

When you go to fix the code, remember: don't just "use bind variables." Using placeholders instead of literals is a means to the end of eliminating Oracle PARSE calls, not an end in and of itself. Pulling prepare() calls out of their loops is where the real performance and efficiency benefits come from. Oracle's "bind variables" are merely a tool you'll need for doing that.
Last Edit: 2 years 1 day ago by Cary Millsap.
The administrator has disabled public write access.

Re:top candidates for switching literals to binds 2 years 21 hours ago #359

Hi Cary,

Thank you for all these details and this quick answer. I will try mrskew on this subject and not only.

Have a nice weekend,
Eugen
Last Edit: 2 years 21 hours ago by Eugen IACOB. Reason: misstype
The administrator has disabled public write access.