cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Db statistics

Former Member

on ‎10-21-2013 12:03 PM

0 Kudos

Hi Folks,

I have a doubt suppose i am running the statistics manually, and i want to check the logs for whether the statistics have been updated or not.

I can check the same from DB02old checks dates of table analysis,

How can i check the same from dbacockpit?

I am not using brconnect to schedule the jobs, i am doing it manually.

Thanks,

Show replies
Show replies
Answer

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
‎01-09-2014 5:58 AM
0 Kudos

Hi,



I am not using brconnect to schedule the jobs, i am doing it manually.

Any specific reason?

Regards,

Nick Loy

Show replies
Show replies
Former Member
‎01-16-2014 10:21 AM
0 Kudos

Customer database team is doing using oracle specific tools, sap doesnt force you to use brconnect right.

fidel_vales
Employee
Employee
‎01-16-2014 10:52 AM
0 Kudos 


p517710 sap basis wrote:




Customer database team is doing using oracle specific tools, sap doesnt force you to use brconnect right.

Thre you are wrong.

You can use the oracle tools (DBMS_STATS) but SAP does not support their usage "directly" only via BRCONNECT.

Check SAP Note 105047 - Support for Oracle functions in the SAP environment

point 21:



21. DBMS_STATS


You cannot use DBMS_STATS to create statistics directly on a regular basis. 


You can use DBMS_STATS to create statistics via BRCONNECT (SAP Note 408532).
Reagan
Product and Topic Expert
Product and Topic Expert
‎10-21-2013 12:16 PM
0 Kudos

Hello

Assuming this is an Oracle database

There will be a log file created in the /oracle/SID/sapcheck location with the extension .sta

You may check that.

Regards

RB

Show replies
Show replies
Former Member
‎10-21-2013 1:16 PM
0 Kudos

Regardless whether you start from sap or from oracle end right?

Reagan
Product and Topic Expert
Product and Topic Expert
‎10-21-2013 1:24 PM
0 Kudos 
Regardless whether you start from sap or from oracle end right?

If you use BRTools to collect the statistics then the logs will be present in the /oracle/SID/sapcheck location with the extension .sta

If you start the statistics collection from SAP then BRTools will be used at the backend.

You can also see the logs using the Tx DB14

Regards

RB

Former Member
‎10-21-2013 1:29 PM
0 Kudos

Suppose i triggered the stats update from oracle level and not using any of the sap tools or from SAP where would be the logs stored.

Reagan
Product and Topic Expert
Product and Topic Expert
‎10-21-2013 1:45 PM
0 Kudos

You need to use SAP provided tools for database administration for Oracle databases in an SAP environment.

As far as I know there wont be any log files created at the OS level for statistics created from the SQL level.

http://docs.oracle.com/cd/B28359_01/server.111/b28274/stats.htm

Regards

RB

Former Member
‎10-21-2013 2:36 PM
0 Kudos

Hi Benjamin,

If you see from DB02old you can see the statistics where they have run or not.

Former Member
‎10-21-2013 2:40 PM
0 Kudos

the exact path i had mentioned in my first thread.

Reagan
Product and Topic Expert
Product and Topic Expert
‎10-21-2013 2:44 PM
0 Kudos

Hello

Are you talking about the "Space Statistics" ?

This is different from the Optimizer Statistics.

Regards

RB

fidel_vales
Employee
Employee
‎10-22-2013 9:14 AM
0 Kudos 
p517710 sap basis wrote:
Suppose i triggered the stats update from oracle level and not using any of the sap tools or from SAP where would be the logs stored.

I assume (as you do not indicate it) that you are calling either

=> ANALYZE TABLE ....

or

=> DBMS_STATS

none of those will leave any "log file".

The first one is deprecated or obsolete so it shoud NOT be used to calculate CBO statistics anymore.

The second one is called implicity by BRCONNECT, that is one of the reasons SAP DO NOT like that it is used "standalone", another reason is that, depending how you call it, you may end calculating histograms for tables that do not need them, higher or lower samples than the one recommended and so on.

So, do not do this on an SAP system.

now, you want to know, what?

I have a doubt suppose i am running the statistics manually, and i want to check the logs for whether the statistics have been updated or not.
I can check the same from DB02old checks dates of table analysis,
How can i check the same from dbacockpit?
I am not using brconnect to schedule the jobs, i am doing it manually.

you cannot check logs, there are not created.

you could use DB02, but then you need to go table by table.

So, if you triggered it at Oracle Level, you may issue a SQL command, also at Oracle level querying DBA_TABLES and checking the LAST_ANALYZE column

Former Member
‎11-13-2013 10:49 AM
0 Kudos

Hi Fidel,

As always very appreciative , but the question is simple dba_tables does give inputs regarding table statistics.

But suppose i am checking from SAP system, in dbacockpit i cannot check it anywhere whereas in db02old i can find the same as i had mentioned in the above thread.

fidel_vales
Employee
Employee
‎11-13-2013 3:14 PM
0 Kudos

Hi,

Sorry, but I do not understand.

You are talking about different things so I do not know what you want and that way NOBODY can help you.

I have a doubt suppose i am running the statistics manually, and i want to check the logs for whether the statistics have been updated or not.
I can check the same from DB02old checks dates of table analysis,
How can i check the same from dbacockpit?
I am not using brconnect to schedule the jobs, i am doing it manually.

explain what you mean "running statistics manually" (I already asked it and you did not replied)

explain what logs you want to see (I already indicated that "manually" do not create any log)

where in DB02OLD?, In DBACOCKPIT I would use the SQL EDITOR as a quick check about DBA_TABLES

If you see from DB02old you can see the statistics where they have run or not.

do you mean "DB02OLD" => DETAIL ANALYSIS => (object) => DBA_VIEWS => DBA_TABLES?

If that is what you want, I would use the SQL EDITOR and it provides the output in an ALV table which can be read easily (not like what  Imentioned)

I can also see dba_tables information on dbacockpit.

Note that DB02 and DB02old are SPACE transactions, you are trying to see CBO information, so it is quite normal and logical that it was not implemented in the new DB02

Resume, if you want help, first you have to know what do you want and explain it properly, because I still do NOT know what you want to know. You keep talking about an old transaction that has a trillion of options and a new one that has another trillion.

Former Member
‎11-26-2013 12:23 PM
0 Kudos

HI Fidel,

Suppose i dont schedule the jobs through DB13, that was my question.

Now when i goto DB02old and then click on checks on the pop menu i get

and then click on date of table analysis,

I dont get the same option in dbacockpit.

Former Member
‎11-26-2013 12:24 PM
0 Kudos

Has it not been implemented in dbacockpit?

stefan_koehler
Active Contributor
‎11-26-2013 2:37 PM
0 Kudos

Hi,

i think you are looking for something like this.

DBACOCKPIT -> Performance -> SQL Statement Analysis -> Special Statistics for CBO -> Locked/Missing Table Statistics

Regards

Stefan

Former Member
‎11-26-2013 4:08 PM
0 Kudos

Hi,

I'm not aware of such functionality in DBACOCKPIT but as suggested already you can always do query in DBACOCKPIT: go to Performance > Additional fucntions > Display DBA-views > search for DBA_TABLES and check date in column LAST_ANALYZED.

Or if you are interested in statistics age/sample size etc. for particular table, use tr. DB20.

Regards,

Michal

Former Member
‎11-27-2013 6:04 AM
0 Kudos

Hi Stefan,

Long time.What i was looking out for is without querying the table dba_tables when was the last statistic run.

DB02old option which i had mentioned is quite amazing, dont know why it wasnt implemented in dbacockpit.

27.11.2013  07:02:59  sid  lddbsid

Tables analyzed for Cost based optimizer

Init<SID>.ora parameter:

  OPTIMIZER_MODE:                ALL_ROWS

  DB_FILE_MULTIBLOCK_READ_COUNT: 128

Last SAPDBA-checkopt/analyze runs for cost based optimizer:

Last SAPDBA-checkopt/analyze runs for cost based optimizer:

  checkopt  (opt):  not available

  analyze   (aly):  not available

For details see Transaction DB14 -> Function-ID´s opt/aly

                                              Table owner

   Date of last analysis           SAPsid         SYSTEM        others

never analyzed                        0              9            233

older one year                        0              0              0

31 - 365 days                         0            149         95.978

  8 -  30 days                         0              0         17.393

  0 -   7 days                         0              0            179

   Total                               0            158        113.783

Former Member
‎01-08-2014 9:05 AM
0 Kudos

But my question again the simple thing , i cannot go into each and every table to check whether stats have run or not.

Wanted to know if dbacockpit resembles in functionality when compared to db02old.

former_member206552
Active Contributor
‎01-17-2014 6:44 AM
0 Kudos

Hi

Seeing that you gathered the stats in oracle directly, use a query to see when last it was analyzed

select OWNER,TABLE_NAME,LAST_ANALYZED from ALL_TAB_STATISTICS order by last_analyzed

Best Regards

Marius

Ask a Question