Optimizing Dynamic PL/SQL

SQL Optimizer SQL Optimizer for Oracle
1

Hi,

I have a package containing lots of Dynamic pl/sql code. Is it possible to use the SQL Optimizer on a package like mine??

Look at the attachment for sample of the code…

Cheers,

Marc Lafontaine

sample.txt (15.1 KB)

2

Hi Marc,

I just reviewed your PL/SQL, the whole package is composed with distributed queries to different databases; there are two ways to optimize your package.
Let me assume there are only two databases DB1 and DB2 in p_src_dblink for the following SQL and you are firing this SQL in DB0.

If all your databases have similar size of data in each table and you know that every database will generate same query plan,when the following SQL is sent to the targeted database (for example DB1)

SELECT organization_id
FROM client_interventions@’||p_src_dblink||’ a
WHERE client_id = ‘||v_client_id||’
AND NOT EXISTS (SELECT 1
FROM organizations
WHERE organization_id = a.organization_id)
UNION
SELECT a.organization_id
FROM client_interventions@’||p_src_dblink||’ a, client_action_plans@’||p_src_dblink||’ b
WHERE b.client_id = ‘||v_client_id||’
AND a.action_plan_id = b.action_plan_id
AND NOT EXISTS (SELECT 1
FROM organizations
WHERE organization_id = a.organization_id)

Now rewrite this SQL that Quest SQL Optimizer can be accepted in the following:
SELECT organization_id
FROM client_interventions@DB1 a
WHERE client_id = :v_client_id
AND NOT EXISTS (SELECT 1
FROM organizations
WHERE organization_id = a.organization_id)
UNION
SELECT a.organization_id
FROM client_interventions@DB1 a, client_action_plans@DB1 b
WHERE b.client_id = :v_client_id
AND a.action_plan_id = b.action_plan_id
AND NOT EXISTS (SELECT 1
FROM organizations
WHERE organization_id = a.organization_id)

Tune on the DRIVING_SITE in preference in Optimizer Hints.

Paste the rewritten SQL into Quest SQL Optimizer, a prompt up screen will be shown for bind variable v_client_id, please input a valid value for optimization. If you find a SQL alternative with better performance, base on the new syntax and reverse it back to the dynamic SQL string like your original SQL.
Method two, if your databases in DB1 and DB2 have significant data volumn of variation from each other, you might not use one SQL syntax to fit all databases, if your databases are running on Oracle 11g+, you can use our Plan Control optimization feature, what you have to do is to capture your SQL from SQL Area in DB1 and DB2, and use the Plan Control function to control the query plan generation for these distributed queries in your targetted databases, for details, please visit my blog here http://toadworld.com/BLOGS/tabid/67/EntryId/615/How-to-Tune-One-SQL-for-Various-Size-of-Databases.aspx
Thanks,
Richard

3

Thanks Richard.

I’ll give it a shot.

What about the dynamic SQL which is executed by EXECUTE IMIDIATE:

IF v_interface_olis_id IS NOT NULL THEN

         if v_debug then
            DBMS_OUTPUT.put_line ('Insert interface_olis');
 	     end if;

      	 lv_sql :='
         INSERT INTO interface_olis
            SELECT id +'|| v_offset||'
                    ,postal_code
                    ,phone_number
                    ,birth_date
                    ,phone_area_code
                    ,creation_date
                    ,SIN
                    ,first_name
                    ,last_name
                    ,gender
                    ,language_of_preference
                    ,disabled_group_ind
                    ,address_line_1
                    ,address_line_2
                    ,pay_cec
                    ,responsible_hrc
                    ,province_id
                    ,address_line_3
              FROM interface_olis@'||p_src_dblink||'
             WHERE id = :v_interface_olis_id';
  EXECUTE_INS (lv_sql, v_interface_olis_id);

How can I run this through the OPTIMIZER?

To speed this pkg up, I was thinking of checking if data exists before trying to insert it.
That way, we don't run the INSERT command for nothing.

But by checking, I will do an I/O anyway. So is it worth it to check before? If not, is there
any other things I can do?

Any other general things I could improve?

Thanks,

Marc.
4

This INSERT statement is simply copying data from remote database to local database, database optimizer will handle it properly, if you want to try in our SQL optimizer, you can rewrite it like the following:
INSERT INTO interface_olis
SELECT id +’|| :v_offset||’
,postal_code
,phone_number
,birth_date
,phone_area_code
,creation_date
,SIN
,first_name
,last_name
,gender
,language_of_preference
,disabled_group_ind
,address_line_1
,address_line_2
,pay_cec
,responsible_hrc
,province_id
,address_line_3
FROM interface_olis@db1
WHERE id = :v_interface_olis_id

Since this insert statement is not in a loop, you will not get improvement to detect data before insert.

I think there are multiple Cursors with complex SQL in your Package is worth to tune, you can focus on tuning those slowest SQL statements first.

5

Thanks for all the Information…

Marc.

6

Thanks for all the HELP…

Very appreciated !