Identifying Sql Statements that Could use Bind Parameters

This function will identify statements that are the same if they used bind variables:

create or replace function remove_constants( p_query in varchar2 )
return varchar2
as l_query long;

l_char varchar2(1);
l_in_quotes boolean default FALSE;

BEGIN
for i in 1 .. length( p_query )
loop l_char := substr(p_query,i,1);
if ( l_char = '''' and l_in_quotes ) then l_in_quotes := FALSE;
elsif ( l_char = '''' and NOT l_in_quotes ) then l_in_quotes := TRUE;
l_query := l_query '''#';
end if;

if ( NOT l_in_quotes ) then
l_query := l_query l_char;
end if;
end loop;

l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );

for i in 0 .. 8 loop
l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
end loop;
return upper(l_query);
END;

Now following this example can show how the above function can be used:

create global temporary table sql_area_tmp
on commit preserve rows
as
select sql_text,sql_text sql_text_two_constants
from v$sqlarea
where 1=0


This is an sql example that does not use bind parameters and so essentially the same statement is read and parsed repeatedly:

DECLARE

cursor c1 is
select object_name
from dba_objects;

l_object dba_objects.object_name%type;

BEGIN

for c1rec in c1 loop
execute immediate 'insert into pn_temp values('''''c1rec.object_name''''')';
end loop;

END;;

Scripts below will show how this statement can be identified as an Sql statement that should be re-examined for re-writing with bind parameters:

insert into sql_area_tmp(sql_text)
select sql_text
from v$sqlarea

update sql_area_tmp
set sql_text_two_constants = remove_constants(sql_text)

select sql_text_two_constants,count(*)
from sql_area_tmp
group by sql_text_two_constants
order by 2 desc

Sql statemnets that should be re-examined will appear at the top with a high count.

Top N Query

I remember back in the days of Oracle V6, I was asked a question in an interview to write a query in Sql - not using Pl/Sql - to show the top 5 Salaries from the EMP table. Ever since then I have been interested in queries that answer this.

Using the analytic function dense_rank as pointed out by Tom Kyte in this month's Oracle magazine, is the best I have seen so far.

select *
from (
select deptno,ename,sal,dense_rank() over (partition by deptno order by sal desc) Salrnk
from emp)
where SalRnk <=3
order by deptno,sal desc

IN A OWB MAPPING GENERATED JOIN IS CORRUPTED

This is not the sort of thing one would expect to be a bug but sadly it is. It seems to happen with complex joins when you add in new attributes. And the only way round it is to do the join again.

See bug no. 4914839 in Metalink.
Problem ========== Customer has a mapping which has many operators including a join operator. In the output group of join operator output attributes are linked to unrelated items from the input group and hence the final result generated is inserting incorrect values into columns.

03/10/06 07:02 am *** Another note from another developer: ====================================
Back in Bombay, we didn't have the joiner input/output matching properties at all, and no "doPropertyUpgrade" method. So this is probably related to an older joiner metadata corruption bug, not our recent paris bug. I recall that the old bug may be triggered by some combination of adding/removing attributes or groups on the joiner in an older version (pre-Bombay), then upgrading the repository. . At this point, I think You are right that the only way to fix it is to recreate the joiner.

Creating SCD2 and SCD1 Mappings Using OWB

I came across this article on the Oracle Technology site on how to address slowly changing dimensions in OWB, which is all well and good.

Personally however my favourite solution is to use the attribute properties and create a merge statement when target is being populated.
See:

The target table's operator properties should be set to update/insert.
The surrogate key populated from a sequence should have its attribute properties set to:
Load Column When Inserting Row - Yes
Load Column When Updating Row - No
Match Column When Updating Row - No
Update Operation - =

Columns which have been decided to be SCD2 types, should have their attribute properties set to:
Load Column When Inserting Row - Yes
Load Column When Updating Row - No
Match Column When Updating Row - Yes
Update Operation - =
Match Column When Deleting - No

and SCD1 Types should be set to:
Load Column When Inserting Row - Yes
Load Column When Updating Row - Yes
Match Column When Updating Row - No
Update Operation - =
Match Column When Deleting - No

If there are any further updates like setting the flag to identify the most current record etc. the target table should be joined with the source in another part of the same mapping with a join condition which will identify the records to be updated in a similar fashion to below:

join operator property:

src.natural_key = trg.natural_key and
trg.current_flag = 'Y'
(src.scd2type_col1 != trg.scd2type_col1 or
src.scd2type_col2 != trg.scd2type_col2)
......

i.e this will identify all the records that were current but are no longer current in accordance with the latest source.

Finally join the above join results to the target table with operator property set to UPDATE.

OWB Error

I was trying to load a flat file into an external table in Oracle Warehouse Builder and I got a strange error. OWB validated both the flat file and the external table, it generated the script, I deployed it and the table was created ok too. However when I tried to do a select from the external table my sql session was terminated.
There was also no .log or .bad file produced which made the problem investigation even harder.

The flat file had 56 columns and at first I thought there may be some limit to the number of columns. The problem however turned out to be a mispelling with the date format. I had accidentally typed hh24:mis:ss format for a date field.

Bit disappointed that OWB did not catch this out in the validation.

ORA-12514 Error

Couldn't connect to a client database via pl/sql developer today and kept getting ora-12514 error. I couldn't see why though. There was only one tnsnames.ora file in the correct place and all the information was correct. pl/sql developer automatically picked up the service name too, which proved it was reading the tnsnames.ora file from the correct place.

What was confusing me more was that the OWB connected to the database without a problem. Of course OWB does not use TCP/IP but it proved the database was up and running.

Tried using sqlplus from the command prompt and I got the same error again. It turned out the problem was a typo error not in tnsnames.ora but in the sqlnet.ora. So something to remember if I get ora-12514 error again.

Rewrite_Table

If your query is not using the Materialized View, use the /*+ rewrite(mvname) */ hint first to make sure that the query can be redirecte dto use the MV. Usually if the MV is correctly written to correspond to the query but it is still not used, is because the optimizer obtains a lower cost by not using the Materialized views. Think of indexing the MV or increasing its parallel execution if possible:

ALTER MATERIALIZED VIEW mv_name PARALLEL n

If using the rewrite hint shows that the cost of using the MV is lower but the optimizer still chooses not to use the MV, then you can use the dbms_mview.explain_rewrite package to obtain more info:


BEGIN

DBMS_MVIEW.EXPLAIN_REWRITE('Query','mv_name','s_id');

END;

Then
SELECT * FROM rewrite_table where statement_id = 's_id'

and hopefully that will tell you the reason.