Thursday, December 27, 2007

"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!!

Wednesday, December 26, 2007

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).



Wednesday, November 28, 2007

Interesting SQL problem

Came across this interesting sql question on oracle-l list while I was searching for something else.

The poster's original question is as below..


create table t(id number);
insert into t values(1);
insert into t values(2);
commit;

I want to query this with an Id set. All values in the set should be
there to return me any row.
e.g.
select * from t where id in (1,2); return 1 and 2

If am serching for 1,2,3 if any one value is missing I should not get any data.
e.g.
select * from t where id in (1,2,3) should not return any row.
How to rewrite the above query with (1,2,3) that should not return me any row.



Here is my solution for that...


ccD>l
1 With inputstr as
2 ( select '&inp' elements from dual),
3 setdata
4 as
5 (
6 select
7 trim( substr (txt,
8 instr (txt, ',', 1, level ) + 1,
9 instr (txt, ',', 1, level+1)
10 - instr (txt, ',', 1, level) -1 ) )
11 as token
12 from (select ','||elements||',' txt
13 from inputstr ) t,inputstr i
14 connect by level <=
15 length(i.elements)-length(replace(i.elements,',',''))+1
16 )
17 select token from
18 (
19 select
20 to_number(token) token,
21 nvl2(t.id,1,0) present,
22 min(nvl2(t.id,1,0)) over() min_over_report
23 from setdata s, t
24 where s.token=t.id(+)
25 )
26* where min_over_report=1



Testing
--------


ccD>select * from t; -- this is what the table contains
ID
----------
1
2
ccD> get the above sql into buffer
ccD>/
Enter value for inp: 1,2 -- input is 1,2 and it retrives two rows
TOKEN
----------
1
2
ccD>/
Enter value for inp: 1,2,3 -- 1,2,3 retrives no rows because 3 is not present
no rows selected
ccD>/
Enter value for inp: 1,2,3,4 -- same with 1,2,3,4
no rows selected
ccD>/
Enter value for inp: 1 -- input 1 retrives one row
TOKEN
----------
1



Monday, November 26, 2007

Software what?

Just saw this picture online and was thinking to myself..

well this day is not very far away and approaching us very soon. we are going to see "wanted software pros" like our current "open house" signs.

but I am curious how to interpret...
too much demand for software professionals?
or
would that be too much supply in market , that employing headhunters to recruit would be considered "not-worth-the-cost".

Take a look at this picture..




Saturday, November 3, 2007

ASM instance & remote client

well we know ASM instance is always in mount state and so you really dont have much access to data dictionary..and listener displays BLOCKED as status leaving you no choice but to login into the OS server and connect to asm instance locally..

But I just learned something new..that by adding "UR=A" to tnsnames, you can actually connect to asm instance (blocked service) via oracle net connection. (remote connection)

thats really cool thing for me..as I dont have to login to a server box just to check asm instance..I can simply have one more sql window open to asm instance from my client..

Add "(UR=A)" to the connect_data section of your tnsnames entry and you should be all set.

(Note: ASM instance lets admin authentication only via password file or OS authentication..so obviously, you have to have password file created before trying to connect to ASM from remote client)


Saturday, October 6, 2007

End of in-house DBAs?

just happened to read this interesting blog in searchoracle.com.
The article discusses about the latest evolution of "Oracle on demand" service and its potential impact (or end) of in-house DBAs

"....members revealed a surprisingly high 37% of you currently use hosted apps.

Does that concern you DBAs? Is this the beginning of the end of the in-house DBA?

For managers, Oracle’s pitch is compelling:

With more than 1.7 million users, including enterprise customers with the most rigorous requirements, Oracle On Demand simplifies enterprise computing by reducing the need to handle software upgrades, patches, and the day-to-day maintenance required to keep customer solutions available and secure.

. . . not to mention a lower TCO, including no six-figure salaries to those pesky senior DBAs. It’s the “best of all worlds” as the Oracle site melodramatically puts it.

"

and finally it posts the question

"Do you think that Oracle DBAs’ days are numbered because of the growth of On Demand?"

that was really interesting to think about. I don't personally foresee something like this to become a successful strategy in at least the next 4-5 years, unless Oracle changes its staff/team and strategy. Forget about data being hosted, currently ask anyone who has to deal with metalink folks.. It sometimes gives such a bad taste, you even wonder how the heck these guys managed to find a job in Oracle..

On the other hand, having a alternative is good for the company, as I have seen many DBAs who are not technically competent but simply want to enforce whatever be their principles. So this alternatives would eventually make them realize they are not the super-bosses anymore to say& act the way they wanted. CEO/CTO now has an option to bypass such egoistic persons in their companies.



Saturday, September 29, 2007

11g 's DBMS_SPM

11g introduces more admin-friendly features to maintain/control query plan stability.

I was going through this new package introduced in 11g for SQL PLAN Management(SPM) called DBMS_SPM.and to be honest, I actually got distracted while I was reading abt this.. Instead of knowing how to actually use this the way its intended to be used (many plans for a sql and flip flopping which one should the DBA wants to be used)..I thought something else and found it useful in a different way..


If you been to environments where developers just go nuts when
- they see the plan in production was completely different than what they expected
- they see a SQL taking forever because the plan changed in Production for no reason (most likely after stats gatheration or shutdown)

and there have been countless situations a DBA/Consultant would walk-in like an emergency doctor and take a look at all those "v$sql" patients and finally conclude "You are suffering from bind peeking problem". (which roughly means the plan in the shared pool was optimized for the first input value and unfit/inefficient for subsequent calls with varying input values)




Setup:
------

Sql>ed
Wrote file afiedt.buf

1 Create table test_sql_plan
2 as select 100 id, rpad(rownum,10,'x') name
3* from dual connect by level<999>/

Table created.

Sql>ed
Wrote file afiedt.buf

1 insert into test_sql_plan
2 select rownum id, rpad(rownum,10,'x') name
3* from dual connect by level<11>/

10 rows created.

Sql>commit;

Commit complete.

Sql>Create index test_sp_idx on test_sql_plan(id);

Index created.

Sql>begin
2 dbms_stats.gather_table_stats(tabname=>'TEST_SQL_PLAN',
3 ownname=>'VENKAT',
4 method_opt=>'for all indexed columns size 254');
5 end;
6 /

PL/SQL procedure successfully completed.




Testing begins from here...

Session 1:
----------
Sql>exec :v := 2;

PL/SQL procedure successfully completed.

Sql>select * from test_sql_plan plan_with_2_first where id=:v;

ID NAME
---------- ----------
2 2xxxxxxxxx


-- session 2
-- ----------
sq2>ed
Wrote file afiedt.buf

1 select sql_text sqltext,sql_id,plan_hash_value,executions from v$sql
2 where sql_text like
3 'select * from test_sql_plan plan_with_2_first%where id=%'
4* and executions>0
sq2>/

SQLTEXT SQL_ID PLAN_HASH_VALUE EXECUTIONS
--------------------------------------------- ------------- --------------- ----------
select * from test_sql_plan plan_with_2_first 69yujf79wwkja 3623521558 1
where id=:v

sq2>-- go back to session 1 and rerun the same sql for different bind value

Sql>exec :v := 100;

PL/SQL procedure successfully completed.

Sql>select * from test_sql_plan plan_with_2_first where id=:v;
...
...
...
100 991xxxxxxx
100 992xxxxxxx
100 993xxxxxxx
100 994xxxxxxx
100 995xxxxxxx
100 996xxxxxxx
100 997xxxxxxx
100 998xxxxxxx

998 rows selected.

Sql>

-- session 2
-- ----------
sq2>/

SQLTEXT SQL_ID PLAN_HASH_VALUE EXECUTIONS
--------------------------------------------- ------------- --------------- ----------
select * from test_sql_plan plan_with_2_first 69yujf79wwkja 3623521558 2
where id=:v

sq2>ed
Wrote file afiedt.buf

1 select OPERATION,OBJECT_OWNER,OBJECT_NAME from v$sql_plan
2 where PLAN_HASH_VALUE=3623521558
3 and sql_id='69yujf79wwkja'
4* order by id
sq2>/

OPERATION OBJECT_OWNER OBJECT_NAME
------------------------------ ------------------------------ -----------------------------
SELECT STATEMENT
TABLE ACCESS VENKAT TEST_SQL_PLAN
INDEX VENKAT TEST_SP_IDX








-- now we know we have a bad plan in shared pool ..
-- how do we go about and eliminate the plan
-- without brutally flushing the entire shared pool
--


DBMS_SPM to our rescue..

sq2>l
1 declare
2 lv_res PLS_INTEGER;
3 begin
4 lv_res:= DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'69yujf79wwkja');
5 dbms_output.put_line(lv_res);
6* end;
sq2>/
1

PL/SQL procedure successfully completed.

sq2>select sql_handle,sql_text from dba_sql_plan_baselines
2 where sql_text like
3 'select * from test_sql_plan plan_with_2_first%where id=%'
4 /

SQL_HANDLE SQL_TEXT
------------------------------ ----------------------------------------------------------
SYS_SQL_5053e5a52d822dcf select * from test_sql_plan plan_with_2_first where id=:v

sq2>declare
2 lv_res pls_integer;
3 begin
4 lv_res:=DBMS_SPM.DROP_SQL_PLAN_BASELINE (sql_handle=>'SYS_SQL_5053e5a52d822dcf');
5 dbms_output.put_line(lv_res);
6 end;
7 /
1

PL/SQL procedure successfully completed.



for bind variable 100, the sql was initially doing index lookup before (whereas full table scan was optimal one)

Lets run it again


--session 1 runs the same sql again (with value =100)
Sql>select * from test_sql_plan plan_with_2_first where id=:v;
...
...
...
100 991xxxxxxx
100 992xxxxxxx
100 993xxxxxxx
100 994xxxxxxx
100 995xxxxxxx
100 996xxxxxxx
100 997xxxxxxx
100 998xxxxxxx

998 rows selected.

-- session 2
sq2>select sql_text sqltext,sql_id,plan_hash_value,executions from v$sql
2 where sql_text like
3 'select * from test_sql_plan plan_with_2_first%where id=%'
4 and executions>0
5 /

SQLTEXT SQL_ID PLAN_HASH_VALUE EXECUTIONS
--------------------------------------------- ------------- --------------- ----------
select * from test_sql_plan plan_with_2_first 69yujf79wwkja 3623521558 2
where id=:v

select * from test_sql_plan plan_with_2_first 69yujf79wwkja 1269960815 1
where id=:v <---- ***** NEW PLAN *****

sq2>ed
Wrote file afiedt.buf

1 select OPERATION,OPTIONS,OBJECT_OWNER,OBJECT_NAME from v$sql_plan
2 where PLAN_HASH_VALUE=1269960815
3 and sql_id='69yujf79wwkja'
4* order by id
sq2>/

OPERATION OPTIONS OBJECT_OWNER OBJECT_NAME
------------------------------ ------------------------------ ------------------------------ -------
SELECT STATEMENT
TABLE ACCESS FULL VENKAT TEST_SQL_PLAN




Now that looks like easy fix without having to run dbms_stats or flushing shared pool or running an alter table (to forcefully invalidate the plan in shared pool)

Sunday, September 23, 2007

something new learnt (outside oracle)

finally I am becoming a little HTML-aware person..
not anymore you are going to see messy formats in this blog as I just learnt (& tested) I could use preserve tags to avoid spaces getting eaten up and making the sql code and display totally clumpsy.

here is a test..

copy and paste of same text (from sql screen)

old way (how It appeared before)

OPERATION OBJECT_OWNER
------------------------------ ------------------------------
SELECT STATEMENT
SELECT STATEMENT
SELECT STATEMENT
SELECT STATEMENT
SELECT STATEMENT
TABLE ACCESS VENKAT
TABLE ACCESS VENKAT
TABLE ACCESS VENKAT
TABLE ACCESS VENKAT
TABLE ACCESS VENKAT
INDEX VENKAT
INDEX VENKAT
INDEX VENKAT
INDEX VENKAT
INDEX VENKAT


new way (from now on)


OPERATION OBJECT_OWNER
------------------------------ ------------------------------
SELECT STATEMENT
SELECT STATEMENT
SELECT STATEMENT
SELECT STATEMENT
SELECT STATEMENT
TABLE ACCESS VENKAT
TABLE ACCESS VENKAT
TABLE ACCESS VENKAT
TABLE ACCESS VENKAT
TABLE ACCESS VENKAT
INDEX VENKAT
INDEX VENKAT
INDEX VENKAT
INDEX VENKAT
INDEX VENKAT



Thats pretty neat!!

Tuesday, September 18, 2007

bye bye to FBI; Lets Welcome VC

(FBI ==> Function Based Indexes ; VC ==> Virtual Columns )

starting 11g, you can have Virtual columns (expression or computations) in a table just like regular columns .. That would make lots of things readable..queries, index scripts etc etc..

you can have constraints based on Virtual columns..you can even partition the table based on VC.

Here is a simple demo
----------------------

Create table test_vc (
id number(5) ,
name varchar2(10) ,
age number(3) ,
sal number(10) ,
comm number(10) ,
grade varchar2(10) GENERATED ALWAYS as
(
CASE WHEN (age>60)
THEN 'Senior'
WHEN ((age between 51 and 60) and (sal>200000))
THEN 'Grade A+'
WHEN ((age between 51 and 60) and (sal<200000))
THEN 'Grade A-'
WHEN ((age between 41 and 50) and (sal>200000))
THEN 'Grade B+'
WHEN ((age between 41 and 50) and (sal<200000))
THEN 'Grade B-'
WHEN ((age between 31 and 40) and (sal>200000))
THEN 'Grade C+'
WHEN ((age between 31 and 40) and (sal<200000))
THEN 'Grade C-'
END
) VIRTUAL ,
grade_auto GENERATED ALWAYS as
(
CASE WHEN (age>60)
THEN 'Senior'
WHEN ((age between 51 and 60) and (sal>200000))
THEN 'Grade A+'
WHEN ((age between 51 and 60) and (sal<200000))
THEN 'Grade A-'
WHEN ((age between 41 and 50) and (sal>200000))
THEN 'Grade B+'
WHEN ((age between 41 and 50) and (sal<200000))
THEN 'Grade B-'
WHEN ((age between 31 and 40) and (sal>200000))
THEN 'Grade C+'
WHEN ((age between 31 and 40) and (sal<200000))
THEN 'Grade C-'
END
) VIRTUAL
)

Both Grade and Grade_auto are Virtual columns ..but the only difference is for Grade_auto Oracle assigned the datatype by its own judgement.

SQL> desc test_vc
Name Null? Type
----------------------------------------- -------- --------------
ID NUMBER(5)
NAME VARCHAR2(10)
AGE NUMBER(3)
SAL NUMBER(10)
COMM NUMBER(10)
GRADE VARCHAR2(10)
GRADE_AUTO VARCHAR2(8) <---

Before 11g, we either would have created an Index on user-defined deterministic function or standard built-in functions.. this would have been the Create Index statement

SQL> Create index test_f_idx on test_vc(
2 CASE WHEN (age>60)
3 THEN 'Senior'
4 WHEN ((age between 51 and 60) and (sal>=200000))
5 THEN 'Grade A+'
6 WHEN ((age between 51 and 60) and (sal<200000))
7 THEN 'Grade A-'
8 WHEN ((age between 41 and 50) and (sal>=200000))
9 THEN 'Grade B+'
10 WHEN ((age between 41 and 50) and (sal<200000))
11 THEN 'Grade B-'
12 WHEN ((age between 31 and 40) and (sal>=200000))
13 THEN 'Grade C+'
14 WHEN ((age between 31 and 40) and (sal<200000))
15 THEN 'Grade C-'
16 END
17 );

Now check out how the same reads..
-- in 11g (indexing on Virtual columns allowed)
-- ---------------------------------------------------------
Create index test_vc_idx1 on test_vc(grade);
Create index test_vc_idx2 on test_vc(grade_auto);
Behind the screens, Oracle handles the Virtual columns just the same way as FBIs

SQL> select index_name,index_type from user_indexes
2 where table_name='TEST_VC';

INDEX_NAME INDEX_TYPE
------------------------------ ------------------------
TEST_VC_IDX2 FUNCTION-BASED NORMAL
TEST_VC_IDX1 FUNCTION-BASED NORMAL
TEST_F_IDX FUNCTION-BASED NORMAL

so lets forget Function based Index and start using Virtual columns..

Sunday, September 16, 2007

11g automagic partition creations - part2

its a bit different when you range partition by date column..you cannot specify a constant as your interval

SQL> Create table test_auto_partitioning_2
2 (c1 number, c2 varchar2(10) , c3 date)
3 partition by range (c3)
4 interval(30)
5 (
6 partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
7 partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
8 partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
9 )
10 /
Create table test_auto_partitioning_2
*
ERROR at line 1:
ORA-14752: Interval expression is not a constant of the correct type

Instead specify as date-interval datatype so oracle is aware of what you are requesting.. Rest is pretty much the same..



SQL> Create table test_auto_partitioning_2
2 (c1 number, c2 varchar2(10) , c3 date)
3 partition by range (c3)
4 interval(numtoyminterval(1,'MONTH'))
5 (
6 partition part1 values less than (to_date('09/15/2007','MM/DD/YYYY')),
7 partition part2 values less than (to_date('10/15/2007','MM/DD/YYYY')),
8 partition part3 values less than (to_date('11/15/2007','MM/DD/YYYY'))
9 )
10 /

Table created.

SQL> select partition_name,num_rows from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_2';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
PART1
PART2
PART3

SQL> Insert into TEST_AUTO_PARTITIONING_2(c3)
2 values (to_date('&mm/&dd/2007','mm/dd/yyyy'));
Enter value for mm: 09
Enter value for dd: 01
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('09/01/2007','mm/dd/yyyy'))

1 row created.


SQL> /
Enter value for mm: 09
Enter value for dd: 13
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('09/13/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 09
Enter value for dd: 17
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('09/17/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 10
Enter value for dd: 16
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('10/16/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 11
Enter value for dd: 16
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('11/16/2007','mm/dd/yyyy'))

1 row created.

SQL> /
Enter value for mm: 12
Enter value for dd: 16
old 2: values (to_date('&mm/&dd/2007','mm/dd/yyyy'))
new 2: values (to_date('12/16/2007','mm/dd/yyyy'))

1 row created.

SQL> commit;

Commit complete.

SQL> begin
2 dbms_stats.gather_table_stats(ownname=>user,tabname=>'TEST_AUTO_PARTITIONING_2');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_2';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
PART1 2
PART2 1
PART3 1
SYS_P44 1 -- auto generated
SYS_P45 1 -- auto generated

11g rocks!!

11g automagic partition creations

11g introduces this new interval partitioning ..bye-bye to all age-old partition maintenance script to create new partitions and capturing unexpected data into "maxvalue" bucket.

all you have to tell Oracle is your logic of partitioning..ie INTERVAL key.

See it in action here..


SQL>connect venkat/venkat

-- range partition by number
-- -------------------------

Create table TEST_AUTO_PARTITIONING_1
(c1 number, c2 varchar2(10) , c3 date)
partition by range (c1)
interval(30) -- INTERVAL specified as 30
(
partition part1 values less than (100),
partition part2 values less than (200),
partition part3 values less than (300)
)
/


SQL> select partition_name from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_1';

PARTITION_NAME
------------------------------
PART1
PART2
PART3

-- insert 3 rows (50,150 and 250)
SQL> select * from TEST_AUTO_PARTITIONING_1
2 /

C1 C2 C3
---------- ---------- ---------
50
150
250

SQL> select partition_name from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_1';

PARTITION_NAME
------------------------------
PART1
PART2
PART3

SQL> insert into TEST_AUTO_PARTITIONING_1 (c1) values (&n);
Enter value for n: 350
old 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (&n)
new 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (350)

1 row created.

SQL> /
Enter value for n: 450
old 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (&n)
new 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (450)

1 row created.

SQL> /
Enter value for n: 550
old 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (&n)
new 1: insert into TEST_AUTO_PARTITIONING_1 (c1) values (550)

1 row created.

SQL> commit;

Commit complete.

SQL> begin
2 dbms_stats.gather_table_stats(ownname=>USER,tabname=>'TEST_AUTO_PARTITIONING_1');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows from user_tab_partitions t
2 where table_name='TEST_AUTO_PARTITIONING_1';

PARTITION_NAME NUM_ROWS
------------------------------ ----------
PART1 1
PART2 1
PART3 1
SYS_P41 1
-- system generated
SYS_P42 1 -- system generated
SYS_P43 1 -- system generated

6 rows selected.


to continue in next post: Interval partitioning for date column

Sunday, August 26, 2007

simple and elegant..something new from 10g

One more time It happened..I was browsing within Oracle documentation for something else and found something else (of course, got deviated from intended search :))



Single quotes within a string literal is always a messy job in Oracle because you need to escape every single single quote within your string to make Oracle understand its a part of data.


SQL> conn venkat/venkat@ORCL

Connected.

SQL> select * from v$version;


BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production




here is the tradtional way I had known ever since

SQL> ed
Wrote file afiedt.buf

1 select
2 'it''s sunday night '||
3 to_char(sysdate,'hh:MI AM')||
4 '. I am testing single quoting ("''") in Oracle' as TestOutput
5* from dual
SQL> /

TESTOUTPUT
------------------------------------------------------------------------
it's sunday night 09:15 PM. I am testing single quoting ("'") in Oracle



Now here is the same using 10g's Q quote delimiter

SQL> ed
Wrote file afiedt.buf

1 select q'(it's sunday night )' ||
2 to_char(sysdate,'hh:MI AM')||
3 q'(. I am testing single quoting ("'") in Oracle)' TestOutput
4* from dual

SQL> /

TESTOUTPUT
----------------------------------------------------------------------------
it's sunday night 09:17 PM. I am testing single quoting ("'") in Oracle


No more messy escaping quotes.. Neat!! (and am I the only one to catch up with this 10g feature this late? Better late than never!!:))

Thursday, August 16, 2007

Pivot or Unpivot? no big deal from 11g

pivoting and unpivoting resultsets - a kids game from 11g onwards

you dont need to be a sql guru to figure out how to transform rows into columns and vice versa.

here comes PIVOT and UNPIVOT clause of 11g.

I dont think I need to explain more..the below text is straight copy & paste from documentation.

Using PIVOT and UNPIVOT: Examples

The oe.orders table contains information about when an order was placed (order_date), how it was place (order_mode), and the total amount of the order (order_total), as well as other information. The following example shows how to use the PIVOT clause to pivot order_mode values into columns, aggregating order_total data in the process, to get yearly totals by order mode:

CREATE TABLE pivot_table AS
SELECT * FROM
(SELECT EXTRACT(YEAR FROM order_date) year, order_mode, order_total FROM orders)
PIVOT
(SUM(order_total) FOR order_mode IN ('direct' AS Store, 'online' AS Internet));

SELECT * FROM pivot_table ORDER BY year;

YEAR STORE INTERNET
---------- ---------- ----------
1990 61655.7
1996 5546.6
1997 310
1998 309929.8 100056.6
1999 1274078.8 1271019.5
2000 252108.3 393349.4

6 rows selected.

The UNPIVOT clause lets you rotate specified columns so that the input column headings are output as values of one or more descriptor columns, and the input column values are output as values of one or more measures columns. The first query that follows shows that nulls are excluded by default. The second query shows that you can include nulls using the INCLUDE NULLS clause.

SELECT * FROM pivot_table
UNPIVOT (yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode;

YEAR ORDER_ YEARLY_TOTAL
---------- ------ ------------
1990 direct 61655.7
1996 direct 5546.6
1997 direct 310
1998 direct 309929.8
1998 online 100056.6
1999 direct 1274078.8
1999 online 1271019.5
2000 direct 252108.3
2000 online 393349.4
9 rows selected.

SELECT * FROM pivot_table
UNPIVOT INCLUDE NULLS
(yearly_total FOR order_mode IN (store AS 'direct', internet AS 'online'))
ORDER BY year, order_mode;

YEAR ORDER_ YEARLY_TOTAL
---------- ------ ------------
1990 direct 61655.7
1990 online
1996 direct 5546.6
1996 online
1997 direct 310
1997 online
1998 direct 309929.8
1998 online 100056.6
1999 direct 1274078.8
1999 online 1271019.5
2000 direct 252108.3
2000 online 393349.4

12 rows selected.

thats neat!!

Saturday, August 11, 2007

11g: One more to remember - CASE matters!!

until now , I always had the luxury of just remembering just my passwords and not its case. Because I think Oracle always took supplied password and apply some hashing function together with userid to generate a new passcode which is what gets stored under password column of dba_users or all_users.

Starting from 11g, oracle passwords are going to be case-sensitive. So its going to take a while for people like me to remember "OracleIsFun" is different than "oracleisFUN".

Another nice addition to Oracle 11g is new view called users_with_defpwd (or something similar)..this view list all the users whose password is supplied default one (scott/tiger). Now, I can already see - this is going to be extremely easy for database auditors (SOX ,HPAA whatever else ) to list the such user accounts ..I remember creating a script for 9i before, where I have to built my own array of known userid+password combinations and then trying to connect for every single known combination.

Oracle is really thinking ahead.

Sunday, August 5, 2007

sequence of errors

I encountered this at one of my client's .. I usually get to see my inbox full of unread messages running for atleast 3 or 4 pages every time I get to check my account set up with this client because I usually get to read them only once in a week and one day I happened to see some mails with some ORA- error (4068 error to be exact) and that caught my attention.

what was happening was a few packages in Production were bombing with ORA-4068 error and there were back and forth emails between development and dba team on how to resolve it. The development team thought it was due to packages in INVALID state but they didnt know how does a package suddenly change the status to INVALID, they simply put the ball on DBA's court asking them to ensure & maintain the production objects with VALID status.
DBA team responded back they had to do DDL changes but replied back saying it may be due to partition maintenance operations which was all coded by DEV team..Eventually they were about to agree on creating a database job to check invalid objects every 15 minutes and automatically compile them..

what surprised me at the time I read all this long email thread was that no one cared to really dig in what the real cause of the problem but was ready to throw in suggestions to whatever problems they thought was creating the issue.




ORA-04068: existing state of packages has been discarded

is pretty simple and straight forward..clearly states your existing state of package has been discarded.. if you change a packag with global variables and some sessions has already stored the prev code, they had to flushed and reloaded because you changed the source code. Just simple as that..
I developed a test case and proved to DBAs and Development team that this can still happen if the status of packages are perfectly valid ..and actually the error was happening because the production releases were pushed in without bringing down client (web clients with connection pooling)..simply bringing down the clients before pushing new PLSQL code & then bringing up new connections would fix the issue without any single line of coding effort.

I didn't get any questions back from any team ..neither did I get any feedback on if they accepted my theory..but it was interesting that two teams were ready to solve an inexistent problem :)

Sunday, July 29, 2007

invisible index

Havent blogged in a long while (thanks to my busy schedule and interesting projects assignments)..



anyway, just thought I would post about this really cool 11g feature...There are countless situations wherein a tuning specialist has to work straight in production environment. But you wont be allowed to create new indexes because thought it may make your current query/sql being analyzed to go faster, there is always a concern that it might affect the other existing queries and potentially generate a new plan which may be worse than the current plan.



11g introduces something called "invisible indexes" to solve this issue (only theoretical knowledge from what I have read..no idea on how to implement as I haven't gotten my hands on or haven't read docs yet).. Now basically what you do is create an invisible index..and this index would be considered by optimizer only when hinted..


so until we fix the performance problem of say a single sql..we can simply have many invisible indexes to make the query go faster..and simply edit the statement to add a hint to use the new indexes and that wouldnt affect anything else which are running smooth in prod already..


Cool & simple ..& I could see its use already :)

Tuesday, May 22, 2007

Keeping up with new features

if there is one thing hard with learning oracle , its catching up with its pace of releasing new features & functionalities.

Recently I saw a plsql code some developer had written at a client place, to remove all unwanted characters from a varchar2 column and just to retain alphabets and numbers..

I was quick to suggest a simple "translate" function would do the trick and PLSQL call can be totally avoided..I learnt about translate function when it was Oracle version 7.3 and I should consider lucky enough that its still there in database kernel, working the same way.

here is a test table to demonstrate the functionality..

SQL> select * from ccs_test_trans ;

C1 C2
---------- ----------
1 12 34 560
2 abcdefgh
3 ABC#$%@(
4 *&^^!%%
5 12345678
6 a;p 27s
7 olka8762;;

7 rows selected.



Assume c2 is the column, which somehow has all extra characters (since varchar2 accepts everything) and needs little bit of cleansing work.

this is how I would have used translate function..
logic is..use nested translate function..
1) first translate - capture all unwanted characters
2) second translate - removes them..leaving only the wanted characters intact.

and then use a replace function to remove the extra spaces (since translate would have replaced all unwanted characters to space/any other character you specify)

SQL> ed
Wrote file afiedt.buf

1 With v1 as
2 (select
3 'abcdefghijklmnopqrstuvwxyz'||
4 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'||
5 '0123456789' retain_chrs
6 from dual)
7 select c1,c2,
8 replace(translate( c2,
9 translate( c2,
10 retain_chrs,
11 lpad( '*', length(retain_chrs), '*' )
12 ),
13 ' ' ), ' ', '') as new_c2
14 from ccs_test_trans ,v1

SQL> /

C1 C2 NEW_C2
---------- ---------- ----------
1 12 34 560 1234560
2 abcdefgh abcdefgh
3 ABC#$%@( ABC
4 *&^^!%%
5 12345678 12345678
6 a;p 27s ap27s
7 olka8762;; olka8762

7 rows selected.



Now coding that wasnt difficult..but understanding or explaining that to anyone new certainly is..because they need to understand how translate works..

but as shown above, it certainly works..and does the work needed..

The question is..do we really need such a complicated query ..isnt there a simple way of solving the same?

Lets see with 10g we have regular expressions..and Oracle allows all POSIX references in SQL. So all I want is to retain all characters which are either numbers or alphabets and remove the rest..

:alnum -> denotes exactly the same ; either alphabets or numbers
REGEXP_REPLACE-> does the replace functionality

SQL> ed
Wrote file afiedt.buf

1 With v1 as
2 (select
3 'abcdefghijklmnopqrstuvwxyz'||
4 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'||
5 '0123456789' retain_chrs
6 from dual)
7 select c1,c2,
8 replace(translate( c2,
9 translate( c2,
10 retain_chrs,
11 lpad( '*', length(retain_chrs), '*' )
12 ),
13 ' ' ), ' ', '') as new_c2,
14 REGEXP_REPLACE( c2, '[^[:alnum:]]' , '') as RE_c2
15 from ccs_test_trans ,v1

SQL> /
-- results

C1 C2 NEW_C2 RE_C2
---------- ---------- ---------- ----------
1 12 34 560 1234560 1234560
2 abcdefgh abcdefgh abcdefgh
3 ABC#$%@( ABC ABC
4 *&^^!%%
5 12345678 12345678 12345678
6 a;p 27s ap27s ap27s
7 olka8762;; olka8762 olka8762

7 rows selected.



How cool and simple is that?

Lets say I also want to allow comma and periods in the c2 column and they should be retained. (added two new rows with sample data to test)

SQL> ed
Wrote file afiedt.buf

1 With v1 as
2 (select
3 'abcdefghijklmnopqrstuvwxyz'||
4 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'||
5 '0123456789' ||
6 ',.' retain_chrs
7 from dual)
8 select c1,c2,
9 replace(translate( c2,
10 translate( c2,
11 retain_chrs,
12 lpad( '*', length(retain_chrs), '*' )
13 ),
14 ' ' ), ' ', '') as new_c2,
15 REGEXP_REPLACE( c2, '[^[:alnum:]{,.}]' , '') as RE_c2
16 from ccs_test_trans ,v1

SQL> /
-- results

C1 C2 NEW_C2 RE_C2
---------- ---------- ---------- ----------
1 12 34 560 1234560 1234560
2 abcdefgh abcdefgh abcdefgh
3 ABC#$%@( ABC ABC
4 *&^^!%%
5 12345678 12345678 12345678
6 a;p 27s ap27s ap27s
7 olka8762;; olka8762 olka8762
8 12,34,56.0 12,34,56.0 12,34,56.0
9 abc, def.g abc,def.g abc,def.g

9 rows selected.



I havent dealt a lot with REGULAR expressions..but it certainly looks an interesting area..if NOT performance, atleast its worth a try - for its readability and flexibility for future modifications.
I am sure you would agree the following SQL reads way too easy that the previous one (using translate) and running for 10+ lines.

SQL> select c1,c2,REGEXP_REPLACE( c2, '[^[:alnum:]{,.}]' , '') as RE_c2
2 from ccs_test_trans
3 /

C1 C2 RE_C2
---------- ---------- ----------
1 12 34 560 1234560
2 abcdefgh abcdefgh
3 ABC#$%@( ABC
4 *&^^!%%
5 12345678 12345678
6 a;p 27s ap27s
7 olka8762;; olka8762
8 12,34,56.0 12,34,56.0
9 abc, def.g abc,def.g

9 rows selected.





Happy SQL-ing until next..





Sunday, May 13, 2007

ANSI & ORACLE MV - still a long way to go..

Anything new is always interesting and so is the my current project at a client place - which is sort of hybrid between Transactional and reporting system. Thanks to this project, I got a break from traditional TUNING tasks and am over-working on catching up with datawarehousing (DW) techniques & implementing them.

Here is something interesting what I figured last week (thanks to a question from client's dev team)..

-- Create 3 tables with 10 rows each
-- & add Primary keys

SQL> Create table test_tb1 as
2 select rownum c1, owner c2 from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb1 add (
2 constraint test_tb1_pk primary key(c1)
3 );

Table altered.

SQL> Create table test_tb2 as
2 select rownum c1, object_name c2 from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb2 add (
2 constraint test_tb2_pk primary key(c1)
3 );

Table altered.

SQL> Create table test_tb3 as
2 select rownum c1, object_type c2,created from dba_objects where rownum<11
3 /

Table created.

SQL> alter table test_tb3 add (
2 constraint test_tb3_pk primary key(c1)
3 )
4 /

Table altered.

-- this exercise is about MATERIALIZED VIEWS..so to have FAST REFRESHING (aka incremental refreshing) Materialized view implemented , we should first have MV LOG created on base tables.

--So here we create them


SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb1
2 with primary key,ROWID,sequence
3* (c2) including new values
SQL> /

Materialized view log created.

SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb2
2 with primary key,ROWID,sequence
3* (c2) including new values
SQL> /

Materialized view log created.

SQL> ed
Wrote file afiedt.buf

1 Create materialized view log on test_tb3
2 with primary key,ROWID,sequence
3* (c2,created) including new values
SQL> /

Materialized view log created.

-- Now that we have base tables and MV logs ready, lets try creating a Materialized view..(using ANSI syntax)

SQL> ed
Wrote file afiedt.buf

1 Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from test_tb1 t1
13 JOIN test_tb2 t2
14 ON t1.c1 = t2.c1
15 JOIN test_tb3 t3
16* on t1.c1 = t3.c1
SQL> /
JOIN test_tb3 t3
*
ERROR at line 15:
ORA-12015: cannot create a fast refresh materialized view from a complex query

-- that fails ..because optimizer thinks its a complex query (I am not good as ANSI..and not sure if there is any way to rewrite within ANSI standard itself )..but the point is , I didnt expect that to be considered as complex query


-- that was the problem reported from Dev team to me..and I just thought to test if it was ANSI SQL which was creating the problem. So here I rewrite the same SQL using traditional joins

SQL> ed
Wrote file afiedt.buf

1 Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from
13 test_tb1 t1,
14 test_tb2 t2,
15 test_tb3 t3
16 Where
17 t1.c1 = t2.c1
18* and t1.c1 = t3.c1
SQL> /

Materialized view created.

-- WORKS!! Thats surprising..


SQL> drop materialized view test_mv1;

Materialized view dropped.

-- Lets try the same with outer joins too

SQL> ed
Wrote file afiedt.buf

1* drop materialized view test_mv1
SQL> Create materialized view test_mv1
2 refresh fast on demand
3 enable query rewrite
4 as
5 select t1.rowid t1_rowid
6 , t2.rowid t2_rowid
7 , t3.rowid t3_rowid
8 , t1.c2 owner
9 , t2.c2 name
10 , t3.c2 objtype
11 , t3.created
12 from
13 test_tb1 t1,
14 test_tb2 t2,
15 test_tb3 t3
16 Where
17 t1.c1 = t2.c1
18 and t1.c1 = t3.c1(+)
19 /

Materialized view created.

-- Selecting from MV

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> select * from test_tb1;

C1 C2
---------- ------------------------------
1 SYS
2 SYS
3 SYS
4 SYS
5 SYS
6 SYS
7 SYS
8 SYS
9 SYS
10 SYS

10 rows selected.

-- Testing MV functionality by adding more data to base tables and
-- seeing if MV works fine.

SQL> insert into test_tb1 select 10+rownum c1 , 'NEW DATA' c2 from test_tb1
2 /

10 rows created.

SQL> select * from test_tb1;

C1 C2
---------- ------------------------------
1 SYS
2 SYS
3 SYS
4 SYS
5 SYS
6 SYS
7 SYS
8 SYS
9 SYS
10 SYS
11 NEW DATA
12 NEW DATA
13 NEW DATA
14 NEW DATA
15 NEW DATA
16 NEW DATA
17 NEW DATA
18 NEW DATA
19 NEW DATA
20 NEW DATA

20 rows selected.

SQL> commit;

Commit complete.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> begin
2 dbms_mview.refresh(list=>'TEST_MV1',METHOD=>'f');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX

10 rows selected.

SQL> insert into test_tb2 select 10+rownum c1, 'New data' c2 from test_tb2
2 /

10 rows created.

SQL> select * from test_tb2;

C1 C2
---------- -------------------------
1 ICOL$
2 I_USER1
3 CON$
4 UNDO$
5 C_COBJ#
6 I_OBJ#
7 PROXY_ROLE_DATA$
8 I_IND1
9 I_CDEF2
10 I_PROXY_ROLE_DATA$_1
11 New data
12 New data
13 New data
14 New data
15 New data
16 New data
17 New data
18 New data
19 New data
20 New data

20 rows selected.

SQL> commit;

Commit complete.

SQL> begin
2 dbms_mview.refresh(list=>'TEST_MV1',METHOD=>'f');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> select rownum,owner,objtype from test_mv1;

ROWNUM OWNER OBJTYPE
---------- ------------------------------ -------------------
1 SYS TABLE
2 SYS INDEX
3 SYS TABLE
4 SYS TABLE
5 SYS CLUSTER
6 SYS INDEX
7 SYS TABLE
8 SYS INDEX
9 SYS INDEX
10 SYS INDEX
11 NEW DATA
12 NEW DATA
13 NEW DATA
14 NEW DATA
15 NEW DATA
16 NEW DATA
17 NEW DATA
18 NEW DATA
19 NEW DATA
20 NEW DATA

20 rows selected.


-- works fine!!!

Moral: Though ANSI standard has been introduced in Oracle since 9.1 version, its always better to check back to traditional join syntax when sometimes you get unexpected error with sql/optimizer.