Learn to use Oracle AWR and ash features

About the Ash:v$session view is the most important dynamic performance view because it is dynamic, so once disconnected, the information previously recorded in V$session is lost, so in order to preserve these historical information, a v$active_session is introduced in 10g The _history view, which means the history of the active session, is the name of the view of ash. Ash, which collects the active session information that is currently in a idle wait event, is saved in the V$active_session_history view, but we should know that The dynamic performance view is actually an Oracle-built virtual table that is stockpiled in the SGA memory area, that is, Ash's data is in memory, in addition, the space that Oracle allocates to Ash is not infinite, the memory space that Ash can use, available Sql,select * from v$ Sgastat where name like ' ash% '; therefore, the number of session information that can be recorded in v$active_session_history depends on the size of the SGA assigned to the ASH buffer, and on the other hand, depending on the startup and shutdown of the database. However, Oracle provides AWR features that Ash collects session information as part of the snapshot information in Awr and is saved to a file. Generation of the Ash report, Oracle provides scripting: Ashrpt.sql: Generates a statistical report for a specified period of time, which requires you to enter the start time and then enter how long it will last. Ashrpti.sql: Generates a statistical report for a specified period of time for the specified instance. AWR, it's the successor to Statspack, and it has the corresponding script, Run it to generate a different report: Awrrpt.sql: Generates a statistical report of the snapshot interval you specify; Awrrpti.sql: Compared to the above, it is possible to generate a statistical report of the specified instance, with more options for selecting instances. The above script can only generate statistics for the instance you are connecting to. Awrsqrpt.sql: Generates the specified snapshot interval, specifying the SQLID statistics report for the SQL statement. Awrsqrpti.sql: The specified snapshot interval for the specified instance is generated, specifying the SQLID statistics report for the SQL statement. Awrddrpt.sql: Specifies two different time periods (referring to comparisons between two time intervals) to generate a statistical comparison report of the two cycles. Awrddrpti.sql: Specifies two statistical comparison reports for different time periods across instances. The above six is a different script, but you can divide it into three groups, and the script finally takes I to represent a script that can specify an instance. Above we see that AWR can analyze different requirements of the statistical report, with ASThe information in h is only stored in a v$active_session_history view, and awr information is kept in 78 views. AWR's snapshot system defaults to 60 minute execution, for snapshots, we can make some system to set up, we can modify are: Snapshot collection frequency, save time, capture the number of SQL three aspects. It corresponds to stored procedures: modify_snapshot_settings Three parameters: retention: Set the time to keep the snapshot, in minutes, the minimum can be set is 1 days, the maximum is 1 years, if 0, the collected snapshot information is persisted. Interval: Snapshot collection frequency, in minutes, the minimum value is 10 minutes, the maximum is 1 years. If set to 0, the AWR feature is disabled. Topnsql: Indicates the amount of SQL collected to compare resource usage, with a minimum value of 30 and a maximum of 10000000. View: Dba_hist_wr_control can view the settings associated with the current snapshot collection. It has only one row of records. Of course, we can also manually generate or delete snapshots by ourselves, syntax: exec dbms_workload_repository.create_snapshot ()/drop_snapshot_range () If we want to create a specimen, This is the normal benchmark for the database, followed by the snapshot we collected on this as the standard for analysis. We can manually use the Create_baseline procedure with the start snapshot number to create a baseline, the same drop_baseline is used to delete baseline.