Its good to know about ASH basic usage, but there are some things which every DBA must need to know.
Let me first compare ASH with SQLTRACE
Comparison of ASH with SQL TRACE
ASH and SQL*Trace are not the same thing, but both are valuable tools for finding out about where processes spend time. SQL*Trace (or event 10046 as we used to call it) has been my weapon of choice for solving performance issues for a very long time, and it is extremely effective, and there is still a place for it.
There are difficulties with using SQL trace, especially in a production environment.
Firstly, it does have a run time overhead. You could afford to trace a single process, but you certainly couldn’t trace the entire database.
1) You have to work with trace in a reactive way. You will probably not already be tracing a process when you experience a performance problem, so you need to run the process again and reproduce the poor performance with trace.
2) Trace will tell you if a session is blocked waiting on a lock. However, it will not tell you who is blocking you. ASH will do this (although there are limitations).
3) A trace file records everything that happens in a session, whereas ASH data samples the session every seconds. Short-lived events will be missed, so the data has to be handled statistically.
4) There are problems with both approaches if you have the kind of application where you have lots of different SQL statements because the application uses literal values rather than bind variables (and cursor sharing is EXACT).
5) Oracle’s TKPROF trace file profiler cannot aggregate these statements, but I have found another called ORASRP www.oracledba.ru/orasrp) that can. With ASH, you will see different SQL_IDs, but it can be effective to group statements with the same execution plan.
6) You may have trouble finding the SQL text in the SGA (or via the DBMS_XPLAN package) because it has already been aged out of the library cache. You may have similar problems with historical ASH data because the statement had been aged out when the AWR snapshot was taken.
7) A trace file, with STATISTICS_LEVEL set to ALL, will give you timings for each operation in the execution plan. So, you can see where in the execution plan the time was spent. ASH will only tell you how long the whole statement takes to execute, and how long was spent on which wait event.
IF you see my blog http://karandba.blogspot.com/2011/11/actual-use-of-active-session-history.html
you will also come to know how to find top SQL's spent more on CPU/WAIT/I/O
Let me first compare ASH with SQLTRACE
Comparison of ASH with SQL TRACE
ASH and SQL*Trace are not the same thing, but both are valuable tools for finding out about where processes spend time. SQL*Trace (or event 10046 as we used to call it) has been my weapon of choice for solving performance issues for a very long time, and it is extremely effective, and there is still a place for it.
There are difficulties with using SQL trace, especially in a production environment.
Firstly, it does have a run time overhead. You could afford to trace a single process, but you certainly couldn’t trace the entire database.
1) You have to work with trace in a reactive way. You will probably not already be tracing a process when you experience a performance problem, so you need to run the process again and reproduce the poor performance with trace.
2) Trace will tell you if a session is blocked waiting on a lock. However, it will not tell you who is blocking you. ASH will do this (although there are limitations).
3) A trace file records everything that happens in a session, whereas ASH data samples the session every seconds. Short-lived events will be missed, so the data has to be handled statistically.
4) There are problems with both approaches if you have the kind of application where you have lots of different SQL statements because the application uses literal values rather than bind variables (and cursor sharing is EXACT).
5) Oracle’s TKPROF trace file profiler cannot aggregate these statements, but I have found another called ORASRP www.oracledba.ru/orasrp) that can. With ASH, you will see different SQL_IDs, but it can be effective to group statements with the same execution plan.
6) You may have trouble finding the SQL text in the SGA (or via the DBMS_XPLAN package) because it has already been aged out of the library cache. You may have similar problems with historical ASH data because the statement had been aged out when the AWR snapshot was taken.
7) A trace file, with STATISTICS_LEVEL set to ALL, will give you timings for each operation in the execution plan. So, you can see where in the execution plan the time was spent. ASH will only tell you how long the whole statement takes to execute, and how long was spent on which wait event.
IF you see my blog http://karandba.blogspot.com/2011/11/actual-use-of-active-session-history.html
you will also come to know how to find top SQL's spent more on CPU/WAIT/I/O
No comments:
Post a Comment