thanks.


For the second question, I should not have used the word "system" stats - rather - statistics on SYS tables

btw - are there any tables owned by SYSTEM schema that we should be analyzing using dbms_Stats(apart from analyzing the tables owned by "SYS" ?

thanks
Regi

Hi tom,
can you tell us what is the job name you are referring to
Thanks

sys%ORA10GR2> select owner, job_name from DBA_SCHEDULER_JOBS;

OWNER                          JOB_NAME
------------------------------ ------------------------------
SYS                            AUTO_SPACE_ADVISOR_JOB
SYS                            GATHER_STATS_JOB
SYS                            FGR$AUTOPURGE_JOB
SYS                            PURGE_LOG
EXFSYS                         RLM$SCHDNEGACTION
EXFSYS                         RLM$EVTCLEANUP

6 rows selected.

Is there any difference between gather_dictionary_stats and gather_Schema_stats('SYSTEM') or put it in another way what is the equivalent gather_schema_stats command for the command gather_dictionary_stats ?

Greetings, Tom

I am a bit confused about the usage of gather_system_stats
in a RAC, since each node can have different loads and the
table for storage of system statistics is just one/common
for all nodes.

What are your recommendations ?

best regards,
Avi Vainshtein

Hey Tom:

     I am reading the notes Note:149560.1 from metalink, it said:

     Before system statistics are gathered and set in dictionary, the CPU cost is
   not computed :

   SQL> explain plan for select * from OLTP.TEST where c='AAAHxGAABAAAJS1AEZ';  
   Explained.

   SQL> select operation, options, object_name, cpu_cost, io_cost
     2  from plan_table;

   OPERATION          OPTIONS              OBJECT_NAME    CPU_COST    IO_COST
   ------------------ -------------------- ------------ ---------- ----------
   SELECT STATEMENT                                                         1
   INDEX              UNIQUE SCAN          SYS_C002218                      1



but on our database, we never collect the system_stats. and we can see there is no relative stats like "SREADTIM" in the sys.aux_stats$ table. but why when I query the table and check the plan_table, there is cpu_cost there? according to the example in the metalink, there should be nothing in that column right?

so where is the cpu_cost from before I gather the system stats?

SQL> select     pname, pval1
from    sys.aux_stats$
where   sname = 'SYSSTATS_MAIN';

  2    3
CPUSPEED
CPUSPEEDNW                     206.321023
IOSEEKTIM                              10
IOTFRSPEED                           4096
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

9 rows selected.


SQL> select operation, options, object_name, cpu_cost, io_cost from plan_table;

SELECT STATEMENT

                                   264415          6

TABLE ACCESS
FULL
MAKE                               264415          6


Thank you very much!

Greetings Thomas,

and thanks like always,

exec dbms_Stats.gather_system_Stats('start');
--do some work load here
exec dbms_Stats.gather_system_Stats('stop');

if i executed the above procedure on the same machine but for different oracle versions (9i,10g and 11g) different results i get from sys.aux_stats$, why?

Greeting Thomas,

And thanks like always,

regarding the above post:

1) 9i
select * from sys.aux_stats$;
CPUSPEED = 955
2) 10g
select * from sys.aux_stats$;
CPUSPEED = 756
3) 11g
select * from sys.aux_stats$;
CPUSPEED = 1163

if the same tests were performed and i have the above resutls for these different versions:

1) Is the following example true?
Example:
a) 10g CPUSPEED = 756
select count(1) from BIG_TABLE; -- will be slower than 11g
b) 11g CPUSPEED = 1163
select count(1) from big_table; -- wil be faster that 10g

2) i am thinking that may be there is no correct values for the sys.aux_stats$, but which one of them is the accurate values in each version (the same workload was performed).

3) CPUSPEEDNW, Is this value related to oracle networking CPU speed or OS networking CPU speed?

Hi Tom :

Can we use the dbms_stat.gather_table_stats procedure to gather stats for both the table and the indexes on the tables (without using dbms_stat.gather_index_stats ).

Thanks as always for your reply.

Hi Tom,

I recently ran gather_system_stats on our 11.2.0.1 instance for 90 minutes. Its a new system, so the load is not yet extremely heavy, but I wanted to get what I can out of it, at least to start.

But for some reason, there was no value collected for MBRC, as well as the thread related values.

I'm hoping you might be able to provide a reason as to why this could occur.

Here is what I have:

SYSSTATS_INFO STATUS COMPLETED
SYSSTATS_INFO DSTART 07-29-2011 13:32
SYSSTATS_INFO DSTOP 07-29-2011 15:02
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 771.068348
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM .569
SYSSTATS_MAIN MREADTIM 1.223
SYSSTATS_MAIN CPUSPEED 1328
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR



Thanks, as always,
Paul

Hi Tom,

Thanks for the reply. To your 2 questions.

Yes, we are always doing FTS and there are two reasons for that. One, the system is still new and smaller than it will eventually be, so the CBO sees them as more effective than using some of the index access methods that will come in to use, later, when the data is much larger. And also, many of the queries are written in such a way that they are forced. (It's one of my scalability concerns; I was not involved in the app or DB design.)

Finally, I can state that I was checking, using OEMGC V11, while it was running and found several queries that were doing FTS, as part of watching the total of what was going on while it was running, just to be certain that there was some kind of load during the the run. Which is part of the reason I was surprised. The SQLs which do FTS are a big part of my concern, long run, for this system.

Here is the command I executed:

exec DBMS_STATS.GATHER_SYSTEM_STATS ( 'INTERVAL', 90 );

I queried the aux_stat$ table while it was running and saw that it was "autogathering".

Thanks, as always.

Hi Tom,

I did not check on whether the scans were doing actual physical I/O.

But you are correct that it is likely that a) many of the tables were likely already in memory and b) many of them did use direct path. As I'm on 11.2.0.1, I've observed that Oracle is using that much more extensively.

Thanks, as always for this explanation. I'll wait a while, until the load becomes greater and then try it again.

Regards,
Paul