CPU Time

[Prabhu_7]

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

[Robert-Jan Goossens]

Hi,

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

# man time

Regards,
Robert-Jan

[Jean-Luc Oudart]

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

real 1.0
user 0.0
sys 0.0


man time

Regards,
jean-Luc

[Maarten van Rossen]

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

[Jean-Luc Oudart]

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

[Hein van den Heuvel]

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

[T G Manikandan]

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.