Tuning time improvement sadly short of real improvement

Toad for Oracle & DevOps Toolkit
1

Good Morning Team,

    I suspect that there is some database privilege I'm missing that is causing this.  

    When I tune a SQL statement the tuner shows a factor of ten plus improvement (9.14 original, 0.81 for tuned), but when I run the code, all I get is a slight improvement (15 second original, 10 second tuned).  How can I get the automatic tuner to report fact, not fiction?

Larry Wickliffe

Performance and Tuning Services Lead, Performance Management Branch

Office of Information Systems and Technology (OIST)

Employment and Training Administration (ETA). US Department of Labor

Wickliffe.Larry@dol.gov: 202-693-3485

l.wickliffe@computer.org: 703-508-4345
Tuning fiction.docx (158 KB)

2

but when I run the code, all I get is a slight improvement (15 second original, 10 second tuned)

How are you timing this in Toad?

For F9 (single statement) execution:

The execution time shown in the Editor status bar and Query Viewer panel/window is the time it takes to initiate the execute within Toad and return. It does not include the creation of the session (if threaded queries is used) nor does it include population of the grid. It might include some of the network time transferring the data, at least for the initial fetch. It's not timing the actual execution on the database.

For F5 (script) execution:

The time will include the execute and possibly session creation and data fetch. If fetching is included then bear in mind script execution fetches all results whereas F9 fetches in blocks.

Perhaps SQL Optimizer is getting its data by some other means? If you're relying on the numbers shown in Toad you may want to find a more accurate method that looks at the execution time on the database and see if that aligns with the optimizer's figure. If you can't get anywhere close you you may have better luck posting on the
SQL Optimizer forum and asking them how timing is done. I suspect both sets of timing figures are accurate, but the methods differ.

Michael

On 11/10/2015 09:14 AM, wickliffe.larry wrote:

Tuning time improvement sadly short of real improvement

Thread created by wickliffe.larry
Good Morning Team,

   I suspect that there is some database privilege I'm missing that is causing this.  

    When I tune a SQL statement the tuner shows a factor of ten plus improvement (9.14 original, 0.81 for tuned), but when I run the code, all I get is a slight improvement (15 second original, 10 second tuned).  How can I get the automatic tuner to report fact, not fiction?

Larry Wickliffe

Performance and Tuning Services Lead, Performance Management Branch

Office of Information Systems and Technology (OIST)

Employment and Training Administration (ETA). US Department of Labor

Wickliffe.Larry@dol.gov: 202-693-3485

l.wickliffe@computer.org: 703-508-4345

Attachments:

Tuning fiction.docx

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

3

MIne 5c thoughts

There is a difference if you run sql on server or client (rows needs to be transported to your pc)

If you tune batch sql (run once) then between two runs you must

alter system flush shared_pool;

to ensure next run of sql do not read blocks in SGA.

So in mine opinion, you have second case problem.

However if you have some sql with binds in loop, then shared_pool is not important to flush.

Also ensure in a case of binds you use the same (or at least same set of binds). Keep in minda that some bind sets may result in non optimal plan … but for that you need to investigate far far deep.

4

Good Morning Michael,

    When tuning I'm looking at the Elapsed Time displayed in the automated tuning tool; before and after  tuning I run a script (F5) that selects the start time, does the timed select and then selects the end time:  After is usually the next day, to let the pool flush; but even right after tuning I do not get that  advertised improvement.  Since tuning usually begins right after I run the SQL for the before tuning time, I expect a faster time in the tool for original, which is what I see.  I'm tuning code for the warehouse which retrieves all records; I think my method is sound.  I was just hoping for similar success to the times advertised in the tuning tool.  Do you have any suggestions?

select to_char(SYSTIMESTAMP,‘HH24:MI:SSxFF’) as “Start” from dual;

– code to tune goes here ;

select to_char(SYSTIMESTAMP,‘HH24:MI:SSxFF’) as “End” from dual;

Larry

703-508-4945

5

Have you tried using SQL Optimizer directly instead of the Auto Optimize feature in Toad?

On Nov 13, 2015, at 8:23 AM, wickliffe.larry bounce-wickliffelarry@toadworld.com wrote:

RE: Tuning time improvement sadly short of real improvement

Reply by wickliffe.larry
Good Morning Michael,

    When tuning I'm looking at the Elapsed Time displayed in the automated tuning tool; before and after  tuning I run a script (F5) that selects the start time, does the timed select and then selects the end time:  After is usually the next day, to let the pool flush; but even right after tuning I do not get that  advertised improvement.  Since tuning usually begins right after I run the SQL for the before tuning time, I expect a faster time in the tool for original, which is what I see.  I'm tuning code for the warehouse which retrieves all records; I think my method is sound.  I was just hoping for similar success to the times advertised in the tuning tool.  Do you have any suggestions?

select to_char(SYSTIMESTAMP,'HH24:MI:SSxFF') as "Start" from dual;

-- code to tune goes here ;

select to_char(SYSTIMESTAMP,'HH24:MI:SSxFF') as "End" from dual;

Larry

703-508-4945

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

6

I think my method is sound. I was just hoping for similar success to the times advertised in the tuning tool. Do you have any suggestions?

Your method is timing much more than the statement execution. It's timing the execution time of your target statement plus the network time to fetch all of its results plus all time taken by Toad to process the results, build out the grids, and any other work Toad does to maintain the script environment. Your method is timing more accurately the time you experience during execution, but it does not accurately measure the execution time of the statement on the database. I bet that SQL Optimizer is more accurately timing the execution time. Time to retrieve and process results is dependent on non-execution factors like the tool you're using and the network conditions.

My knowledge of how you would go about accurately timing is limited, but I think something like the following block would get you a more accurate representation. It will time the statement and none of the Toad processing or n etwork operations. Execute with F9 and set the two bind vars as OUT params of VARCHAR2 type. From a quick search it looks like there are several other ways that would be more accurate than what you're doing now.

`DECLARE``

n NUMBER;

starttime VARCHAR2 (20);

endtime VARCHAR2 (20);

BEGIN

SELECT TO_CHAR (SYSTIMESTAMP, 'HH24:MI:SSxFF') AS "Start"

INTO starttime

FROM DUAL;

``

-- Your statement here

SELECT COUNT (*) INTO n FROM user_tables;

``

SELECT TO_CHAR (SYSTIMESTAMP, 'HH24:MI:SSxFF') AS "End"

INTO endtime

FROM DUAL;

``

:out_starttime := starttime;

:out_endtime := endtime;

END;

`

Michael

On 11/13/2015 08:23 AM, wickliffe.larry wrote:

RE: Tuning time improvement sadly short of real improvement

Reply by wickliffe.larry
Good Morning Michael,

    When tuning I'm looking at the Elapsed Time displayed in the automated tuning tool; before and after  tuning I run a script (F5) that selects the start time, does the timed select and then selects the end time:  After is usually the next day, to let the pool flush; but even right after tuning I do not get that  advertised improvement.  Since tuning usually begins right after I run the SQL for the before tuning time, I expect a faster time in the tool for original, which is what I see.  I'm tuning code for the warehouse which retrieves all records; I think my method is sound.  I was just hoping for similar success to the times advertised in the tuning tool.  Do you have any suggestions?

select to_char(SYSTIMESTAMP,'HH24:MI:SSxFF') as "Start" from dual;

-- code to tune goes here ;

select to_char(SYSTIMESTAMP,'HH24:MI:SSxFF') as "End" from dual;

Larry

703-508-4945

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

7

Another thing you could to do time it is check this box:

20151113092613.jpeg

Run it with F9. After it runs, Toad will pull load the trace file into the Trace File Browser (if you have the DBA Module) where you can see all kinds of timings of your statement. Parse time, execute time, fetch time, and wait time.

If you don’t have the DBA Module then we’ll run the trace file through TKProf for you.

8

Thank you Everyone!

         I marked John's answer as the correct answer as it shows me the timing at the database; not the network to my workstation.  

    All your answers were helpful!  Thanks!!!

    Although Dell SQL Optimizer for Oracle provides the same information as TOAD (better numbers than my workstation can achieve).  Dell SQL Optimizer for Oracle  will run with large queries, since it does not bring the results to my workstation.  Thank you Gregory!

Larry

703-508-4945

9

I marked John's answer as the correct answer as it shows me the timing at the database

When you open a cursor this is first record. When you reach to end of loop in PLSQL you have timings exactly that you want-in database and not included network!

Please you must be more clear because it is hard to understand.

Keep in min taht first record and all records may depend verry.