Home » RDBMS Server » Performance Tuning » Oracle Statistics (Oracle 11g2.0.3)
Oracle Statistics [message #575934] Tue, 29 January 2013 10:10 Go to next message
Rumak18
Messages: 9
Registered: April 2009
Location: Germany
Junior Member
Hello,
i've got a question about statistics. Please be gentle with me , cuz i'm quite new to this Razz

The following script starts the statistics every night:

conn sys/pass@db as sysdba
spool c:\oracle\statistics\Oracle_Statistics.log
execute dbms_stats.gather_database_stats( cascade=> TRUE, gather_sys=> FALSE, estimate_percent=> null, degree=> DBMS_STATS.AUTO_DEGREE, no_invalidate=> FALSE, granularity=> 'AUTO', method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER');
exit

The problem about this is, that spool doesn't work, while in Oracle 10 a similar script worked well.
Re: Oracle Statistics [message #575935 is a reply to message #575934] Tue, 29 January 2013 10:22 Go to previous messageGo to next message
gazzag
Messages: 332
Registered: November 2010
Location: Bristol, UK
Senior Member
You need a "SPOOL OFF" command before "exit".

I.E.

conn sys/pass@db as sysdba
spool c:\oracle\statistics\Oracle_Statistics.log
execute dbms_stats.gather_database_stats( cascade=> TRUE, gather_sys=> FALSE, estimate_percent=> null, degree=> DBMS_STATS.AUTO_DEGREE, no_invalidate=> FALSE, granularity=> 'AUTO', method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER');
SPOOL OFF
exit


HTH
-g
Re: Oracle Statistics [message #575937 is a reply to message #575935] Tue, 29 January 2013 10:30 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
You do realize that oracle 11g (and 10g) automatically gather statistics for you?
Re: Oracle Statistics [message #575940 is a reply to message #575935] Tue, 29 January 2013 11:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You need a "SPOOL OFF" command before "exit".


No it is not the reason, exit closes the spool file.

Regards
Michel
Re: Oracle Statistics [message #575985 is a reply to message #575934] Wed, 30 January 2013 02:38 Go to previous messageGo to next message
LNossov
Messages: 289
Registered: July 2011
Location: Germany
Senior Member
Did you get any error?
Re: Oracle Statistics [message #575992 is a reply to message #575985] Wed, 30 January 2013 06:36 Go to previous messageGo to next message
Rumak18
Messages: 9
Registered: April 2009
Location: Germany
Junior Member
Hi there,
@cookiemonster: No. First time i hear it.
@Michel Cadot: What is then the reason?
@LNossov: No errors.
Re: Oracle Statistics [message #575993 is a reply to message #575992] Wed, 30 January 2013 06:39 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well since oracle does it for you you might as well stop trying to do it yourself.
Re: Oracle Statistics [message #576009 is a reply to message #575992] Wed, 30 January 2013 09:54 Go to previous messageGo to next message
LNossov
Messages: 289
Registered: July 2011
Location: Germany
Senior Member
Could you please do the following:

- run these commands in SQL*Plus

conn sys/pass@db as sysdba
spool c:\oracle\statistics\test1_2.log
select 'This is a test nr. 1' from dual;
select 'This is a test nr. 2' from dual;
execute dbms_stats.gather_database_stats( cascade=> TRUE, gather_sys=> FALSE, estimate_percent=> null, degree=> DBMS_STATS.AUTO_DEGREE, no_invalidate=> FALSE, granularity=> 'AUTO', method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER');
exit


- after this check the result in the DOS window

dir c:\oracle\statistics
type c:\oracle\statistics\test1_2.log


- upload the outputs




Re: Oracle Statistics [message #576175 is a reply to message #576009] Fri, 01 February 2013 03:31 Go to previous messageGo to next message
Rumak18
Messages: 9
Registered: April 2009
Location: Germany
Junior Member
Hello LNossov:

Here are the results:
The only thing that was created is :
'GATHER')
and this file is empty.
Nothing more happened.
Re: Oracle Statistics [message #576178 is a reply to message #576175] Fri, 01 February 2013 03:50 Go to previous messageGo to next message
LNossov
Messages: 289
Registered: July 2011
Location: Germany
Senior Member
Please die outputs upload (s. my last post).

[Updated on: Fri, 01 February 2013 03:59]

Report message to a moderator

Re: Oracle Statistics [message #576183 is a reply to message #576175] Fri, 01 February 2013 04:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rumak18 wrote on Fri, 01 February 2013 10:31
Hello LNossov:

Here are the results:
The only thing that was created is :
'GATHER')
and this file is empty.
Nothing more happened.


you MUST copy and paste what you did and got otherwise we can't help you.
And post it FORMATTED that is:
Read How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel



Re: Oracle Statistics [message #576206 is a reply to message #576183] Fri, 01 February 2013 06:55 Go to previous messageGo to next message
LNossov
Messages: 289
Registered: July 2011
Location: Germany
Senior Member
Thank you, Michael

I was writing my message speaking German with my colleague and did't realized, that my message is a mix of German and English.

Regards
Leonid
Re: Oracle Statistics [message #576230 is a reply to message #576206] Fri, 01 February 2013 09:53 Go to previous messageGo to next message
Rumak18
Messages: 9
Registered: April 2009
Location: Germany
Junior Member
C:\Users\administrator.ASP-RZ>dir c:\batch\
 Datenträger in Laufwerk C: ist SYSTEM
 Volumeseriennummer: 00EB-DD46

 Verzeichnis von c:\batch

31.01.2013  17:20    <DIR>          .
31.01.2013  17:20    <DIR>          ..
31.01.2013  17:20                 0 'GATHER')
21.01.2013  09:41    <DIR>          bginfo
29.01.2013  16:54    <DIR>          blat
30.01.2013  16:53    <DIR>          ORACLE
31.01.2013  17:18               400 temp_statistics.bat
               2 Datei(en),            400 Bytes
               5 Verzeichnis(se), 30.437.404.672 Bytes frei

C:\Users\administrator.ASP-RZ>type c:\batch\test1_2.log
Das System kann die angegebene Datei nicht finden.

C:\Users\administrator.ASP-RZ>

Re: Oracle Statistics [message #576231 is a reply to message #576230] Fri, 01 February 2013 10:03 Go to previous messageGo to next message
cookiemonster
Messages: 11070
Registered: September 2008
Location: Rainy Manchester
Senior Member
I don't see anyway that your code could generate that file name.
So either:
1) you have a really weird oracle bug and you need to be talking to oracle support about it, not us.
2) That file was generated by you or someone else making a mistake when typing/copying and pasting.

My money is on option 2.

That said, I still don't understand:
1) why you are using spool. What do you expect the file to say? I'd expect to see "PL/SQL procedure successfully completed.", which seems rather pointless.
2) Why you don't just leave oracle to do it for you. It's routinely generating stats anyway, unless you explicitly stopped it.


EDIT: typo

[Updated on: Fri, 01 February 2013 10:04]

Report message to a moderator

Re: Oracle Statistics [message #576232 is a reply to message #576230] Fri, 01 February 2013 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59408
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste the WHOLE SQL*Plus session Leonid posted you.
We have NO idea of what you did.

I remind you the session:
conn sys/pass@db as sysdba
spool c:\oracle\statistics\test1_2.log
select 'This is a test nr. 1' from dual;
select 'This is a test nr. 2' from dual;
execute dbms_stats.gather_database_stats( cascade=> TRUE, gather_sys=> FALSE, estimate_percent=> null, degree=> DBMS_STATS.AUTO_DEGREE, no_invalidate=> FALSE, granularity=> 'AUTO', method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER');
exit


Regards
Michel
Re: Oracle Statistics [message #576960 is a reply to message #576232] Mon, 11 February 2013 06:45 Go to previous messageGo to next message
Rumak18
Messages: 9
Registered: April 2009
Location: Germany
Junior Member
Ok...now i got it to work with the spool file just as descriped aboth. What i get is "PL/SQL procedure successfully completed".
Now...@cookiemonster:
To tell the truth...i'm just the second DB administrator and try to reach the knowledge of my colleague who successfully tries to stop my development in oracle knowledge. Also he was the one who introduced this script for statistics. Now, where can i see the "default" statistics process in oracle? Perhapts it is still configured.
Re: Oracle Statistics [message #576962 is a reply to message #576960] Mon, 11 February 2013 07:15 Go to previous message
John Watson
Messages: 4676
Registered: January 2010
Location: Global Village
Senior Member
Quote:
Now, where can i see the "default" statistics process in oracle? Perhapts it is still configured.
Look at the view DBA_AUTOTASK_CLIENT, and related views and packages. Read up on the autotask system.

Quote:
i'm just the second DB administrator and try to reach the knowledge of my colleague who successfully tries to stop my development in oracle knowledge
Oh dear. What can one say?
Previous Topic: Server Specification
Next Topic: Loading and reading at the sametime
Goto Forum:
  


Current Time: Tue Oct 21 10:09:16 CDT 2014

Total time taken to generate the page: 0.09712 seconds