Vertex, cursor_space_for_time, gets, pin saw a post on asktom last weekend, some people mentioned the effect of session_cached_cursors and cursor_space_for_time on library cache gets & pin, the Post url is http://asktom.oracle.com/pls/ask/f? P = 4950: 8 ::::: F4950_P8_DISPLAYID: 465420331879 here, tom also showed us that session_cached_cursors and cursor_space_for_time have different effects on gets & pin of library cache in 9i and 10g. Let's take a look at session_cached_cursors, cursor_space_for_time, library cache gets, pin session_cached_cursors: Set the length of the cache list on the pga end. When the cache is set to 0, the length of the cache list on the pga, at this time, when the cursor in sga is disabled, the lock bit of its related library cache handle is cleared to 0, and the disabled cursor cannot be seen from v $ open_cursor, it is subject to the lru mechanism of the shared pool. When the shared pool requires a new buffer space, it will be flushed out of the shared pool. When session_cached_cursors is set to a non-zero value, the length of the pga cache list is the size of the session_cached_cursors value, and the pga cache list retains a copy, at this time, even if the cursor in sga is disabled, its library cache handle is always added with null mode lock, when the shared pool space is insufficient, the library cache handle will always be kept in the shared pool. when the new application accesses this cursor, it will directly search in its own pga cache list. Cursor_space_for_time: When session_cached_cursors is set to a non-zero value, if the value of cursor_space_for_time is set to false, when the shared pool space is insufficient, although the library cache handle will not be flushed out, however, it points to the library cached object (lco, which contains the address, permission, type, status, and pointer to the kgl block of handle and children handle, the kgl block contains real code segments, execution plans, and so on. It will be flushed out regardless of whether the related cursor is closed. If lco is required, reloads will be performed. If the value of cursor_space_for_time is set to true, when the cursor is on, the lco that handle points to will not be flushed out of the shared pool, thus reducing the frequency of reloads. However, setting cursor_space_for_time may cause some problems for databases with poor SQL sharing. A 04031 error may occur in the share pool. Gets: when attempting to parse an SQL statement, oracle must first obtain a handle and load a lock on the handle. gets indicates handle request times. Pin: After handle is obtained, locate lco and pin lco so that it is not flushed out when executed. Since we understand the above concepts, we can use some code to demonstrate the effect of session_cached_cursors and cursor_space_for_time on pin and gets in 9i and 10g on pin and gets. The effects of gets on 9i and 10g are also different: vi 1. SQL set wrap off
Set linesize 100
Set pagesize 0
Set verify off
Select namespace, gets, gethits, pins, pinhits from v $ librarycache where namespace = 'SQL region ';
Select namespace, gets, gethits, pins, pinhits from v $ librarycache where namespace = 'SQL region ';
Select namespace, gets, gethits, pins, pinhits from v $ librarycache where namespace = 'SQL region ';
Select namespace, gets, gethits, pins, pinhits from v $ librarycache where namespace = 'SQL region ';
Select namespace, gets, gethits, pins, pinhits from v $ librarycache where namespace = 'SQL region ';
Select namespace, gets, gethits, pins, pinhits from v $ librarycache where namespace = 'SQL region ';
Select namespace, gets, gethits, pins, pinhits from v $ librarycache where namespace = 'SQL region ';
Select namespace, gets, gethits, pins, pinhits from v $ librarycache where namespace = 'SQL region ';
Select namespace, gets, gethits, pins, pinhits from v $ librarycache where namespace = 'SQL region'; execute 10 SQL SQL 9I> show parameter cursorNAME TYPE VALUE
-----------------------------------------------------------------------------
Cursor_space_for_time boolean FALSE
Session_cached_cursors integer 0 SQL 9I >@$ HOME/1. sqlNAMESPACE GETS GETHITS PINS PINHITS
-------------------------------------------------------
SQL AREA 2942908 2939105 75218597 75211231
SQL AREA 2942909 2939106 75218600 75211234
SQL AREA 2942910 2939107 75218603 75211237
SQL AREA 2942911 2939108 75218606 75211240
SQL AREA 2942912 2939109 75218609 75211243
SQL AREA 2942913 2939110 75218612 75211246
SQL AREA 2942914 2939111 75218615 75211249
SQL AREA 2942915 2939112 75218618 75211252
SQL AREA 2942916 2939113 75218621 75211255
SQL area 2942917 2939114 75218624 75211258 you can see that gets adds 1 at a time, and the pin increases 3 at a time. Modify session_cached_cursors = 100; SQL 9I> alter session set session_cached_cursors = 100;
SQL 9I> @ $ HOME/1. SQL
SQL AREA 2942935 2939123 75218728 75211344
SQL AREA 2942935 2939123 75218730 75211346
SQL AREA 2942935 2939123 75218732 75211348
SQL AREA 2942935 2939123 75218734 75211350
SQL AREA 2942935 2939123 75218736 75211352
SQL AREA 2942935 2939123 75218738 75211354
SQL AREA 2942935 2939123 75218740 75211356
SQL AREA 2942935 2939123 75218742 75211358
SQL AREA 2942935 2939123 75218744 75211360
SQL area 2942935 2939123 75218746 75211362 gets does not increase, and the pin is increased by 2 SQL 9I> show parameter cursorNAME TYPE VALUE
-----------------------------------------------------------------------------
Cursor_space_for_time boolean TRUE
Session_cached_cursors integer 100SQL 9I >@$ HOME/1. sqlNAMESPACE GETS GETHITS PINS PINHITS
-------------------------------------------------------
SQL AREA 932 508 3561 3159
SQL AREA 933 509 3563 3161
SQL AREA 934 510 3565 3163
SQL AREA 934 510 3566 3164
SQL AREA 934 510 3567 3165
SQL AREA 934 510 3568 3166
SQL AREA 934 510 3569 3167
SQL AREA 934 510 3570 3168
SQL AREA 934 510 3571 3169
After changing SQL area 934 510 3572 cursor_space_for_time to true, the pin is increased to 1 10 Gb each time: SQL 10g> show parameter cursor
Cursor_space_for_time boolean FALSE
Session_cached_cursors integer 0SQL 10G> @ $ HOME/1. SQL
SQL AREA 3328 98 23112 21363
SQL AREA 3328 98 23113 21364
SQL AREA 3328 98 23114 21365
SQL AREA 3328 98 23115 21366
SQL AREA 3328 98 23116 21367
SQL AREA 3328 98 23117 21368
SQL AREA 3328 98 23118 21369
SQL AREA 3328 98 23119 21370
SQL AREA 3328 98 23120 21371
SQL area 3328 98 23121 21372 gets does not change, and the pin increases by 1 SQL 10G each time> alter session set session_cached_cursors = 100;
SQL 10G> @ $ HOME/1. SQL
SQL AREA 3513 98 28456 26335
SQL AREA 3513 98 28457 26336
SQL AREA 3513 98 28458 26337
SQL AREA 3513 98 28459 26338
SQL AREA 3513 98 28460 26339
SQL AREA 3513 98 28461 26340
SQL AREA 3513 98 28462 26341
SQL AREA 3513 98 28463 26342
SQL AREA 3513 98 28464 26343
SQL area 3513 98 28465 26344 after the session set session_cached_cursors is set, SQL 10G> show parameter cursorNAME TYPE VALUE is not changed.
-----------------------------------------------------------------------------
Cursor_space_for_time boolean TRUE
Session_cached_cursors integer 100
SQL 10G> @ $ HOME/1. SQL NAMESPACE GETS GETHITS PINS PINHITS
-------------------------------------------------------
SQL AREA 1038 24 4404 3488
SQL AREA 1038 24 4405 3489
SQL AREA 1038 24 4406 3490
SQL AREA 1038 24 4407 3491
SQL AREA 1038 24 4408 3492
SQL AREA 1038 24 4409 3493
SQL AREA 1038 24 4410 3494
SQL AREA 1038 24 4411 3495
SQL AREA 1038 24 4412 3496
SQL area 1038 24 4413 3497 setting cursor_space_for_time to true does not change, but when cursor_space_for_time is set to true, pl/SQL block will stop pin growth. See the example of tom ops $ tkyte @ ORA10G.> create or replace procedure p
2 authid current_user
3
4 l_ns varchar2 (4000 );
5 l_gets number;
6 Rochelle gethits number;
7 l_pins number;
8 l_pinhits number;
9 l_sgets number;
10 l_sgethits number;
11 l_spins number;
12 l_spinhits number;
13 begin
14 for I in 1 .. 1000
15 loop
16 execute immediate
17 'select namespace, gets, gethits, pins, pinhits
18 from v $ librarycache
19 where namespace = ''SQL AREA '''
20 into l_ns, l_gets, l_gethits, l_pins, l_pinhits;
21
22 if (I in (1,1000 ))
23 then
24 if (I = 1)
25 then
26 l_sgets: = l_gets; l_sgethits: = l_gethits;
27 l_spins: = l_pins; l_spinhits: = l_pinhits;
28 end if;
29 dbms_output.put_line
30 (l_ns | to_char (l_gets, '123') |
31 to_char (l_gethits, '123') |
32 to_char (l_pins, '123') |
33 to_char (l_pinhits, '123 '));
34 if (I = 1000)
35 then
36 dbms_output.put_line
37 (l_ns | to_char (l_gets-l_sgets, '123') |
38 to_char (l_gethits-l_sgethits, '123') |
39 to_char (l_pins-l_spins, '123') |
40 to_char (l_pinhits-l_spinhits, '123 '));
41 end if;
42 end if;
43 end loop;
44 end;
45/
Procedure created. sys @ ORA10G> alter system set session_cached_cursors = 100 scope = spfile;
System altered.
Sys @ ORA10G> alter system set cursor_space_for_time = TRUE scope = spfile;
System altered.
Sys @ ORA10G> startup force
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 777956 bytes
Variable Size 145760540 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.
Sys @ ORA10G> @ connect/
Sys @ ORA10G> set termout off
Ops $ tkyte @ ORA10G> @ login
Ops $ tkyte @ ORA10G> set termout off
Ops $ tkyte @ ORA10G> rem get afiedt. buf NOLIST
Ops $ tkyte @ ORA10G> set termout on
Ops $ tkyte @ ORA10G> exec p
SQL AREA 1,181 373 4,828 4,171
SQL AREA 1,181 373 4,828 4,171
SQL AREA 0 0 0 0
PL/SQL procedure successfully completed.
We can see that the pl/SQL engine of 10 Gb has also changed. Let's take a look at the dump BUCKET 110279 of handle after setting session_cached_cursors:
Library object handle: handle = 9df283a8 mutex = 0x9df2845c (1)
Name = select namespace, gets, gethits, pins, pinhits from v $ librarycache where namespace = 'SQL region'
Hash = f1deb637acf7a42dd55d86a8ae3baec7 timestamp = 08-30-2005 17:07:35
Namespace = CRSR flags = RON/KGHP/TIM/KEP/PN0/MED/KST/DBN/CTX/[500100d4]
Kkkk-dddd-llll = 0001-0001-0001 lock = N pin = 0 latch # = 7 hpc = 0002 hlc = 0002 without setting BUCKET 110279:
Library object handle: handle = 9df283a8 mutex = 0x9df2845c (1)
Name = select namespace, gets, gethits, pins, pinhits from v $ librarycache where namespace = 'SQL region'
Hash = f1deb637acf7a42dd55d86a8ae3baec7 timestamp = 08-30-2005 17:07:35
Namespace = CRSR flags = RON/KGHP/TIM/KEP/PN0/MED/KST/DBN/CTX/[500100d4]
Kkkk-dddd-llll = 0001-0001-0001 lock = 0 pin = 0 latch # = 7 hpc = 0002 hlc = 0002 if alter system flush shared_pool is executed, null mode lock is not loaded. handle will be flushed out of the shared pool.
Http://wzwanghai.spaces.live.com/blog/cns! 56626E237AFBD116! 187. entry
