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.

Materialized View

Here is a query which takes a long time because of the join:

SELECT a13.PT_CODE PT_CODE,
a16.PT_NAME PT_NAME,
a15.SCIENTIFICGROUPCODE SCIENTIFICGROUPCODE,
a15.SCIETNIFICGROUPNAME SCIENTIFICGROUPNAME,
count(distinct a11.SAFETYREPORTKEY) WJXBFS1
from DWH.F_SAFETYREPORT a11
join DWH.F_ADVERSEREACTION a12
on (a11.SAFETYREPORTKEY = a12.SAFETYREPORTKEY)
join DWH.M_LLT_PT_61_CUR1_VW a13
on (a12.REACTIONMEDDRALLT = a13.LLT_CODE)
join DWH.C_INDEX_SCIENTIFIC a14
on (a12.PRODUCTINDEXCODE = a14.PRODUCTINDEXCODE)
join DWH.H_SCIENTIFICPRODUCT a15
on (a14.SCIENTIFICPRESENTATIONID = a15.SCIENTIFICPRESENTATIONID)
join DWH.H_MD61_PSFGY_VW a16
on (a13.PT_CODE = a16.PT_CODE)
where (a11.CASEVALIDFROM <= To_Date('09-06-2006', 'dd-mm-yyyy') and a11.CASEVALIDTO > To_Date('09-06-2006', 'dd-mm-yyyy')
and a11.CLASSIFICATION not in (4)
and a15.SCIENTIFICPRODUCTID in (20923))
group by a13.PT_CODE,
a16.PT_NAME,
a15.SCIENTIFICGROUPCODE,
a15.SCIETNIFICGROUPNAME

To use Materialized View, this is how we should create a Materialized View:

CREATE MATERIALIZED VIEW mv_scigroup_pt
build immediate
refresh on DEMAND
enable query rewrite
AS
select a13.PT_CODE PT_CODE,
a16.PT_NAME PT_NAME,
a15.SCIENTIFICGROUPCODE SCIENTIFICGROUPCODE,
a15.SCIETNIFICGROUPNAME SCIENTIFICGROUPNAME,
a11.casevalidfrom,
a11.casevalidto,
a15.scientificproductid,
a11.safetyreportkey
from DWH.F_SAFETYREPORT a11
join DWH.F_ADVERSEREACTION a12
on (a11.SAFETYREPORTKEY = a12.SAFETYREPORTKEY)
join DWH.M_LLT_PT_61_CUR1_VW a13
on (a12.REACTIONMEDDRALLT = a13.LLT_CODE)
join DWH.C_INDEX_SCIENTIFIC a14
on (a12.PRODUCTINDEXCODE = a14.PRODUCTINDEXCODE)
join DWH.H_SCIENTIFICPRODUCT a15
on (a14.SCIENTIFICPRESENTATIONID = a15.SCIENTIFICPRESENTATIONID)
join DWH.H_MD61_PSFGY_VW a16
on (a13.PT_CODE = a16.PT_CODE)
where a11.CLASSIFICATION not in (4)

In this case the where clause is common to all queries and the other where clauses are removed and the columns used in the select statement. Once the Materialized view is created, if you run an explain plan for the query, it should use the Materialized View instead of the underlying tables.

You can drop the Materialized View if you dont want the plan to use it or you can do this:
ALTER MATERIALIZED VIEW mv_scigroup_pt DISABLE QUERY REWRITE

to analyze the MV, you treat it as a table:

Analyze table mv_scigroup_pt compute statistics;

To put indexes on the Materialized View, again treat it as an index on an ordinary table:
CREATE INDEX mv_srid_idx ON mv_scigroup_pt(safetyreportkey)

and analyze the index in the usual way.

In this example we have used on demand clause to Refresh the Materialized View. In order to refresh it, say for a DWH, use the dbms_mview package at the end of the ETL process.
See:
http://www.lc.leidenuniv.nl/awcourse/oracle/appdev.920/a96612/d_mview2.htm#94135

Defragmenting Tables

This is the best way to defrag a table:

alter table tablename
move tablespace tablespacename

But be careful doing this means the table's indexes will become unstable and so you need to rebuild all the indexes again:

ALTER INDEX index_name REBUILD

Parallel Query Plan

Had a case of developing ETL in a 10g environment but the production environment being a 9i. When we released the ETL into production, there was a performance issue with one of the reports. Comparing the execution plan in 9i and 10g for the query looked like the problem was the parallel query not running in 9i. The init.ora parameters were also different, so it looked very likely that not running in parallel query was the problem.

The init.ora parameters in 9i were changed to mirror the ones in 10g, yet after restarting with the new parameters, the query path still did not indicate a parallel run. So what was the problem?

The problem is that the plan in 9i does not indicate parallel query in the same informative way as in 10g. In fact both databases were running in parallel, and the problem with the query was somewhere else.

To make sure the query runs in parallel rather than relying on the plan, examine the following:

BEFORE RUNNING:
SQL> select * from v$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 0 0
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 0 0
Server Threads 0 0
Allocation Height 0 0
Allocation Width 0 0
Local Msgs Sent 0 0
Distr Msgs Sent 0 0
Local Msgs Recv'd 0 0
Distr Msgs Recv'd 0 0

AFTER RUNNING THE SQL WITH PARALLLEL HINT:
SQL> select * from V$pq_sesstat;

STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 1 1
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 1 1
Server Threads 0 0
Allocation Height 7 0
Allocation Width 2 0
Local Msgs Sent 28 0
Distr Msgs Sent 28 0
Local Msgs Recv'd 63 0
Distr Msgs Recv'd 78 0

11 rows selected.

Using Bulk Collect Exceptions

Example to use Bulk Collect exceptions - new in 9i -

CREATE TABLE t (text VARCHAR2(4))

declare
type words_t is table of varchar2(10);
words words_t := words_t ( 'dog', 'fish', 'cat', 'ball', 'bat', 'spoke', 'pad','elephant' );

bulk_errors exception;
pragma exception_init ( bulk_errors, -24381 );

begin
Forall j in words.first..words.last
save exceptions
insert into t ( text ) values ( words(j) );

exception

when bulk_errors THEN
for j in 1..sql%bulk_exceptions.count
loop
Dbms_Output.Put_Line (
sql%bulk_exceptions(j).error_index || ', ' ||
Sqlerrm(-sql%bulk_exceptions(j).error_code)||words(sql%bulk_exceptions(j).error_index));
end loop;

end;

Regular Expressions to Show the First Occurance of n Upper Case Characters

I must admit, I still can't feel comfortable with regular expressions, but here is a neat one:

SELECT (REGEXP_SUBSTR('AbcDERTK1DEcdhfjUWXvbDERTF' , '[[:upper:]]{5,}'))
FROM dual

It selects the first sequence of characters which has 5 upper cases together.
So the result is: DERTK

or

SELECT (REGEXP_SUBSTR('AbcDEcdhfjUWXvbDERTF' , '[[:upper:]]{3,}'))
FROM dual

is : UWX

How to Automate Max Partition Alert

http://www.dbaoncall.net/references/ht_max_partition_alert.html

One of my ETL scripts once failed with the following error:
ORA-14400: inserted partition key is beyond highest legal partition key

So I decided to write an alert which would check my date partitioned tables and pre-warn me of the ones which I have to add partitions to. The problem was that the information writes the maximum date condition of a partition is in column high_value in user_tab_partitions table. This column is still a long type, and the information is in such a format:
TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIAN')

So the problem was to extract the date portion from a long column and evaluate the maximum date value per partitioned table.

For this I wrote the following function first:
--=====================================================================
--= Function : MaxPartValue
--= By : Potkin Nemat on 21-Jul-2003
--= Parameters :
--= IN : p_table (name o fthe table to evaluate max date partition for.
--= Return : max_date
--= Version :
--= 1.0 wrote the script
--=====================================================================
create or REPLACE FUNCTION MaxPartValue(p_table IN VARCHAR2)
RETURN DATE
IS

TYPE cv_type IS REF CURSOR;
cv cv_type;

p_query VARCHAR2(2000); -- used to retrieve the value of the long
column
p_text VARCHAR2(32000); -- used to store the long value in for
substr operation

CURSOR csub(p_text IN VARCHAR2) IS
SELECT to_date(substr(p_text,11,10),'YYYY-MM-DD') v_date
FROM dual;

max_date DATE;

BEGIN


p_query := 'SELECT high_value FROM user_tab_partitions WHERE
table_name = upper(';
p_query := p_query ||''''|| p_table||''''||')';

max_date := to_date('01-JAN-01'); -- initialise max_date with a date
in the past

OPEN cv FOR p_query;
LOOP
FETCH cv INTO p_text; -- the value of the long column is now
assigned to a varchar2 var
EXIT WHEN CV%NOTFOUND;
FOR csubrec IN csub(p_text) LOOP
IF max_date < csubrec.v_date THEN
max_date := csubrec.v_date; -- record the max partition
date per table
END IF;
END LOOP;
END LOOP;

RETURN max_date;

END MAXpartVALUE;

Once this function is created then it can be used in a simple query to list all the partitioned tables and their maximum date threshold partition:
SELECT p.name,to_char(maxpartvalue(p.name),'dd-MON-yyyy')
FROM user_part_key_columns p
,user_tab_columns t
WHERE p.object_type LIKE 'TABLE%' -- Oracle has spaces after
TABLE in this column!!
AND t.column_name = p.column_name
AND t.table_name = p.NAME
AND t.data_type = 'DATE'
ORDER BY 2 DESC

Counting strings

I posted this:
http://www.oracle.com/technology/oramag/code/tips2005/022105.html


Suppose we want to find out how many times 'aa' is repeated in a string, for example 'aakhgghghjaahhjghghaajghgaa', using SQL only. The SQL statement below can easily give the answer:



SELECT (length('aakhgghghjaahhjghghaajghgaa') -
length(REPLACE('aakhgghghjaahhjghghaajghgaa','aa')))/length('aa')
FROM dual


The code can be easily modified to extract the number of times any pattern is repeated in any string.

Ilya Petrenko, a Sr.DBA at ICT Group Inc., in Newtown, PA. however came up with a neater solution:

http://www.oracle.com/technology/oramag/code/tips2005/051605.html

Using Translate

I had this tip published before on : http://www.dbaoncall.net/references/tt_translate.html

But just in case one day that site is no longer available, I thought I better keep a copy in my own blog too:

If you have a varchar2 field and you want only the numerics, or perhaps some extra escape characters have found their way in, you can strip the field just to numerics by performing a translate such as below:
select translate('abc123'||chr(10)||'F',
'0'||translate('abc123'||chr(10)||'F', 'A1234567890', 'A'), '0')
from ...

This takes all the numbers out of the string and uses the resulting string as the translate so that the result is then only the numbers. Needless to say that one can also clean up the database using:

update tableA
set field = translate(field,'0'||translate(field,'A1234567890','A'),'0')

Avoiding the Table Does not Exist Error when trying to drop it

A quick clever way to avoid Table Does not Exist Error when you have to drop the table in a pl/sql routine. This method saves you searching the data dictionary user_table:

Begin
execute immediate 'Drop table PRODUCT';
Exception
when others then null;
End;

Resizing Temp Tablespace

These are the step by step instructions for resizing a Temp tablespace with AUTOEXTEND ON which has got way too big as a result of a rogue query.
Change filenames and paths accordingly.

The example here is good ebcause the Temp Tablespace is actually made up of two datafiles. I couldnt find any syntax examples for Temp tablespaces with more than one datafile.

## Create another temporary tablespace
CREATE TEMPORARY TABLESPACE TEMPXXX TEMPFILE '/u02/oracle/oradata/EV6PROD/temp03.dbf'
SIZE 1000M REUSE AUTOEXTEND ON NEXT 8K MAXSIZE 2000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

## make it the default temporary tablespace for the database
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPXXX;

## drop the old tablespace and datafile to free space - CANNOT TAKE A TEMPORARY TABLESPACE OFFLINE
##DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; hangs

alter database tempfile '/u01/app/oracle/product/10.1.0/oradata/EV6PROD/temp01.dbf','/u02/oracle/oradata/EV6PROD/temp02.dbf' drop including datafiles;
drop tablespace temp;
-- rm /u02/oradata/EVWHP/temp02.dbf

## recreate the old TEMP tablespace
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oracle/oradata/EV6PROD/temp01.dbf'
SIZE 1000M REUSE AUTOEXTEND ON NEXT 8K MAXSIZE 2048M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

## make it the default temporary tablespace for the database
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

## drop the new, small tablespace and datafile to free space
DROP TABLESPACE TEMPXXX INCLUDING CONTENTS AND DATAFILES

Some good sources for TEMP Tablespace related stuff:

http://www.orafaq.com/node/2
http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml
http://www.oracle.com/technology/oramag/oracle/05-jan/o15dba.html


What can go wrong:

The temp files which were deleted by the command:

alter database tempfile '/u01/app/oracle/product/10.1.0/oradata/EV6PROD/temp01.dbf','/u02/oracle/oradata/EV6PROD/temp02.dbf' drop including datafiles;

will not show when doing a du or ls -l, however df command still shows the space is allocated and not released if a process still thinks its using the file.

lsof unix may show all the processes that still think the file is open but this didnt work for me. At the end I had to bounce the database, so much for trying to do this online :)

The DBMS_UTILITY.GET_HASH_VALUE function

I always forget the name and syntax of this function. So I thought I better make a note of it on my blog.

Parameters

There are three IN parameters to the function

Name :- Text string to be used as the basis of the hashing.

Base :- The lowest number you want for the hash value.

Hash_size:- Size of the hash table.

Returns

A numeric value which is derived from the IN parameter ‘name’.

Dropping an Index to Speed a Query

Had a complex query - generated by Microstrategy - which was taking over 2000 seconds to come back. Two main tables in the query, Table A had 256000 records and table B over 5million records. The execution plan was not using the index on table A as it should. By implementing an index hint the query time was reduced to 44 seconds.
But being a Microstrategy report, this was not an option.

I examined all the underlying tables and indexes in the query, and all were recently analyzed.

At the end, the problem was solved by dropping the index on Table B, which was based on the prinmary key column of Table A. The query returned in 44 seconds once this index was dropped.

Oracle Project Raptor

Downloaded Oracle Project Raptor today on my PC. No problems in download and installation. It seems to be modelled on an older established product, pl/sql Developer, from Allround Automation. What I mean is that the layout is more similar to plsql developer than to Sql Navigator.

But I have to say it seems much slower than plsql Developer in comparison.

How to Alert by email when errors occur in Log file

This is a nice and simple shell script:

pass the alert log filename to the script below:

cat $1 | grep ORA- > alert.err

if [ 'cat alert.err|wc -l' -gt 0 ]
then
mail -s "$0 $1 Errors" potkin@yourcompany.com < alert.err
fi

The title of the message contains the script being executed ($0), the name of the log being searched ($1), and the lines that matched our initial search (ORA-) as the body of the message.

Based on the principles introduced in this script, a larger one can be written and scheduled to execute at periodic intervals that will search the contents of the alert log (or another file of interest) and send an e-mail if any errors exist. Then the contents of the log can be moved to another file, so that only the most recent error messages will be sent via e-mail.

Using Analytical NTILE() Function

I find it hard to remember the syntax of analytical functions, but this one - NTILE()- I came across today on Tom Kyte's site is easy to use and very useful

As Tom puts it, its a "do-it-yourself parallelism". Its as if your table was partitioned and you were only interested in one portion of it.

Lets use user_tables which we are all familiar with. Suppose I want to get a list of the oldest analysed tables.

SELECT table_name
FROM
(SELECT table_name,
last_analyzed,
ntile(6) over
(ORDER BY last_analyzed) nt
FROM user_tables)
WHERE nt=1

Problem with Sqllldr

Had a problem loading data from an ASCII file using sqlldr which is worth noting down.

The dta file contained two description fields which were about 1200 characters long.
Even though I had declared the relevant table columns as Varchar2(4000), the records were rejected saying Max Field Length exceeded.

This was the original CTL file:

LOAD DATA
INFILE 'C:\PN\smq_list.asc' "str X'0a0d'"
BADFILE 'C:\PN\smq_list.bad'
DISCARDFILE 'C:\PN\smq_list.dsc'

INTO TABLE "SMQ_LIST"

FIELDS TERMINATED BY '$'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(SMQ_CODE INTEGER EXTERNAL,
SMQ_NAME CHAR,
SMQ_LEVEL INTEGER EXTERNAL,
SMQ_DESCRIPTION CHAR,
SMQ_SOURCE CHAR,
SMQ_NOTE CHAR,
MEDDRA_VERSION CHAR,
STATUS CHAR,
SMQ_ALGORITHM CHAR
)


I wasted a lot of time thinking there was some espace character in the fields. Especially if I took just a chunk of the fields, the ctl worked.

The problem was solved however by modifying the CTL file:

LOAD DATA
INFILE 'c:\pn\smq_list.asc'
BADFILE 'c:\pn\smq_list.bad'
DISCARDFILE 'c:\pn\smq_list.dsc'

INTO TABLE "SMQ_LIST"

FIELDS TERMINATED BY '$'
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(SMQ_CODE INTEGER EXTERNAL,
SMQ_NAME CHAR,
SMQ_LEVEL INTEGER EXTERNAL,
SMQ_DESCRIPTION CHAR(2000),
SMQ_SOURCE CHAR(2000),
SMQ_NOTE CHAR,
MEDDRA_VERSION CHAR,
STATUS CHAR,
SMQ_ALGORITHM CHAR
)

It looks like there is a default length for CHAR fields, and if we need it to be over that amount, we should specify in the CTL file.

Another dig at 10g Sql Advisory

First time I sat through a 10g demo, I was led to believe that this is it. No more need for Oracle practitioners to tune their sql, the new Sql Advisory feature will do this. Well I have written about a number of cases where the advisory just doesnt have a clue, here is yet another example of when only a clever human being can help.

I have a large update, the outer loop I select is from the cursor below:

CURSOR C1
is
SELECT f.casenumberid,MAX(f.caseversionnumber)
FROM f_safetyreport F
GROUP BY F.CASENUMBERID;


Now for the update I need to do, I a human being can see that I will not need to select the records which have max(f.caseversionnumber) of 1

so a re-write:
CURSOR C1
IS
SELECT f.casenumberid,MAX(f.caseversionnumber)
FROM f_safetyreport F
HAVING MAX(f.caseversionnumber) > 1
GROUP BY F.CASENUMBERID;

but the GROUP BY can slow things down, so only I a human being who understands the application is aware of a table which records the max(caseversionnumber) against a casenumberid, so here is a further rewrite which really spped things up:

CURSOR C1
IS
SELECT h.casenumberid,h.casemaxversion
FROM h_caseversions H
WHERE h.casemaxversion > 1;

There you go, clever human beings are still needed :)

Re-writing an Update for Massive Performance Gain

Had the following update on a table with more than 5 million records, which was taking forever:

DECLARE

CURSOR c1 IS
SELECT DISTINCT substr(rowid,1,13)
FROM f_adversereaction;


TYPE RowIdType IS TABLE OF VARCHAR2(18) INDEX BY BINARY_INTEGER;
p_rowid RowIdType;

TYPE p_idType IS TABLE OF f_adversereaction.adversereactionid%TYPE INDEX BY BINARY_INTEGER;
p_id p_idType;

BEGIN


OPEN c1;
FETCH c1 BULK COLLECT INTO p_rowid;

FORALL indx IN p_rowid.FIRST..p_rowid.LAST
UPDATE f_adversereaction A
SET a.originalsenderid = (
SELECT F.ORiginalsenderid
FROM f_safetyreport F
WHERE a.safetyreportid = f.safetyreportid )
WHERE a.ROWID LIKE p_rowid(indx)||'%';
END;

Rewrote like this, and update was done in minutes:

DECLARE

CURSOR c1 IS
SELECT a.adversereactionid,F.ORiginalsenderid
FROM f_safetyreport F
,f_adversereaction A
WHERE a.safetyreportid = f.safetyreportid;

TYPE p_idType IS TABLE OF f_adversereaction.adversereactionid%TYPE INDEX BY BINARY_INTEGER;
p_id p_idType;

TYPE p_SenderType IS TABLE OF f_adversereaction.originalsenderid%TYPE INDEX BY BINARY_INTEGER;
p_sender p_senderType;

BEGIN

OPEN c1;
FETCH c1 BULK COLLECT INTO p_id,p_sender;

FORALL indx IN p_id.FIRST..p_id.LAST
UPDATE f_adversereaction A
SET a.originalsenderid = p_sender(indx)
WHERE a.adversereactionid = p_id(indx);

COMMIT;

END;

Regular Expressions

Ed Edwards from Florida Department Of Education asked me if we can use the new Oracle 10g Regular Expressions to rewrite this sql he had written to determine ALL the position of ',' in a string.

select distinct text1, 'A "," is located in position ' || instr(text1, ',', 1, level) pos
from (select text1 from test2)
connect by level <= length(text1) - length(replace(text1, ','))
order by text1 desc, pos asc


I must say I really liked his clever way of repeating the rows as many times as the commas using the connectby level <= numberof commas.

Worthy of saving it as a tip.

Faster Update

If you have an update which is taking a long time even when you do it via forall:

- copy the update procedure a few times, depending on how many cpu are available.
- modify each of the above to update a portion of the table.
- run the above copied procedure in parallel.

So if your total update takes 4 hours, and you break the code into 4 each working on a range of the data, then this will reduce the update to 1 hour.

Flashback in 10g

Flashback in 10 g is really easy.

Suppose you drop a table by mistake. This creates a BIN$ table in the recyclebin which you can not select from the BIN$

SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE

To get it back simply type:

SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;


For more info:
http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html

Interstingly:
plsql developer does not show the bin$ tables in the tree list panel but sqlnavigator does.

Dropping a Primary Key

If a primary key is referenced by foreign key from other tables, its no good disabling the foreign keys, you need to drop them before dropping the primary key.

Submitting the Suggested Oracle Profile

DECLARE
l_sql_tune_task_id VARCHAR2(20);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (task_name => 'a16',name => 'a16');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' l_sql_tune_task_id);

END;

Temp Tablespace Unable to Extend

I had a complex query generated by a Microstrategy report. It kept falling over with a "unable to extend Temp Tablespace". I knew its to do with the group by statement as it ran ok without the group by.The Temp tablespace was 4 GB which I tend to think should be enough.

I searched a lot on the internet for solution of this problem. Tried all the clever things using the no_parallel hint, increasing the sort_area_size, the pga_aggregate_target, used the new 10g facility to suggest a new profile which changed the plan but all to no avail in solving the temp tablespace problem.

Finally I went back to the drawing board and re-examined the query generated by microstrategy. I then relaized that two of the tables in the join were not needed at all to get the columns and the aggregate we wanted and that was the solution to the problem.

Conclusion is who said with 10g there is no more need for DBA???

10g Sql Advisory

grant advisor to

wasted a lot of time trying to figure out why dbms_sql.submit kept claiming that table or view does not exist.

It turned out I had created the tables while logged in as my schema but as sysdba using plsql developer!

SQL > CREATE or REPLACE PROCEDURE create_tuning_task IS
2 tuning_task VARCHAR2(30);
3 sqltext CLOB;
4 BEGIN
5 sqltext := 'select cust_name,count(*)'
6 ' from customer, cust_order'
7 ' where customer.cust_no = cust_order.cust_no'
8 ' and customer.cust_no = 8'
9 ' group by cust_name';
10 tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
11 sql_text => sqltext,
12 user_name => 'SYS',
13 scope => 'COMPREHENSIVE',
14 time_limit => 30,
15 task_name => 'CUST_ORDERS',
16 description => 'Tuning effort for counting customer orders');
17 END create_tuning_task;
18 /

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'CUST_ORDERS') FROM DUAL;

Parallel Query

This is a good link:http://www.oracle.com/technology/oramag/oracle/05-may/o35dba.html

when tables are created with degrees > 1 Oracle can assume a parallel execution plan of a large query.

In the plan you can see this by PX

Its a good idea to have the parameter parallel_automatic_tuning set to TRUE.

To disable parallel query use no_parallel hint./* no_parallel(T1) no_paralle(T2) */

Use the table aliases as in the query.