[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

Oracle SLQ Timing Stats

We have been having some issues with our Oracle performance.  To keep an eye on true system performance and not to rely on end-user perception, I am trying to write a procedure that will read a table populated with select statements, record the system timestamp, process the select statement, record another system timestamp and the write the comparison to a table.  Everything is functional as designed except I am not getting times back (or at least the precision that I need).  I have attached my code, what am I doing wrong?  As you can see, I had a loop in there to try to increase the time span to make the difference more dramatic.  The table that it writes to have three columns:

desc cmi_perf_time
Name                          
SELECTSTMT - VarChar2(2000), RUNTIME - Date, Timer - INTERVAL Day(3) TO SECOND (9)
create or replace
PROCEDURE CMI_PERFTIMER
AS
  s    timestamp(9);
  e    timestamp(9);
  diff    interval day(3) to second(9);
 
  Hold VARCHAR2(2000);
  CURSOR SelectString
  IS
    SELECT selectstmt FROM cmi_perf_select;
BEGIN
  OPEN selectstring;
  FETCH selectstring INTO hold;
  
  WHILE selectstring%found
  LOOP
    s:=to_timestamp(sysdate);
--    for i in 1..3000 loop
    EXECUTE IMMEDIATE 'select count(*) from (' || hold || ')';
--    end loop;
    e:=to_timestamp(sysdate);
    diff:=e-s;
    INSERT INTO CMI_PERF_TIME VALUES
      (hold, systimestamp, diff
      );
    COMMIT;
    FETCH selectstring INTO hold;
  END LOOP;
END CMI_PERFTIMER;

Open in new window

0
tnowacoski
Asked:
tnowacoski
  • 4
  • 4
1 Solution
 
sdstuberCommented:
don't use

to_timestamp(sysdate)

use

  sys_timestamp
0
 
sdstuberCommented:
oops

systimestamp

no "_"
0
 
tnowacoskiAuthor Commented:
sdstuber,

I had systimestamp in there originally and it didn't make a difference.  I assume these selects are running subsecond but this should be granular enough.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tnowacoskiAuthor Commented:
If I enable the 3000x loop, I can see a subsecond response.  However, I can run one of these same selects through SQL Developer and get a .97311 second response.  I would figure that even with Execute Immediate, I would be able to record some variance between start and finish.
0
 
sdstuberCommented:
it works for me,  however your test isn't a valid way to check your query performance.


select count(*) from (--some query--)

does not mean your inner query will be executed just as it is by a user and then counted.
Oracle will evaluate the entire statement  of outer and inner together and derive a new plan for that query.

so, your count times may be very must faster than your end user times

you also may be get some distortion from parsing your dynamic sql making your counting slower too.

how those faster/slower factors will cancel each other will depend on the queries and objects
0
 
tnowacoskiAuthor Commented:
What would be a better way to "spot check" some queries on a scheduled basis and record that in a table to analysis?
0
 
tnowacoskiAuthor Commented:
What I am really looking for is a baseline that can tell me how the system is behaving in general.  It does not need to mirror the exact results that the users are seeing (as long as it increases and decrease consistently with their experience).
0
 
sdstuberCommented:
For 9i, I recommend using statspack with 10 minute snapshots.

That way if a user complains, even in hindsight ("It was bad yesterday, but not as bad today, but I was too busy to call you yesterday")

and you'll be capturing "real" activity, not a simulation.

and realistically,  the only performance you really need to worry about is that of your users.  after all, who cares how the system is when nobody is using it?
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now