Home » RDBMS Server » Performance Tuning » Statspack
Statspack [message #303855] Mon, 03 March 2008 00:50 Go to next message
sairajesh
Messages: 7
Registered: April 2007
Junior Member
Does StatsPack provide SQL timestamps (The time when the SQL was executed)
Re: Statspack [message #303865 is a reply to message #303855] Mon, 03 March 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No. And nothing gives that information.

Regards
Michel
Re: Statspack [message #303872 is a reply to message #303865] Mon, 03 March 2008 01:23 Go to previous messageGo to next message
sairajesh
Messages: 7
Registered: April 2007
Junior Member
Thanks Mich
Re: Statspack [message #305060 is a reply to message #303872] Fri, 07 March 2008 21:52 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Michel, is that right? Pls correct me if I am wrong
SQL> set timing on
SQL> create table test as select * from all_objects
  2  where object_id=100;

Table created.

Elapsed: 00:00:01.37 <<--
SQL> select * from test;

OWNER                          OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
--------- --------- ------------------- ------- - - -
SYS                            I_TYPED_VIEW1
                                      100            100 INDEX
31-JAN-08 31-JAN-08 2008-01-31:17:18:24 VALID   N N N


Elapsed: 00:00:00.09 <<--
SQL>

Thank you!
Re: Statspack [message #305067 is a reply to message #305060] Sat, 08 March 2008 00:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the clock time elapsed between you hit "enter" and SQL*Plus gave you the last row.
This is not "the time when the SQL was executed".

Regards
Michel
Re: Statspack [message #305096 is a reply to message #303855] Sat, 08 March 2008 07:17 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Sql must have executed within this time .
Re: Statspack [message #305103 is a reply to message #305096] Sat, 08 March 2008 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sql must have executed within this time .

What does this mean? Which time are you talinkg about?

Regards
Michel
Re: Statspack [message #305109 is a reply to message #305096] Sat, 08 March 2008 10:13 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Output of "set time on" is not a reliable calibrated measure even in a single user environment. This is been demonstrated again and again in our forums.
Paraphrasing Michell, it is just a timer we see in our "environment" (that includes all the network traffic) and has nothing to do with actual SQL start time or time sql took to complete
Re: Statspack [message #305216 is a reply to message #303855] Mon, 10 March 2008 03:17 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Quote:
What does this mean? Which time are you talinkg about?


The difference between the time user hit enter and oracle retrieves the row.
Isn't that difference is the time of query execution?
Re: Statspack [message #305218 is a reply to message #305216] Mon, 10 March 2008 03:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But is this "The time when the SQL was executed"?

Regards
Michel
Re: Statspack [message #305235 is a reply to message #305218] Mon, 10 March 2008 03:51 Go to previous messageGo to next message
trantuananh24hg
Messages: 668
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Quote:

The time when the SQL was executed



Correct me if I am wrong to understand this statement

The time when the SQL was executed are not same in twice time. If it was parsed by first time, this is hard parse, for the second, this is soft parse. Of course, the second time is always faster than one.

Can we get the time when one statement was executed by ELAPSED_TIME colum in v$sql?
SQL>select sql_id, elapsed_time from v$sql
where sql_id like '5%'
/
SQL_ID        ELAPSED_TIME
------------- ------------
5j35wpppf003m         6386
5rh0tbv3d80bh       574401
5f4fxcnw8h15g        17190
5rgfh8a8bc1n1        28775
5mpk55t8bw1y7       487774
5apa5r99y027p       593894
5dgt7zg1bc2ur         7769


Thank you!

[Updated on: Mon, 10 March 2008 03:53]

Report message to a moderator

Re: Statspack [message #305525 is a reply to message #305235] Tue, 11 March 2008 03:28 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think we're having some language problems here.

The phrase 'The time when the SQL was executed' would naturally refer to the time shown on a clock at which the SQL statement was sent to the database for execution.

Several people seem to be using this phrase to mean 'The time the Sql took to execute'
Previous Topic: IMPROVE PERFORMANCE WHEN INSERT RECORDS
Next Topic: Query takes too much time
Goto Forum:
  


Current Time: Sat Dec 10 06:55:40 CST 2016

Total time taken to generate the page: 0.08623 seconds