SQL Optimizer "run on server option" how to emulate in SQL*Plus

SQL Optimizer SQL Optimizer for Oracle
1

Hi

I would like to check results from SQL Optimizer.

When I use “run on server” option where timing is only important, how can I write SQL and run in plain Oracle SQL*Plus to see the same results … I mean is there any ALTER SESSION in behind which must be place to achieve the same behavior …
I mean I could trace the session and see but think this question may be answered more quickly with some deeper explanation.

Another need for this is when I have 100 values (binds) and want to run in SQL*Plus to see timings and not to start 100 times SQL Optimizer with different bind value.

Thx in advance,
Damir

2

Here the following is an simulation of run on server, you can create cursor of your SQL and use loop to fetch each record without sending to client or spooling into harddisk. Of course, this approach will introduce overhead to the SQL, but it is ok for comparison purpose of the multiple semantically equivalent SQL statements.

If you want to test many bind values, you can change the “V1:=120000”, you should be awared if the SQL is bind sensitive or not. It is all depends on how you use your SQL. If it is a statics SQL cursor, it will be not bind sensitive. If you use it as a dynamic SQL/cursor, it will become bind sensitive.

Static SQL cursor

=======================

declare

fetch_time number;

start_time number;

stop_time number;

row_count number;

nomorerow boolean;

V1 NUMBER(6);

Col1 NUMBER(9);

Col2 VARCHAR2(60);

Col3 VARCHAR2(45);

Col4 DATE;

Col5 VARCHAR2(10);

Col6 VARCHAR2(40);

Col7 VARCHAR2(15);

Col8 VARCHAR2(7);

Col9 NUMBER(6);

Col10 DATE;

Col11 NUMBER(8,2);

Col12 VARCHAR2(1);

Col13 NUMBER(3);

Col14 NUMBER(2,2);

Col15 VARCHAR2(50);

Col16 VARCHAR2(50);

Col17 VARCHAR2(50);

Col18 VARCHAR2(50);

CURSOR C1 is select * from employee where emp_id < V1;

begin

V1:=120000;

start_time := SYS.DBMS_UTILITY.GET_TIME;

OPEN C1;

FETCH C1 INTO

Col1,

Col2,

Col3,

Col4,

Col5,

Col6,

Col7,

Col8,

Col9,

Col10,

Col11,

Col12,

Col13,

Col14,

Col15,

Col16,

Col17,

Col18;

fetch_time := SYS.DBMS_UTILITY.GET_TIME;

LOOP

FETCH c1 INTO

Col1,

Col2,

Col3,

Col4,

Col5,

Col6,

Col7,

Col8,

Col9,

Col10,

Col11,

Col12,

Col13,

Col14,

Col15,

Col16,

Col17,

Col18;

EXIT WHEN c1%NOTFOUND;

END LOOP;

stop_time := SYS.DBMS_UTILITY.GET_TIME;

row_count := c1%ROWCOUNT;

nomorerow := c1%NOTFOUND;

CLOSE C1;

:response_time := fetch_time - start_time;

:elapsed_time := stop_time - start_time;

:row_count := row_count;

if nomorerow then

:nomorerow := 1;

else

:nomorerow := 0;

end if;

exception

when others then

if C1%ISOPEN then

CLOSE C1;

end if;

raise;

end;

Dynamic SQL cursor

====================

declare

fetch_time number;

start_time number;

stop_time number;

row_count number;

nomorerow boolean;

V1 NUMBER(6);

Col1 NUMBER(9);

Col2 VARCHAR2(60);

Col3 VARCHAR2(45);

Col4 DATE;

Col5 VARCHAR2(10);

Col6 VARCHAR2(40);

Col7 VARCHAR2(15);

Col8 VARCHAR2(7);

Col9 NUMBER(6);

Col10 DATE;

Col11 NUMBER(8,2);

Col12 VARCHAR2(1);

Col13 NUMBER(3);

Col14 NUMBER(2,2);

Col15 VARCHAR2(50);

Col16 VARCHAR2(50);

Col17 VARCHAR2(50);

Col18 VARCHAR2(50);

C1 SYS_REFCURSOR;

LONGSQL CLOB;

begin

V1:=120000;

start_time := SYS.DBMS_UTILITY.GET_TIME;

LONGSQL := ‘’;

LONGSQL := LONGSQL || 'select * ';

LONGSQL := LONGSQL || ’ from employee ';

LONGSQL := LONGSQL || ’ where emp_id < :var’;

OPEN C1 FOR LONGSQL

USING V1 ;

FETCH C1 INTO

Col1,

Col2,

Col3,

Col4,

Col5,

Col6,

Col7,

Col8,

Col9,

Col10,

Col11,

Col12,

Col13,

Col14,

Col15,

Col16,

Col17,

Col18;

fetch_time := SYS.DBMS_UTILITY.GET_TIME;

LOOP

FETCH c1 INTO

Col1,

Col2,

Col3,

Col4,

Col5,

Col6,

Col7,

Col8,

Col9,

Col10,

Col11,

Col12,

Col13,

Col14,

Col15,

Col16,

Col17,

Col18;

EXIT WHEN c1%NOTFOUND;

END LOOP;

stop_time := SYS.DBMS_UTILITY.GET_TIME;

row_count := c1%ROWCOUNT;

nomorerow := c1%NOTFOUND;

CLOSE C1;

:response_time := fetch_time - start_time;

:elapsed_time := stop_time - start_time;

:row_count := row_count;

if nomorerow then

:nomorerow := 1;

else

:nomorerow := 0;

end if;

exception

when others then

if C1%ISOPEN then

CLOSE C1;

end if;

raise;

end;

3

Richard,

Thank you. Just have a question when DML SQL’s are in question…is there something else to think about (except rollback of action and time to wait that rollback takes place)?
Btw, from your perspective, what would be the most efficient way to do that (forcing the rollback) ?

Brg

Damir

4

Unless your DML is re-executable without validation of the data integrity, I don’t find any safe way to do that without rollback. But for INSERT statement, you can build a temp table as the targeted insert table and drop it after insert.

5

Ok I understood all.

Brg
Damir