Bo's Oracle Station

查看: 3264|回复: 0

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

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-7-8 09:38:21 | 显示全部楼层 |阅读模式
探索AWR:
  1. select    substr(t.table_name,3,1), count(*)
  2.   from dba_tables t
  3.   where t.tablespace_name='SYSAUX'  and
  4.              t.owner='SYS' and
  5.              t.table_name like 'WR_$%'
  6.   group by  substr(t.table_name,3,1)  ;
  7.   
  8.   select  * from dba_views v where  v.view_name like '%ACTIVE_SE%';
  9.   
  10.   /* V_$ACTIVE_SESSION_HISTORY
  11. DBA_HIST_ACTIVE_SESS_HISTORY    */

  12. select  text from dba_views v where v.view_name='DBA_HIST_ACTIVE_SESS_HISTORY';

复制代码

  1. select /* ASH/AWR meta attributes */
  2.        ash.snap_id, ash.dbid, ash.instance_number,
  3.        ash.sample_id, ash.sample_time,
  4.        /* Session/User attributes */
  5.        ash.session_id, ash.session_serial#,
  6.        decode(ash.session_type, 1,'FOREGROUND', 'BACKGROUND'),
  7.        ash.flags,
  8.        ash.user_id,
  9.        /* SQL attributes */
  10.        ash.sql_id,
  11.        decode(bitand(ash.flags, power(2, 4)), NULL, 'N', 0, 'N', 'Y'),
  12.        ash.sql_child_number, ash.sql_opcode,
  13.        (select command_name from DBA_HIST_SQLCOMMAND_NAME
  14.         where command_type = ash.sql_opcode
  15.         and dbid = ash.dbid) as sql_opname,
  16.        ash.force_matching_signature,
  17.        decode(ash.top_level_sql_id, NULL, ash.sql_id, ash.top_level_sql_id),
  18.        decode(ash.top_level_sql_id, NULL, ash.sql_opcode,
  19.               ash.top_level_sql_opcode),
  20.        /* SQL Plan/Execution attributes */
  21.        ash.sql_plan_hash_value,
  22.        decode(ash.sql_plan_line_id, 0, to_number(NULL), ash.sql_plan_line_id),
  23.        (select operation_name from DBA_HIST_PLAN_OPERATION_NAME
  24.         where  operation_id = ash.sql_plan_operation#
  25.           and  dbid = ash.dbid) as sql_plan_operation,
  26.        (select option_name from DBA_HIST_PLAN_OPTION_NAME
  27.         where  option_id = ash.sql_plan_options#
  28.           and  dbid = ash.dbid) as sql_plan_options,
  29.        decode(ash.sql_exec_id, 0, to_number(NULL), ash.sql_exec_id),
  30.        ash.sql_exec_start,
  31.        /* PL/SQL attributes */
  32.        decode(ash.plsql_entry_object_id,0,to_number(NULL),
  33.               ash.plsql_entry_object_id),
  34.        decode(ash.plsql_entry_object_id,0,to_number(NULL),
  35.               ash.plsql_entry_subprogram_id),
  36.        decode(ash.plsql_object_id,0,to_number(NULL),
  37.               ash.plsql_object_id),
  38.        decode(ash.plsql_object_id,0,to_number(NULL),
  39.               ash.plsql_subprogram_id),
  40.        /* PQ attributes */
  41.        decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_instance_id),
  42.        decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_id),
  43.        decode(ash.qc_session_id, 0, to_number(NULL), ash.qc_session_serial#),
  44.        /* Wait event attributes */
  45.        decode(ash.wait_time, 0, evt.event_name, NULL),
  46.        decode(ash.wait_time, 0, evt.event_id,   NULL),
  47.        ash.seq#,
  48.        evt.parameter1, ash.p1,
  49.        evt.parameter2, ash.p2,
  50.        evt.parameter3, ash.p3,
  51.        decode(ash.wait_time, 0, evt.wait_class,    NULL),
  52.        decode(ash.wait_time, 0, evt.wait_class_id, NULL),
  53.        ash.wait_time,
  54.        decode(ash.wait_time, 0, 'WAITING', 'ON CPU'),
  55.        ash.time_waited,
  56.        (case when ash.blocking_session = 4294967295
  57.                then 'UNKNOWN'
  58.              when ash.blocking_session = 4294967294
  59.                then 'GLOBAL'
  60.              when ash.blocking_session = 4294967293
  61.                then 'UNKNOWN'
  62.              when ash.blocking_session = 4294967292
  63.                then 'NO HOLDER'
  64.              when ash.blocking_session = 4294967291
  65.                then 'NOT IN WAIT'
  66.              else 'VALID'
  67.         end),
  68.        (case when ash.blocking_session between 4294967291 and 4294967295
  69.                then to_number(NULL)
  70.              else ash.blocking_session
  71.         end),
  72.        (case when ash.blocking_session between 4294967291 and 4294967295
  73.                then to_number(NULL)
  74.              else ash.blocking_session_serial#
  75.         end),
  76.        (case when ash.blocking_session between 4294967291 and 4294967295
  77.                then to_number(NULL)
  78.              else ash.blocking_inst_id
  79.           end),
  80.        (case when ash.blocking_session between 4294967291 and 4294967295
  81.                then NULL
  82.              else decode(bitand(ash.flags, power(2, 3)), NULL, 'N',
  83.                          0, 'N', 'Y')
  84.           end),
  85.        /* Session's working context */
  86.        ash.current_obj#, ash.current_file#, ash.current_block#,
  87.        ash.current_row#, ash.top_level_call#,
  88.        (select top_level_call_name from DBA_HIST_TOPLEVELCALL_NAME
  89.         where top_level_call# = ash.top_level_call#
  90.         and dbid = ash.dbid) as top_level_call_name,
  91.        decode(ash.consumer_group_id, 0, to_number(NULL),
  92.               ash.consumer_group_id),
  93.        ash.xid,
  94.        decode(ash.remote_instance#, 0, to_number(NULL), ash.remote_instance#),
  95.        ash.time_model,
  96.        decode(bitand(ash.time_model,power(2, 3)),0,'N','Y')
  97.                                                          as in_connection_mgmt,
  98.        decode(bitand(ash.time_model,power(2, 4)),0,'N','Y')as in_parse,
  99.        decode(bitand(ash.time_model,power(2, 7)),0,'N','Y')as in_hard_parse,
  100.        decode(bitand(ash.time_model,power(2,10)),0,'N','Y')as in_sql_execution,
  101.        decode(bitand(ash.time_model,power(2,11)),0,'N','Y')
  102.                                                          as in_plsql_execution,
  103.        decode(bitand(ash.time_model,power(2,12)),0,'N','Y')as in_plsql_rpc,
  104.        decode(bitand(ash.time_model,power(2,13)),0,'N','Y')
  105.                                                        as in_plsql_compilation,
  106.        decode(bitand(ash.time_model,power(2,14)),0,'N','Y')
  107.                                                        as in_java_execution,
  108.        decode(bitand(ash.time_model,power(2,15)),0,'N','Y')as in_bind,
  109.        decode(bitand(ash.time_model,power(2,16)),0,'N','Y')as in_cursor_close,
  110.        decode(bitand(ash.time_model,power(2,17)),0,'N','Y')as in_sequence_load,
  111.        decode(bitand(ash.flags,power(2,5)),NULL,'N',0,'N','Y')
  112.                                                        as capture_overhead,
  113.        decode(bitand(ash.flags,power(2,6)), NULL,'N',0,'N','Y' )
  114.                                                            as replay_overhead,
  115.        decode(bitand(ash.flags,power(2,0)),NULL,'N',0,'N','Y') as is_captured,
  116.        decode(bitand(ash.flags,power(2,2)), NULL,'N',0,'N','Y' )as is_replayed,
  117.        /* Application attributes */
  118.        ash.service_hash, ash.program, ash.module, ash.action, ash.client_id,
  119.        ash.machine, ash.port, ash.ecid,
  120.        /* stash columns */
  121.        ash.tm_delta_time,
  122.        ash.tm_delta_cpu_time,
  123.        ash.tm_delta_db_time,
  124.        ash.delta_time,
  125.        ash.delta_read_io_requests,
  126.        ash.delta_write_io_requests,
  127.        ash.delta_read_io_bytes,
  128.        ash.delta_write_io_bytes,
  129.        ash.delta_interconnect_io_bytes,
  130.        ash.pga_allocated,
  131.        ash.temp_space_allocated
  132. from WRM$_SNAPSHOT sn, WRH$_ACTIVE_SESSION_HISTORY ash, WRH$_EVENT_NAME evt
  133. where      ash.snap_id          = sn.snap_id(+)
  134.       and  ash.dbid             = sn.dbid(+)
  135.       and  ash.instance_number  = sn.instance_number(+)
  136.       and  ash.dbid             = evt.dbid
  137.       and  ash.event_id         = evt.event_id
复制代码

同步播放:
a.png

在连接wrc之前,可以考虑先校准wrc的个数:
  1. [oracle@station36 solutions]$ wrc USERID=system PASSWORD=oracle_4U REPLAYDIR=dbreplay  mode=calibrate

  2. Workload Replay Client: Release 11.2.0.1.0 - Production on Sun Jul 8 11:31:24 2018

  3. Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


  4. Report for Workload in: dbreplay
  5. -----------------------

  6. Recommendation:
  7. Consider using at least 1 clients divided among 1 CPU(s)
  8. You will need at least 26 MB of memory per client process.
  9. If your machine(s) cannot match that number, consider using more clients.

  10. Workload Characteristics:
  11. - max concurrency: 7 sessions
  12. - total number of sessions: 7

  13. Assumptions:
  14. - 1 client process per 50 concurrent sessions
  15. - 4 client process per CPU
  16. - 256 KB of memory cache per concurrent session
  17. - think time scale = 100
  18. - connect time scale = 100
  19. - synchronization = TRUE

  20. [oracle@station36 solutions]$
复制代码
d.png


人工分析结果:
      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-20180708111911CAPTURE-orcl-20180708111045
          Status        COMPLETEDCOMPLETED
          Database Name        ORCLORCL
          Database Version        11.2.0.1.011.2.0.1.0
          Start Time        08-07-18 03:35:5208-07-18 03:11:47
          End Time        08-07-18 03:36:5708-07-18 03:13:56
          Duration        1 minute 5 seconds2 minutes 9 seconds
          Directory Object        DBREPLAYDBREPLAY
          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的加载:

  1. [root@station90 oracle]# ls -ld *dir*
  2. drwxr-xr-x 2 oracle oinstall 4096  2月 24 2017 baddir
  3. drwxr-xr-x 2 oracle oinstall 4096  2月 24 2017 datadir1
  4. drwxr-xr-x 2 oracle oinstall 4096  9月  5 2015 datadir2
  5. drwxr-xr-x 2 oracle oinstall 4096  7月  8 15:48 execdir
  6. drwxr-xr-x 2 oracle oinstall 4096  9月  5 2015 logdir
复制代码
  1. create directory  baddir as '/home/oracle/baddir';
  2. create directory  logdir as '/home/oracle/logdir';
  3. create directory  datadir1  as '/home/oracle/datadir1';
  4. create directory  datadir2 as '/home/oracle/datadir2';
  5. create directory  execdir as '/home/oracle/execdir';

  6. select  * from dba_directories d  where d.directory_name like '%DIR%';
复制代码

datadir1里的文件data01.dat.gz:
  1. 100,SAN,ZHANG,01-jan-2001
  2. 101,
  3. 102,XXX,XXX,2001-01-01
复制代码
datadir2里的文件data02.dat.gz:
  1. 200,SI,LI,02-feb-2002
  2. 201,
  3. 202,YYY,YYY,2002-02-02
复制代码

利用EM帮我们产生控制文件:
1. 先建内部表:
  1. CREATE TABLE hr.extab_employees
  2. (
  3.   "EMPLOYEE_ID" NUMBER(4),
  4.   "FIRST_NAME" VARCHAR2(20),
  5.   "LAST_NAME" VARCHAR2(25),
  6.   "HIRE_DATE" DATE
  7. );
复制代码
a.png


b.png

   
c.png

d.png

手工修改一下控制文件:
  1. LOAD DATA
  2. infile 'data01.dat'
  3. APPEND
  4. INTO TABLE HR.EXTAB_EMPLOYEES
  5. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  6. trailing nullcols
  7. (
  8.   EMPLOYEE_ID INTEGER EXTERNAL,
  9.   FIRST_NAME CHAR,
  10.   LAST_NAME CHAR,
  11.   HIRE_DATE DATE(11)  "dd-mon-yyyy"
  12. )

复制代码

用 控制文件来换外部表:

  1. sqlldr  hr/oracle_4U control=ext.ctl   external_table=GENERATE_ONLY log=ext.sql
复制代码
编辑ext.sql:
  1. CREATE TABLE hr.extab_employees
  2. (
  3.   "EMPLOYEE_ID" NUMBER(4),
  4.   "FIRST_NAME" VARCHAR2(20),
  5.   "LAST_NAME" VARCHAR2(25),
  6.   "HIRE_DATE" DATE
  7. )
  8. ORGANIZATION external
  9. (
  10.   TYPE oracle_loader
  11.   DEFAULT DIRECTORY datadir1
  12.   ACCESS PARAMETERS
  13.   (
  14.     RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
  15.     BADFILE baddir:'data_%a_%p.bad'
  16.     LOGFILE logdir:'data_%a_%p.log'
  17.     READSIZE 1048576
  18.     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
  19.     MISSING FIELD VALUES ARE NULL
  20.     REJECT ROWS WITH ALL NULL FIELDS
  21.     (
  22.       "EMPLOYEE_ID" CHAR(255)
  23.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  24.       "FIRST_NAME" CHAR(255)
  25.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  26.       "LAST_NAME" CHAR(255)
  27.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  28.       "HIRE_DATE" CHAR(11)
  29.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
  30.         DATE_FORMAT DATE MASK "dd-mon-yyyy"
  31.     )
  32.   )
  33.   location
  34.   (
  35.     datadir1:'data01.dat',datadir2:'data02.dat'
  36.   )
  37. )  parallel REJECT LIMIT UNLIMITED;
复制代码
授权完,执行脚本ext.sql:
  1. CREATE TABLE hr.extab_employees
  2. (
  3.   "EMPLOYEE_ID" NUMBER(4),
  4.   "FIRST_NAME" VARCHAR2(20),
  5.   "LAST_NAME" VARCHAR2(25),
  6.   "HIRE_DATE" DATE
  7. );

  8. drop table hr.extab_employees  purge;

  9. grant read,write on directory baddir to hr;
  10. grant read,write on directory logdir to hr;
  11. grant read,write on directory datadir1 to hr;
  12. grant read,write on directory datadir2 to hr;

复制代码

查询这个外部表,同时查看baddir和logdir:
  1. [oracle@station90 baddir]$ cat data000_4038.bad
  2. 202,YYY,YYY,2002-02-02
  3. [oracle@station90 baddir]$ ls
  4. data_000_1046.bad  data000_4038.bad  data_001_1048.bad  data001_4040.bad
  5. [oracle@station90 baddir]$
复制代码

接下来,把所有的数据文件gz,同时在execdir中拷贝/bin/gunzip,再写一个shell脚本(11.2要避开OPTIONS):
  1. #!/bin/sh
  2. /home/oracle/execdir/gunzip -c $1
复制代码

改ext.sql最终的版本:
  1. CREATE TABLE hr.extab_employees
  2. (
  3.   "EMPLOYEE_ID" NUMBER(4),
  4.   "FIRST_NAME" VARCHAR2(20),
  5.   "LAST_NAME" VARCHAR2(25),
  6.   "HIRE_DATE" DATE
  7. )
  8. ORGANIZATION external
  9. (
  10.   TYPE oracle_loader
  11.   DEFAULT DIRECTORY datadir1
  12.   ACCESS PARAMETERS
  13.   (
  14.     RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
  15.     preprocessor execdir:'gunzip.sh'
  16.     BADFILE baddir:'data_%a_%p.bad'
  17.     LOGFILE logdir:'data_%a_%p.log'
  18.     READSIZE 1048576
  19.     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
  20.     MISSING FIELD VALUES ARE NULL
  21.     REJECT ROWS WITH ALL NULL FIELDS
  22.     (
  23.       "EMPLOYEE_ID" CHAR(255)
  24.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  25.       "FIRST_NAME" CHAR(255)
  26.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  27.       "LAST_NAME" CHAR(255)
  28.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  29.       "HIRE_DATE" CHAR(11)
  30.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
  31.         DATE_FORMAT DATE MASK "dd-mon-yyyy"
  32.     )
  33.   )
  34.   location
  35.   (
  36.     datadir1:'data01.dat.gz',datadir2:'data02.dat.gz'
  37.   )
  38. )  parallel REJECT LIMIT UNLIMITED;
复制代码
-------------------------------------zip格式的:
  1. CREATE TABLE hr.extab_employees
  2. (
  3.   "EMPLOYEE_ID" NUMBER(4),
  4.   "FIRST_NAME" VARCHAR2(20),
  5.   "LAST_NAME" VARCHAR2(25),
  6.   "HIRE_DATE" DATE
  7. )
  8. ORGANIZATION external
  9. (
  10.   TYPE oracle_loader
  11.   DEFAULT DIRECTORY datadir1
  12.   ACCESS PARAMETERS
  13.   (
  14.     RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
  15.     preprocessor execdir:'zcat'
  16.     BADFILE baddir:'data_%a_%p.bad'
  17.     LOGFILE logdir:'data_%a_%p.log'
  18.     READSIZE 1048576
  19.     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
  20.     MISSING FIELD VALUES ARE NULL
  21.     REJECT ROWS WITH ALL NULL FIELDS
  22.     (
  23.       "EMPLOYEE_ID" CHAR(255)
  24.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  25.       "FIRST_NAME" CHAR(255)
  26.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  27.       "LAST_NAME" CHAR(255)
  28.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
  29.       "HIRE_DATE" CHAR(11)
  30.         TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
  31.         DATE_FORMAT DATE MASK "dd-mon-yyyy"
  32.     )
  33.   )
  34.   location
  35.   (
  36.     datadir1:'data01.dat.zip',datadir2:'data02.dat.zip'
  37.   )
  38. )  parallel REJECT LIMIT UNLIMITED;
复制代码

--------------------------------interval分区:
  1. select    pt.interval    from dba_part_tables  pt
  2. where pt.owner='SH' and pt.table_name='NEWSALES';

  3. select  * from dba_tab_partitions  tp
  4. where tp.table_owner='SH' and tp.table_name='NEWSALES';
复制代码
merge parttions:
  1. alter table sh.newsales merge partitions sys_p21, sys_p22
  2. into partition p_before_3_jan_2005;
复制代码

把普通的rang变成interval:
  1. alter table sh.HISTORICAL_NEWSALES  
  2. set interval (numtoyminterval(1,'year'  );

  3. select  * from sh.HISTORICAL_NEWSALES  partition (SYS_p27) ;
复制代码















回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-11-1 09:30 , Processed in 0.060220 second(s), 28 queries .

快速回复 返回顶部 返回列表