[20170103]关于latch shared pool.txt
--网友问的问题:http://www.itpub.net/thread-2074374-1-1.html
SCOTT@book> select * from V$EVENT_NAME where name='latch: shared pool';
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
---------- ---------- -------------------- -------------------- -------------------- -------------------- ------------- ----------- --------------------
285 2696347763 latch: shared pool address number tries 3875070507 4 Concurrency
--问的问题是P1的address表示什么做1个测试:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
--建立执行如下脚本:
drop table t purge ;
create table t as select rownum id ,'test' pad from dual connect by level<=2e5;
create unique index pk_t on t(id);
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1');
CREATE OR REPLACE PROCEDURE testmutex0
AS
v_pad VARCHAR2 (200);
v_pad1 VARCHAR2 (6);
BEGIN
--v_pad1 := DBMS_RANDOM.STRING ('U', 6);
FOR i IN 1 .. 400000
LOOP
--EXECUTE IMMEDIATE 'select pad from t ' || v_pad1 || ' where id = :j' INTO v_pad USING i;
EXECUTE IMMEDIATE 'select pad from t where id = '|| i INTO v_pad;
END LOOP;
END;
/
CREATE OR REPLACE PROCEDURE do_testmutex0
AS
v_jobno NUMBER := 0;
BEGIN
FOR i IN 0 .. 9
LOOP
DBMS_JOB.submit (v_jobno, 'testmutex0;', SYSDATE);
END LOOP;
commit;
END;
/
alter system flush shared_pool;
exec do_testmutex0
DECLARE
job_count NUMBER;
v_jobno NUMBER := 0;
BEGIN
DBMS_WORKLOAD_REPOSITORY.create_snapshot ();
FOR i IN 1 .. 10
LOOP
DBMS_JOB.submit (v_jobno, 'testmutex0;', SYSDATE);
END LOOP;
COMMIT;
COMMIT;
DBMS_LOCK.sleep (1);
SELECT COUNT (*) INTO job_count FROM dba_jobs_running WHERE ROWNUM <= 1;
WHILE (job_count >= 1)
LOOP
DBMS_LOCK.sleep (1);
SELECT COUNT (*) INTO job_count FROM dba_jobs_running WHERE ROWNUM <= 1;
END LOOP;
DBMS_LOCK.sleep (1);
DBMS_WORKLOAD_REPOSITORY.create_snapshot ();
END;
/
2.观察测试:
SYS@book> select * from (select sql_id,p1,p1text,p2,p2text,p3,p3text from V$ACTIVE_SESSION_HISTORY where event='latch: shared pool' order by sample_time desc) where rownum<=3;
SQL_ID P1 P1TEXT P2 P2TEXT P3 P3TEXT
------------- ---------- ------- --- ------ -- ------
dp98rbnqccpr2 1611716624 address 336 number 0 tries
0f1c0v0p87qvg 1611716624 address 336 number 0 tries
dp98rbnqccpr2 1611716624 address 336 number 0 tries
>select * from V$LATCH where name = 'shared pool';
Record View
As of: 2017/1/3 15:18:23
ADDR: 000000006003C1B0
LATCH#: 336
LEVEL#: 7
NAME: shared pool
HASH: 2276811941
GETS: 57388442
MISSES: 14691680
SLEEPS: 1248342
IMMEDIATE_GETS: 0
IMMEDIATE_MISSES: 0
WAITERS_WOKEN: 0
WAITS_HOLDING_LATCH: 0
SPIN_GETS: 13461094
SLEEP1: 0
SLEEP2: 0
SLEEP3: 0
SLEEP4: 0
SLEEP5: 0
SLEEP6: 0
SLEEP7: 0
SLEEP8: 0
SLEEP9: 0
SLEEP10: 0
SLEEP11: 0
WAIT_TIME: 32645859
--1611716624 = 0x6010d810.
SYS@book> select addr,LATCH#,LEVEL#,NAME,HASH,GETS from V$LATCH_parent where name = 'shared pool';
ADDR LATCH# LEVEL# NAME HASH GETS
---------------- ---------- ---------- -------------------- ---------- ----------
000000006003C1B0 336 7 shared pool 2276811941 30
SYS@book> select addr,LATCH#,CHILD#,LEVEL#,NAME,HASH,GETS from V$LATCH_CHILDREN where name = 'shared pool';
ADDR LATCH# CHILD# LEVEL# NAME HASH GETS
---------------- ---------- ---------- ---------- -------------------- ---------- ----------
000000006010DBD0 336 7 7 shared pool 2276811941 12
000000006010DB30 336 6 7 shared pool 2276811941 12
000000006010DA90 336 5 7 shared pool 2276811941 12
000000006010D9F0 336 4 7 shared pool 2276811941 12
000000006010D950 336 3 7 shared pool 2276811941 12
000000006010D8B0 336 2 7 shared pool 2276811941 12
000000006010D810 336 1 7 shared pool 2276811941 106798669
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
7 rows selected.
--从我前面的执行看主要是没有使用绑定变量.
3.检查生成的awr报表:
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~~~~ --------------- --------------- --------- ---------
DB Time(s): 29.1 135.7 0.00 40.85
DB CPU(s): 6.7 31.4 0.00 9.45
Redo size (bytes): 8,522.4 39,755.0
Logical read (blocks): 84,432.5 393,858.7
Block changes: 19.4 90.4
Physical read (blocks): 12.5 58.4
Physical write (blocks): 0.1 0.4
Read IO requests: 5.1 24.0
Write IO requests: 0.0 0.1
Read IO (MB): 0.1 0.5
Write IO (MB): 0.0 0.0
User calls: 0.7 3.3
Parses (SQL): 27,145.3 126,626.7
Hard parses (SQL): 7,391.5 34,479.8
SQL Work Area (MB): 1.7 7.7
Logons: 0.2 1.0
Executes (SQL): 28,055.9 130,874.4
Rollbacks: 0.1 0.7
Transactions: 0.2
--//每秒的硬分析7XXX.
Top 10 Foreground Events by Total Wait Time
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Tota Wait % DB
Event Waits Time Avg(ms) time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
DB CPU 972. 23.1
kksfbc child completion 14,558 679. 47 16.1 Other
cursor: pin S wait on X 535,331 616. 1 14.7 Concurrenc
library cache: mutex X 154,048 326. 2 7.8 Concurrenc
cursor: pin S 8,732 26.6 3 .6 Concurrenc
library cache lock 41,656 22.2 1 .5 Concurrenc
latch: shared pool 857,354 21.3 0 .5 Concurrenc
library cache load lock 25,434 4.4 0 .1 Concurrenc
latch: row cache objects 1,156 0 0 .0 Concurrenc
enq: JD - contention 7 0 5 .0 Other
