Shulou(Shulou.com)06/01 Report--

Dba_his_* class statistics tables have been added to Oracle10g, which can be referenced to diagnose bottleneck sources when bottleneck time is out of date.

1. Determine the time period:

Select * from dba_hist_snapshot

Where snap_id between & snapid1 and & snapid2

Order by end_interval_time

For example, the values of & snapid1 and & snapid2 above are 10910 and 10913 respectively.

2. Summarize and sort the waiting time of the bottleneck period:

Select event,count (*) from dba_hist_active_sess_history

Where snap_id between 10910 and 10913

Group by event

Order by 2

[@ more@]

3. According to the sorting situation, determine the waiting time and further observe the relevant fields according to the determined waiting time:

Select * from dba_hist_active_sess_history

Where snap_id between 10910 and 10913

And event='enq: TX-row lock contention'

Order by sample_time

4. Identify the SQL_ID related to the waiting time:

Select sql_id,count (*) from dba_hist_active_sess_history

Where snap_id between 10910 and 10913

And event='enq: TX-row lock contention'

Group by sql_id

5. Find the SQL statement according to SQL_ID:

Select * from dba_hist_active_sess_history

Where snap_id between 10910 and 10913

And event='enq: TX-row lock contention'

And sql_id='fhdxrqd4stwqk'

6. View the corresponding execution plan of SQL at that time:

Select id,operation, options,object_owner,object_name,object_type,cost,cardinality,bytes,cpu_cost,io_cost

From DBA_HIST_SQL_PLAN where sql_id='djpvmvjddy8av'

Order by id

You can also call the dbms_xplan.display_awr package to view the execution plan:

SQL > select * from table (dbms_xplan.display_awr ('djpvmvjddy8av'))

7. You can also view more SQL of this object:

Select * from dba_hist_active_sess_history

Where snap_id between 10910 and 10913

And sql_text like'% QRTZ_SCHEDULE%'

According to the above results, the corresponding SQL or waiting time is optimized.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.