cancel
Showing results for 
Search instead for 
Did you mean: 

show Oracle table column statistics

Former Member
0 Kudos

Dear all, I search for the right entry in DBACOCKPIT to display the column statistics of tables on ORACLE platform like MSSQL DBACOCKPIT > Performance > Indexes > Index Usage to find the distinct values. In old help.sap.com articles I can only find hints to DB02 what is not existing anymore. And the most articles descibe how to update these, but not how to display

Accepted Solutions (1)

Accepted Solutions (1)

volker_borowski2
Active Contributor
0 Kudos

Hi,

the "old" transaction DB02 still exists as transaction DB02OLD.

But I favor to drop a

"select * from BNKA"

into an ST05 explain and doubleclick the table BNKA in the explain.

You will get a nice display of the stats of table and indexes together.

In newer patchlevels you can expand index and column stats in addition.

Rightclick into the statsbox to get all data in a DOWNLOAD -> TO CLIPBOARD

Hope this helps

Volker

Former Member
0 Kudos

Thanks for that, but I'm looking for the collected statistic information without strace overhead etc.

Similar to DB02OLD > Tables and Indices: Detailed analyses > Object 'VBAK' including of Index-type info and Compression info > Table colums

Is there a way to execute this from the DBACOCKPIT navigation tree?

@Volker

I will also try your hint; is the following way the same? DBACOCKPIT > Performance > SQL Statement Analysis > Shared Cursor Cache > double click to a SQL statement > Explain > click on table or index? These stats are not the same as above but I can see the column #Distinct; thanks

In MSSQL I can also see samples in the stats of the values RANGE_ROWS and EQ_ROWS; is there an equivalent in oracle stats?

Thanks; René

Former Member
0 Kudos

Hi René,

At the first step, MSSQL and Oracle are different databases. You shouldn't expect that the both database engines are same and use completely same methods while building an execution plan to access the requested data from the tables. Oracle has the cost based optimizer and uses the different statistical values to build execution plan to access the data. You can access these statistical values related by indexes on the tables such as DBA_IND_STATISTICS, DBA_HIST_SQL_PLAN, DBA_HIST_SQLSTAT.

Secondly, you can access the table columns on DBACOCKPIT by the path, below;

DBACOCKPIT -> Space -> Segments -> Detailed analysis -> Enter the segment name (table name) -> press ENTER -> Click on "table columns" tab

Thirdly, what kind of distinct values you want to see? Do you mean selectivity analysis on DB05 transaction in order to find the distinct values?

Best regards,

Orkun Gedik

Former Member
0 Kudos

To access colum stats, go to DBACOCKPIT -> Performance -> SQL Statement Analysis -> Column usage

volker_borowski2
Active Contributor
0 Kudos

@Volker

I will also try your hint; is the following way the same? DBACOCKPIT > Performance > SQL Statement Analysis > Shared Cursor Cache > double click to a SQL statement > Explain > click on table or index? These stats are not the same as above but I can see the column #Distinct; thanks

Yes that should be the same, but you do not need to "catch" the statement online or execute a real trace in ST05 to get stats. You can simply enter a statement to play around with plan options.

Check

http://scn.sap.com/community/abap/testing-and-troubleshooting/blog/2007/09/05/the-sql-trace-st05-qui...

and use the "Enter SQL Statement" Button

Volker

Answers (1)

Answers (1)

Former Member
0 Kudos

thanks for all your helpfull postings!