Shulou(Shulou.com)06/01 Report--

Conclusion 1. The test environment is oracle 10.2.0.5.

2 the number of shared pools is 1

3. Shared pool latch is required for hard parsing.

4. Soft parsing needs to obtain shared pool latch

5. Soft parsing does not need to obtain shared pool latch

6. Last time, all kinds of parsing had to get library cache latch

7. The shared pool latch to be obtained for the above parsing is child#=1, that is, the first child latch.

But will not get the other six available sub-latch

8, increase the number of shared pools to 5

That is, parameter _ kghdsidx_count=5

8.1. Only hard parsing needs to get shared pool latch

8.2, soft parsing and soft parsing no longer need to obtain shared pool latch

8.3.If the shared pool subpool is configured as 1, the soft resolution also needs to obtain shared pool latch

By increasing the number of shared pool subpools to 5, soft parsing no longer needs to obtain shared pool latch, which is the advantage of increasing subpools.

test

-oracle version

SQL > select * from v$version where rownum=1

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bi

-only 1 subpool

_ kghdsidx_count 1 max kghdsidx count

SQL > select addr,latch#,level#,name from v$latch where name='shared pool'

ADDR LATCH# LEVEL# NAME

0000000060022CD0 216 7 shared pool

SQL > select addr,latch#,level#,name,child# from v$latch_children where name='shared pool' order by 5

ADDR LATCH# LEVEL# NAME CHILD#

-

00000000600E7AF0 216 7 shared pool 1

00000000600E7B90 216 7 shared pool 2

00000000600E7C30 216 7 shared pool 3

00000000600E7CD0 216 7 shared pool 4

00000000600E7D70 216 7 shared pool 5

00000000600E7E10 216 7 shared pool 6

00000000600E7EB0 216 7 shared pool 7

7 rows selected.

-shared pool latch of hang child#=1. There are still 6 shared pool latch at this time.

SQL > oradebug setmypid

Statement processed.

SQL > oradebug poke 0x00000000600E7AF0 4 1

BEFORE: [0600E7AF0, 0600E7AF4) = 00000000

AFTER: [0600E7AF0, 0600E7AF4) = 00000001

Freshman conversation HANG residence and SQL query HANG residence

[ora10g@seconary ~] $sqlplus'/ as sysdba'

SQL > select sid,serial#,program,event from v$session where type='USER'

Analyze SYSTEMSTATE DUMP to see what the above HANG session is waiting for

SQL > oradebug dump systemstate 10

Statement processed.

SQL > oradebug tracefile_name

/ home/ora10g/admin/ora10g/udump/ora10g_ora_3580.trc

PROCESS 33:

-

SO: 0xa42dc1b0, type: 2, owner: (nil), flag: INIT/-/-/0x00

(process) Oracle pid=33, calls cur/top: 0xa43e82d0/0xa43e82d0, flag: (0)-

Int error: 0, call error: 0, sess error: 0, txn error 0

(post info) last post received: 0 000

Last post received-location: No post

Last process to post me: none

Last post sent: 0 0 0

Last post sent-location: No post

Last process posted by me: none

(latch info) wait_event=0 bits=20

Location from where call was made: kghupr1: Chunk Header

Context saved from call: 2678808096

Waiting for 600e7af0 Child shared pool level=7 child#=1-wait for shared pool latch

Location from where latch is held: kghupr1: Chunk Header

Context saved from call: 2658951960

State=busy, wlstate=free

Waiters [orapid (seconds since: put on list, posted, alive check)]:

10 (1447926130, 328)

11 (1447926130, 325)

34 (1447926130, 258)

8 (177177177)

33 (153,147147)

9 (144,144,144)

Waiter count=6

Gotten 70198 times wait, failed first 0 sleeps 0

Gotten 0 times nowait, failed: 0

On wait list for 600e7af0

Holding (efd=3) a14ee0b0 Child library cache level=5 child#=1-also holds library cache latch

Location from where latch is held: kgllkdl: child: no lock handle: latch

Context saved from call: 0

State=busy, wlstate=free

Process Group: DEFAULT, pseudo proc: 0xa42fe938

O/S info: user: ora10g, term: pts/5, ospid: 4154

OSD pid info: Unix process pid: 4154, image: oracle@seconary (TNS V1-V3)

(FOB) flags=2 fib=0xa27d54b0 incno=0 pending iCompo cnt=0

Fname=/home/ora10g/ora10g/system01.dbf

Fno=1 lblksz=8192 fsiz=75520

At the same time, it is found that the smon,mmon background process is also waiting for shared pool latch and will no longer post the contents of the relevant TRC files.

Release child#=1 shared pool latch

SQL > oradebug poke 0x00000000600E7AF0 4 0

BEFORE: [0600E7AF0, 0600E7AF4) = 000000FF

AFTER: [0600E7AF0, 0600E7AF4) = 00000000

Found a strange problem, there are seven shared pool latch, why all wait for the same HANG shared pool latch, why not get the other six shared pool latch?

So now try hang child=2 's shared pool latch and see what happens.

SQL > oradebug poke 0x00000000600E7B90 4 1

BEFORE: [0600E7B90, 0600E7B94) = 00000000

AFTER: [0600E7B90, 0600E7B94) = 00000001

Login session can be generated normally

[ora10g@seconary ~] $sqlplus'/ as sysdba'

The new SQL can also be run

SQL > select count (*) from dba_objects

COUNT (*)

-

50228

SQL > select count (*) from dict

COUNT (*)

-

1882

Thus it can be seen that oracle does not use polling mechanism to obtain shared pool latch, so what mechanism does it use? let's study other problems for the time being.

Release child#=2 shared pool latch

SQL > oradebug poke 0x00000000600E7B90 4 0

BEFORE: [0600E7B90, 0600E7B94) = 00000001

AFTER: [0600E7B90, 0600E7B94) = 00000000

Then take a look at the soft parsing, that is, the SQL execution plan and text are already in the shared pool to see if you will hold shared pool latch.

SQL > select sid from v$mystat where rownum=1

SID

-

one hundred and twenty two

SQL > select * from t_2_latch

A

-

one

Hold child#=1 shared pool latch

SQL > oradebug poke 0x00000000600E7AF0 4 1

BEFORE: [0600E7AF0, 0600E7AF4) = 00000000

AFTER: [0600E7AF0, 0600E7AF4) = 00000001

Run the above query repeatedly

SQL > select * from t_2_latch

-- hang residence

Looking at the TRC file, we can see that the soft parsing will also wait for shared pool latch and hold the library cache latch.

PROCESS 33:

-

SO: 0xa42dc1b0, type: 2, owner: (nil), flag: INIT/-/-/0x00

(process) Oracle pid=33, calls cur/top: 0xa43e82d0/0xa43e82d0, flag: (0)-

Int error: 0, call error: 0, sess error: 0, txn error 0

(post info) last post received: 11004

Last post received-location: kslpsr

Last process to post me: a42cea88 1 6

Last post sent: 0 0 24

Last post sent-location: ksasnd

Last process posted by me: a42cea88 1 6

(latch info) wait_event=0 bits=20

Location from where call was made: kghupr1: Chunk Header

Context saved from call: 2675911648

Waiting for 600e7af0 Child shared pool level=7 child#=1

Location from where latch is held: kghupr1: Chunk Header

Context saved from call: 2658951960

State=busy, wlstate=free

Waiters [orapid (seconds since: put on list, posted, alive check)]:

10 (114,1447927688)

11 (99, 1447927688, 99)

33 (33, 1447927688, 33)

Waiter count=3

Gotten 111441 times wait, failed first 6 sleeps 7

Gotten 0 times nowait, failed: 0

On wait list for 600e7af0

Holding (efd=3) a14ee0b0 Child library cache level=5 child#=1

Location from where latch is held: kgllkdl: child: no lock handle: latch

Context saved from call: 0

State=busy, wlstate=free

Process Group: DEFAULT, pseudo proc: 0xa42fe938

O/S info: user: ora10g, term: pts/5, ospid: 4860

OSD pid info: Unix process pid: 4860, image: oracle@seconary (TNS V1-V3)

Release the shared pool latch of child#=1

SQL > oradebug poke 0x00000000600E7AF0 4 0

BEFORE: [0600E7AF0, 0600E7AF4) = 000000FF

AFTER: [0600E7AF0, 0600E7AF4) = 00000000

Finally, let's take a look at soft parsing. Will you hold shared pool latch?

SQL > show user

USER is "SCOTT"

SQL > select sid from v$mystat where rownum=1

SID

-

one hundred and twenty two

SQL > select * from t_2_latch

A

-

one

SQL > select * from t_2_latch

A

-

one

SQL > select * from t_2_latch

A

-

one

SQL > select * from t_2_latch

A

-

one

SQL >

Hold child#=1 shared pool latch

SQL > oradebug setmypid

Statement processed.

SQL > oradebug poke 0x00000000600E7AF0 4 1

BEFORE: [0600E7AF0, 0600E7AF4) = 00000000

AFTER: [0600E7AF0, 0600E7AF4) = 00000001

It can be seen that soft parsing will no longer hold shared pool latch.

SQL > select * from t_2_latch

A

-

one

Adjust shared pool subpools to 5

SQL > alter system set "_ kghdsidx_count" = 5 scope=spfile

System altered.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup

ORA-32004: obsolete and/or deprecated parameter (s) specified

ORACLE instance started.

Total System Global Area 1157627904 bytes

Fixed Size 2095800 bytes

Variable Size 301991240 bytes

Database Buffers 822083584 bytes

Redo Buffers 31457280 bytes

Database mounted.

Database opened.

SQL > col name_1 for A50

SQL > col value_1 for A50

SQL > col desc1 for A50

SQL > set linesize 300

SQL > /

Enter value for parameter: _ kghdsidx_count

Old 6: where (x.indx = y.indx) and lower (x.ksppinm) like'% & parameter%'

New 6: where (x.indx = y.indx) and lower (x.ksppinm) like'% _ kghdsidx_count%'

NAME_1 VALUE_1 DESC1

-

_ kghdsidx_count 5 max kghdsidx count

SQL > select addr,latch#,level#,name from v$latch where name='shared pool'

ADDR LATCH# LEVEL# NAME

0000000060022CD0 216 7 shared pool

SQL > select addr,latch#,level#,name,child# from v$latch_children where name='shared pool' order by 5

ADDR LATCH# LEVEL# NAME CHILD#

-

00000000600E7AF0 216 7 shared pool 1

00000000600E7B90 216 7 shared pool 2

00000000600E7C30 216 7 shared pool 3

00000000600E7CD0 216 7 shared pool 4

00000000600E7D70 216 7 shared pool 5

00000000600E7E10 216 7 shared pool 6

00000000600E7EB0 216 7 shared pool 7

7 rows selected.

-- Let's look at hard analysis first.

-hang child#=1 shared pool latch

SQL > oradebug setmypid

Statement processed.

SQL > oradebug poke 0x00000000600E7AF0 4 1

BEFORE: [0600E7AF0, 0600E7AF4) = 00000000

AFTER: [0600E7AF0, 0600E7AF4) = 00000001

SQL > select sid from v$mystat where rownum=1

SID

-

one hundred and twenty three

-- visible hard resolution hang resident, and visible request to get child latch of other available shared pool latch due to the addition of shared pool

SQL > select count (1), count (1) from t_row

-release child#=1 shared pool latch

SQL > oradebug poke 0x00000000600E7AF0 4 0

BEFORE: [0600E7AF0, 0600E7AF4) = 000000FF

AFTER: [0600E7AF0, 0600E7AF4) = 00000000

Let's take a look at soft parsing again.

SQL > select count (1), count (1) from emp

COUNT (1) COUNT (1)

--

14 14

-hang child#=1 shared pool latch

SQL > oradebug poke 0x00000000600E7AF0 4 1

BEFORE: [0600E7AF0, 0600E7AF4) = 00000000

AFTER: [0600E7AF0, 0600E7AF4) = 00000001

It can be seen that soft parsing will no longer get shared pool latch.

SQL > select count (1), count (1) from emp

COUNT (1) COUNT (1)

--

14 14

Let's take a look at soft analysis again.

SQL > select count (1) from dept

COUNT (1)

-

four

SQL > select count (1) from dept

COUNT (1)

-

four

SQL > select count (1) from dept

COUNT (1)

-

four

SQL > oradebug poke 0x00000000600E7AF0 4 1

BEFORE: [0600E7AF0, 0600E7AF4) = 00000000

AFTER: [0600E7AF0, 0600E7AF4) = 00000001

It can be seen that soft parsing will no longer get shared pool latch.

SQL > select count (1) from dept

COUNT (1)

-

four

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.