Home » RDBMS Server » Performance Tuning » dynamic sql (oracle 11.2.0.3)
dynamic sql [message #646801] Sat, 09 January 2016 12:53 Go to next message
pointers
Messages: 439
Registered: May 2008
Senior Member
Hi,

I don't have any code to share with you right now.
But the idea of this post to get some pointers to start with.

We have a procedure (update_proc) written using dynamic sql (dbms_sql) which basically builds up a query (say update) and runs it.
It accepts input parameters as "where clause column names and values". These input paramets are used in building up the query.
The procedure then run for almost 20 differnt set of parameters thus 20 times.
Each run takes a while and the whole procedure for 20 different set of inputs run for almost 8 hours.
I strongly believe that as it is dynamic sql for different parameters and each query is hard parsed all the times.

Can you please suggest how to tune such queries.

Regards,
Pointers
Re: dynamic sql [message #646802 is a reply to message #646801] Sat, 09 January 2016 12:59 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/

The "overhead" of the hard parses is totally insignificant when measured against total of 8+ hours elapsed time.
Re: dynamic sql [message #646805 is a reply to message #646801] Sat, 09 January 2016 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Can you please suggest how to tune such queries.


Sure, read again the same links than usual.

Re: dynamic sql [message #646818 is a reply to message #646801] Sun, 10 January 2016 09:08 Go to previous messageGo to next message
FDAVIDOV
Messages: 20
Registered: December 2014
Junior Member
I might had misunderstood your question/wording, but ...

During those 8 hours of running, how many times are the 20 dynamic queries being parse? Or, better, are they parsed more than once each?

If only once, I'd re-check the queries themselves (e.g. construct and run them manually). Most likely, you will find that what keeps busy the DB is not the parsing but the actual queries.

Also (and I'm not saying this is the case as you haven't provided any example) it is quite often that people use dynamic queries without a real need.



Re: dynamic sql [message #647850 is a reply to message #646818] Wed, 10 February 2016 01:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
I am guessing a lot here, but below is (assuming my guessing is right) a demonstration of variations on theme.

1. you run 3 different queries on the same data but with different parameters.
each "condition set" requires its own table access.


select * from insurance_claims where category = 'XYZ';
select * from insurnace_claims where total_paid > 100000;
select * from insurance_claims where loss_date between to_date('2015','rrrr') and to_date('2015','rrrr')-1;


2. use a bitmap to house the condition sets each row satisfies and operate on it accordingly.
(this method limited to maximum of 128 conditions?)
this method allows for the table to be scanned only once instead of once for each condition set.
depending upon the nature of your process, you may not even need the bitmap, just a process rewrite into a smarter process.

select
            case when a.category = 'XYZ' then power(2,0) else 0 end
          + case when a.total_paid > 1000 then power(2,1) else 0 end
          + case when a.loss_date between to_date('2015','rrrr') and to_date('2015','rrrr')-1 the power(2,2) else 0 end
       query_key
     ,a.*
from insurance_claims a
where (
             a.category = 'XYZ'
         or  a.total_paid > 100000
         or  a.loss_date between to_date('2015','rrrr') and to_date('2015','rrrr')-1
      )
/


To determine if a row is part of some condition set, use bitand to check. This example shows a simple set of tests to see if the #2 set of conditions is found in the different combinations. Read up on BITAND if you are confused.

select bitand(1,power(2,1)) from dual;
select bitand(2,power(2,1)) from dual;
select bitand(3,power(2,1)) from dual;
select bitand(4,power(2,1)) from dual;

01:48:47 SQL> select bitand(1,power(2,1)) from dual;

BITAND(1,POWER(2,1))
--------------------
                   0

1 row selected.

Elapsed: 00:00:00.01
01:51:23 SQL> select bitand(2,power(2,1)) from dual;

BITAND(2,POWER(2,1))
--------------------
                   2

1 row selected.

Elapsed: 00:00:00.00
01:51:23 SQL> select bitand(3,power(2,1)) from dual;

BITAND(3,POWER(2,1))
--------------------
                   2

1 row selected.

Elapsed: 00:00:00.02
01:51:23 SQL> select bitand(4,power(2,1)) from dual;

BITAND(4,POWER(2,1))
--------------------
                   0

1 row selected.

Elapsed: 00:00:00.02


OK so I have assumed a lot here. But my intent is to suggest that your time may be due to 20 scans of a table which if you were willing to rewrite, might be doable as one scan. You do not tell us what you do with the data, and if it is possible to get the list of 20 "condition sets" before you any work.

Or I could be way off the mark. But then that would be because I have no real concrete idea of what results you expect and what you are doing with them.

Good luck. Kevin
Re: dynamic sql [message #649318 is a reply to message #647850] Tue, 22 March 2016 09:24 Go to previous messageGo to next message
michael_bialik
Messages: 619
Registered: July 2006
Senior Member
Can you post TKPROF?
Re: dynamic sql [message #649319 is a reply to message #649318] Tue, 22 March 2016 09:31 Go to previous message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
OP has long history of starting threads and then quietly abandoning them without ANY follow up response or closure.
Previous Topic: improve sql performance (long sql here)
Next Topic: Clustering Factor for LOB Index_Type is NULL
Goto Forum:
  


Current Time: Wed Jan 17 23:12:01 CST 2018

Total time taken to generate the page: 0.02018 seconds