Home » SQL & PL/SQL » Client Tools » spool only time and stats (oracle 11g r2)
spool only time and stats [message #455001] Sun, 09 May 2010 16:53 Go to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
Hi, im spooling the results of some performance tests to a text file,

the test script im using calls various sql statements in other scripts and spools to the results, stats and time to the same file...

Howver I dont want to spool all the results... i only want the stats and time.. how can i do this?

my script looks like:

=====================================================
SET TERMOUT OFF
SET AUTOTRACE ON STAT

SPOOL f:/testresults.txt

Timing START timer1
START c:/reqdetail
Timing STOP timer1

Timing START timer2
START c:/nurseavail
Timing STOP timer2

SET TERMOUT ON
SET AUTOTRACE OFF
SPOOL off
======================================================
Re: spool only time and stats [message #455002 is a reply to message #455001] Sun, 09 May 2010 16:55 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

> i only want the stats and time.. how can i do this?
Then only print those values

[Updated on: Sun, 09 May 2010 16:58]

Report message to a moderator

Re: spool only time and stats [message #455004 is a reply to message #455002] Sun, 09 May 2010 17:04 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
thanks, your so helpful, how exactly do i do that? hence the question....
Re: spool only time and stats [message #455005 is a reply to message #455004] Sun, 09 May 2010 17:17 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
sqlplus -H
sqlplus -S
Re: spool only time and stats [message #455006 is a reply to message #455005] Sun, 09 May 2010 17:28 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
what?!?

where / how / what do i need to put in the script that i posted in my first post?
Re: spool only time and stats [message #455007 is a reply to message #455006] Sun, 09 May 2010 17:29 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
so that it only spools stats and time for each test, currently it spools results, stats and time
Re: spool only time and stats [message #455008 is a reply to message #455007] Sun, 09 May 2010 17:36 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
bcm@bcm-laptop:~$ cat testresults.txt

PL/SQL procedure successfully completed.

timing for: timer1
Elapsed: 00:00:01.03

PL/SQL procedure successfully completed.

timing for: timer2
Elapsed: 00:00:02.00

PL/SQL procedure successfully completed.

timing for: timer4
Elapsed: 00:00:04.00

Re: spool only time and stats [message #455009 is a reply to message #455008] Sun, 09 May 2010 17:41 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
what??

The first post i made, shows a test script im using.... the script is spooling the results, stats and time for each query....

I only want to spool stats and time ... how do i ammend the script i posted to do this....?

Do you understand what I mean?
Re: spool only time and stats [message #455010 is a reply to message #455009] Sun, 09 May 2010 17:42 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
spooling to a text file that is... see the script i posted in my first post
Re: spool only time and stats [message #455011 is a reply to message #455010] Sun, 09 May 2010 17:57 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
Does anybody else know how to do this?
Re: spool only time and stats [message #455012 is a reply to message #455011] Sun, 09 May 2010 19:20 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
After asking on another forum, apparently it CANT be done.
Re: spool only time and stats [message #455013 is a reply to message #455012] Sun, 09 May 2010 21:57 Go to previous messageGo to next message
rleishman
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Have you tried
SET AUTOTRACE TRACEONLY


Ross Leishman
Re: spool only time and stats [message #455127 is a reply to message #455001] Mon, 10 May 2010 06:37 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
Ahh, yes that works, however it includes an explain plan for each tesr...

Can you tell me why on the explain plan it says...

Note
----
- 'PLAN_TABLE' is old version
Re: spool only time and stats [message #455128 is a reply to message #455127] Mon, 10 May 2010 06:39 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
Sorry i mean an execution plan... (is this the same as an explain plan?)

[Updated on: Mon, 10 May 2010 06:39]

Report message to a moderator

Re: spool only time and stats [message #455132 is a reply to message #455127] Mon, 10 May 2010 06:58 Go to previous messageGo to next message
Littlefoot
Messages: 21493
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
dsub42

'PLAN_TABLE' is old version

It means what is says - you use an old version of the PLAN_TABLE table (i.e. its description differs from a "new" one).

Drop it and create a new one. Script is here:
oracle_home/rdbms/admin/utlxplan.sql
Re: spool only time and stats [message #455137 is a reply to message #455132] Mon, 10 May 2010 07:33 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
Im still unsure what you mean, ... whats the difference between an old and a new plan table?... does it mean like old formatting? and oracle havedeveloped a new one?

also

Im running about 50 different tests, I want to clear the SGA and all caches after each test to get a 'fresh' performance time.

I have been using the command : ALTER SYSTEM FLUSH BUFFER_CACHE; .... however when i do this im still getting shorter times for quieries than if i do a startup / shutdown..... whats the best way to clear everything via SQL?
Re: spool only time and stats [message #455138 is a reply to message #455137] Mon, 10 May 2010 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 66516
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
whats the difference between an old and a new plan table?... does it mean like old formatting? and oracle havedeveloped a new one?

Yes, it means that. What about reading the documentation?

Quote:
all caches after each test to get a 'fresh' performance time.

And totally artificial situation that was clean for your tests but means nothing for the real production database.

You canNOT make performances test on unitary process.

Regards
Michel
Re: spool only time and stats [message #455139 is a reply to message #455137] Mon, 10 May 2010 07:55 Go to previous messageGo to next message
Littlefoot
Messages: 21493
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What I mean is that - through many years - description of the PLAN_TABLE was changed.

This is 1998 version:
create table PLAN_TABLE (
	statement_id 	varchar2(30),
	timestamp    	date,
	remarks      	varchar2(80),
	operation    	varchar2(30),
	options       	varchar2(30),
	object_node  	varchar2(128),
	object_owner 	varchar2(30),
	object_name  	varchar2(30),
	object_instance numeric,
	object_type     varchar2(30),
	optimizer       varchar2(255),
	search_columns  number,
	id		numeric,
	parent_id	numeric,
	position	numeric,
	cost		numeric,
	cardinality	numeric,
	bytes		numeric,
	other_tag       varchar2(255),
	partition_start varchar2(255),
        partition_stop  varchar2(255),
        partition_id    numeric,
	other		long,
	distribution    varchar2(30));

This is 2004 version:
create table PLAN_TABLE (
        statement_id       varchar2(30),
        plan_id            number,
        timestamp          date,
        remarks            varchar2(4000),
        operation          varchar2(30),
        options            varchar2(255),
        object_node        varchar2(128),
        object_owner       varchar2(30),
        object_name        varchar2(30),
        object_alias       varchar2(65),
        object_instance    numeric,
        object_type        varchar2(30),
        optimizer          varchar2(255),
        search_columns     number,
        id                 numeric,
        parent_id          numeric,
        depth              numeric,
        position           numeric,
        cost               numeric,
        cardinality        numeric,
        bytes              numeric,
        other_tag          varchar2(255),
        partition_start    varchar2(255),
        partition_stop     varchar2(255),
        partition_id       numeric,
        other              long,
        distribution       varchar2(30),
        cpu_cost           numeric,
        io_cost            numeric,
        temp_space         numeric,
        access_predicates  varchar2(4000),
        filter_predicates  varchar2(4000),
        projection         varchar2(4000),
        time               numeric,
        qblock_name        varchar2(30),
        other_xml          clob
);

As you can see, several columns have been added. Oracle found out that your PLAN_TABLE version is an "old" one and notified you. You can use it, but you won't see all the information. So - drop an old one and create a new one.
Re: spool only time and stats [message #455140 is a reply to message #455138] Mon, 10 May 2010 07:58 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
YES I HAVE READ THE DOCUMENTATION, IM GETTING CLARIFICATION ON HERE!

it needs to be fresh, because I am only asssing the value of indexing (for academic purposes only)

what do you mean by unitary process?

before each test im running the following 2 commands...

ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH SHARED_POOL;

does this not mean that the database is in a clean state (as so the query has never been ran before)
Re: spool only time and stats [message #455141 is a reply to message #455140] Mon, 10 May 2010 07:59 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
thanks littlefoot, thats what i was asking...

any idea about the clearing of cache's?
Re: spool only time and stats [message #455142 is a reply to message #455141] Mon, 10 May 2010 08:02 Go to previous messageGo to next message
cookiemonster
Messages: 13648
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why do you want to clear the cache?
Seems pointless - when it runs in production you won't ever get an empty cache.
If you want timings for queries the best way is to run it several times against a representative sample of data.
Leave the cache alone.
Re: spool only time and stats [message #455143 is a reply to message #455142] Mon, 10 May 2010 08:06 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
because im testing a query on a table that does not use indexing ... collecting the times

then im indexing the table and testing the query again .... collecting the time

so that I can observe the performance increase that the indexes give...

if i run the query on a table without indexes, add indexes and then run a second test on the query.. the result will be biaest will in not? .. as the data / execution plans will be cached and not show the true performance increase of a table with an index compared to a table without..

[Updated on: Mon, 10 May 2010 08:06]

Report message to a moderator

Re: spool only time and stats [message #455144 is a reply to message #455143] Mon, 10 May 2010 08:22 Go to previous messageGo to next message
cookiemonster
Messages: 13648
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well if you run it several times without indexes the data/plans will get cached and you can get the average time for that.
Then add the indexes and collect stats.
Then run again several times and get the average with indexes.
No need / point in clearing the cache.
Re: spool only time and stats [message #455145 is a reply to message #455001] Mon, 10 May 2010 08:32 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
but for example lets say...

i run the following statement:

select * from emp where empid = 111

i run it the first time and the time is 00:01:99

i run the second and third time and the time is 00:00:05

then I add an index to empid and run the query again...

the time is going to be 00:00:05 again is it not (because plans and data are cached - oracle simply retrieves them again)

however if i flush the cache before the index test...

time is 00:00:99

so... the difference between not having an index and having an index on empid is 00:01:99 / 00:00:99

not flushing the cache i will not be able to see the benefit of having an index the first time the query is ran?

do you see what i mean?
Re: spool only time and stats [message #455148 is a reply to message #455145] Mon, 10 May 2010 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 66516
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Elapsed time is meaningless, you choose the wrong statistics.

Regards
Michel
Re: spool only time and stats [message #455149 is a reply to message #455148] Mon, 10 May 2010 08:57 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
what?? what do you mean?

do you keep giving me these vague answers on purpose? is what your saying suppost to be helpful? .. or do you do this on purpose micheal?

neither does your post answer the question im asking or go anywhere near helping... it sets out to make the situation worse!... and you keep following me round the forum doing it to me!

Thanks alot

Regards

Matt

[Updated on: Mon, 10 May 2010 09:05]

Report message to a moderator

Re: spool only time and stats [message #455150 is a reply to message #455149] Mon, 10 May 2010 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 66516
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But it is true.
You concentrate in elapsed on stand alone query with no activity on empty caches, that is another way to say you are wasting your time.
For you, what is the purpose of this?

Regards
Michel
Re: spool only time and stats [message #455154 is a reply to message #455150] Mon, 10 May 2010 09:13 Go to previous messageGo to next message
cookiemonster
Messages: 13648
Registered: September 2008
Location: Rainy Manchester
Senior Member
To clarify that point further you are getting metrics for a scenario which will never happen on a production machine.
Re: spool only time and stats [message #455155 is a reply to message #455001] Mon, 10 May 2010 09:17 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
but if i dont clear the cache after the no-index test

am i not just going to get back results based on cached execution plans and cached date..

therefore i wont be testing the improvement that the index deliverss?

or when adding an index and run the tests again does it not use the execution plans and cached data?
Re: spool only time and stats [message #455156 is a reply to message #455155] Mon, 10 May 2010 09:21 Go to previous messageGo to next message
cookiemonster
Messages: 13648
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you run it without indexes and ignore the 1st result then you'll get the result allowing for caching.
If you then add the index, gather the stats and re-run the query and ignore the 1st result you'll get the result with indexes allowing for caching.
If you add an index and gather stats oracle should ditch the existing plan and come up with a new one, the data blocks will still be cached though.
Compare cached to cached in other words.
Still doesn't allow for multi-user but is better than nothing.
Re: spool only time and stats [message #455158 is a reply to message #455156] Mon, 10 May 2010 09:27 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
Thanks cookiemonster, that makes sense.

Im just learning so appologise if im not being clear.

Now I have one more question, "gathering stats" i presumed that this was the table that im spooling to the textfile after the query...

Statistics
----------------------------------------------------------
1877 recursive calls
0 db block gets
698 consistent gets
75 physical reads
0 redo size
104431 bytes sent via SQL*Net to client
2715 bytes received via SQL*Net from client
211 SQL*Net roundtrips to/from client
49 sorts (memory)
0 sorts (disk)
3141 rows processed

You seem to be refering to gathering the statistics prior to running the index tests... i presume i do this by the following command

analyze table mytable compute statistics...

I have done this but noticed that it does not output anything?... i the purpose in doing this simply just so that oracle develops a new execution plan based upon the table which now has indexes used on it?

i really appriciate your help here.

[Updated on: Mon, 10 May 2010 09:28]

Report message to a moderator

Re: spool only time and stats [message #455160 is a reply to message #455158] Mon, 10 May 2010 09:31 Go to previous messageGo to next message
cookiemonster
Messages: 13648
Registered: September 2008
Location: Rainy Manchester
Senior Member
You should be using the dbms_stats package not analyze table - that's the old way of doing it.
Also bear in mind that if you don't have a lot of data in the table oracle will ignore the index anyway.
Re: spool only time and stats [message #455162 is a reply to message #455160] Mon, 10 May 2010 09:36 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
ok, is that what you mean by get the statistics though prior to the index tests? .. it simply makes oracle re-calculate its statitsics based on the new indexes etc (is there anything that can be output from this?)

Re: spool only time and stats [message #455164 is a reply to message #455162] Mon, 10 May 2010 09:39 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
also, i ahve been trying to find out how to use the dbms_stats package, looking at the following link:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1036276

However I am totally baffled....

how do i run the stats this way for a table called 'mytable'? ... is it not doing exactly the same thing as saying... analyze table 'mytable' compute statistics?
Re: spool only time and stats [message #455168 is a reply to message #455164] Mon, 10 May 2010 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
>how do i run the stats this way for a table called 'mytable'?
DBMS_STATS can collect statistics for a variety of objects

PROCEDURE GATHER_DATABASE_STATS
PROCEDURE GATHER_DATABASE_STATS_JOB_PROC
PROCEDURE GATHER_DICTIONARY_STATS
PROCEDURE GATHER_FIXED_OBJECTS_STATS
PROCEDURE GATHER_INDEX_STATS
PROCEDURE GATHER_SCHEMA_STATS
PROCEDURE GATHER_SYSTEM_STATS
PROCEDURE GATHER_TABLE_STATS

You get to specify which you desire

>is it not doing exactly the same thing as saying... analyze table 'mytable' compute statistics?

DBMS_STATS collects more/better statistics.
Re: spool only time and stats [message #455169 is a reply to message #455164] Mon, 10 May 2010 09:55 Go to previous messageGo to next message
cookiemonster
Messages: 13648
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well for gather_index_stats you'd have to specify the index name. There's a gather_table_stats as well.
And I couldn't guarantee it's doing exactly the same as analyze table.
Re: spool only time and stats [message #455170 is a reply to message #455169] Mon, 10 May 2010 10:00 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
is the point in gathering stats simply to make the optimiser pick up changes in table/index/... etc ?

Also if i gather table stats.. am i correct that this also gathers the stats for the indexes in the table...


im using the following statement...


exec dbms_stats.gather_table_stats( Owner=>'dave', Tabname=>'mytable');

where dave is the schema name... and mytable is the table name....
i just get errors...

can someone post me a code snippet? plzz

[Updated on: Mon, 10 May 2010 10:06]

Report message to a moderator

Re: spool only time and stats [message #455173 is a reply to message #455170] Mon, 10 May 2010 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 26588
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#ARPLS059

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

please use COPY & PASTE so we can see what you do & how Oracle responds
Re: spool only time and stats [message #455175 is a reply to message #455173] Mon, 10 May 2010 10:14 Go to previous messageGo to next message
dsub42
Messages: 72
Registered: April 2010
Location: uk
Member
is the point in gathering stats simply to make the optimiser pick up changes in table/index/... etc ?

Also if i gather table stats.. am i correct that this also gathers the stats for the indexes in the table... ?????

And could you please give me a code snippet for the dbms stat pack...??
Re: spool only time and stats [message #455176 is a reply to message #455170] Mon, 10 May 2010 10:17 Go to previous messageGo to previous message
Michel Cadot
Messages: 66516
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i just get errors...

This is not an Oracle error message, isn't it?
Copey and paste your session.
Before read forum guide to know how to format (but I think you already read it like you read the documentation).

And if you want an advice, do not look at elapsed time, look at "gets" ststistics.

Regards
Michel
Previous Topic: SP2-0640: Not connected
Next Topic: Debugging PL/SQL Web Forms in Toad
Goto Forum:
  


Current Time: Thu Aug 22 10:54:17 CDT 2019