How to perform 'SQL Statement Analysis'?

[Former Member]

How to perform 'SQL Statement Analysis'?

Dear Gurus,

'DB02->Performance->SQL Statement Analysis->Shared Cursor Cache/Table Access/Column Usage/System Statistics for CBO'

Does anyone know how to use the 'SQL Statement Analysis'?

For instance,

>Shared Cursor Cache

There are many technical analysis indexes(Buffer gets,Reads/Execution...). What are the most important/significant?

>Table Access

When I run this anyalysis. It popupped a 'Runtime Errors COMPUTE_BCD_OVERFLOW'. Why? and What's the point of this analysis?

>Column Usage

>System Statistics for CBO'

Regards!

Jason

[former_member189725 · Accepted solution]

Hi,

You can go to DB02->Performance->SQL Statement Analysis->Shared Cursor Cache

Select Disk Reads , that would give you a sorted output on the column "Disk Reads".

Now you can analyze the statements in the SQL cache. Check the diskreads/execution which gives you statements with high I/O.

As analysis would can be on various criteria , I am not going any further.

You can carry out the performance tuning.

[Former Member · Accepted solution]

This is a must read for you: [766349 - FAQ: Oracle SQL optimization|https://service.sap.com/sap/support/notes/766349]

Cheers Michael