Oracle 9i historical long queries since instance startup

Question

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

Answer 1

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.