| Oracle Statistics [message #575934] |
Tue, 29 January 2013 10:10  |
Rumak18
Messages: 20
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 
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  |
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, 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 #576175 is a reply to message #576009] |
Fri, 01 February 2013 03:31 |
Rumak18
Messages: 20
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 #576230 is a reply to message #576206] |
Fri, 01 February 2013 09:53 |
Rumak18
Messages: 20
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 |
cookiemonster
Messages: 13962
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 |
 |
Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
Rumak18
Messages: 20
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 |
John Watson
Messages: 8963
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?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
