| spool only time and stats [message #455001] |
Sun, 09 May 2010 16:53  |
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 #455009 is a reply to message #455008] |
Sun, 09 May 2010 17:41  |
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 #455127 is a reply to message #455001] |
Mon, 10 May 2010 06:37 |
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 #455137 is a reply to message #455132] |
Mon, 10 May 2010 07:33 |
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 #455139 is a reply to message #455137] |
Mon, 10 May 2010 07:55 |
 |
Littlefoot
Messages: 21823
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 |
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 #455142 is a reply to message #455141] |
Mon, 10 May 2010 08:02 |
cookiemonster
Messages: 13963
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 |
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 |
cookiemonster
Messages: 13963
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 |
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 #455149 is a reply to message #455148] |
Mon, 10 May 2010 08:57 |
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 #455155 is a reply to message #455001] |
Mon, 10 May 2010 09:17 |
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 |
cookiemonster
Messages: 13963
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 |
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 |
cookiemonster
Messages: 13963
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 |
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 #455169 is a reply to message #455164] |
Mon, 10 May 2010 09:55 |
cookiemonster
Messages: 13963
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 |
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 #455175 is a reply to message #455173] |
Mon, 10 May 2010 10:14 |
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...??
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
