Autotrace Setup

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 29 14:46:26 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
/* Connect as a SYSDBA */

SQL> conn sys/password@oraprc as sysdba
Connected.



SQL> @E:\TestDB\sqlplus\admin\plustrce.sql

SQL> drop role plustrace;

SQL> create role plustrace;
Role created.

SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.

SQL> grant select on v_$statname to plustrace;
Grant succeeded.

SQL> grant select on v_$session to plustrace;
Grant succeeded.

SQL> grant plustrace to dba with admin option;
Grant succeeded.

SQL> set echo off
/* Grant the plustrace to pulic */

SQL> grant plustrace to public;
Grant succeeded.

/* Connect as a Demo */

SQL> conn demo/demo@oraprc
Connected.

SQL> set autotrace traceonly
SQL> select * from t where num = 2;
no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T'


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
219 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>

Read More

Explain Plan Setup

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 29 14:46:26 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
1. Creation of the plan table.
SQL> @E:\TestDB\rdbms\admin\utlxplan.sql
Table created.
SQL> create table t( num number(2));
Table created.
SQL> delete from plan_table;
0 rows deleted.
2. Collect the plan for SQL script.
SQL> explain plan for 2 select * from t where num = 2;
Explained.
3. View the Explain plan.

SQL> @E:\TestDB\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------
Id Operation Name Rows Bytes Cost
--------------------------------------------------------------------
0 SELECT STATEMENT
* 1 TABLE ACCESS FULL T
--------------------------------------------------------------------

Predicate Information (identified by operation id):---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------1 - filter("T"."NUM"=2)
Note: rule based optimization
14 rows selected.
SQL>

Read More

Solution for Dynamic String search

Script to implement it in Oracle:

Initial step:
SQL> conn sys/password@dba3 as sysdba

Connected.

SQL> GRANT create type TO Demo1;

Grant succeeded.

SQL> GRANT CREATE ANY PROCEDURE to Demo1;

Grant succeeded.

SQL> conn demo1/demo1@dba3;

Connected.create table product ( produt_name varchar2(50), version varchar2(20));

insert into product values('WinZip 6.3','6.3.0');
insert into product values('WinZip 8.0','8.0');
insert into product values('WinZip 8.1','8.1');
insert into product values ('UltraEdit 14.10','14.10');
insert into product values ('UltraEdit 15.0','15.0');
insert into product values ('PL/SQL Developer 5.0','5.0');
insert into product values ('PL/SQL Developer 5.1','5.1');
commit;

SQL> select PRODUT_NAME from product;

PRODUT_NAME --------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0
PL/SQL Developer 5.0
PL/SQL Developer 5.1

7 rows selected.
Dynamic String search:
Need to identify the list product names based on the dynamic parameter.

1. The first parameter is WinZip,UltraEdit. So the below script is having the two conditions in where clause.

SQL> select PRODUT_NAME from product
2 where PRODUT_NAME like 'WinZip%'
3 or PRODUT_NAME like 'UltraEdit%';

PRODUT_NAME
----------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0
2. The first parameter is WinZip,UltraEdit, PL/SQL Developer. So the below script is having the three Conditions in where clause.

SQL> select PRODUT_NAME from product
2 where PRODUT_NAME like 'WinZip%'
3 or PRODUT_NAME like 'UltraEdit%'
4 or PRODUT_NAME like 'PL/SQL Developer%';

PRODUT_NAME
------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0
PL/SQL Developer 5.0
PL/SQL Developer 5.1

7 rows selected.
Issue: if we need to search 10 products then we need to add 10 Conditions in where clause.

Solution for Dynamic String search:

SQL> create or replace type str2tblType as table of varchar2(30);
2 /
Type created.


SQL> create or replace function str2tbl( p_str in varchar2 ) return str2tblType
2 PIPELINED
3 as
4 l_str long default p_str ',';
5 l_n number;
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
11 l_str := substr( l_str, l_n+1 );
12 end loop;
13 return;
14 end;
15 /

Function created.
Pass the parameter as a CSV.
SQL> variable foo varchar2(50);
SQL> exec :foo := 'WinZip%,UltraEdit%';

PL/SQL procedure successfully completed.
SQL> select product.PRODUT_NAME from product ,
2 (select *
3 from table( cast( str2tbl(:foo) as str2tblType ) )
4 where rownum >= 0) x
5 where product.PRODUT_NAME like x.column_value;


PRODUT_NAME
-------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0



SQL> exec :foo := 'WinZip%,UltraEdit%,PL/SQL Developer%';

PL/SQL procedure successfully completed.

SQL> select product.* from product ,
2 (select *
3 from table( cast( str2tbl(:foo) as str2tblType ) )
4 where rownum >= 0) x
5 where product.PRODUT_NAME like x.column_value;

PRODUT_NAME
------------------
WinZip 6.3
WinZip 8.0
WinZip 8.1
UltraEdit 14.10
UltraEdit 15.0
PL/SQL Developer 5.0
PL/SQL Developer 5.1

7 rows selected.
Script to implement it in SQL Server:


BEGIN

DECLARE @value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint,
@sText varchar(8000),
@sDelim varchar(20)


/* Provide list of software names */
SET @sText = 'WinZip,UltraEdit'
SET @sDelim =','
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1



if (SELECT OBJECT_ID('tempdb..#retArray')) > 0
begin
Drop table #retArray
end

create table #retArray (value char(1000))

WHILE @bcontinue = 1
BEGIN
IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT #retArray (value)
VALUES ('%'+@value+'%')
END
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
END
ELSE
BEGIN
SET @value = @sText
BEGIN
INSERT #retArray ( value) VALUES ('%'+@value+'%')
END
SET @bcontinue = 0
END
END




select distinct title ProductTitle,
Publisher Vendor,
version Productversion
from Product a , #retArray b
where rtrim(ltrim(a.title )) like rtrim(ltrim(b.value))
order by title
END

Read More