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.