|
探索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
No data exists for this section of the report.
| Top Service/Module/Action
(-) Hide
No data exists for this section of the report.
| Top SQL with Top Events
(-) Hide
No data exists for this section of the report.
| Top Sessions with Top Events
(-) Hide
No data exists for this section of the report.
| Replay Divergence
Session Failures
By Application
(-) Hide
No data exists for this section of the report.
| Error Divergence
By Application
(-) Hide
No data exists for this section of the report.
| By SQL
(-) Hide
No data exists for this section of the report.
| By Session
(-) Hide
No data exists for this section of the report.
| DML Data Divergence
By Application
(-) Hide
No data exists for this section of the report.
| By SQL
(-) Hide
No data exists for this section of the report.
| By Divergence magnitude
(-) Hide
No data exists for this section of the report.
| SELECT Data Divergence
By Application
(-) Hide
No data exists for this section of the report.
| By Divergence magnitude
(-) Hide
No data exists for this section of the report.
| Replay Clients Alerts
(-) Hide
No data exists for this section of the report.
| Replay Filters
(-) Hide
No data exists for this section of the report.
|
End of Report.
---------------------------------------------------------------------------------------------------------------
外部表带有preprossesor的加载:
- [root@station90 oracle]# ls -ld *dir*
- drwxr-xr-x 2 oracle oinstall 4096 2月 24 2017 baddir
- drwxr-xr-x 2 oracle oinstall 4096 2月 24 2017 datadir1
- drwxr-xr-x 2 oracle oinstall 4096 9月 5 2015 datadir2
- drwxr-xr-x 2 oracle oinstall 4096 7月 8 15:48 execdir
- drwxr-xr-x 2 oracle oinstall 4096 9月 5 2015 logdir
复制代码- create directory baddir as '/home/oracle/baddir';
- create directory logdir as '/home/oracle/logdir';
- create directory datadir1 as '/home/oracle/datadir1';
- create directory datadir2 as '/home/oracle/datadir2';
- create directory execdir as '/home/oracle/execdir';
- select * from dba_directories d where d.directory_name like '%DIR%';
复制代码
datadir1里的文件data01.dat.gz:
- 100,SAN,ZHANG,01-jan-2001
- 101,
- 102,XXX,XXX,2001-01-01
复制代码 datadir2里的文件data02.dat.gz:
- 200,SI,LI,02-feb-2002
- 201,
- 202,YYY,YYY,2002-02-02
复制代码
利用EM帮我们产生控制文件:
1. 先建内部表:
- CREATE TABLE hr.extab_employees
- (
- "EMPLOYEE_ID" NUMBER(4),
- "FIRST_NAME" VARCHAR2(20),
- "LAST_NAME" VARCHAR2(25),
- "HIRE_DATE" DATE
- );
复制代码
手工修改一下控制文件:
- LOAD DATA
- infile 'data01.dat'
- APPEND
- INTO TABLE HR.EXTAB_EMPLOYEES
- FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
- trailing nullcols
- (
- EMPLOYEE_ID INTEGER EXTERNAL,
- FIRST_NAME CHAR,
- LAST_NAME CHAR,
- HIRE_DATE DATE(11) "dd-mon-yyyy"
- )
复制代码
用 控制文件来换外部表:
- sqlldr hr/oracle_4U control=ext.ctl external_table=GENERATE_ONLY log=ext.sql
复制代码 编辑ext.sql:
- CREATE TABLE hr.extab_employees
- (
- "EMPLOYEE_ID" NUMBER(4),
- "FIRST_NAME" VARCHAR2(20),
- "LAST_NAME" VARCHAR2(25),
- "HIRE_DATE" DATE
- )
- ORGANIZATION external
- (
- TYPE oracle_loader
- DEFAULT DIRECTORY datadir1
- ACCESS PARAMETERS
- (
- RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
- BADFILE baddir:'data_%a_%p.bad'
- LOGFILE logdir:'data_%a_%p.log'
- READSIZE 1048576
- FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
- MISSING FIELD VALUES ARE NULL
- REJECT ROWS WITH ALL NULL FIELDS
- (
- "EMPLOYEE_ID" CHAR(255)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
- "FIRST_NAME" CHAR(255)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
- "LAST_NAME" CHAR(255)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
- "HIRE_DATE" CHAR(11)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
- DATE_FORMAT DATE MASK "dd-mon-yyyy"
- )
- )
- location
- (
- datadir1:'data01.dat',datadir2:'data02.dat'
- )
- ) parallel REJECT LIMIT UNLIMITED;
复制代码 授权完,执行脚本ext.sql:
- CREATE TABLE hr.extab_employees
- (
- "EMPLOYEE_ID" NUMBER(4),
- "FIRST_NAME" VARCHAR2(20),
- "LAST_NAME" VARCHAR2(25),
- "HIRE_DATE" DATE
- );
- drop table hr.extab_employees purge;
- grant read,write on directory baddir to hr;
- grant read,write on directory logdir to hr;
- grant read,write on directory datadir1 to hr;
- grant read,write on directory datadir2 to hr;
复制代码
查询这个外部表,同时查看baddir和logdir:
- [oracle@station90 baddir]$ cat data000_4038.bad
- 202,YYY,YYY,2002-02-02
- [oracle@station90 baddir]$ ls
- data_000_1046.bad data000_4038.bad data_001_1048.bad data001_4040.bad
- [oracle@station90 baddir]$
复制代码
接下来,把所有的数据文件gz,同时在execdir中拷贝/bin/gunzip,再写一个shell脚本(11.2要避开OPTIONS):
- #!/bin/sh
- /home/oracle/execdir/gunzip -c $1
复制代码
改ext.sql最终的版本:
- CREATE TABLE hr.extab_employees
- (
- "EMPLOYEE_ID" NUMBER(4),
- "FIRST_NAME" VARCHAR2(20),
- "LAST_NAME" VARCHAR2(25),
- "HIRE_DATE" DATE
- )
- ORGANIZATION external
- (
- TYPE oracle_loader
- DEFAULT DIRECTORY datadir1
- ACCESS PARAMETERS
- (
- RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
- preprocessor execdir:'gunzip.sh'
- BADFILE baddir:'data_%a_%p.bad'
- LOGFILE logdir:'data_%a_%p.log'
- READSIZE 1048576
- FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
- MISSING FIELD VALUES ARE NULL
- REJECT ROWS WITH ALL NULL FIELDS
- (
- "EMPLOYEE_ID" CHAR(255)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
- "FIRST_NAME" CHAR(255)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
- "LAST_NAME" CHAR(255)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
- "HIRE_DATE" CHAR(11)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
- DATE_FORMAT DATE MASK "dd-mon-yyyy"
- )
- )
- location
- (
- datadir1:'data01.dat.gz',datadir2:'data02.dat.gz'
- )
- ) parallel REJECT LIMIT UNLIMITED;
复制代码 -------------------------------------zip格式的:
- CREATE TABLE hr.extab_employees
- (
- "EMPLOYEE_ID" NUMBER(4),
- "FIRST_NAME" VARCHAR2(20),
- "LAST_NAME" VARCHAR2(25),
- "HIRE_DATE" DATE
- )
- ORGANIZATION external
- (
- TYPE oracle_loader
- DEFAULT DIRECTORY datadir1
- ACCESS PARAMETERS
- (
- RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
- preprocessor execdir:'zcat'
- BADFILE baddir:'data_%a_%p.bad'
- LOGFILE logdir:'data_%a_%p.log'
- READSIZE 1048576
- FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
- MISSING FIELD VALUES ARE NULL
- REJECT ROWS WITH ALL NULL FIELDS
- (
- "EMPLOYEE_ID" CHAR(255)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
- "FIRST_NAME" CHAR(255)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
- "LAST_NAME" CHAR(255)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
- "HIRE_DATE" CHAR(11)
- TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
- DATE_FORMAT DATE MASK "dd-mon-yyyy"
- )
- )
- location
- (
- datadir1:'data01.dat.zip',datadir2:'data02.dat.zip'
- )
- ) parallel REJECT LIMIT UNLIMITED;
复制代码
--------------------------------interval分区:
- select pt.interval from dba_part_tables pt
- where pt.owner='SH' and pt.table_name='NEWSALES';
-
- select * from dba_tab_partitions tp
- where tp.table_owner='SH' and tp.table_name='NEWSALES';
复制代码 merge parttions:
- alter table sh.newsales merge partitions sys_p21, sys_p22
- into partition p_before_3_jan_2005;
复制代码
把普通的rang变成interval:
- alter table sh.HISTORICAL_NEWSALES
- set interval (numtoyminterval(1,'year' );
- select * from sh.HISTORICAL_NEWSALES partition (SYS_p27) ;
复制代码
|
|