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