Help - Search - Members - Calendar
Full Version: Issue with sql pinned in the sga
Oracle DBA Forums > Oracle > Oracle Forum
Arthritic Toe
Hi all,

I have an issue thats driving me mad. Our production database (hosted by a third party) seems to be suffereing from a very low hit ratio on the sql area. Indeed, if you just issue a "SELECT 'hello' FROM dual", you can see that it disappears from V$SQL in just a few seconds. Further investigation reveals that there is a ton of 'stuff' pinned in the SGA (as seen in V$DB_OBJECT_CACHE where kept='YES'), and weirder still, this list seems to be changing every minute. If you select the count of objects pinned, it remains static for a minute or so, then changes - just as if its part of a scheduled job.

My theory is that this ton of junk is filling up a large part of the shared pool, leaving insufficient space for anything else. The database has ASMM turned on - but the auto sizing does not seem to take account of the pinned objects.

Any ideas what could be dynamically pinning stuff?
note: there is some sort of oem job running as sysman - could this be the culprit?

Database version is 10.2.0.1.
andrew kerber
Do you have any form of cursor_sharing turned on (exact, similar?)
Arthritic Toe
QUOTE (andrew kerber @ Jun 18 2008, 04:08 PM) *
Do you have any form of cursor_sharing turned on (exact, similar?)


cursor_sharing = exact (=the default)
burleson
Hi,

>> My theory is that this ton of junk is filling up a large part of the shared pool, leaving insufficient space for anything else.

Can you reveal the name of the application?

What size is your shared pool? Can you increase it?

Do you have lots of SQL where executions = 1?

Would setting cursor_sharing=force help you?

http://www.dba-oracle.com/t_cusror_sharing.htm

Does this confirm pinned packages?

set pagesize 60;
column executions format 999,999,999;
column Mem_used format 999,999,999;

SELECT SUBSTR(owner,1,10) Owner,
SUBSTR(type,1,12) Type,
SUBSTR(name,1,20) Name,
executions,
sharable_mem Mem_used,
SUBSTR(kept||' ',1,4) "Kept?"
FROM v$db_object_cache
WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
ORDER BY executions desc;
Arthritic Toe
QUOTE (burleson @ Jun 18 2008, 09:42 PM) *
Hi,

>> My theory is that this ton of junk is filling up a large part of the shared pool, leaving insufficient space for anything else.

Can you reveal the name of the application?

What size is your shared pool? Can you increase it?

Do you have lots of SQL where executions = 1?

Would setting cursor_sharing=force help you?

http://www.dba-oracle.com/t_cusror_sharing.htm

Does this confirm pinned packages?

set pagesize 60;
column executions format 999,999,999;
column Mem_used format 999,999,999;

SELECT SUBSTR(owner,1,10) Owner,
SUBSTR(type,1,12) Type,
SUBSTR(name,1,20) Name,
executions,
sharable_mem Mem_used,
SUBSTR(kept||' ',1,4) "Kept?"
FROM v$db_object_cache
WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
ORDER BY executions desc;


Thanks for the reply.
The application is Siebel - but I dont think the app is doing the pinning, it seems to be something going on at the database level.

The above query confirms no pinned packages, procedures etc, but the issue here really relates to the pinning of cursors:
1* SELECT type,COUNT(*) from v$db_object_cache WHERE kept='YES' GROUP BY type
SQL> /

TYPE COUNT(*)
---------------------------- ----------
CURSOR 6183
INDEX 7
IFS 2
INVALID TYPE 29
TABLE 26
CLUSTER 6

6 rows selected.


And yes, there is lots of sql with executions <= 1:

SQL> select count(*) from v$db_object_cache where executions<=1;

COUNT(*)
----------
73094

SQL> select count(*) from v$db_object_cache where executions>1;

COUNT(*)
----------
5837

The database is using ASMM, and it has a huge sga_target of 25G, of which it has decided to allocate about 1G to the shared pool. We could force the shared pool larger, by assigning a value to the shared_pool_size, which I understand acts as a minimum under ASMM. However, I can't help feeling that this business of the pinned cursors is somehow interfering with its autosizing calculations. It seems crazy that we have such a large sga, but manage to hit so few cached objects - our sqlarea gethitratio is around 39%:

NAME VALUE
------------------------ ----------
Buffer Cache 99.7
Execute/NoParse 81.13
Memory Sort 100
SQL Area get hitrate 38.93
Avg Latch Hit (No Miss) 98.81
Avg Latch Hit (No Sleep) 99.97

6 rows selected.


I wonder if there is some 'feature' of enterprise manager, where you can configure some sort of dynamic pinning mechanism? -and perhaps our third party database platform host has inadvertantly switched something like this on. However, I dont see anything like this in the EM documentation, and I don't see what it would hope to achieve. [My reason for suspecting EM is that I see a job called emd_maintenance running every minute, which I am tempted to point an accusing finger at...]

Thanks again,
Mike.
burleson
Hi Mike,

>> The database is using ASMM

Aha! Turn it OFF! I've had tons of problems with it in 10g, causing huge memory thrashing.

Does your STATSPACK/AWR report show lots of re-size operations?

http://www.dba-oracle.com/t_amm_automatic_memory_resize.htm

Really, turn it off, you may be amazed. . . .

********************************************************************

Pinning is commonly done at instance startup time.

Do you have a startup trigger in-place?

Can you re-run the whole script I pasted-in, so we can see exactly what packages are bring pinned.

Also, I have some notes on tuning Oracle with Siebel here:

http://www.dba-oracle.com/t_siebel_tuning.htm

**************************************************************
>> And yes, there is lots of sql with executions <= 1:

Do you have lots of SQL, which are identical, except for the variable values?

If so, consider cursor_sharing=force.
Arthritic Toe
Here's the output of the script, which confirms NO pinned packages. (the pinned objects we have are mainly of type CURSOR):


OWNER TYPE NAME EXECUTIONS MEM_USED Kept
---------------------------------------- ------------ -------------------------------------------------------------------------------- ------------ ------------ ----
SYS PACKAGE BODY DBMS_ASSERT 19,148,637 567 NO
SYS PACKAGE BODY STANDARD 5,724,923 29,260 NO
SYS PACKAGE BODY DBMS_SYS_SQL 5,671,778 568 NO
SYSMAN PACKAGE BODY MGMT_GLOBAL 5,284,380 8,762 NO
SYSMAN PACKAGE BODY MGMT_JOB_ENGINE 5,212,643 253,670 NO
SYS PACKAGE BODY DBMS_RCVMAN 2,865,258 567 NO
SYS PACKAGE BODY DBMS_OUTPUT 1,380,688 8,759 NO
SYSMAN PACKAGE BODY EMD_LOADER 1,238,677 81,153 NO
SYS PACKAGE BODY DBMS_APPLICATION_INF 999,218 4,673 NO
SYSMAN PACKAGE BODY MGMT_LOG 790,081 8,759 NO
SYSMAN TRIGGER RAW_METRICS_AFTER_IN 733,076 5,791 NO
SYSMAN PACKAGE BODY EMD_SCHEMA 419,985 17,025 NO
SYSMAN PACKAGE BODY MGMT_FAILOVER 404,451 12,860 NO
SYS PACKAGE BODY DBMS_AQ 339,300 29,283 NO
SYSMAN PACKAGE BODY MGMT_USER 298,808 568 NO
SYSMAN PACKAGE BODY EMD_NOTIFICATION 291,682 94,879 NO
SIEBEL TRIGGER DT_20 274,357 5,772 NO
SYSMAN PACKAGE BODY EM_PING 272,986 34,014 NO
SYSMAN TRIGGER CHECK_DUPLICATE_TARG 260,839 10,998 NO
SYS PACKAGE BODY PRVT_ADVISOR 194,751 568 NO
SYS PACKAGE BODY DBMS_ADVISOR 172,045 26,728 NO
SIEBEL TRIGGER DT_58 153,393 5,772 NO
SYSMAN PROCEDURE SETEMUSERCONTEXT 150,628 4,671 NO
SYSMAN PACKAGE BODY EMD_MAINTENANCE 149,396 73,462 NO
SYSMAN PACKAGE BODY EM_SEVERITY_REPOS 131,660 39,584 NO
SYSMAN PACKAGE BODY EMD_COLLECTION 131,660 38,477 NO
SIEBEL TRIGGER DT_C08 129,234 5,773 NO
SYSMAN PACKAGE BODY MGMT_BLACKOUT_ENGINE 72,170 579 NO
DBSNMP PACKAGE BODY MGMT_RESPONSE 56,566 48,164 NO
SYS PACKAGE BODY DBMS_UTILITY 53,315 568 NO
XDB PACKAGE BODY DBMS_XDBZ0 33,120 566 NO
SYS PACKAGE BODY DBMS_SESSION 31,005 568 NO
SYSMAN TRIGGER MGMT_DB_HDM_METRIC_T 28,284 1,692 NO
SIEBEL TRIGGER DT_C07 23,348 5,533 NO
SYS PACKAGE BODY KUPP$PROC 21,640 565 NO
SYS PACKAGE BODY KUPC$QUEUE_INT 18,787 570 NO
SYS PACKAGE BODY DBMS_METADATA_INT 10,924 573 NO
SYS PACKAGE BODY DBMS_LOCK 8,448 565 NO
SYS PACKAGE BODY KUPF$FILE 4,841 565 NO
SYS PACKAGE BODY DBMS_PICKLER 4,538 568 NO
SYS PACKAGE BODY KUPV$FT 4,530 563 NO
SYS PACKAGE BODY DBMS_TRANSACTION 4,354 572 NO
SYS PACKAGE BODY UTL_RAW 1,738 563 NO
SYS PACKAGE BODY DBMS_AQADM_SYS 862 570 NO
SYSMAN PACKAGE BODY MGMT_JOBS 337 33,488 NO
SYS PACKAGE BODY KUPC$QUEUE 244 566 NO
SYSMAN TRIGGER METRICS_SEVERITY_DUP 92 586 NO
SYS PACKAGE BODY DBMS_RULE_ADM 83 569 NO
SYS PACKAGE BODY DBMS_LOGREP_EXP 5 571 NO
SYS PACKAGE BODY DBMS_SCHED_MAIN_EXPO 4 578 NO
SYS PACKAGE BODY DBMS_SCHED_EXPORT_CA 4 582 NO
SIEBEL TRIGGER DT_33 3 564 NO
SYS PACKAGE BODY DBMS_SCHED_JOB_EXPOR 3 577 NO
EXFSYS PACKAGE BODY DBMS_RLMGR_DR 2 572 NO
SYSMAN PACKAGE MGMT_JOBS 0 21,112 NO
SYS PACKAGE DBMS_RULE_EXP_RULES 0 575 NO
SYSMAN PACKAGE EMD_LOADER 0 70,217 NO
SYS PACKAGE DBMS_AQADM 0 566 NO
SYS PACKAGE DBMS_TRANSFORM_EXIMP 0 576 NO
SYS PACKAGE DBMS_PICKLER 0 568 NO
XDB PACKAGE DBMS_XDBZ0 0 566 NO
SYS PACKAGE PRVT_HDM 0 564 NO
SYS TRIGGER CDC_DROP_CTABLE_BEFO 0 578 NO
SYSMAN PACKAGE EMD_SCHEMA 0 8,761 NO
SYSMAN PACKAGE EMD_MAINTENANCE 0 8,766 NO
SYS PACKAGE DBMS_REFRESH_EXP_LWM 0 576 NO
SYS PACKAGE PRVT_ADVISOR 0 17,024 NO
SYS PACKAGE DBMS_SQL 0 564 NO
SYS PACKAGE DBMS_TRANSACTION 0 572 NO
SYS PACKAGE DBMS_OUTPUT 0 4,663 NO
SYS PACKAGE DBMS_METADATA_UTIL 0 574 NO
DBSNMP PACKAGE MGMT_RESPONSE 0 25,148 NO
SYSMAN PACKAGE EMD_COLLECTION 0 8,765 NO
SYS PACKAGE DBMS_AW_EXP 0 567 NO
SYSMAN PACKAGE BODY MGMT_ADMIN_DATA 0 76,526 NO
SYS PACKAGE DBMS_STANDARD 0 33,337 NO
SYS PACKAGE DBMS_LOCK 0 565 NO
SYS PACKAGE KUPC$QUE_INT 0 568 NO
SYS PACKAGE DBMS_ADVISOR 0 568 NO
SYS PACKAGE DBMS_RCVMAN 0 567 NO
MDSYS TRIGGER SDO_NETWORK_DROP_USE 0 579 NO
SYS PACKAGE DBMS_SYSTEM 0 567 NO
SYS PACKAGE DBMS_PRVTAQIS 0 569 NO
SYS PACKAGE PLITBLM 0 8,755 NO
SYSMAN PACKAGE MGMT_JOB_ENGINE 0 29,278 NO
SYS PACKAGE DBMS_APPLICATION_INF 0 25,153 NO
SYSMAN PACKAGE EM_PING 0 12,854 NO
SYS PACKAGE XML_SCHEMA_NAME_PRES 0 579 NO
SYS PACKAGE KUPM$MCP 0 564 NO
SYSMAN PACKAGE MGMT_USER 0 568 NO
SYS PACKAGE DBMS_AQ_EXP_QUEUE_TA 0 580 NO
SYSMAN PACKAGE EMD_BCN_AVAIL 0 572 NO
SYS PACKAGE DBMS_AQ 0 16,947 NO
SYSMAN PACKAGE MGMT_LOG 0 4,663 NO
SYS PACKAGE DBMS_SYS_ERROR 0 570 NO
SYS PACKAGE DBMS_LOB 0 564 NO
SYS PACKAGE DBMS_SCHEDULER 0 570 NO
SYS PACKAGE KUPC$QUEUE_INT 0 570 NO
SYS PACKAGE KUPP$PROC 0 565 NO
MDSYS TRIGGER SDO_TOPO_DROP_FTBL 0 576 NO
SYS PACKAGE DBMS_AQADM_SYS 0 570 NO
SYS TRIGGER NO_VM_DROP_A 0 568 NO
SYSMAN PACKAGE EMD_NOTIFICATION 0 17,047 NO
MDSYS TRIGGER SDO_GEOR_DROP_USER 0 576 NO
SYS PACKAGE KUPD$DATA 0 565 NO
SYS PACKAGE DBMS_CDC_EXPVDP 0 571 NO
XDB PACKAGE XDB_PITRIG_PKG 0 570 NO
SYS PACKAGE STANDARD 0 498,772 NO
SYS PACKAGE DBMS_SCHED_JOB_EXPOR 0 577 NO
SYSMAN PACKAGE MGMT_FAILOVER 0 4,668 NO
SYSMAN PACKAGE MGMT_BLACKOUT_ENGINE 0 62,075 NO
EXFSYS TRIGGER EXPFIL_RESTRICT_TYPE 0 585 NO
EXFSYS PACKAGE DBMS_RLMGR_DR 0 572 NO
SYS PACKAGE DBMS_LOGREP_UTIL 0 572 NO
SYS PACKAGE KUPF$FILE_INT 0 569 NO
SYSMAN PACKAGE EM_SEVERITY_REPOS 0 8,768 NO
SYS PACKAGE DBMS_IJOB 0 565 NO
XDB TRIGGER XDB_PI_TRIG 0 567 NO
SYS PACKAGE DBMS_SCHED_MAIN_EXPO 0 578 NO
SYSMAN PACKAGE MGMT_ADMIN_DATA 0 71,270 NO
SYS PACKAGE BODY DBMS_ODCI 0 565 NO
SYS PACKAGE LT_EXPORT_PKG 0 569 NO
XDB PACKAGE DBMS_XDBZ 0 565 NO
MDSYS TRIGGER SDO_NETWORK_UD_DROP_ 0 582 NO
SYS PACKAGE DBMS_AQADM_SYSCALLS 0 575 NO
SYSMAN PACKAGE MGMT_GLOBAL 0 34,122 NO
SYS PACKAGE DBMS_SESSION 0 568 NO

127 rows selected.



We are seeing SGA resize ops every few hours, but I'm still at a complete loss to understand why these six thousand or so cursors are apparently being pinned:

1* SELECT type,COUNT(*) from v$db_object_cache WHERE kept='YES' GROUP BY type
SQL> /

TYPE COUNT(*)
---------------------------- ----------
CURSOR 6183
INDEX 7
IFS 2
INVALID TYPE 29
TABLE 26
CLUSTER 6

6 rows selected.

If you rerun this query, the counts change every minute or so, but remain similar. Surely the ASMM isn't responsible for the pinning of objects? - or is it???? there are no startup triggers doing any pinning.

Regarding your final point - whilst siebel commits a whole raft of other sins, I can't really criticise their use of bind variables - so my feeling is that changing to cursor_sharing=similar or force wont have a positive impact.

Thanks again for your help,
Mike.
burleson
Hi Mike,

>> so my feeling is that changing to cursor_sharing=similar or force wont have a positive impact.

Rirhgt, not unless you have made somead-hoc query extention tool.

*******************************************************************
>> cursors are apparently being pinned:

Could this be related?

http://www.dba-oracle.com/t_cursor_sharing...hed_cursors.htm

At a recent client site I had the perturbing problem that the shared pool (at over 500 megabytes) was filled to 100%, but only with a little over 1700 SQL objects! For those familiar with shared pools and the amount of SQL they can hold, you will realize this size pool should be able to hold 30,000 or more SQL areas. Looking at the statspack SQL reports, showed some of the SQL areas had as many as 900 versions. In V$SQLAREA, these versions all get showed as one object so typical monitoring using counts of the SQL areas in V$SQLAREA didn't show this issue.

Further investigation using the V$SQL_SHARED_CURSOR view showed that in the case of one SQL with over 700 versions, only 7 showed any indication that they shouldn't be shared. We attempted to reconcile this using:

"_sqlexec_progression_cost=0"
"session_shared_cursors=150"

and based on some metalink research, setting event "10503 trace name context forever, level 128" which was suposed to correct some issues when character, raw, long raw and long values where replaced with bind variables. We bounced the database and found absolutely no difference. Using a search on v$sql_shared_cursor (searchs against various forms of "high number of SQL versions" proved fruitless) found bug report 3406977.8 which talked about a bug, 3406977, which affects versions 9.2.0.2 through 9.2.0.5 and is fixed in 9.2.0.6 that causes statements like "select * from test where id in (1,2,3);" and "select * from test where id in (2,2,3);" to be made into multiple versions if CURSOR_SHARING is set to FORCE or SIMILAR!
Arthritic Toe
- some great leads there. Thanks for the information Donald.
burleson
Let us know how it works out.

With a huge SGA like you have, AMM should not be doing resize operations, and I would seriously consider turning it off.
Arthritic Toe
QUOTE (burleson @ Jun 20 2008, 03:28 PM) *
Let us know how it works out.

With a huge SGA like you have, AMM should not be doing resize operations, and I would seriously consider turning it off.

Interesting further discovery - this strange random pinning behavior is happening on ALL our 10.2.0.1 platforms. This exact version only. 10.2.0.2 and above, show no pinned objects. Now looking increasingly like a bug specific to 10.2.0.1. I've raised an oracle SR. I'll let you know what they come back with. I guess they will tell me to upgrade :-) But at least it would be good to read the bug notes, to confirm all this.

- Point taken re AMM. Thanks.
Mike.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.