Feb 21, 2009

Performance Tuning without Toad ie(In oracle)

DECLARE
my_task_name VARCHAR2(30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select golflink,course_name,x,y from (select b.golflink,b.course_name,( select distinct sdo_geom.sdo_min_mbr_ordinate(a.sdo_geometry,1) x
from username.golf_ground a where b.golflink=a.golflink and rownum=1 ) x
,( select distinct sdo_geom.sdo_min_mbr_ordinate(a.sdo_geometry,2) y
from username.golf_ground a where b.golflink=a.golflink and rownum=1 ) y
from username.golf_area b ) where x is not null and y is not null';

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
bind_list => null,--sql_binds(anydata.ConvertNumber(0)),
user_name => 'USER',
scope => 'COMPREHENSIVE',
time_limit => 10,
task_name => 'RuR',
description => 'Task to tune a query on a specified employee');
END;



SELECT * FROM DBA_ADVISOR_LOG where task_name='RuR'




BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'RuR' );
END;




SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'RuR'



SELECT * FROM V$ADVISOR_PROGRESS



SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'RuR') FROM DUAL;

By executing this query the details including explain-plan will be listed whether indexes to be added or not


BEGIN
DBMS_SQLTUNE.drop_tuning_task( task_name => 'RuR' );
END;

Feb 11, 2009

Rownum between

select rin from (select rownum as rin
from floor_address where rownum <= 5)
where rin between 2 and 4

Flashback Query

Flashback Query in 9i
---------------

/*In 9i flashback is for User level (ie : Schema level)*/

CONN SYS
GRANT EXECUTE ON dbms_flashback TO user;

CONN TO USER

EXEC DBMS_FLASHBACK.ENABLE_AT_TIME(TO_TIMESTAMP('31-JAN-2009:115427','DD-MON-YYYY:HH24MISS'))

SELECT * FROM ......

1)WITHOUT DISABLING DBMS_FLASHBACK WE CANT DO ANY TRANSACTIONS
2)U CANNOT GET DATA FROM A TRUNCATED TABLE

EXEC DBMS_FLASHBACK.DISABLE; --After this only DML transactions can be done

--------------------------------------------------------------------------------
Flashback Query in 10g
----------------

/*In 10g flashback is for Table level */

ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

FLASHBACK TABLE scott.emp
TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '05' minute);

Split values ;///; Pyramid

To split values

CREATE OR REPLACE Function Tokenizer(p_string In VarChar2,
p_separators In VarChar2)
Return varchar2_table Pipelined
Is
v_strs dbms_sql.varchar2s;
Begin
/*SELECT Tokenizer('1E030N,898989,6565,3535,67676',',') FROM DUAL;*/
With sel_string As (Select p_string fullstring From dual)
Select substr(fullstring, beg+1, end_p-beg-1) token
Bulk Collect Into v_strs
From (Select beg, Lead(beg) Over (Order By beg) end_p, fullstring
From (Select beg, fullstring
From (Select Level beg, fullstring
From sel_string
Connect By Level <= length(fullstring)
)
Where instr(p_separators,substr(fullstring,beg,1)) >0
Union All
Select 0, fullstring
From sel_string
Union All
Select length(fullstring)+1, fullstring
From sel_string)
)
Where end_p Is Not Null
And end_p > beg + 1;
For i In v_strs.first..v_strs.last Loop
PIPE ROW(v_strs(i));
End Loop;
RETURN;
End Tokenizer;

----------------------------------------------------------------
Check this query

select wmsys.wm_concat(case when Rn <=46 then Rn end) over(order by Rn) str
from (select RowNum as rn from all_catalog where RowNum <= 46);

Standard objects in Oracle ;///; Generate Series

To get the list of standard objects in Oracle

SQL> desc sys.standard;

-----------------------------------
To generate Series


CREATE OR REPLACE FUNCTION generate_series ( p_start number, p_end number, p_step number )
RETURN number
AS
BEGIN
/* select generate_series(10,10,9) from dual*/
DECLARE
v_i number;
begin
v_i := CASE WHEN p_start IS NULL THEN 1 ELSE p_start END;

DECLARE v_step number;
begin
v_step := CASE WHEN p_step IS NULL OR p_step = 0 THEN 1 ELSE p_step END;

DECLARE
v_terminating_value number;
begin
v_terminating_value := p_start + ABS( p_start- p_end) / ABS( v_step) * v_step;

-- Check for impossible combinations
IF NOT ( ( p_start > p_end AND SIGN( p_step) = 1 )
OR
( p_start < p_end AND SIGN( p_step) = -1 )) then

-- INSERT INTO Integers ( [IntValue] ) VALUES ( v_i )
IF ( v_i = v_terminating_value ) then

v_i := v_i + v_step;
end if;
end if;

RETURN v_i;
END;
END ;
END;
end ;

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