请问shared pool中的KQR L PO存放哪些数据

kingofworl 发表于 2014-7-19 10:27:04

数据库版本10.2.0.4.12 系统 HP-UX 11.31

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 19 10:19:28 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> set lines 200
SQL> set pagesize 1000
SQL> select * from (select parameter ,count,usage,gets,getmisses from v$rowcache order by usage desc) where rownum<6 ;

PARAMETER                            COUNT    USAGE    GETS  GETMISSES
-------------------------------- ---------- ---------- ---------- ----------
dc_histogram_defs                3061568 3061568 31192650 3377259
dc_segments                      2760386 2760386 3547473 2794369
dc_objects                         35118    35118    514443    76595
dc_files                            4563    4563 1421993    4563
dc_object_ids                         3756    3756    180955    46889

SQL> select * from (select * from v$sgastat where pool='shared pool' order by bytes desc ) where rownum<6;

POOL       NAME                         BYTES
------------ -------------------------- ----------
shared pool  KQR L PO                6001745488
shared pool  free memory             3587671272
shared pool  ges resource             1593189848
shared pool  partitioning d          1279625800
shared pool  sql area                   603049288

发现KQR L PO占用内存很多而且不释放，请问这个都存放哪些数据

Maclean Liu(刘相兵 发表于 2014-7-19 12:53:56

ROW CACHE 也叫做 dictionary cache ，缓存数据字典基表如 OBJ$、COL$、IND$、SEQ$的信息以便解析SQL和library cache object。
包括 KQR S PO ， KQR M PO，KQR L PO ，等

KQR => ROW CACHE
kqr.h 1323 KSDTRADV("ROW_CACHE", FADDR(kqrdac))

V$ROWCACHE is based on X$KQRST

KQROBC : data from obj$
KQROIC : data from oid$ (object ids)

SQL> select * from v$sgastat where NAME like '%KQR%';

POOL       NAME                         BYTES
------------ -------------------------- ----------
shared pool  KQR M SO                      160368
shared pool  KQR L SO                      37888
shared pool  KQR ENQ                      53664
shared pool  KQR X PO                      5600
shared pool  KQR M PO                      399136
shared pool  KQR L PO                      125984
shared pool  KQR S SO                         768

7 rows selected.

SQL> select type,parameter from v$rowcache where parameter like '%seq%';

TYPE       PARAMETER
----------- --------------------------------
PARENT    dc_sequences

SQL>  select pid from v$process where addr = ( select paddr from v$session where sid=(select distinct sid from v$mystat));

   PID
----------
   15

SQL>
SQL>  alter system set "_trace_events"='10000-10999:255:15';

System altered.

SQL>
SQL> select check_seq_cache.nextval from dual;

NEXTVAL
----------
      43

SQL>
SQL> oradebug setmypid;
Statement processed.
SQL>
SQL> oradebug dump row_cache 10;

  oradebug dump errorstack 4;Statement processed.
SQL> SQL>
Statement processed.

NOCACHE情况下更新SEQUENCE的过程(，首先 acquire SQ -> SEQUENCE ENQUEUE LOCK da17=> 55831 object_id => SEQUENCE check_seq_cache

之后acquire SEQ$表的TM SX MODE LOCK ，并banding事务TX，update SEQ$ 表，之后释放 TX TM SQ。

1F9D2145:00007636 15 159 10704  83 ksqgtl: acquire SQ-0000da17-00000000 mode=X flags=SHORT why="contention"
1F9D2151:00007637 15 159 10704  19 ksqgtl: SUCCESS
1F9D2310:00007638 15 159 10704  83 ksqgtl: acquire TM-00000044-00000000 mode=SX flags=GLOBAL|XACT why="contention"
1F9D231A:00007639 15 159 10704  19 ksqgtl: SUCCESS
1F9D238C:0000763A 15 159 10811 1 0x00000000004001F3 0x0000000000000000 0x0000000000237F9F 0x0000000000000002
1F9D2398:0000763B 15 159 10811 2 0x00000000004001F3 0x0000000000000000 0x000000000023D217 0x00007F96C4D6FE40
1F9D23B5:0000763C 15 159 10813 1 ktubnd: Bind usn 8 nax 1 nbx 0 lng 0 par 0
1F9D23CC:0000763D 15 159 10813 2 ktubnd: Txn Bound xid: 8.25.542
1F9D23D5:0000763E 15 159 10704  83 ksqgtl: acquire TX-00080019-0000021e mode=X flags=GLOBAL|XACT why="contention"
1F9D23E5:0000763F 15 159 10704  19 ksqgtl: SUCCESS
1F9D249F:00007640 15 159 10005 4 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0
1F9D24CD:00007641 15 159 10021  11 0x000000000023D218 0x000000000023D217
1F9D24D9:00007642 15 159 10704 117 ksqrcl: release TX-00080019-0000021e mode=X
1F9D24E3:00007643 15 159 10813 3 ktudnx: dec cnt xid:8.25.542 nax:0 nbx:0
1F9D24F8:00007644 15 159 10704 117 ksqrcl: release TM-00000044-00000000 mode=SX
1F9D2509:00007645 15 159 10704 117 ksqrcl: release SQ-0000da17-00000000 mode=X
1F9D2544:00007649 15 159 10005 4 KSL POST SENT postee=6 loc='ksasnd' id1=0 id2=0 name= type=0

通过关键词 da17 可以在row_cache dump中找到  dc_sequences SEQUENCE check_seq_cache的信息

BUCKET 7:
  row cache parent object: address=0x8ea0beb8 cid=13(dc_sequences)
  hash=db18fa06 typ=9 transaction=(nil) flags=00000002
  own=0x8ea0bf88 wat=0x8ea0bf98 mode=N
  status=VALID/-/-/-/-/-/-/-/-
  data=
  0000da17 00020000 000f0002 00020002 000002c1 00000000 00000000 00000000
  02c10000 00000000 00000000 00000000 00000000 64640ace 64646464 64646464
  00646464 15c10000 00000000 00000000 00000000 00000000 00003ec1 00000000
  00000000 00000000 2d2d0000 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d 2d2d2d2d
  2d2d2d2d 2d2d2d2d 00002d2d 00000000
  BUCKET 7 total object count=10

http://t.askmaclean.com/thread-1025-1-1.html

Maclean Liu(刘相兵 发表于 2014-7-19 12:55:37

给出下面的结果

select count(*) from sys.obj$;
select count(*) from sys.tab$;
select count(*) from sys.col$;
select count(*) from sys.seq$;
select count(*) from sys.ind$;

kingofworl 发表于 2014-7-20 10:18:06

SQL> select count(*) from sys.obj$;

  COUNT(*)
----------
5674451

SQL> select count(*) from sys.tab$;

  COUNT(*)
----------
   11540

SQL> select count(*) from sys.col$;

  COUNT(*)
----------
195523

SQL> select count(*) from sys.seq$;

  COUNT(*)
----------
   219

SQL> select count(*) from sys.ind$;

  COUNT(*)
----------
   3308

Maclean Liu(刘相兵 发表于 2014-7-20 14:57:55

select count(*) from sys.obj$;

COUNT(*)
----------
5674451

呵呵，这是什么应用程序，是不是系统里大量的临时对象？ 5674451个对象

kingofworl 发表于 2014-7-20 21:57:11

经分系统大量分区表

Maclean Liu(刘相兵 发表于 2014-7-21 11:51:24

kingofworl 发表于 2014-7-20 21:57 static/image/common/back.gif
经分系统大量分区表

这分区分到2050年了吧？

kingofworl 发表于 2014-7-21 14:36:46

Maclean Liu(刘相兵发表于 2014-7-21 11:51 static/image/common/back.gif
这分区分到2050年了吧？

没那么夸张但确实很多，数据量40T，前几天出现过04031 ，重启了数据库然后把共享池从9G改为10G，结果24小时不到任何语句都04031，看trace KQR L PO内存占绝大部分，就像LRU突然失灵了，所以咨询一下 KQR L PO的组成看看除了增加共享池大小还有什么可以做的

kingofworl 发表于 2014-7-21 15:01:32

对了之前分析问题时用了以前你分享的一个脚本diagnosis1，发现脚本最后一个语句有问题，能提供一个修改版的吗

问题语句：
SELECT alloc_type, alloc_size, num_objs_flushed, object_loaded
  FROM (SELECT ksmlrcom alloc_type,
            ksmlrsiz alloc_size,
            ksmlrnum num_objs_flushed,
            ksmlrhon object_loaded,
            RANK() OVER(ORDER BY ksmlrsiz DESC) AS order_ranking
      FROM x$ksmlru
      WHERE inst_id = USERENV('INSTANCE')
         AND ksmlrsiz > 0)
WHERE order_ranking  400)
WHERE order_ranking  0
         AND o.type LIKE 'JAVA%')
WHERE order_ranking  0
         AND o.type in
            ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'SEQUENCE'))
WHERE order_ranking  0
         AND o.type = 'CURSOR'
)
WHERE order_ranking

Maclean Liu(刘相兵 发表于 2014-7-21 15:47:43

SELECT alloc_type, alloc_size, num_objs_flushed, object_loaded
  FROM (SELECT ksmlrcom alloc_type,
            ksmlrsiz alloc_size,
            ksmlrnum num_objs_flushed,
            ksmlrhon object_loaded,
            RANK() OVER(ORDER BY ksmlrsiz DESC) AS order_ranking
      FROM x$ksmlru
      WHERE inst_id = USERENV('INSTANCE')
         AND ksmlrsiz > 0)
WHERE order_ranking <= 100
ORDER BY order_ranking
/

Maclean Liu(刘相兵 发表于 2014-7-21 15:49:06

kingofworl 发表于 2014-7-21 14:36 static/image/common/back.gif
没那么夸张但确实很多，数据量40T，前几天出现过04031 ，重启了数据库然后把共享池从9G改为10G，结果24小 ...

这么多分区不可能全被使用的，如果你们使用的是套件产品的话这个问题显然产品开放商应当是知道的，因为就是这么设计的，具体哪些对象不被用可以被清理可能他们大致也清楚。如果是自己开发的话那需要自己摸索了。

页: [1]

Oracle数据库数据恢复、性能优化's Archiver

请问shared pool中的KQR L PO存放哪些数据