Fun with Bind variables

Recently one of my fellow-colleagues was there at a client site where I had visited more than a year ago.

during my period of stay, I had pointed the developers about their lack of bind variables usage and why the plans suddenly change over night for no reason and it stays there in shared pool ..I remember having the same discussion with DBAs too and the DBA folks believed "alter system flush shared pool" is probably statement discovered to fix any SQL running longtime

Interestingly when I was casually talking with my colleague he mentioned that he noted that bind variable peeking had been turned off (note: underscore parameter, thanks to whichever DBA there). I was like "Hmm..someone wanted to have a short-cut way to solve everything without fixing the code"..

and interestingly over the next coupla days we discovered more issues..all sqls are going unoptimized because of bind variable being turned off..Initially when I was told it didnt strike me to relate..but then a quick test and peek into 10053 reveals it so clear..

without bind variable peeking, CBO had no clue about incoming values and so it didnt consider histograms - which is to an extent okay..but the bigger issue was it wont even look into partition statistics (think about it in a 24*7 system where u have so much data flowing in every single day and you have all partitions nicely build to partition the data at various physical segments)..CBO looks at table statistics and decides on plan (no partition pruning or elimination) - the plan is guaranteed to be wrong ..& to get it right you would have ensure global statistics should be perfect reflection of actual data.

you fix a small problem with a short-cut way..opened up another can of worms.. after all, most performance problems are man-made (well developers & dba made)

I mean ..I have to be thankful to all these folks who work so hard on keeping consultants like me in business..otherwise my living would be in a soup

so thanks folks..mess up more all you can.. sometimes it gets really interesting to see how far can you go that lane.:)

consider..but never code

I came across this interesting piece of code while I was actually looking for searching for something else.

Create or replace procedure <>...
begin
 merge into...
 using...
 
 -- more merge statements ..followed by all condition checks

EXCEPTION
   WHEN NO_DATA_FOUND THEN
   NULL;
   WHEN OTHERS THEN
 -- CONSIDER LOGGING THE ERROR AND THEN RE-RAISE
 --      RAISE;
   NULL;
END <>; 

:)

SQL tuning ...

2008 = busy, very busy!

haven't had chance to blog much off late, thanks to the admirable maintenance window at my client place (starts at 12 midnight & goes till 2 or 3 am)

anyways I had bookmarked something interested (abt SQL tuning & Coding) I bumped into youtube longwhile ago and completely forgot abt it. Recently I see the same being referred & forwarded in oracle-list & got reminded.

here they are..enjoy!! (technology helps!! instead of typing pages..its so easy to just embed a video)

Part-1:



Part-2:




Part-3:

..and if you didnt believe DBAs are NOSY

I have been a fan of Morgen's articles. But their latest myth-buster on "oracle installation is tedious and complex" is delightfully funny to watch.

True oracle-ities are real "nosy" creatures, indeed. :)

Can you solve this?

I did the worst thing to myself just before the start of holidays..thats by reading some sql question somewhere on the list and jumping in to solve it. (that was complex enough that it was actually reserved to be asked to the Oracle guru Tom Kyte)

Obviously over the holidays I didnt get too much time..but with whatever little I could work, I havent been able to accomplish much.

Just posting the question for those like-minded souls who get a kick out of solving everything in SQL.

SQL> col id    format 99
SQL> col customer_id  format 99  heading CID
SQL> col service_name  format a12 trunc
SQL> col start_date  format a12
SQL> col end_date  format a12

SQL> desc sql_test
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 ID                                        NOT NULL NUMBER(38)
 CUSTOMER_ID                                        NUMBER(10)
 SERVICE_NAME                                       VARCHAR2(100)
 START_DATE                                         DATE
 END_DATE                                           DATE

SQL> select * from sql_test ;

 ID CID SERVICE_NAME START_DATE   END_DATE
--- --- ------------ ------------ ------------
  1   1 Service-S2   01-AUG-07    05-AUG-07
  2   1 Service-S1   02-AUG-07    01-NOV-07
  3   1 Service-S3   07-AUG-07    30-AUG-07
  4   1 Service-S4   07-AUG-07    30-NOV-07
  5   1 Service-S5   02-DEC-07    20-DEC-07

assume thats a table with customer info..what we need to output is the period during which customer had continued service..(doesnt matter what the service is..but as long as its continous..we need to consider it one row)

so the output should be something like this

CID  START_DATE   END_DATE
---  ------------ ------------
  1  01-AUG-07    30-NOV-07 
  1  02-DEC-07    20-DEC-07

PS: extend your solution to work for many other customers too (the sample is only for one customer_id (identified as 1)

Even though I havent solved this yet (well I did, but only to find if I change the data..it fails :) )

(the only good thing so far is..I never worked with MODEL clause before..now I have extensively researched that to solve this..thats a hint hint)

"I am a Senior DBA..but I am NEW"

I don't know if this is real or meant as joke..but had a good laugh when I read that in Oracle forums.

Here is the thread URL

and here is the scoop

"
Hi everyone,

Can any one advise me? I have joined a company as new senior dba.

i am not understanding what shall be done at beginning?

Can anybody advice me how to check all the database and what to do in the beginning ?

I have been reading all the stuff,documents fr. a week?

"

:) I am looked at my business card and it says I am a "Senior oracle DBA" too. Something I should rethink about :)..

Happy Holidays!!

lobs vs strings

Recently one of the emails I read at a client site (wherein the lead had recommended his team to use dbms_lob.substr in the sqls) probed me do this research and as always learning new stuff in oracle is never ending. :)

SqL>Create table test_clob (c1 clob,c2 number); Table created. SqL>ed Wrote file afiedt.buf 1* Insert into test_clob values (Rpad('Test',50000,'*'),null) SqL>/ 1 row created. SqL>ed Wrote file afiedt.buf 1* Insert into test_clob values (Rpad('Test2',100000,'*'),null) SqL>/ 1 row created. SqL>update test_clob set c2=dbms_lob.getlength(c1); 2 rows updated. SqL>commit; Commit complete. SqL>set long 30 SqL>col c1 format a30 trunc SqL>select c1,c2 from test_clob; C1 C2 ------------------------------ ---------- Test************************** 4000 Test2************************* 4000 SqL>ed Wrote file afiedt.buf 1* select c1,c2,length(c1) from test_clob SqL>/ C1 C2 LENGTH(C1) ------------------------------ ---------- ---------- Test************************** 4000 4000 Test2************************* 4000 4000 SqL>ed Wrote file afiedt.buf 1 declare 2 lv_tmp clob; 3 lv_tmp1 varchar2(4000); 4 begin 5 lv_tmp1:= Rpad('Test3',4000,'*'); 6 for i in 1..10 loop 7 lv_tmp:= lv_tmp||lv_tmp1; 8 end loop; 9 Insert into test_clob(c1) values (lv_tmp); 10 commit; 11* end; 12 / PL/SQL procedure successfully completed. SqL>update test_clob set c2=dbms_lob.getlength(c1) where c2 is null; 1 row updated. SqL>commit; Commit complete. SqL>select c1,c2,length(c1) from test_clob; C1 C2 LENGTH(C1) ------------------------------ ---------- ---------- Test************************** 4000 4000 Test2************************* 4000 4000 Test3************************* 40000 40000

first interesting thing I noticed was I couldnt use rpad function to generate a string more than 4000 chrs..Its strange that oracle silently ignores the request to create longer strings (request was made to make it as long as 100000 chrs long) but it extended only upto 4000.

Well, that takes me to using PLSQL to insert clob data.

now comes the interesting part..lets say we need to fetch only part of the data from clob..what would you use? dbms_lob.substr function or regular substr function?

SqL>ed Wrote file afiedt.buf 1 With v1 as (select &length len,&starting_chr startpos from dual) 2 select 3 length(substr(c1,startpos,len)) substr_len 4 , length(dbms_lob.substr(c1,len,startpos)) substr_lob 5* from test_clob,v1 where c2=40000 SqL>/ Enter value for length: 4000 Enter value for starting_chr: 1 old 1: With v1 as (select &length len,&starting_chr startpos from dual) new 1: With v1 as (select 4000 len,1 startpos from dual) SUBSTR_LEN SUBSTR_LOB ---------- ---------- 4000 4000 SqL>/ Enter value for length: 4001 Enter value for starting_chr: 1 old 1: With v1 as (select &length len,&starting_chr startpos from dual) new 1: With v1 as (select 4001 len,1 startpos from dual) , length(dbms_lob.substr(c1,len,startpos)) substr_lob * ERROR at line 4: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 SqL>ed Wrote file afiedt.buf 1 With v1 as (select &length len,&starting_chr startpos from dual) 2 select 3 length(substr(c1,startpos,len)) substr_len 4 , dbms_lob.getlength(substr(c1,startpos,len)) substr_lob 5 -- , length(dbms_lob.substr(c1,len,startpos)) substr_lob 6* from test_clob,v1 where c2=40000 SqL>/ Enter value for length: 5000 Enter value for starting_chr: 1 old 1: With v1 as (select &length len,&starting_chr startpos from dual) new 1: With v1 as (select 5000 len,1 startpos from dual) SUBSTR_LEN SUBSTR_LOB ---------- ---------- 5000 5000

what does that tell? dbms_lob.substr cannot return more than 4K characters ..whereas substr can..

It all comes back to datatypes and why understanding them is necessary..I checked the manual and its clearly stated substr can take a clob as its input and return "clob" ..whereas dbms_lob if it accepts clob, returns varchar2 (hence limited to 4000 characters).