SQL> SET ECHO OFF
SQL> SET LINE 10000
SQL> COLUMN BLOCKER FORMAT A11;
SQL> COLUMN BLOCKEE FORMAT A10;
SQL> COLUMN SID FORMAT 99999;
SQL> COLUMN BLOCKER_MODULE FORMAT A15 TRUNC;
SQL> COLUMN BLOCKEE_MODULE FORMAT A15 TRUNC;
SQL>
SQL> ALTER SESSION SET OPTIMIZER_MODE=RULE;
Session altered.
SQL>
SQL> SELECT a.inst_id,
2 (SELECT username FROM gv$session s WHERE s.inst_id=a.inst_id AND s.sid=a.sid) blocker,
3 a.sid,
4 (SELECT module FROM gv$session s WHERE s.inst_id=a.inst_id AND s.sid=a.sid) blocker_module ,
5 ' is blocking ' "IS BLOCKING",
6 b.inst_id,
7 (SELECT username FROM gv$session s WHERE s.inst_id=b.inst_id AND s.sid=b.sid) blockee,
8 b.sid ,
9 (SELECT module FROM gv$session s WHERE s.inst_id=b.inst_id AND s.sid=b.sid) blockee_module
10 FROM gv$lock a, gv$lock b
11 where a.block <>0
12 AND b.request > 0
13 AND a.id1 = b.id1
14 AND a.id2 = b.id2
15 AND a.sid <> b.sid
16 order by 1, 2
17 /
INST_ID BLOCKER SID BLOCKER_MODULE IS BLOCKING INST_ID BLOCKEE SID BLOCKEE_MODULE
---------- ----------- ------ --------------- ------------- ---------- ---------- ------ ---------------
1 SYSADM 15 SQL*Plus is blocking 1 SYSADM 11 T.O.A.D.
SQL>
SQL> ALTER SESSION SET OPTIMIZER_MODE=CHOOSE;
Session altered.
SQL> BEGIN
2 dbms_output.enable(1000000);
3 FOR do_loop IN (SELECT session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
4 b.object_name object_name, b.object_type object_type
5 FROM v$locked_object a, dba_objects b
6 WHERE xidsqn != 0
7 and b.object_id = a.object_id)
8 LOOP
9 dbms_output.put_line('.');
10 dbms_output.put_line('Blocking Session : '||do_loop.session_id);
11 dbms_output.put_line('Object (Owner/Name): '||do_loop.owner||'.'||do_loop.object_name);
12 dbms_output.put_line('Object Type : '||do_loop.object_type);
13 FOR next_loop IN (SELECT sid
14 FROM v$lock
15 WHERE id2 = do_loop.xidsqn
16 AND sid != do_loop.session_id)
17 LOOP
18 dbms_output.put_line('Sessions being blocked : '||next_loop.sid);
19 END LOOP;
20 END LOOP;
21 END;
22 /
.
Blocking Session : 15
Object (Owner/Name): SYSADM.T
Object Type : TABLE
Sessions being blocked : 11
PL/SQL procedure successfully completed.
SQL>
Wednesday, June 18, 2008
Find out Blocking Session
I came across this pieace of code in Oracle form which helps us to find the blocking sessions. I tought of sharing it here.
Monday, June 16, 2008
Lesson for the day
With cost based optimizer if you don’t collect statistics properly then it means that you are screwed.
This is what I did.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
I created the following table.
SQL> create table hx_index_test_tbl(
2 no integer not null,
3 name varchar2(50) not null,
4 old_value varchar2(100),
5 new_value varchar2(100));
Table created.
Next I added these two indexes to the table.
SQL> create index hx_index_test_tbl_idx on hx_index_test_tbl(name,no);
Index created.
SQL> create index hx_index_test_tbl_idx_001 on hx_index_test_tbl(name);
Index created.
Then I populated some data.
SQL> insert into hx_index_test_tbl
2 select row_number() over(partition by name order by 1) no, name, rpad('*',100,'*') old_value, rpad('*',100,'*') new_value
3 from (select 'A'mod(level,100) name from dual connect by level <=1000);
1000 rows created.
SQL> commit;
Commit complete.
Got the statistics gathered.
SQL> exec dbms_stats.gather_table_stats(user,'hx_index_test_tbl',method_opt=>'for all indexed columns');
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> set autotrace traceonly explain
SQL> select * from hx_index_test_tbl where name = 'A0';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=10 Bytes=1000)
1 0 TABLE ACCESS (FULL) OF 'HX_INDEX_TEST_TBL' (Cost=5 Card=10 Bytes=1000)
Oops its going for a full scan. It’s not considering my index. I want it to pick up the index HX_INDEX_TEST_TBL_IDX_001.
SQL> select * from hx_index_test_tbl where name = 'A0' and no = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=100)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HX_INDEX_TEST_TBL' (Cost=2 Card=1 Bytes=100)
2 1 INDEX (RANGE SCAN) OF 'HX_INDEX_TEST_TBL_IDX' (NON-UNIQUE) (Cost=1 Card=1)
But when I add no in the where clause its considering the index why is it so???
The reason is very simple. See how I have gathered statistics.
dbms_stats.gather_table_stats(user,'hx_index_test_tbl',method_opt=>'for all indexed columns');
I forgot to gather for Index of this table. Now let me gather for the index of this table also.
SQL> EXEC dbms_stats.gather_table_stats(user,'hx_index_test_tbl',method_opt=>'for all indexed columns', cascade=>TRUE);
PL/SQL procedure successfully completed.
Now see what happens...
SQL> select * from hx_index_test_tbl where name = 'A0';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=1000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HX_INDEX_TEST_TBL' (Cost=2 Card=10 Bytes=1000)
2 1 INDEX (RANGE SCAN) OF 'HX_INDEX_TEST_TBL_IDX_001' (NON-UNIQUE) (Cost=1 Card=10)
So if you don’t gather stats properly with cost based optimizer you will get weird execution plans.
This is what I did.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
I created the following table.
SQL> create table hx_index_test_tbl(
2 no integer not null,
3 name varchar2(50) not null,
4 old_value varchar2(100),
5 new_value varchar2(100));
Table created.
Next I added these two indexes to the table.
SQL> create index hx_index_test_tbl_idx on hx_index_test_tbl(name,no);
Index created.
SQL> create index hx_index_test_tbl_idx_001 on hx_index_test_tbl(name);
Index created.
Then I populated some data.
SQL> insert into hx_index_test_tbl
2 select row_number() over(partition by name order by 1) no, name, rpad('*',100,'*') old_value, rpad('*',100,'*') new_value
3 from (select 'A'mod(level,100) name from dual connect by level <=1000);
1000 rows created.
SQL> commit;
Commit complete.
Got the statistics gathered.
SQL> exec dbms_stats.gather_table_stats(user,'hx_index_test_tbl',method_opt=>'for all indexed columns');
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> set autotrace traceonly explain
SQL> select * from hx_index_test_tbl where name = 'A0';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=10 Bytes=1000)
1 0 TABLE ACCESS (FULL) OF 'HX_INDEX_TEST_TBL' (Cost=5 Card=10 Bytes=1000)
Oops its going for a full scan. It’s not considering my index. I want it to pick up the index HX_INDEX_TEST_TBL_IDX_001.
SQL> select * from hx_index_test_tbl where name = 'A0' and no = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=100)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HX_INDEX_TEST_TBL' (Cost=2 Card=1 Bytes=100)
2 1 INDEX (RANGE SCAN) OF 'HX_INDEX_TEST_TBL_IDX' (NON-UNIQUE) (Cost=1 Card=1)
But when I add no in the where clause its considering the index why is it so???
The reason is very simple. See how I have gathered statistics.
dbms_stats.gather_table_stats(user,'hx_index_test_tbl',method_opt=>'for all indexed columns');
I forgot to gather for Index of this table. Now let me gather for the index of this table also.
SQL> EXEC dbms_stats.gather_table_stats(user,'hx_index_test_tbl',method_opt=>'for all indexed columns', cascade=>TRUE);
PL/SQL procedure successfully completed.
Now see what happens...
SQL> select * from hx_index_test_tbl where name = 'A0';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10 Bytes=1000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'HX_INDEX_TEST_TBL' (Cost=2 Card=10 Bytes=1000)
2 1 INDEX (RANGE SCAN) OF 'HX_INDEX_TEST_TBL_IDX_001' (NON-UNIQUE) (Cost=1 Card=10)
So if you don’t gather stats properly with cost based optimizer you will get weird execution plans.
Monday, June 9, 2008
String List
Being a PLSQL developer many a times I have been in a situation where I would get list of values as input in a string and have to check if they exist in a particular table. The values would be separated by a delimiter. Let’s consider the below example
String Input: ‘ORACLE, SQL SERVER, DB2, MYSQL, SYBASE’
I have to use this string to see if these values exist in the table.
One possible way is to build a dynamic SQL. Just pad single quotes before and after each value in the string and build a SQL with the IN clause. But I want to do it without a dynamic SQL. How is this possible?
I came up with this SQL which splits this string and gives me the values in the string as rows.
Here is the SQL…
So what have I done here? Let me split it into steps for your understanding.
1. I am identifying the possible letters that would appear in the values and translating them into blank space. Following command does that for me.
Now you can see I don’t have anything other than comma (The delimiter).
2. Now I am going to remove all the blank space from the output that I got in step 1.
Now the length of the output will give me the total number of values in the list. Note that I have padded a extra comma to the string as the number of delimiter is always less than the number of values by one.
3. So the third step is obviously to count the number of comma in the above input and determine total number of values in the string list.
4. Next step is to generate the string list for 5 times in rows using the CONNECT BY clause.
5. Now its simple with the help of SUBSTR and INSTR I can split each values as shown below.
So now I can use this SQL to convert any value list into rows and use in my SQL statements.
And if you can use Regular expression things can be much more easier.
Check this out.
NOTE: This might need have to be customized based on the given requirement.
String Input: ‘ORACLE, SQL SERVER, DB2, MYSQL, SYBASE’
I have to use this string to see if these values exist in the table.
One possible way is to build a dynamic SQL. Just pad single quotes before and after each value in the string and build a SQL with the IN clause. But I want to do it without a dynamic SQL. How is this possible?
I came up with this SQL which splits this string and gives me the values in the string as rows.
Here is the SQL…
SQL> var a varchar2(100)
SQL> exec :a := 'ORACLE, SQL SERVER, DB2, MYSQL, SYBASE'
PL/SQL procedure successfully completed.
SQL> SELECT TRIM(SUBSTR(:a, INSTR(','||:a,',',1,level), INSTR(:a||',',',',1,level) - INSTR(','||:a,',',1,level))) my_list
2 FROM dual, (SELECT length(replace(translate(lower(:a)||',','abcdefghijklmnopqrstuvwxyz1234567890',' '),' ','')) cnt
3 FROM dual)
4 CONNECT BY level <= cnt
5 /
MY_LIST
--------------------------------------------------------------------------------
ORACLE
SQL SERVER
DB2
MYSQL
SYBASE
So what have I done here? Let me split it into steps for your understanding.
1. I am identifying the possible letters that would appear in the values and translating them into blank space. Following command does that for me.
SQL> SELECT translate(lower(:a)||',','abcdefghijklmnopqrstuvwxyz1234567890',' ') val
2 FROM dual;
VAL
--------------------------------------------------------------------------------
, , , , ,
Now you can see I don’t have anything other than comma (The delimiter).
2. Now I am going to remove all the blank space from the output that I got in step 1.
SQL> SELECT replace(translate(lower(:a)||',','abcdefghijklmnopqrstuvwxyz1234567890',' '),' ','') val
2 FROM dual;
VAL
--------------------------------------------------------------------------------
,,,,,
Now the length of the output will give me the total number of values in the list. Note that I have padded a extra comma to the string as the number of delimiter is always less than the number of values by one.
3. So the third step is obviously to count the number of comma in the above input and determine total number of values in the string list.
SQL> SELECT length(replace(translate(lower(:a)||',','abcdefghijklmnopqrstuvwxyz1234567890',' '),' ','')) cnt
2 FROM dual
3 /
CNT
----------
5
4. Next step is to generate the string list for 5 times in rows using the CONNECT BY clause.
SQL> SELECT level, :a my_list
2 FROM dual, (SELECT length(replace(translate(lower(:a)||',','abcdefghijklmnopqrstuvwxyz1234567890',' '),' ','')) cnt
3 FROM dual)
4 CONNECT BY level <= cnt
5 /
LEVEL MY_LIST
---------- ---------------------------------------------------------------------------------------------------------------
1 ORACLE, SQL SERVER, DB2, MYSQL, SYBASE
2 ORACLE, SQL SERVER, DB2, MYSQL, SYBASE
3 ORACLE, SQL SERVER, DB2, MYSQL, SYBASE
4 ORACLE, SQL SERVER, DB2, MYSQL, SYBASE
5 ORACLE, SQL SERVER, DB2, MYSQL, SYBASE
5. Now its simple with the help of SUBSTR and INSTR I can split each values as shown below.
SQL> SELECT TRIM(SUBSTR(:a, INSTR(','||:a,',',1,level), INSTR(:a||',',',',1,level) - INSTR(',':a,',',1,level))) my_list
2 FROM dual, (SELECT length(replace(translate(lower(:a)',','abcdefghijklmnopqrstuvwxyz1234567890',' '),' ','')) cnt
3 FROM dual)
4 CONNECT BY level <= cnt
5 /
MY_LIST
--------------------------------------------------------------------------------
ORACLE
SQL SERVER
DB2
MYSQL
SYBASE
So now I can use this SQL to convert any value list into rows and use in my SQL statements.
And if you can use Regular expression things can be much more easier.
Check this out.
SQL> SELECT trim(regexp_substr(:a,'[^,]+', 1, level)) my_list
2 FROM dual
3 CONNECT BY level <= NVL( LENGTH( REGEXP_REPLACE( :a, '[^,]+', NULL ) ), 0 ) + 1
4 /
MY_LIST
--------------------------------------------------------------------------------
ORACLE
SQL SERVER
DB2
MYSQL
SYBASE
NOTE: This might need have to be customized based on the given requirement.
Monday, June 2, 2008
Code reusability in oracle pl/sql
Again today when checking out tom’s blog I lead me to this post in Tayler Muth’s Blog. This sounds to be an interesting topic. So I thought for a while and this is what came up to my mind…
Code reusability in oracle pl/sql
First to start with pl/sql is procedural language which is not object oriented. Once said that it implies that this language is not familiar with the concept of inheritance. I have been using pl/sql for quite some time. It’s a great programming language. It would be even better if it had the class concept in it.
Class concept would come handy when we want to build a framework which can be utilized over and over.
Currently I am working on a masking utility. We have different masking algorithms say…
Algorithm 1, Algorithm 2, Algorithm 3 etc.
The general framework of this masking is something like this.
1. Get the field from user which needs to be masked.
2. Get the master table for that field.
3. Load the distinct data for the field from master table into a staging table
4. Apply your masking logic on the staging table.
5. Find all the transaction tables that have this field in it.
6. Using the staging table update all the transaction table.
Here this is a very good candidate for class concept where I can have a parent class which will have the entire frame work of what I have given in six steps. And I can have one child class for each algorithm and in each child class I can just over write the step four where I can apply my masking algorithm. But unfortunately we don’t have it. Hence I went for package, Use some CASE statements and hooked in my algorithms and made it as optimal as possible. I basically hate writing lot of code. If Oracle can introduce OOP(Object Oriented Programming) in PL/SQL it would be of great use and even more flexible.
Code reusability in oracle pl/sql
First to start with pl/sql is procedural language which is not object oriented. Once said that it implies that this language is not familiar with the concept of inheritance. I have been using pl/sql for quite some time. It’s a great programming language. It would be even better if it had the class concept in it.
Class concept would come handy when we want to build a framework which can be utilized over and over.
Currently I am working on a masking utility. We have different masking algorithms say…
Algorithm 1, Algorithm 2, Algorithm 3 etc.
The general framework of this masking is something like this.
1. Get the field from user which needs to be masked.
2. Get the master table for that field.
3. Load the distinct data for the field from master table into a staging table
4. Apply your masking logic on the staging table.
5. Find all the transaction tables that have this field in it.
6. Using the staging table update all the transaction table.
Here this is a very good candidate for class concept where I can have a parent class which will have the entire frame work of what I have given in six steps. And I can have one child class for each algorithm and in each child class I can just over write the step four where I can apply my masking algorithm. But unfortunately we don’t have it. Hence I went for package, Use some CASE statements and hooked in my algorithms and made it as optimal as possible. I basically hate writing lot of code. If Oracle can introduce OOP(Object Oriented Programming) in PL/SQL it would be of great use and even more flexible.
Subscribe to:
Posts (Atom)
