课程第9/10次（2018-07-08星期日上下午）

botang · 发表于 2018-7-8 09:38:21

探索AWR：

select substr(t.table_name,3,1), count(*)
from dba_tables t
where t.tablespace_name='SYSAUX' and
t.owner='SYS' and
t.table_name like 'WR_$%'
group by substr(t.table_name,3,1) ;
select * from dba_views v where v.view_name like '%ACTIVE_SE%';
/* V_$ACTIVE_SESSION_HISTORY
DBA_HIST_ACTIVE_SESS_HISTORY */
select text from dba_views v where v.view_name='DBA_HIST_ACTIVE_SESS_HISTORY';

复制代码

select /* ASH/AWR meta attributes */
ash.snap_id, ash.dbid, ash.instance_number,
ash.sample_id, ash.sample_time,
/* Session/User attributes */
ash.session_id, ash.session_serial#,
decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'),
ash.flags,
ash.user_id,
/* SQL attributes */
ash.sql_id,
decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
ash.sql_child_number, ash.sql_opcode,
(select command_name from DBA_HIST_SQLCOMMAND_NAME
where command_type = ash.sql_opcode
and dbid = ash.dbid) as sql_opname,
ash.force_matching_signature,
decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id),
decode(ash.top_level_sql_id, NULL, ash.sql_opcode,
ash.top_level_sql_opcode),
/* SQL Plan/Execution attributes */
ash.sql_plan_hash_value,
decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id),
(select operation_name from DBA_HIST_PLAN_OPERATION_NAME
where operation_id = ash.sql_plan_operation#
and dbid = ash.dbid) as sql_plan_operation,
(select option_name from DBA_HIST_PLAN_OPTION_NAME
where option_id = ash.sql_plan_options#
and dbid = ash.dbid) as sql_plan_options,
decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id),
ash.sql_exec_start,
/* PL/SQL attributes */
decode(ash.plsql_entry_object_id,0,to_number(NULL),
ash.plsql_entry_object_id),
decode(ash.plsql_entry_object_id,0,to_number(NULL),
ash.plsql_entry_subprogram_id),
decode(ash.plsql_object_id,0,to_number(NULL),
ash.plsql_object_id),
decode(ash.plsql_object_id,0,to_number(NULL),
ash.plsql_subprogram_id),
/* PQ attributes */
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#),
/* Wait event attributes */
decode(ash.wait_time, 0, evt.event_name, NULL),
decode(ash.wait_time, 0, evt.event_id, NULL),
ash.seq#,
evt.parameter1, ash.p1,
evt.parameter2, ash.p2,
evt.parameter3, ash.p3,
decode(ash.wait_time, 0, evt.wait_class, NULL),
decode(ash.wait_time, 0, evt.wait_class_id, NULL),
ash.wait_time,
decode(ash.wait_time, 0, 'WAITING', 'ON CPU'),
ash.time_waited,
(case when ash.blocking_session = 4294967295
then 'UNKNOWN'
when ash.blocking_session = 4294967294
then 'GLOBAL'
when ash.blocking_session = 4294967293
then 'UNKNOWN'
when ash.blocking_session = 4294967292
then 'NO HOLDER'
when ash.blocking_session = 4294967291
then 'NOT IN WAIT'
else 'VALID'
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else ash.blocking_session
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else ash.blocking_session_serial#
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then to_number(NULL)
else ash.blocking_inst_id
end),
(case when ash.blocking_session between 4294967291 and 4294967295
then NULL
else decode(bitand(ash.flags, power(2, 3)), NULL, 'N',
0, 'N', 'Y')
end),
/* Session's working context */
ash.current_obj#, ash.current_file#, ash.current_block#,
ash.current_row#, ash.top_level_call#,
(select top_level_call_name from DBA_HIST_TOPLEVELCALL_NAME
where top_level_call# = ash.top_level_call#
and dbid = ash.dbid) as top_level_call_name,
decode(ash.consumer_group_id, 0, to_number(NULL),
ash.consumer_group_id),
ash.xid,
decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#),
ash.time_model,
decode(bitand(ash.time_model,power(2, 3)),0,'N','Y')
as in_connection_mgmt,
decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse,
decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse,
decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution,
decode(bitand(ash.time_model,power(2,11)),0,'N','Y')
as in_plsql_execution,
decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc,
decode(bitand(ash.time_model,power(2,13)),0,'N','Y')
as in_plsql_compilation,
decode(bitand(ash.time_model,power(2,14)),0,'N','Y')
as in_java_execution,
decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind,
decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close,
decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load,
decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y')
as capture_overhead,
decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' )
as replay_overhead,
decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured,
decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed,
/* Application attributes */
ash.service_hash, ash.program, ash.module, ash.action, ash.client_id,
ash.machine, ash.port, ash.ecid,
/* stash columns */
ash.tm_delta_time,
ash.tm_delta_cpu_time,
ash.tm_delta_db_time,
ash.delta_time,
ash.delta_read_io_requests,
ash.delta_write_io_requests,
ash.delta_read_io_bytes,
ash.delta_write_io_bytes,
ash.delta_interconnect_io_bytes,
ash.pga_allocated,
ash.temp_space_allocated
from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt
where ash.snap_id = sn.snap_id(+)
and ash.dbid = sn.dbid(+)
and ash.instance_number = sn.instance_number(+)
and ash.dbid = evt.dbid
and ash.event_id = evt.event_id

复制代码

同步播放：

在连接wrc之前，可以考虑先校准wrc的个数：

[oracle@station36 solutions]$ wrc USERID=system PASSWORD=oracle_4U REPLAYDIR=dbreplay mode=calibrate
Workload Replay Client: Release 11.2.0.1.0 - Production on Sun Jul 8 11:31:24 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Report for Workload in: dbreplay
-----------------------
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s)
You will need at least 26 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.
Workload Characteristics:
- max concurrency: 7 sessions
- total number of sessions: 7
Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- 256 KB of memory cache per concurrent session
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
[oracle@station36 solutions]$

复制代码

人工分析结果：
DB Replay Report for REPLAY-orcl-20180708111911

[/table][table=98%]
DB Name  DB Id  Release  RAC  Replay Name  Replay Status
ORCL150738568211.2.0.1.0NOREPLAY-orcl-20180708111911COMPLETED
   Replay Information

Information	Replay	Capture
Name	REPLAY-orcl-20180708111911	CAPTURE-orcl-20180708111045
Status	COMPLETED	COMPLETED
Database Name	ORCL	ORCL
Database Version	11.2.0.1.0	11.2.0.1.0
Start Time	08-07-18 03:35:52	08-07-18 03:11:47
End Time	08-07-18 03:36:57	08-07-18 03:13:56
Duration	1 minute 5 seconds	2 minutes 9 seconds
Directory Object	DBREPLAY	DBREPLAY
Directory Path	/home/oracle/solutions/dbreplay	/home/oracle/solutions/dbreplay

   Replay Options

Option Name	Value
Synchronization	SCN
Connect Time	100%
Think Time	100%
Think Time Auto Correct	TRUE
Number of WRC Clients	2 (2 Completed, 0 Running )

   Replay Statistics

Statistic	Replay	Capture
DB Time	6.053 seconds	3.364 seconds
Average Active Sessions	.09	.03
User calls	4263	4263
Network Time	20.332 seconds	.
Think Time	402.122 seconds	.

   Replay Divergence Summary

Divergence Type	Count	% Total
Session Failures During Replay	0	0.00
Errors No Longer Seen During Replay	0	0.00
New Errors Seen During Replay	0	0.00
Errors Mutated During Replay	0	0.00
DMLs with Different Number of Rows Modified	0	0.00
SELECTs with Different Number of Rows Fetched	0	0.00

            Workload Profile
            Top Events
                                                            (-) Hide