0

please I would like to know any query that shows the queries or the top 10 queries that take more time to finish since the beginning of the instance with user and the sql sentence, I have seen several that show those that are currently running and active but can not find any that show a historical of these queries since the instance was started, it is usually very simple from Toad with ADDM/AWR Reports but the database in question is Oracle 9i and does not give that option. Thank you

0

Dynamic performance views are not guaranteed to have all SQL information since instance startup because of the limited amount of memory.

Oracle 9i does not have AWR.

You can use Statspack, but it is not installed by default.

Install it as SYSDBA with:

@?/rdbms/admin/spcreate

Creating a snapshot (does not happen automatically, this needs to be scheduled manually or with spauto.sql):

EXECUTE statspack.snap;

Creating a report:

@?/rdbms/admin/spreport

More details can be found in the documentation following the above link.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.