Class Slave Wait | Slave - Wait For Me (Don-Kirshners:1981) Remastered.Mpg 27798 투표 이 답변

당신은 주제를 찾고 있습니까 “class slave wait – Slave – wait for me (Don-Kirshners:1981) Remastered.mpg“? 다음 카테고리의 웹사이트 https://chewathai27.com/you 에서 귀하의 모든 질문에 답변해 드립니다: https://chewathai27.com/you/blog. 바로 아래에서 답을 찾을 수 있습니다. 작성자 R472 Funk Channel TV. 이(가) 작성한 기사에는 조회수 152,660회 및 좋아요 2,430개 개의 좋아요가 있습니다.

class slave wait 주제에 대한 동영상 보기

여기에서 이 주제에 대한 비디오를 시청하십시오. 주의 깊게 살펴보고 읽고 있는 내용에 대한 피드백을 제공하세요!

아래 상세 동영상 보기

d여기에서 Slave – wait for me (Don-Kirshners:1981) Remastered.mpg – class slave wait 주제에 대한 세부정보를 참조하세요

class slave wait 주제에 대한 자세한 내용은 여기를 참조하세요.

Oracle slave class – Burleson Consulting

Oracle definition slave . … The following sys_event_int_10g.sql query can be used to retrieve wait events data for a particular snapshot interval:.

+ 여기에 표시

Source: www.dba-oracle.com

Date Published: 11/22/2022

ASM class slave wait – DBA-Village

Oracle info: oracle 10.1.0. Message: I have configured ASM database . On this there is always ” slave wait” consuming 95% CPU SYS 156 …

+ 여기에 더 보기

Source: www.dba-village.com

Date Published: 11/21/2022

Preformance and tuning – Bing’s Knowledge Base – Google Sites

Streams AQ: qmn coordinator waiting for slave to start 1. slave wait 1. LGWR wait for redo copy 2. enq: CF – contention 3. SQL*Net message to client 3.

+ 여기에 보기

Source: sites.google.com

Date Published: 5/20/2022

how to find which wait event related to which object

‘Streams AQ: waiting for time management or cleanup tasks’, ‘ slave wait’ ) group by inst_, event,CURRENT_OBJ#

+ 자세한 내용은 여기를 클릭하십시오

Source: kcsdba.blogspot.com

Date Published: 2/7/2022

class slave wait等待- tonnytangy – 博客园

操作系统：linux RHEL 7.4 数据库：Oracle 12c R2 问题现象：生产中某个核心数据库升级至12cR2后，出现一些“ slave wait”等待事件， …

+ 더 읽기

Source: www.cnblogs.com

Date Published: 9/1/2021

Oracle | Wait Events – Morgan’s Library

V$EVENT_NAME, One row for each named Oracle kernel wait event. V$SYSTEM_EVENT, One row for each instance-we … slave wait. Data Guard: Gap Manager

+ 더 읽기

Source: www.morganslibrary.org

Date Published: 2/10/2021

“class slave wait” is the top wait event on AWR snapshot

原创. 前两天看到一份sp报告，在wait events事件中，有：. Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn

+ 자세한 내용은 여기를 클릭하십시오

Source: blog.itpub.net

Date Published: 1/30/2021

show ～ Oracle チューニング待機イベント class slave wait

Oracle において待機イベント slave wait とは以下を示します。 slave wait この待機イベントは、le時間なので現状維持で経過をみる。チューニング.

+ 여기에 표시

Source: www.bishounen.sakura.ne.jp

Date Published: 9/11/2022

주제와 관련된 이미지 class slave wait

주제와 관련된 더 많은 사진을 참조하십시오 Slave – wait for me (Don-Kirshners:1981) Remastered.mpg. 댓글에서 더 많은 관련 이미지를 보거나 필요한 경우 더 많은 관련 기사를 볼 수 있습니다.

주제에 대한 기사 평가 class slave wait

Author: R472 Funk Channel TV.
Views: 조회수 152,660회
Likes: 좋아요 2,430개
Date Published: 2016. 10. 14.
Video Url link: https://www.youtube.com/watch?v=em9hOSAFFCA

Oracle slave class

This view stores snapshots of the v$system_event system dynamic view. The following sys_event_int_10g.sql query can be used to retrieve wait events data for a particular snapshot interval:

< sys_event_int_10g.sql select event "Event Name", waits "Waits", timeouts "Timeouts", time "Wait Time (s)", avgwait "Avg Wait (ms)", waitclass "Wait Class" from (select e.event_name event , e.total_waits - nvl(b.total_waits,0) waits , e.total_timeouts - nvl(b.total_timeouts,0) timeouts , (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time , decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0)) ) avgwait , e.wait_class waitclass from dba_hist_system_event b , dba_hist_system_event e where b.snap_id(+) = &pBgnSnap and e.snap_id = &pEndSnap and b.dbid(+) = &pDbId and e.dbid = &pDbId and b.instance_number(+) = &pInstNum and e.instance_number = &pInstNum and b.event_id(+) = e.event_id and e.total_waits > nvl(b.total_waits,0) and e.wait_class <> ‘Idle’ ) order by time desc, waits desc

In the above and some subsequent queries the following parameters need to have appropriate values substituted for them:

§ BgnSnap: the start snapshot number for the AWR snapshot interval of interest.

§ EndSnap: the finish snapshot number for the AWR snapshot interval of interest.

§ DbId: the database identified of the target database.

§ InstNum: the instance number of the target database.

The sample output for this query looks like following:

SQL> @ Sys_event_int_10g.sql

Event Name Waits Timeouts Wait Time (s) Avg Wait (ms) Wait Class

—————————- —– ——– ————- ————- ———-

control file parallel write 11719 0 119.13 10.17 System I/O

class slave wait 20 20 102.46 5,122.91 Other

Queue Monitor Task Wait 74 0 66.74 901.86 Other

log file sync 733 6 20.60 28.11 Commit

db file sequential read 1403 0 14.27 10.17 User I/O

log buffer space 178 0 10.17 57.16 Configuration

process startup 114 0 7.65 67.07 Other

db file scattered read 311 0 2.14 6.87 User I/O

control file sequential read 7906 0 1.33 .17 System I/O

latch free 254 0 1.13 4.45 Other

log file switch completion 20 0 1.11 55.67 Configuration

DBA-Village

Message:

Hi,

By definition, a wait event is not consuming CPU as it waits.

What is the query that returns that result ?

“class slave wait” is an idle event

Regards,

Franck.

Preformance and tuning

Class Notes ‎ > ‎ Preformance and tuning explain plan

dbms_stats.gather_table_stats(‘table_name’) dbms_stats.gather_schema_stats(‘schema_name’)

Oracle optimizer requests gathering the statics on table and index.

alter user sysman identified by values ‘110BD99706F6F456 ‘; alter profile default limit password_life_time unlimited; SQL> select name,password,EXPTIME from sys.user$;
[oracle@oralx bin]$ oerr ora 15294 — check the detail of error msg . 15294, 00000, “SYSASM privilege not allowed on database instance” // Cause: The SYSASM privilege was not allowed on this database instance; // it is allowed only on an ASM instance. // Action: Use either the SYSDBA or the SYSOPER privilege. // [oracle@oralx bin]$

dba_outstanding_alerts dba_alert_history

alter user sysman identified by sysman; alter user sysman

order

— which table has the max rows. select table_name, max(nvl(NUM_ROWS,0)) from dba_tables group by table_name order by 2; ORDER_ITEMS 5346613 SQL> select count(*) from order_entry.order_items; COUNT(*) ———- 5346613

select sid, username from v$session; select sid, time_waited from v$session_event order by 2; — which user has the longest waiting time. select a.sid, a.event, a.username, b. time_waited from v$session a left outer join v$session_event b on a.sid = b.sid order by 3 ; 3 VKTM Logical Idle Wait 1338510

— which envent have the longest waiting time. SQL> select event, total_waits from v$system_event order by 2;

— all the parameter set for the database. select name, value from v$parameter;

select object_name, object_type from dba_objects where status = ‘INVALID’;

alter procedure hr.add_job_history compile; alter package hr.maintainemp compile; alter package hr.maintainemp compile body;

select index_name, table_name from dba_indexes where status = ‘UNUSABLE’; alter index hr.emp_empid_pk rebuild; alter index hr.emp_empid_pk rebuild online; alter index hr.email rebuild tablespace users;

for an instance, using SGA, PGA, backgroud process.

Hard parse(compile) soft parse(

cursors stores in the shared pool.

SQL> show parameter sga

NAME TYPE VALUE ———————————— ———– —————————— lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 264M sga_target big integer 0 SQL> show sga

Total System Global Area 276205568 bytes Fixed Size 1335952 bytes Variable Size 218107248 bytes Database Buffers 54525952 bytes Redo Buffers 2236416 bytes

11G only set this is enough, oracle will set other automatically. alter system set memory_max_target=264M scope=spfile; alter system set memory_target=264M scope=spfile;

10G need to set these 3 parameters. alter system set sga_target=264M scope=spfile; alter system set sga_max_size=264M scope=spfile; + pga_aggregate_target big integer 150M = memory target.

library cache -> check sql is there or not, if there do soft parse, then check buffer cache the data is there or not, if not do a physical I/O go to the datafile to get data. if there do a Logical I/O

if another sql statement having different where clause, and if cursor_sharing= similar, they can

SQL> show parameter cursor NAME TYPE VALUE ———————————— ———– —————————— cursor_sharing string EXACT

alter system set cursor_sharing=similar;

bind variable stores in PGA, a temporary space for program.

SQL> select name, value isdefault from v$parameter where name like ‘%size’; SQL> show parameter size

NAME TYPE VALUE ———————————— ———– —————————— bitmap_merge_area_size integer 1048576 client_result_cache_size big integer 0 create_bitmap_area_size integer 8388608 db_16k_cache_size big integer 0 db_2k_cache_size big integer 0 db_32k_cache_size big integer 0 db_4k_cache_size big integer 0 db_8k_cache_size big integer 0 db_block_size integer 8192 db_cache_size big integer 52M db_flash_cache_size big integer 0 db_keep_cache_size big integer 0 db_recovery_file_dest_size big integer 4062M db_recycle_cache_size big integer 0 global_context_pool_size string hash_area_size integer 131072 java_max_sessionspace_size integer 0 java_pool_size big integer 4M large_pool_size big integer 4M max_dump_file_size string unlimited object_cache_max_size_percent integer 10 object_cache_optimal_size integer 102400 olap_page_pool_size big integer 0 parallel_execution_message_size integer 16384 result_cache_max_size big integer 2M sga_max_size big integer 264M shared_pool_reserved_size big integer 10M shared_pool_size big integer 200M sort_area_retained_size integer 0 sort_area_size integer 65536 streams_pool_size big integer 0 workarea_size_policy string AUTO

set a value to memory_target enables AMM, set a value to sga_target enables ASMM.

SQLPLUS EDITOR COMMANDS Useful…

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ———- ———- ——— ———- —————— ———- ———- ———- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> / — recall the commands

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ———- ———- ——— ———- —————— ———- ———- ———- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> l — list the previous command. 1* select * from emp SQL> a where ename like ‘T%’; — a, append, a__add commands….2 spaces 1* select * from emp where ename like ‘T%’ SQL> l 1* select * from emp where ename like ‘T%’ SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ———- ———- ——— ———- —————— ———- ———- ———- 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

SQL> c/like ‘T%’/= ‘KING’ — c, change, c/original command/new command 1* selecT * from emp where ename = ‘KING’ SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ———- ———- ——— ———- —————— ———- ———- ———- 7839 KING PRESIDENT 17-NOV-81 5000 10

SQL> select ename, job from emp 2 where ename 3 like ‘T%’ 4 /

ENAME JOB ———- ——— TURNER SALESMAN

SQL> l1 — l1, move the first line, and you can edit it. 1* select ename, job from emp SQL> c/job/job, sal 1* select ename, job, sal from emp SQL> /

ENAME JOB SAL ———- ——— ———- TURNER SALESMAN 1500

SQL> l 1 select ename, job, sal from emp 2 where ename 3* like ‘T%’ SQL> l3 — l3 move focus on 3 line 3* like ‘T%’ SQL> del — delete the 3rd line SQL> l 1 select ename, job, sal from emp 2* where ename SQL> a like ‘K%’ 2* where ename like ‘K%’ SQL> /

ENAME JOB SAL ———- ——— ———- KING PRESIDENT 5000

SQL> l 1 select ename, job, sal from emp 2* where ename like ‘K%’ SQL> SQL> c/ename like ‘K%’/ename 2* where ename SQL> I like ‘T%’ — I, upper case I, mean insert a new line. SQL> l 1 select ename, job, sal from emp 2 where ename 3* like ‘T%’ SQL> /

ENAME JOB SAL ———- ——— ———- TURNER SALESMAN 1500

SQL> save bing.sql Created file bing.sql SQL> get bing.sql 1 select order_date,sum(order_total) from orders where order_id in (select order_id from order_items where quantity>3) 2* group by order_date SQL> save bing.sql replace Wrote file bing.sql

SQL> show parameter index NAME TYPE VALUE ———————————— ———– —————————— optimizer_index_caching integer 0 0 means nest loop, change to a number force oracle use hash join optimizer_index_cost_adj integer 100 100 means full table scan, change to 1000 force oracle use index optimizer_use_invisible_indexes boolean FALSE skip_unusable_indexes boolean TRUE Don’t change it, it will affect every sql!!!! But you can put set parameter in a PLSQL/trigger, means you can use it in only a session@@@@

performance tuning focus on each application.

SQL> select pname,pval1 from sys.aux_stats$;

PNAME PVAL1 —————————— ———- STATUS DSTART DSTOP FLAGS 1 CPUSPEEDNW 2696.05568 IOSEEKTIM 10 IOTFRSPEED 4096 SREADTIM MREADTIM CPUSPEED MBRC MAXTHR SLAVETHR

13 rows selected.

SQL> exec dbms_stats.gather_system_stats(‘start’);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_system_stats(‘stop’);

PL/SQL procedure successfully completed.

SQL> select pname,pval1 from sys.aux_stats$;

PNAME PVAL1 —————————— ———- STATUS DSTART DSTOP FLAGS 1 CPUSPEEDNW 2696.05568 IOSEEKTIM 10 IOTFRSPEED 4096 SREADTIM 18417.129 MREADTIM CPUSPEED 1630 MBRC = multiblock_read_count MAXTHR SLAVETHR

13 rows selected.

SQL> show parameter multi NAME TYPE VALUE ———————————— ———– —————————— db_file_multiblock_read_count integer 42 means when you do a FTS, it will read 42 blocks, one time.

exec dbms_stats.gather_schema_stats(‘scott’);

SQL> select table_name, last_analyzed from user_tables;

TABLE_NAME LAST_ANALYZED —————————— —————— DEPT EMP BONUS SALGRADE

SQL> exec dbms_stats.gather_schema_stats(‘scott’);

PL/SQL procedure successfully completed.

SQL> select table_name, last_analyzed from user_tables;

TABLE_NAME LAST_ANALYZED —————————— —————— DEPT 26-JUL-12 EMP 26-JUL-12 BONUS 26-JUL-12 SALGRADE 26-JUL-12

SQL> select index_name, last_analyzed from user_indexes;

INDEX_NAME LAST_ANALYZED —————————— —————— PK_EMP 26-JUL-12 PK_DEPT 26-JUL-12

if the table has not change more than 10%, it will not gather statistics. you can change the 10% to 5%….or ….

SQL> show parameter samp NAME TYPE VALUE ———————————— ———– —————————— optimizer_dynamic_sampling integer 2

SQL> select event, sum(time_waited) from v$system_event group by event order by 2; EVENT SUM(TIME_WAITED) —————————————————————- —————- buffer busy waits 0 log file single write 0 ADR file lock 0 instance state change 0 log file sequential read 0 direct path write 0 latch: redo allocation 0 SQL*Net more data to client 0 latch free 0 Parameter File I/O 0 asynch descriptor resize 1 ADR block file write 1 Disk file operations I/O 1 direct path sync 1 SQL*Net more data from client 1 db file single write 1 Streams AQ: qmn coordinator waiting for slave to start 1 class slave wait 1 LGWR wait for redo copy 2 enq: CF – contention 3 SQL*Net message to client 3 rdbms ipc reply 4 db file parallel read 5 control file sequential read 9 reliable message 10 enq: PV – syncstart 12 SQL*Net break/reset to client 15 ADR block file read 26 direct path read 28 db file scattered read 114 os thread startup 120 log file sync 182 log file parallel write 308 db file async I/O submit 313 control file parallel write 399 control file heartbeat 400 db file sequential read 511 Streams AQ: waiting for time management or cleanup tasks 3453 Space Manager: slave idle wait 602663 smon timer 627532 dispatcher timer 630053 Streams AQ: waiting for messages in the queue 632655 wait for unread message on broadcast channel 632908 Streams AQ: qmn slave idle wait 632994 Streams AQ: qmn coordinator idle wait 633020 shared server idle wait 633532 pmon timer 633805 DIAG idle wait 1267507 SQL*Net message from client 3365050 rdbms ipc message 6853067

50 rows selected.

SQL> exec dbms_workload_repository.create_snapshot; — get a snapshot manually. SQL> @?/rdbms/admin/awrrpt.sql — create a report of snapshot.

Current Instance ~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance ———– ———— ——– ———— 1272080324 ORCL 1 orcl

Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter ‘html’ for an HTML report, or ‘text’ for plain text Defaults to ‘html’ Enter value for report_type: text

Type Specified: text

Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host ———— ——– ———— ———— ———— * 1272080324 1 ORCL orcl oralx.sait.c a 1272080324 1 ORCL orcl localhost.lo caldomain

Using 1272080324 for database Id Using 1 for instance number

Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots.

Enter value for num_days: 1

Listing the last day’s Completed Snapshots

Snap Instance DB Name Snap Id Snap Started Level ———— ———— ——— —————— —– orcl ORCL 89 26 Jul 2012 00:00 1 90 26 Jul 2012 01:00 1 91 26 Jul 2012 02:00 1 92 26 Jul 2012 03:00 1 93 26 Jul 2012 04:00 1 94 26 Jul 2012 05:00 1 95 26 Jul 2012 06:00 1 96 26 Jul 2012 07:00 1 97 26 Jul 2012 08:00 1 98 26 Jul 2012 09:00 1 99 26 Jul 2012 10:00 1 100 26 Jul 2012 11:00 1 101 26 Jul 2012 11:31 1 102 26 Jul 2012 11:32 1

Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 100 Begin Snapshot Id specified: 100

Enter value for end_snap: 102 End Snapshot Id specified: 102

Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is awrrpt_1_100_102.txt. To use this name, press to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_100_102.txt

WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Startup Time Release RAC ———— ———– ———— ——– ————— ———– — ORCL 1272080324 orcl 1 26-Jul-12 09:27 11.2.0.1.0 NO

Host Name Platform CPUs Cores Sockets Memory(GB) —————- ——————————– —- —– ——- ———- oralx.sait.ca Linux IA (32-bit) 1 2.96

nested loop join, from a.id search all the id from b, then go to next. b is better having a index. hash join, throw the large table in to memory, and match the small one, sort join, sort both table, and match each other.

SQL> explain plan for select e.ename,d.dname from dept d, emp e where e.deptno = d.deptno;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT ———————————————————————————————————————————— Plan hash value: 844388907

—————————————————————————————- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————————- | 0 | SELECT STATEMENT | | 14 | 308 | 6 (17)| 00:00:01 | | 1 | MERGE JOIN | | 14 | 308 | 6 (17)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 | | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | |* 4 | SORT JOIN | | 14 | 126 | 4 (25)| 00:00:01 | | 5 | TABLE ACCESS FULL | EMP | 14 | 126 | 3 (0)| 00:00:01 | —————————————————————————————-

Predicate Information (identified by operation id): —————————————————

4 – access(“E”.”DEPTNO”=”D”.”DEPTNO”) filter(“E”.”DEPTNO”=”D”.”DEPTNO”)

18 rows selected.

Execution Plan ———————————————————- Plan hash value: 2137789089

——————————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ——————————————————————————————— | 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 | ——————————————————————————————— SQL> conn / as sysdba Connected. SQL> grant plustrace to scott; Grant succeeded. SQL> conn scott/tiger Connected. SQL> set autotrace on —per session… SQL> select sysdate from dual; SYSDATE —————— 26-JUL-12 Execution Plan ———————————————————- Plan hash value: 1388734953

—————————————————————– | Id | Operation | Name | Rows | Cost (%CPU)| Time | —————————————————————– | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | —————————————————————–

Statistics ———————————————————- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 426 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

SQL> conn order_entry/order_entry Connected. SQL> set autotrace on SQL> select count(*) from order_items;

COUNT(*) ———- 5346613

Execution Plan ———————————————————- Plan hash value: 3741773792

——————————————————————————– | Id | Operation | Name | Rows | Cost (%CPU)| Time | ——————————————————————————– | 0 | SELECT STATEMENT | | 1 | 3702 (1)| 00:00:45 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| ORDER_ITEMS_PK | 5346K| 3702 (1)| 00:00:45 | ——————————————————————————–

Statistics ———————————————————- 331 recursive calls 0 db block gets 13682 consistent gets 13608 physical reads 0 redo size 425 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 5 sorts (memory) 0 sorts (disk) 1 rows processed

SQL> l 1 select a.order_date,sum(a.order_total) from orders a, order_items b 2 where a.order_id = b.order_id and b.quantity>3 3* group by order_date SQL> /

ORDER_DATE SUM(A.ORDER_TOTAL) ————————————————————————— —————— 23-MAR-07 12.00.00.000000 AM 6716 23-MAR-07 12.59.52.000000 PM 660 09-APR-07 04.53.07.000000 PM 267

Execution Plan ———————————————————- Plan hash value: 1472704135

——————————————————————————————- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ——————————————————————————————- | 0 | SELECT STATEMENT | | 60098 | 1760K| | 19524 (1)| 00:03:55 | | 1 | HASH GROUP BY | | 60098 | 1760K| 102M| 19524 (1)| 00:03:55 | |* 2 | HASH JOIN | | 2673K| 76M| 48M| 11497 (1)| 00:02:18 | | 3 | TABLE ACCESS FULL| ORDERS | 1529K| 30M| | 1708 (1)| 00:00:21 | |* 4 | TABLE ACCESS FULL| ORDER_ITEMS | 2673K| 22M| | 4725 (2)| 00:00:57 | ——————————————————————————————-

Predicate Information (identified by operation id): —————————————————

2 – access(“A”.”ORDER_ID”=”B”.”ORDER_ID”) 4 – filter(“B”.”QUANTITY”>3)

Statistics ———————————————————- 166 recursive calls 0 db block gets 23368 consistent gets 28303 physical reads 0 redo size 569 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed

SQL> select a.order_date,sum(a.order_total) from orders a, (select order_id from order_items where quantity>3) b 2 where a.order_id = b.order_id 3 group by order_date;

ORDER_DATE SUM(A.ORDER_TOTAL) ————————————————————————— —————— 23-MAR-07 12.00.00.000000 AM 6716 23-MAR-07 12.59.52.000000 PM 660 09-APR-07 04.53.07.000000 PM 267

Execution Plan ———————————————————- Plan hash value: 1472704135

——————————————————————————————- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ——————————————————————————————- | 0 | SELECT STATEMENT | | 60098 | 1760K| | 19524 (1)| 00:03:55 | | 1 | HASH GROUP BY | | 60098 | 1760K| 102M| 19524 (1)| 00:03:55 | |* 2 | HASH JOIN | | 2673K| 76M| 48M| 11497 (1)| 00:02:18 | | 3 | TABLE ACCESS FULL| ORDERS | 1529K| 30M| | 1708 (1)| 00:00:21 | |* 4 | TABLE ACCESS FULL| ORDER_ITEMS | 2673K| 22M| | 4725 (2)| 00:00:57 | ——————————————————————————————-

Predicate Information (identified by operation id): —————————————————

2 – access(“A”.”ORDER_ID”=”ORDER_ID”) 4 – filter(“QUANTITY”>3)

Statistics ———————————————————- 87 recursive calls 0 db block gets 23353 consistent gets 28020 physical reads 0 redo size 569 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed

select order_date,sum(order_total) from orders where order_id in (select order_id from order_items where quantity>3) group by order_date

SQL> select order_date,sum(order_total) from orders where order_id in (select order_id from order_items where quantity>3) 2 group by order_date;

ORDER_DATE SUM(ORDER_TOTAL) ————————————————————————— —————- 23-MAR-07 12.00.00.000000 AM 6716 23-MAR-07 12.59.52.000000 PM 660 09-APR-07 04.53.07.000000 PM 267

Execution Plan ———————————————————- Plan hash value: 3231766287

——————————————————————————————- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ——————————————————————————————- | 0 | SELECT STATEMENT | | 60098 | 1760K| | 15834 (1)| 00:03:11 | | 1 | HASH GROUP BY | | 60098 | 1760K| 53M| 15834 (1)| 00:03:11 | |* 2 | HASH JOIN SEMI | | 1393K| 39M| 48M| 11497 (1)| 00:02:18 | | 3 | TABLE ACCESS FULL| ORDERS | 1529K| 30M| | 1708 (1)| 00:00:21 | |* 4 | TABLE ACCESS FULL| ORDER_ITEMS | 2673K| 22M| | 4725 (2)| 00:00:57 | ——————————————————————————————-

Predicate Information (identified by operation id): —————————————————

2 – access(“ORDER_ID”=”ORDER_ID”) 4 – filter(“QUANTITY”>3)

Statistics ———————————————————- 48 recursive calls 0 db block gets 23347 consistent gets 28020 physical reads 0 redo size 567 bytes sent via SQL*Net to client 415 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 3 rows processed

SQL> create index abcd on order_items(quantity) tablespace users compute statistics; Index created.

SQL> alter system flush BUFFER_CACHE; System altered.

SQL> alter table emp enable ROW MOVEMENT;

Table altered.

SQL> alter table emp shrink space compact;

Table altered.

SQL> alter table emp shrink space;

Table altered.

SQL> select sum(bytes)*100/125829120 from dba_extents where tablespace_name=’TBSALERT’;

SUM(BYTES)*100/125829120 ———————— 75

SQL> select reason from dba_outstanding_alerts where object_name=’TBSALERT’;

REASON ———————————————————————————————————————————— Tablespace [TBSALERT] is [60 percent] full

IN VI…. ESC :%s/^/ select * from customer where cust_id = 1/ — add select …..to the first column ESC :%s/$/;/ — add ; to the last column

another report…. AWR = statspack, manual snapshots automatica and configurable

@?/rdbms/admin/spreport.sql

How read awr report:

Oracle STATSPACK installation steps Overview of the STATSPACK Scripts The STATSPACK scripts have completely changed. All of the STATSPACK scripts are located in the $ORACLE_HOME/rdbms/admin directory. Oracle 8.1.7 and Oracle9i Script Name Pre Oracle 8.1.7 Script Name Script Function spdoc.txt statspack.doc Installation documentation spcreate.sql statscre.sql Create user, tables & install packages spreport.sql statsrep.sql Standard STATSPACK report spauto.sql statsauto.sql Schedule automatic data collection spuexp.par statsuexp.par Parameter file for full STATSPACK export sppurge.sql – new file – Purge SQL for removing old snapshots sptrunc.sql – new file – Script to truncate all STATSPACK tables spup816.sql – new file – Upgrade script to moving to 8.1.6 spup817.sql – new file – Upgrade script to moving to 8.1.7 spdrop.sql statsdrp.sql Script to drop all STATSPACK tables spcpkg.sql statspack.sql Script to create statspack package spctab.sql statsctab.sql Creates STATSPACK tables spcusr.sql statscusr.sql Creates STATSPACK user & assigns grants spdtab.sql statsdtab.sql Drops all STATSPACK tables spdusr.sql statsdusr.sql Drops the statspack user Next, let’s take a closer look at these scripts and see details on how to install STATSPACK. Because of the differences between versions, we will have two sections: one for pre-8.1.7 and another for Oracle 8.1.7 and Oracle9i STATSPACK. STATSPACK scripts for Oracle8 and Oracle8i You can see all of the scripts by going to the $ORACLE_HOME/rdbms/admin directory and listing all files that begin with “stat”: >cd $ORACLE_HOME/rdbms/admin

server1*db01-/u01/app/oracle/product/8.1.6_64/rdbms/admin

>ls -al stat*

-rw-r–r– 1 oracle oinstall 1739 Dec 6 1999 statsauto.sql

-rw-r–r– 1 oracle oinstall 843 Dec 6 1999 statscre.sql

-rw-r–r– 1 oracle oinstall 27183 Nov 10 1999 statsctab.sql

-rw-r–r– 1 oracle oinstall 4686 Nov 10 1999 statscusr.sql

-rw-r–r– 1 oracle oinstall 792 Aug 27 1999 statsdrp.sql

-rw-r–r– 1 oracle oinstall 3236 Nov 10 1999 statsdtab.sql

-rw-r–r– 1 oracle oinstall 1081 Nov 10 1999 statsdusr.sql

-rw-r–r– 1 oracle oinstall 26667 Dec 6 1999 statspack.doc

-rw-r–r– 1 oracle oinstall 49821 Nov 10 1999 statspack.sql

-rw-r–r– 1 oracle oinstall 46873 Nov 10 1999 statsrep.sql

-rw-r–r– 1 oracle oinstall 559 Aug 27 1999 statsuexp.par Let’s begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files in a hierarchy: · statscre.sql This is the first install script run after you create the tablespace. It calls several subscripts: statscusr.sql This script creates a user called PERFSTAT with the required permissions. statsctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user. statspack.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures. · statsauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour. · statsdrp.sql This script is used to drop all STATSPACK entities. This script calls these subscripts: · statsdtab.sql This drops all STATSPACK tables and indexes. · statsdusr.sql This script drops the PERFSTAT user. · statsuexp.par This is an export parameter file for exporting the STATSPACK objects. This can be useful if you want to consolidate STATSPACK reports for several databases into a single STATSPACK structure. · statspack.doc This is a generic read-me file explaining the installation and operation of the STATSPACK utility. · statsrep.sql This is the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report. · statsrep80.sql This is a version of the STATSPACK report for Oracle 8.0. Now that we understand the functions of each of the files, we are ready to install STATSPACK. Our first step is to review the installation files for the STATSPACK install. STATSPACK scripts for post 8.1.6 STATSPACK You can see all of the scripts by going to the $ORACLE_HOME/rdbms/admin directory and listing all files that begin with “sp”: >cd $ORACLE_HOME/rdbms/admin

server1*db01-/u01/app/oracle/product/8.1.6_64/rdbms/admin

>ls -al sp*

-rw-r–r– 1 oracle oinstall 1771 May 10 2001 spauto.sql

-rw-r–r– 1 oracle oinstall 82227 May 10 2001 spcpkg.sql

-rw-r–r– 1 oracle oinstall 877 May 10 2001 spcreate.sql

-rw-r–r– 1 oracle oinstall 42294 May 10 2001 spctab.sql

-rw-r–r– 1 oracle oinstall 7949 May 10 2001 spcusr.sql

-rw-r–r– 1 oracle oinstall 69074 May 10 2001 spdoc.txt

-rw-r–r– 1 oracle oinstall 758 May 10 2001 spdrop.sql

-rw-r–r– 1 oracle oinstall 4342 May 10 2001 spdtab.sql

-rw-r–r– 1 oracle oinstall 1363 May 10 2001 spdusr.sql

-rw-r–r– 1 oracle oinstall 7760 May 10 2001 sppurge.sql

-rw-r–r– 1 oracle oinstall 113753 May 10 2001 sprepins.sql

-rw-r–r– 1 oracle oinstall 1284 May 10 2001 spreport.sql

-rw-r–r– 1 oracle oinstall 26556 May 10 2001 sprepsql.sql

-rw-r–r– 1 oracle oinstall 2726 May 10 2001 sptrunc.sql

-rw-r–r– 1 oracle oinstall 588 May 10 2001 spuexp.par

-rw-r–r– 1 oracle oinstall 30462 May 10 2001 spup816.sql

-rw-r–r– 1 oracle oinstall 23309 May 10 2001 spup817.sql Let’s begin by reviewing the functions of each of these files. Several of the files call subfiles, so it helps if we organize the files as a hierarchy: · spcreate.sql This is the first install script run after you create the tablespace. It calls several subscripts: · spcsr.sql This script creates a user called PERFSTAT with the required permissions. · spctab.sql This creates the STATSPACK tables and indexes, owned by the PERFSTAT user. · spcpkg.sql This creates the PL/SQL package called STATSPACK with the STATSPACK procedures. · spauto.sql This script contains the dbms_job.submit commands that will execute a STATSPACK snapshot every hour. · spdrop.sql This script is used to drop all STATSPACK entities. This script calls these subscripts: · spdtab.sql This drops all STATSPACK tables and indexes. · spdusr.sql This script drops the PERFSTAT user. · spdoc.txt This is a generic read-me file explaining the installation and operation of the STATSPACK utility. · spreport.sql This is the shell for the only report provided in STATSPACK. It prompts you for the start and end snapshots, and then produces an elapsed-time report. · sprepins.sql This is the actual SQL that produces the STATSPACK report. · sppurge.sql This is a script to delete older unwanted snapshots. · spuexp.par This is a export parameter file to export all of the STATSPACK data. · sptrunc.sql This is a script to truncate all STATSPACK tables. · spup816.sql This is a script to upgrade pre-8.1.7 STATSPACK tables to use the latest schema. Note that you must export the STATSPACK schema before running this script. · spup817.sql This is a script to upgrade to Oracle 8.1.7 from Oracle 8.1.6. Now, let’s take a closer look at the installation procedures.

create the resource profile which gives the breakdown of timed events in order of greatest response time duration to least.

view exactly which oracle statements are being executed.

wait events, and wait event response times for each statement being executed.

SQL exectution plans for each statement executed.

Logic I/O (buffer cache blocks read)

Physical I/O (OS reads/writes to oracle data/temp/redo/archive/control files)

number of parse calls, executes, fetches.

Bind variables values.

ETC. Oracle extended SQL trace:

DBA’s response. set trace user exec code trace generated user log off vi trace file. use Orasrp to read the trace files, covert trace file to user readable format.

SQL> select username, status, sid, serial# from v$session where lower(username) = ‘order_entry’ order by 4;

USERNAME STATUS SID SERIAL# —————————— ——– ———- ———- ORDER_ENTRY INACTIVE 40 28295

SQL> @?/rdbms/admin/dbmssupp.sql

Package created. Package body created.

SQL> exec DBMS_SUPPORT.start_trace_in_session(40,28295,true,true); PL/SQL procedure successfully completed.

SQL> show parameter dump user_dump_dest string /u01/app/oracle/diag/rdbms/orcl/orcl/trace

user order_entry to run ‘SQL> select ‘X’ from dual;’

SQL> exec DBMS_SUPPORT.stop_trace_in_session(40,28295);

view /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5083.trc
[oracle@oralx ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5083.trc /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5083.tkprofs
[oracle@oralx ~]$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_5083.tkprofs Now it’s readable. ———————————————————————————— trace file will be placed in user_dump_dest in 10g, ADR-home/trace in 11g.

All the options: DBMS_SUPPORT.start_trace_in_session(sid,serial,waits,binds)

DBMS_MONITOR.session_trace_enable

DBMS_MONITOR . client_id_trace_enable

client_id_trace_enable DBMS_MONITOR.serv_mod_act_trace_enable — All versions. SQL> ALTER SESSION SET sql_trace=TRUE; SQL> ALTER SESSION SET sql_trace=FALSE; SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE); SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE); SQL> ALTER SESSION SET EVENTS ‘10046 trace name context forever, level 8’; SQL> ALTER SESSION SET EVENTS ‘10046 trace name context off’; SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE); SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE); SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>’ ‘); SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>’ ‘); — Available from SQL*Plus since 8i (commandline utility prior to this. SQL> CONN sys/password AS SYSDBA; — User must have SYSDBA. SQL> ORADEBUG SETMYPID; — Debug current session. SQL> ORADEBUG SETOSPID 1234; — Debug session with the specified OS process. SQL> ORADEBUG SETORAPID 123456; — Debug session with the specified Oracle process ID. SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12; SQL> ORADEBUG TRACEFILE_NAME; — Display the current trace file. SQL> ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF; — All versions, requires DBMS_SUPPORT package to be loaded. SQL> EXEC DBMS_SUPPORT.start_trace(waits=>TRUE, binds=>FALSE); SQL> EXEC DBMS_SUPPORT.stop_trace; SQL> EXEC DBMS_SUPPORT.start_trace_in_session(sid=>123, serial=>1234, waits=>TRUE, binds=>FALSE); SQL> EXEC DBMS_SUPPORT.stop_trace_in_session(sid=>123, serial=>1234); SQL> select username, status, sid, serial# from v$session where lower(username) = ‘hr’;

USERNAME STATUS SID SERIAL# —————————— ——– ———- ———- HR INACTIVE 43 20792

SQL> exec DBMS_SUPPORT.start_trace_in_session(43,20792,true,true);

PL/SQL procedure successfully completed.

SQL> exec DBMS_SUPPORT.stop_trace_in_session(43,20792);

PL/SQL procedure successfully completed.

alter session set max_dump_file_size=unlimited; alter session set timed_statistics=true; alter session set statistics_level=all; alter session set tracefile_identifier=trace_hr.log;

SQL> execute dbms_monitor.session_trace_enable(waits=>true,binds=>true);

PL/SQL procedure successfully completed.

SQL> select EMPLOYEE_ID, LAST_NAME from EMPLOYEES where EMPLOYEE_ID=25;

no rows selected

SQL> select EMPLOYEE_ID, LAST_NAME from EMPLOYEES where EMPLOYEE_ID=1;

no rows selected

SQL> select EMPLOYEE_ID, LAST_NAME from EMPLOYEES where EMPLOYEE_ID=200;

EMPLOYEE_ID LAST_NAME ———– ————————- 200 Whalen

SQL> execute dbms_monitor.session_trace_disable(session_id=>null,serial_num=>null);

PL/SQL procedure successfully completed.

ls -ltr = dir /od

alter session set statistics_level=all; return all the info.,set back to typical.

PIO = datafile => SGA(buffer cache), request a block from datafile. cr,cu in a raw trace file. LIO = session => SGA(buffer cache), 1 block read from database buffer cache, physical reads statistic or reads in a Raw trace file.

reduce the LIO, Lio is more expensive….. more LIOs, more latches. formula : 1 row should require <= 10 LIOs per joined table. MAX tolerated IO = #joined-tables X #rows returned X 10 = PIO + LIO Exception: sum(), aggregate functions. cnt row number return...2 tables, so 2*5*10 = 100, access 100 buffer_caches. cr = 2678, LIO...so it was not acceptable.= consistent gets FTS customer 2669 ord_customer_ix 4 autotrace is a explain plan. dbms_xpaln.display_cursor give a execution plan for the query. run you SQL, then ..... select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST')); or SQL> explain plan for select e.ename,d.dname from dept d, emp e where e.deptno = d.deptno; Explained. SQL> select * from table(dbms_xplan.display);

Autotrace is a sql plus feature that shows how much work it took to perform your query.

set autotrace on | traceonly | off [explain | statistics ]
set autotrace off no report generated

set autotrace on explain query runs reports show execution path

set auotrace on statistics query runs and report shows execution statistics

set autotrace on query run and report shows both the exec path and execution statistics

set autotrace traceonly like (autotrace on) except surpresses query results

set autotrace traceonly statistics supresses query results, and shows only exec.stats

set autotrace traceonly explain does not actually exec query results and shows only the execution plan.

* AWR REPORT: take a before and after picture(snapshot)

take every hour and 8 days.

in reports top 5 waits, sql statistics.

statpack to replace it.

overview of the system. * EXTENDED Trace:

* dbms_xplan.display_cursor * set autotrace on * logical IOS, physical IOS
[oracle@oralx trace]$ tkprof orcl_ora_7039.trc orcl_ora_7039.out

TKPROF: Release 11.2.0.1.0 – Development on Wed Aug 1 08:31:47 2012

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

db_file_squential_read, means index read.. solution: create a new efficient index..
[oracle@oralx ~]$ oerr ora 1 00001, 00000, “unique constraint (%s.%s) violated” // *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. // For Trusted Oracle configured in DBMS MAC mode, you may see // this message if a duplicate entry exists at a different level. // *Action: Either remove the unique restriction or do not insert the key.

SQL> !view gav.sql select /*+ gather_plan_statistics */ first_name from employees; select * from table(dbms_Xplan.display_cursor(null,null,’IOSTATS LAST’)); / 108 rows selected.

PLAN_TABLE_OUTPUT ———————————————————————————————————————————— SQL_ID dk15kzhug86x1, child number 0 ————————————- select /*+ gather_plan_statistics */ first_name from employees

Plan hash value: 1445457117

————————————————————————————————– | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ————————————————————————————————– | 0 | SELECT STATEMENT | | 1 | | 108 |00:00:00.01 | 15 | 6 | | 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 108 | 108 |00:00:00.01 | 15 | 6 | ————————————————————————————————–

13 rows selected.

explain plan for… set autotrace on… tracing a session… Dbms_xplan.display_cursor…

class slave wait等待

操作系统：linux RHEL 7.4

数据库：Oracle 12c R2

问题现象：生产中某个核心数据库升级至12cR2后，出现一些“class slave wait”等待事件，且发起用户是sys和customer user，program是后台进程，感觉很奇怪

问题原因：

查询MOS得知

Subject: “class slave wait” is the top wait event on AWR snapshot

前两天看到一份sp报告，在wait events事件中，有：

Avg

Total Wait wait Waits

Event Waits Timeouts Time (s) (ms) /txn

—————————- ———— ———- ———- —— ——–

db file sequential read 4,490,735 0 14,958 3 3,024.1

control file parallel write 5,256 0 179 34 3.5

db file scattered read 51,799 0 100 2 34.9

class slave wait 10 10 51 5117 0.0

看到有class slave wait等待avg wait（ms）居然是5117，一直没弄明白为啥能这么高？？

找了几天，最后想起来metalink上search下，居然发现是bug：

Applies to:

Symptoms

Enterprise Manager for RDBMS – Version: 10.1.0.3.0This problem can occur on any platform.

“class slave wait” is the top wait event on AWR snapshot.

It is grouped under wait class “other”

Output of ADDM reports for these AWR snapshots:

FINDING 1: 100% impact (3431 seconds)

————————————-

Wait class “Other” was consuming significant database time.

NO RECOMMENDATIONS AVAILABLE

ADDITIONAL INFORMATION: Database latches in the “Other” wait class were not

consuming significant database time.

Cause

Bug 3876475

Hdr: 3876475 10.1 RDBMS 10.0 VOS PRODID-5 PORTID-453

Abstract: MIS-CLASSIFICATION OF KSV SLAVE CLASS WAIT

“class slave wait” has the wrong wait classification.It should be marked as an “idle” wait

Solution

Issue is fixed in 10.1.0.4 and 10.2

Upgrade to either 10.1.0.4 or 10.2

数据库版本恰好就是oracle 10.1.0.3.0，这个点背啊！！！

키워드에 대한 정보 class slave wait

다음은 Bing에서 class slave wait 주제에 대한 검색 결과입니다. 필요한 경우 더 읽을 수 있습니다.

이 기사는 인터넷의 다양한 출처에서 편집되었습니다. 이 기사가 유용했기를 바랍니다. 이 기사가 유용하다고 생각되면 공유하십시오. 매우 감사합니다!

사람들이 주제에 대해 자주 검색하는 키워드 Slave – wait for me (Don-Kirshners:1981) Remastered.mpg

동영상
공유
카메라폰
동영상폰
무료
올리기

Slave #- #wait #for #me #(Don-Kirshners:1981) #Remastered.mpg

YouTube에서 class slave wait 주제의 다른 동영상 보기

주제에 대한 기사를 시청해 주셔서 감사합니다 Slave – wait for me (Don-Kirshners:1981) Remastered.mpg | class slave wait, 이 기사가 유용하다고 생각되면 공유하십시오, 매우 감사합니다.

Class Slave Wait | Slave – Wait For Me (Don-Kirshners:1981) Remastered.Mpg 27798 투표 이 답변

class slave wait 주제에 대한 동영상 보기

d여기에서 Slave – wait for me (Don-Kirshners:1981) Remastered.mpg – class slave wait 주제에 대한 세부정보를 참조하세요

class slave wait 주제에 대한 자세한 내용은 여기를 참조하세요.

Oracle slave class – Burleson Consulting

ASM class slave wait – DBA-Village

Preformance and tuning – Bing’s Knowledge Base – Google Sites

how to find which wait event related to which object

class slave wait等待- tonnytangy – 博客园

Oracle | Wait Events – Morgan’s Library

“class slave wait” is the top wait event on AWR snapshot

show ～ Oracle チューニング待機イベント class slave wait

주제와 관련된 이미지 class slave wait

주제에 대한 기사 평가 class slave wait

Oracle slave class

DBA-Village

Preformance and tuning

class slave wait等待

Subject: “class slave wait” is the top wait event on AWR snapshot

키워드에 대한 정보 class slave wait

사람들이 주제에 대해 자주 검색하는 키워드 Slave – wait for me (Don-Kirshners:1981) Remastered.mpg

Leave a Comment Cancel reply

class slave wait 주제에 대한 동영상 보기

d여기에서 Slave – wait for me (Don-Kirshners:1981) Remastered.mpg – class slave wait 주제에 대한 세부정보를 참조하세요

class slave wait 주제에 대한 자세한 내용은 여기를 참조하세요.

Oracle slave class – Burleson Consulting

ASM class slave wait – DBA-Village

Preformance and tuning – Bing’s Knowledge Base – Google Sites

how to find which wait event related to which object

class slave wait等待- tonnytangy – 博客园

Oracle | Wait Events – Morgan’s Library

“class slave wait” is the top wait event on AWR snapshot

show ～ Oracle チューニング 待機イベント class slave wait

주제와 관련된 이미지 class slave wait

주제에 대한 기사 평가 class slave wait

Oracle slave class

DBA-Village

Preformance and tuning

class slave wait等待

Subject: “class slave wait” is the top wait event on AWR snapshot

키워드에 대한 정보 class slave wait

사람들이 주제에 대해 자주 검색하는 키워드 Slave – wait for me (Don-Kirshners:1981) Remastered.mpg

Leave a Comment Cancel reply

show ～ Oracle チューニング待機イベント class slave wait