SQL_ID EXECUTIONS_TOTAL PARSE_CALLS_TOTAL EXECUTE_TO_PARSE EXECUTIONS_DELTA PARSE_CALLS_DELTA DELTA_RATIO
------------- ---------------- ----------------- ---------------- ---------------- ----------------- -----------
az33m61ym46y4 91316 91390 -.08 12530 12542 -.1
az33m61ym46y4 78786 78848 -.08 12504 12517 -.1
az33m61ym46y4 41137 41166 -.07 12388 12399 -.09
az33m61ym46y4 66282 66330 -.07 12550 12561 -.09
az33m61ym46y4 28749 28767 -.06 12589 12599 -.08
az33m61ym46y4 53732 53769 -.07 12595 12603 -.06
–从上面的查询可以看出sql_id az33m61ym46y4是罪魁祸首 Metalink上有唯一一篇关于这个sql的描述，不过也没有看到太多的建议。 同样的情形在Oracle
11.1.1.7上也存在，不过这条SQL在awr报告中没有当前11.2.1.0中突出 以下是metalink上的具体描述 Bug 12318969 : V2301072 MUST RECYCLE THE BATCH TPW WHEN DATABASE INSTANCE
IS RECYCLED Bug Attributes Type B - Defect Fixed in Product Version
Severity 2 - Severe Loss of Service Product Version 2.2 Status 92 -
Closed, Not a Bug Platform 226 - Linux x86-64
Created 02-Apr-2011 Platform Version NO DATA Updated 12-Oct-2011 Base
Bug N/A Database Version N/A Affects Platforms Generic Product
Source Oracle Knowledge, Patches and Bugs related to this bug
Related Products Line More Applications &
Technologies Family Industry Solutions Area Utilities Product 2245 -
Oracle Utilities Framework
Hdr: 12318969 N/A BATCH 2.2 BTJOBSUB PRODID-2245 PORTID-226 Abstract:
V2301072 MUST RECYCLE THE BATCH TPW WHEN DATABASE INSTANCE IS RECYCLED
* 04/01/11 03:56 pm * Short Description:
—————— instance is recycled Detailed Problem Statement:
————————– When database node crashes or when database node is recycled , there will error they, however do not
re-connect to the database. way the online application reconnects
after a database recycle. Workaround
———- groups. Impact on Business see attachments
* 04/01/11 04:11 pm *
* 04/05/11 03:39 pm *
* 04/05/11 04:35 pm * (CHG: Sta->11 Asg->FJOCSON)
* 04/06/11 08:56 am * (CHG: Sta->30 Asg->MZEEMAN)
* 04/06/11 08:56 am *
* 04/06/11 04:40 pm *
* 04/08/11 12:34 pm *
* 04/08/11 12:35 pm * (CHG: Sta->10 Asg->FJOCSON)
* 04/08/11 12:35 pm *
* 04/08/11 12:56 pm * (CHG: Sta->30 Asg->MZEEMAN)
* 04/08/11 12:56 pm *
* 04/15/11 05:38 pm *
* 05/05/11 01:25 pm *
* 05/05/11 03:29 pm * (CHG: Sta->10 Asg->ASHORTEN)
* 05/05/11 03:29 pm *
* 05/05/11 06:38 pm *
* 05/05/11 06:38 pm * Updated the Batch Best Practices under “Threadpools and Database Recycling”
* 05/06/11 02:42 pm * (CHG: Sta->30)
* 05/06/11 02:42 pm *
* 05/17/11 03:53 pm * (CHG: Sta->92)
* 05/17/11 03:53 pm * (CHG: Sta->30)
* 05/17/11 04:38 pm *
* 05/24/11 03:55 pm *
* 07/06/11 09:05 am *
* 07/11/11 03:46 pm *
* 07/11/11 03:49 pm * (CHG: Sta->11 Asg->ASHORTEN)
* 07/11/11 03:49 pm *
* 07/11/11 04:15 pm * (CHG: Sta->92 Asg->MZEEMAN)
* 07/21/11 09:04 am * (CHG: Sta->10 Asg->ASHORTEN)
* 07/21/11 09:04 am * When we changed the hibernate.c3p0.idle_test_period = 10 property per SPL recommendation,
a side affect of this change is that a SQL is executed way too
frequently.
* 07/21/11 09:05 am *
* 07/21/11 09:06 am * When PG&E changed the hibernate.c3p0.idle_test_period = 10 property per SPL recommendation,
a side affect of this change is that a SQL is executed way too
frequently. SQL_ID az33m61ym46y4 SELECT NULL AS table_cat, o.owner
AS table_schem, o.object_name AS table_name, o.object_type AS
table_type, NULL AS remarks FROM all_objects o WHERE o.owner LIKE
:1 ESCAPE ‘/’ AND o.object_name LIKE :2 ESCAPE ‘/’ AND
o.object_type IN (‘xxx’, ‘TABLE’) ORDER BY table_type, table_schem,
table_name
—————— Query is run 150,000+ per hour–apparently forever, on nodes 3,4,5, which totals 500,000 executions per hour
* 07/21/11 09:06 am *
* 08/02/11 04:08 pm *
* 08/02/11 04:32 pm * The SQL shown in the example is really not from the product. It is not an SQL I think exists in the product as
such (It is a database query and in fact CISADM should not really
have access to ALL_TABLES. Please verify this is the ONLY SQL that
is excessive. The settings do not get the SQL to execute more than
they should. It only should affect reconnection checks.
* 08/09/11 03:53 pm *
* 08/09/11 03:54 pm *
* 08/10/11 08:56 am *
* 08/10/11 12:22 pm * (CHG: Sta->11 Asg->FJOCSON)
* 08/10/11 02:32 pm *
* 08/10/11 02:32 pm * (CHG: Sta->30 Asg->MZEEMAN)
* 08/31/11 11:06 pm *
* 08/31/11 11:14 pm *
* 10/11/11 03:23 pm * (CHG: Sta->92)
* 10/11/11 03:23 pm *