topic Re: CPU Time in Operating System - HP-UX

CPU Time

Prabhu_7 — Wed, 11 Feb 2004 10:49:43 GMT

Hi,

I'm using unix shell script , which calls series of Oracle SQL scripts to load many tables.I want to know the exact CPU time for
each SQL query. Can anyone help me in writing the Shell script for this.

Thanks

Re: CPU Time

Robert-Jan Goossens — Wed, 11 Feb 2004 10:55:45 GMT

Hi,

How about the time command, or do you need more specified info.

# man time

Regards,
Robert-Jan

Re: CPU Time

Jean-Luc Oudart — Wed, 11 Feb 2004 10:56:32 GMT

for a script
time <scriptname>
e.g.
# time sleep 1

real 1.0
user 0.0
sys 0.0

man time

Regards,
jean-Luc

Re: CPU Time

Maarten van Rossen — Wed, 11 Feb 2004 10:57:25 GMT

You could get a estimated with time or timex
but i don't think you can time the exact cpu time of the oracle processes this way.
It should be enough to compare though

Re: CPU Time

Jean-Luc Oudart — Wed, 11 Feb 2004 11:13:41 GMT

Also
in SQLPLUS
set timing on
gives the command elapsed time.

SET
TIMI[NG] {ON|OFF}
Controls the display of timing statistics. ON displays timing statistics on each SQL command or PL/SQL block run. OFF suppresses timing of each command. For information about the data SET TIMING ON displays, see the Oracle installation and user's manual(s) provided for your operating system. Refer to the TIMING command for information on timing multiple commands

Regards,
Jean-Luc

Re: CPU Time

Hein van den Heuvel — Wed, 11 Feb 2004 22:53:22 GMT

I'm afraid you can not get there from here... with a shell script. Sure you can pick up how much time the process with sqlplus takes, but that is just a modest amount of parsing and argument marshalling leading a call to ORACLE (SQLnet) where the real work will be done in a slave task. It is rather tricky to associate the right Oracle slave with your sqlplus. The easy/better way to find that is to ask Oracle which processes are associated.. and even then you could not sccount for the CPU time executed in teh Database Writer and Logwriter on hehalve of the sqlplus task.

I recently posted a sample client-server report script in: http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=433109
That may serve as a starting point.

But I suppose that while you are in SQL, you might as well use SQL to ask Oracle how much CPUtime it recorded for the session!

Hope this helps some,
Hein.

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=433109

Re: CPU Time

T G Manikandan — Wed, 11 Feb 2004 23:17:57 GMT

The CPU elapsed time calculated from the application side will be more useful.

you can find that by

1.Enabling the SQL_TRACE for the instance
Using TKPROF to find out the CPU used seconds for the query.