Question about gathering table stats
Product FSCM - Supply Chain, Grants. Version 9.2. Tools 8.59.05 Image 41; Oracle database 19c, on Red Hat Linux 7.9
We've been gathering table stats in the Oracle database for every table in the SYSADM schema. Recently we realize that according to
E-ORA Methods to Gather Statistics on PeopleSoft Oracle Databases (Doc ID 1345124.1)
we should not gather stats on all the tables whose type is 7 ("in Peoplesoft records with Rectype=7 are temporary tables and those still get build on the database, but these temporary tables are volatile in nature and should be excluded from this exercise"). The document doesn't say but we think it means that the tables whose names are in column recname, excluding name prefix 'PS_', in table PSRECDEFN where rectype=7 should be excluded. For example,
SQL> select recname, fieldcount, indexcount, rectype from PSRECDEFN where rownum<=10; RECNAME FIELDCOUNT INDEXCOUNT RECTYPE --------------- ---------- ---------- ---------- AP_RETURN_WRK 2 0 2 AP_RT_TYPE_VW 3 0 1 AP_RULECNTL_WRK 10 0 2 AP_RULESET_DTL 3 1 0 AP_RULETYPE_DTL 4 1 0 AP_RUN_DRAFT 6 1 0 AP_RUN_DRAFT2 6 1 0 AMI_POOL 9 1 7 AMI_RQDTL_AUP 11 1 0 AMIF_AST_TAO 8 1 7
This means we should not gather table stats on tables ps_ami_pool and ps_amif_ast_tao because they contain temporary data (but do so on other tables). Can anybody confirm?
Secondly, we find that not all tables are listed in psrecdefn, e.g.
SQL> select recname, fieldcount, indexcount, rectype from PSRECDEFN where recname like 'GM_PT_PRJ_TA%4' order by 1; RECNAME FIELDCOUNT INDEXCOUNT RECTYPE --------------- ---------- ---------- ---------- GM_PT_PRJ_TA4 4 1 7 SQL> select table_name from dba_tables where owner='SYSADM' and table_name like 'PS_GM_PT_PRJ_TA%4'; TABLE_NAME ------------------------------ PS_GM_PT_PRJ_TA24 PS_GM_PT_PRJ_TA14 PS_GM_PT_PRJ_TA04 PS_GM_PT_PRJ_TA34 PS_GM_PT_PRJ_TA4 PS_GM_PT_PRJ_TA44 PS_GM_PT_PRJ_TA54
Only 1 out of 7 is listed in PSRECDEFN, and is correctly marked as type 7 i.e. temporary. If the other 6 all contained non-temporary data, that would be fine. But that's probably not true. For example, to our knowledge, PS_GM_PT_PRJ_TA04 (note the extra 0) is a table with temporary data; it gets truncated and re-loaded. Unless we gather stats right after data loading, we'd rather rely on dynamic sampling to get the stats right instead of getting it totally wrong if the gather stats job runs at the time the table happens to be empty.
So, do we understand the Oracle document correctly? And how do we find the type of the tables not listed in PSRECDEFN? Thank you!
Answers
-
Hi,
I think you should better use this one:
pscbo_stats - Improving Statistics in Oracle RDBMS for PeopleSoft Enterprise (Doc ID 1322888.1)
1 -
Hi - There are 2 parts Peoplesoft metadata and oracle DB metadata. When you use regular stats gathering job oracle database will simply look for tables present in DBA_TABLES and gather the stats as oracle db is not aware how peoplesoft system works. as you stated temp table should not have stats collected.
to avoid this situation oracle developed a PSCBO_stats as mentioned in previous comment . But implementing this would be a customization as oracle stoppped supporting this package since long time.
Also regarding your second question why tables count mismatch in PSRECDEFN & DBA_TABLES. this is working as designed. when you create a temp table with count of 4 it creates the base table in database and same table does not exist in PSRECDEFN thus in App designer.
Consider implementing the lock stats of table with type as 7 which will be excluded from stats gathering.
As far as your question is concerned its working as designed and i dont see anything wrong with it. its just you need to understand on how this works.
-Velu
1 -
Thank you, user566822 and Velu. Doc 1322888.1 (on pscbo_stats) is very helpful. I'll implement it soon. The point about temp tables and dynamic sampling is what I care about. Another point about histograms is something I didn't expect; I always thought PeopleSoft database should never have histograms but that's no longer true after 10g.
I understand the pscbo_stats package is not supported by Oracle. But it won't render the PeopleSoft database unsupportable. Correct me if I'm wrong.
Velu: We didn't create additional tables in SYSADM schema. The 6 tables not in PSRECDEFN in my original message were created by Oracle. (I verified by checking a tiny PeopleSoft database created by the initial installation but never used by anybody here.) My question is, How do I find the extra temp tables if PSRECDEFN doesn't give me a full list? PS_GM_PT_PRJ_TA04 is kind of easy because the name resembles PS_GM_PT_PRJ_TA4, which is in PSRECDEFN. But table name resemblance is not reliable. But I think the pscbo_stats package will solve the problem.
We do have performance problems. Some are definitely related to gathering stats on temp tables.
0
