Hdr: 9020054 10.2.0.4.0 RDBMS 10.2.0.4.0 BUFFER CACHE PRODID-5 PORTID-23 ORA-8103
Abstract: ORA-8103 BEING HIT DURING GATHERING OF STATISTICS ON TABLE PARTITION
*** 10/14/09 06:37 am ***
TAR:
----
PROBLEM:
--------
Provide the following:
1. Clear description of the problem encountered
ora-8103 is being hit at analyze of table partition
2. Pertinent configuration information (MTS/OPS/distributed/etc)
3. Indication of the frequency and predictability of the problem
Problem is not reproducable at will but frequency is high
4. Sequence of events leading to the problem
- truncate of multiple partitions
- populating new content in partitions
- gathering statistics per table partition
5. Technical impact on the customer. Include persistent after effects.
Failure in job as done, gathering of statistics at later timestamp is
successful
DIAGNOSTIC ANALYSIS:
--------------------
1) double checked that gathering of statistics is done at partition level:
- checked the code of the customer: etl_load.sql + etl_util.sql
==> confirms that statistics are gathered at table level
==> logging as done confirms that statistics are gathered at table level
- checked the tracefile of a failing run (library_cache) + successful run
==> confirms that statistics are gathered at table level
2) during the job run multiple partitions are being truncated
(library_cache), the failing gathering of statistics is started in between
3) tried to reproduce the problem using the steps as done by customer but was
not successful
WORKAROUND:
-----------
WorkAround is to gather statistics after all table manipulation (truncate +
populate of data) has finished
RELATED BUGS:
-------------
bug:7011872 was created before to address this problem. events as asked in
bug (10236 + 8103 ..) did generate a tremendous tracefile which only showed
blockdumps in the 1st couple of Gb.
REPRODUCIBILITY:
----------------
Although problem is not reproducable at will it does occur frequently
TEST CASE:
----------
Unfortunately there is no testcase available, have tried but was unsuccessful
STACK TRACE:
------------
*** 10:29:00.081
ksedmp: internal or fatal error
ORA-8103: object no longer exists
Current SQL statement for this session:
select /*+ parallel(t,8) parallel_index(t,8) dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring */
count(*),sum(sys_op_opnsize("PARTY_OP_ISSUANCE_PLACE")),sum(sys_op_opnsize("PA
RTY_PASSPORT_NUM")),sum(sys_op_opnsize("PARTY_TITLE_BEFORE")),sum(sys_op_opnsi
ze("PARTY_TITLE_AFTER")),sum(sys_op_opnsize("PARTY_BUSINESS_NAME")),sum(sys_op
_opnsize("PARTY_ICO_NUM")),sum(sys_op_opnsize("PARTY_DIC_NUM")),sum(sys_op_opn
size("LEGALFORM_CODE")),sum(sys_op_opnsize("CONS_PARTY_KEY")),sum(sys_op_opnsi
ze("IC_PARTY_KEY")),sum(sys_op_opnsize("PARTY_RC_NUM")),sum(sys_op_opnsize("PA
RTY_OP_NUM")),sum(sys_op_opnsize("STDALN_CUST_RATING_KEY")),sum(sys_op_opnsize
("STDALN_COLL_RATING_KEY")),sum(sys_op_opnsize("MIS_CUST_RATING_KEY")),sum(sys
_op_opnsize("MIS_COLL_RATING_KEY")),sum(sys_op_opnsize("APPROVED_RATING_KEY"))
,count("PARTY_TAX_RATE"),sum(sys_op_opnsize("PARTY_TAX_RATE")),sum(sys_op_opns
ize("PARTY_FULL_NAME")),sum(sys_op_opnsize("PARTY_IDENTIFICATION_NUM")),sum(sy
s_op_opnsize("PARTY_COCUNUT_NUM")),sum(sys_op_opnsize("PARTY_SURNAME")),sum(sy
s_op_opnsize("PARTY_FIRST_NAME")),sum(sys_op_opnsize("PROFITCENTER_CODE")),sum
(sys_op_opnsize("BANKRELATION_CODE")),sum(sys_op_opnsize("PARTY_SOURCE_CODE"))
,sum(sys_op_opnsize("PARTY_NUM")),sum(sys_op_opnsize("PARTYTYPE_CODE")),sum(sy
s_op_opnsize("DOMICILE_COUNTRY_CODE")),sum(sys_op_opnsize("CITIZENSHIP_COUNTRY
_CODE")),sum(sys_op_opnsize("OKEC_CODE")),sum(sys_op_opnsize("ESA95_CODE")),su
m(sys_op_opnsize("OENACE_CODE")),sum(sys_op_opnsize("COCUNUTTYPE_CODE")),sum(s
ys_op_opnsize("CNBCLASS_CODE")) from "ETL_OWNER"."SCURR_ODS_PARTY" sample
block ( 5.0000000000) t
----- PL/SQL Call Stack -----
object line object
handle number name
39ab58ff8 1218 package body SYS.DBMS_SYS_SQL
39998ed90 334 package body SYS.DBMS_SQL
39991f250 10982 package body SYS.DBMS_STATS
39991f250 12468 package body SYS.DBMS_STATS
39991f250 12892 package body SYS.DBMS_STATS
39991f250 13345 package body SYS.DBMS_STATS
39991f250 13457 package body SYS.DBMS_STATS
398256460 137 package body ETL_OWNER.ETL_UTIL
3981e0c58 500 package body ETL_OWNER.ETL_LOAD
3981e0c58 1734 package body ETL_OWNER.ETL_LOAD
39a624c68 2 anonymous block
----- Call Stack Trace -----
ksesec0 <- kcbzib <- kcbgtcr <- ktecgsc <- ktecgetsh <- ktecgshx <-
kteinicnt1 <- qertbFetch <- qergiFetch
SUPPORTING INFORMATION:
-----------------------
BUGnnnnn_20091014 will be uploaded containing the following:
etl_load.sql + etl_util.sql - pl/sql code as shown in stacktrace
ods1_ora_1544.trc tracefile of failing run, event 8103 stacktrace level 3 +
library_cache level 8
ods1_ora_14508_successfull_analyze.trc - tracefile of successful run
s_m_ods_party_xps_log.txt - logging made during failing run
do_it - testcase as being used to try to reproduce the problem of customer
24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------
DIAL-IN INFORMATION:
--------------------
IMPACT DATE:
------------
*** 10/14/09 07:50 am *** (CHG: Sta->10 Asg->CAHOLLAN)
*** 10/14/09 07:50 am ***
*** 10/14/09 09:11 am ***
*** 10/22/09 02:27 am ***
*** 11/03/09 12:18 am ***
*** 11/03/09 01:57 am *** (CHG: Sta->92 SubComp->BUFFER CACHE)
182
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
