AWR report is needed to analyze the cause of low performance, as you can see there is a button on the screen.
You may check this out.
Performance Diagnosis with Automatic Workload Repository (AWR) (Doc ID 1674086.1)
And...we don't run AWR for too long, typically one hour is good.
I been running this AWR many times, but does not know how to interpret it.
I always look for Hi-cpu, hi-memory, hi-I/O users but still they are even belongs to the Oracle programs, and can not do about it.
AWR focuses on database and SQL statistics during a specific period of time in the past, if you are trying to identify a concurrent request (of course you will get user info in the records), you need to know the database session.
Try the ASH report - Active Session History - the output contains top session statistics, in which you will find sid and serial# to be linked to concurrent requests.
You can find many useful queries to achieve it by searching, for example "find concurrent program session id".
You can identify particular problematic SQLs and use the SQL ID and SQL address to locate associated records in the v$sessions table, then join the concurrent requests tables to find particular requests, then particular requesters (users).
Following tables may be of good use.
For the AWR interpretation, please review the note that I provided.
There are also other notes that may cover slightly different details regarding AWR interpretation.
Collecting Diagnostic Data for Performance Issues in Oracle E-Business Suite (Doc ID 1121043.1)
How to Generate an AWR Report and Create Baselines (Doc ID 748642.1)
How to Use AWR Reports to Diagnose Database Performance Issues (Doc ID 1359094.1)
Hope it helps.
Its pretty simple. Lets start with SQL Tuning Advisor.
In EM Go to: Performance --> Advisors Home --> SQL Advisors --> SQL Tuning Advisor
I can see SQL ID in your screenshot. Provide the SQL ID.
- Once you run advisor, you can see recommendations
- Analyze recommendations
- Don't apply the recommendations unless you review
You can alternatively do all this from backend as well - Replace SQL ID with your ID:
1. Create Task
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '87s8z2zzpsg88',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '87s8z2zzpsg88_tuning_task11',
description => 'Tuning task1 for statement 87s8z2zzpsg88');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
2. Execute Tuning Task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '87s8z2zzpsg88_tuning_task11');
3. Get recommendations/report
set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('87s8z2zzpsg88_tuning_task11') from dual;
4. Execute only if satisfied by analysis and result
exec dbms_sqltune.accept_sql_profile(task_name => '87s8z2zzpsg88_tuning_task11', task_owner => 'SYS', replace => TRUE);
5. Drop Tuning Task Later
EXEC DBMS_SQLTUNE.drop_tuning_task(task_name => '87s8z2zzpsg88_tuning_task11');