Hi,

Our encyclopedia has 50M rows for DOBJ and 83M rows for DASC. Subsets are pretty slow to download.

Is there anyone with experience dealing with such big encyclopedias?

Anyone willing to explain successful stories for improving performance of a big encyclopedia?

Thanks a lot!

Best regards.

In my experience the performance of downloading subsets is affected more by the model size rather than the encyclopaedia size. How many objects are there in the model that has poor download times?

Hi Darius,

the models we are talking are:

  Model Statistics

     Model name . . . . . . . . . . : SLM_CUS_CUSTOMER_01_01_ID

     Number of objects  . . . . . . : 2986165

     Number of subsets  . . . . . . : 245

     Number checked out . . . . . . : 41

  Model Statistics

     Model name . . . . . . . . . . : SLM_RAT_RATES_01_01_ID

    Number of objects  . . . . . . : 2784720

     Number of subsets  . . . . . . : 291

     Number checked out . . . . . . : 37

on cse enciclopedia.

Also, the number of DASC rows for each:

4,912,224

4,490,309

These do not seem like excessively large models. What are typical download times and the size of the subset?

What tuning activities have you performed so far on the server, disk io, oracle and CSE settings?

For example, on this same week:

These are mean times of all the download of the whole week.

We do rebuild of the indexes from time to time.

Also, the firm has hired several consulting services for DB tunning. Last one said there was not much to do. He said the queries were perfect in the sense that they were using indexes. The problem was that they were repeated millions of times. He was kind of surprised for the meta-model.

For example, the top query had a very low elapsed time (by itself). But since it was repeated millions of times, it was on the top of the elapsed time, with almost 50% of all global elapsed time of the examined period. This was the query that fetches 1 row from dobj filtering by obj_id.

Also, developers seem to agree to download things at once   which I guess makes things worse.

Regarding the subsets size: what do you exactly mean? Trn size or number of objects?

just in case there is link on docops.ca.com regarding

Monitoring and Tuning on Oracle

Monitoring and Tuning on Oracle - CA Gen - 8.5 - CA Technologies Documentation

P.S I know couple of things is already followed in your case

How many objects in the subset expansion table for that subset (DSUBEX) and also size of the checkout.trn? If they are downloading a huge subset with a large number of complex action diagrams or windows then 30 mins is probably about right. If they are downloading one small action block, then not so good.

How much memory have you allocated to Oracle and have you checked your buffer cache hit ratio? We aim for over 95% on our CSE and just use the Oracle memory_target setting. Ours is 8Gb and gives acceptable performance for our CSE (32m objects total and models ~ 1.5m objects).

Have you changed the CSE object cache property? The default is very low and we set it to 5,000,000.

Do you ever update the database stats?

Can you remove any unwanted models?

Disk IO makes a big difference. We moved our oracle tables to a fast enterprise grade SSD drive and this improved performance, but if you are running it on fast 15krpm RAID arrays with a lot of striping, this also helps. However if you have the oracle data files on a slow network attached storage, this may not help...

If tuning is optimal then the only sure way to reduce the download times would be to split up the models into smaller ones. For example you could create a model containing a few p-steps and then subset form this and see what the difference in download times are for the same subset from the large model and the smaller one. This would give you an idea of the performance improvement to be gained from splitting up the model. This would have to be balanced against increased complexity of development and model management that occurs with a multiple model architecture.

>> How many objects in the subset expansion table for that subset (DSUBEX) and also size of the checkout.trn?

for example, a subset that needed 1 hour to download:

trn size = 9.31 MB

objects = 59629 (DSUBEX)

>> How much memory have you allocated to Oracle and have you checked your buffer cache hit ratio?

Oracle's assigned memory is 12GB

Form which:

Oracle's PGA = 1,800 MB

     Currently assigned: 223 MB

     Max ever assigned: 350 MB

Oracle's SGA = 10,176 MB

     From which:

          Shared pool         416

          Buffer cache         9600

          Large Pool           32

          Java Pool           32

          Other                96

>> Have you changed the CSE object cache property? The default is very low and we set it to 5,000,000.

Our current value is 2,000,000. We remember time ago to have tried several different values (some of them higher), some of them recommended by CA. We didn't notice any impact with any value.

>> Do you ever update the database stats?

Yes

>> Can you remove any unwanted models?

I wish we could.

>> Disk IO makes a big difference.

Unforunatelly, We don't know what "disks" we have. This is a VPS and it seems that IBM does not want to give much information. All that we know is that they must be mechanical drives on some kind of raid on a rack of disks.

I think SSDs could be the big deal. We believe so because, for example, there's some difference if the subset has been download several times or not. As long as Oracle seems to cache more data related to the subset in memory, the downloads are faster.

By your estimates (and more or less), how much increase in performance did you notice on downloads of subset migrating to ssd?

If I recall correctly, we got about a 50% performance improvement. The big difference was with the write performance since the reads usually come from the cache. Given how much the SSD cost and the raw performance numbers, I was expecting a better improvement that we got, but at least it was not worse! The SSD did not give us a huge performance gain over the previous configuration since the data files were previously on a very fast RAID array of locally attached 15k RPM disks, so you might see a better improvement if your disk access is slow.

A 50% improvement seems promising.

Another idea I have come up is using Oracle's IOT tables. It may be useful for DOBJ since most of the queries on that table filter by obj_id.

Darius, Have you ever used an IOT on dobj on your CSE? With which results?

Thank you.

could you please
list data files and associated tablespaces by executing some thing below
select * from dba_data_files order by tablespace_name;

and similarly list free space in tablespaces
select tablespace_name, sum(bytes) from dba_free_space group by tablespace_name;

have you tried out anything related to oracle optimizer

by default it is best throughput; i.e., using the least amount of resources necessary to process all rows accessed by the statement.

but could be optimize a statement with the goal of best response time; i.e., using the least amount of resources necessary to process the first row accessed by a SQL statement.

SQL> run

  1* select * from dba_data_files order by tablespace_name

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

G:\ORACLE\ORADATA\ENCYNEW\ENCYDATOS07.DBF                                      

        25 ENCY_DATOS                     4294967296     524288 AVAILABLE      

          25 NO           0          0            0 4293918720      524160     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

G:\ORACLE\ORADATA\ENCYNEW\ENCYDATOS06.DBF                                      

        22 ENCY_DATOS                     4294967296     524288 AVAILABLE      

          22 NO           0          0            0 4293918720      524160     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

G:\ORACLE\ORADATA\ENCYNEW\ENCYDATOS05.DBF                                      

         9 ENCY_DATOS                     4294967296     524288 AVAILABLE      

           9 NO           0          0            0 4293918720      524160     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

G:\ORACLE\ORADATA\ENCYNEW\ENCYDATOS04.DBF                                      

         8 ENCY_DATOS                     4294967296     524288 AVAILABLE      

           8 NO           0          0            0 4293918720      524160     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

G:\ORACLE\ORADATA\ENCYNEW\ENCYDATOS01.DBF                                      

         5 ENCY_DATOS                     4294967296     524288 AVAILABLE      

           5 NO           0          0            0 4293918720      524160     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

G:\ORACLE\ORADATA\ENCYNEW\ENCYDATOS02.DBF                                      

         6 ENCY_DATOS                     4294967296     524288 AVAILABLE      

           6 NO           0          0            0 4293918720      524160     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

G:\ORACLE\ORADATA\ENCYNEW\ENCYDATOS03.DBF                                      

         7 ENCY_DATOS                     4294967296     524288 AVAILABLE      

           7 NO           0          0            0 4293918720      524160     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

H:\ORACLE\ORADATA\ENCYNEW\ENCY_INDICES02.DBF                                   

        14 ENCY_INDICES                   2411724800     294400 AVAILABLE      

          14 NO           0          0            0 2410676224      294272     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

H:\ORACLE\ORADATA\ENCYNEW\ENCY_INDICES08.DBF                                   

        26 ENCY_INDICES                   2411724800     294400 AVAILABLE      

          26 NO           0          0            0 2410676224      294272     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

H:\ORACLE\ORADATA\ENCYNEW\ENCY_INDICES07.DBF                                   

        21 ENCY_INDICES                   2411724800     294400 AVAILABLE      

          21 NO           0          0            0 2410676224      294272     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

H:\ORACLE\ORADATA\ENCYNEW\ENCY_INDICES01.DBF                                   

        15 ENCY_INDICES                   2411724800     294400 AVAILABLE      

          15 NO           0          0            0 2410676224      294272     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

H:\ORACLE\ORADATA\ENCYNEW\ENCY_INDICES09.DBF                                   

        27 ENCY_INDICES                   2411724800     294400 AVAILABLE      

          27 NO           0          0            0 2410676224      294272     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

H:\ORACLE\ORADATA\ENCYNEW\ENCY_INDICES03.DBF                                   

        13 ENCY_INDICES                   2411724800     294400 AVAILABLE      

          13 NO           0          0            0 2410676224      294272     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

H:\ORACLE\ORADATA\ENCYNEW\ENCY_INDICES04.DBF                                   

        12 ENCY_INDICES                   2411724800     294400 AVAILABLE      

          12 NO           0          0            0 2410676224      294272     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

H:\ORACLE\ORADATA\ENCYNEW\ENCY_INDICES05.DBF                                   

        11 ENCY_INDICES                   2411724800     294400 AVAILABLE      

          11 NO           0          0            0 2410676224      294272     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

H:\ORACLE\ORADATA\ENCYNEW\ENCY_INDICES06.DBF                                   

        10 ENCY_INDICES                   2411724800     294400 AVAILABLE      

          10 NO           0          0            0 2410676224      294272     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

K:\ORACLE\ORADATA\ENCYNEW\GDN_DATOS02.DBF                                      

        16 GDN_DATOS                       524288000      64000 AVAILABLE      

          16 NO           0          0            0  523239424       63872     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

K:\ORACLE\ORADATA\ENCYNEW\GDN_DATOS01.DBF                                      

        17 GDN_DATOS                       524288000      64000 AVAILABLE      

          17 NO           0          0            0  523239424       63872     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

L:\ORACLE\ORADATA\ENCYNEW\GDN_INDICES02.DBF                                    

        18 GDN_INDICES                     209715200      25600 AVAILABLE      

          18 NO           0          0            0  208666624       25472     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

L:\ORACLE\ORADATA\ENCYNEW\GDN_INDICES04.DBF                                    

        23 GDN_INDICES                     209715200      25600 AVAILABLE      

          23 NO           0          0            0  208666624       25472     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

L:\ORACLE\ORADATA\ENCYNEW\GDN_INDICES03.DBF                                    

        20 GDN_INDICES                     209715200      25600 AVAILABLE      

          20 NO           0          0            0  208666624       25472     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

L:\ORACLE\ORADATA\ENCYNEW\GDN_INDICES01.DBF                                    

        19 GDN_INDICES                     209715200      25600 AVAILABLE      

          19 NO           0          0            0  208666624       25472     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

F:\ORACLE\ORADATA\ENCYNEW\SYSAUX01.DBF                                         

         2 SYSAUX                         1258291200     153600 AVAILABLE      

           2 YES 3.4360E+10    4194302         1280 1257242624      153472     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

F:\ORACLE\ORADATA\ENCYNEW\SYSTEM01.DBF                                         

         1 SYSTEM                         1635778560     199680 AVAILABLE      

           1 YES 3.4360E+10    4194302         1280 1634729984      199552     

SYSTEM                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

F:\ORACLE\ORADATA\ENCYNEW\TS_AUDIT01.DBF                                       

        24 TS_AUD                         1153433600     140800 AVAILABLE      

          24 NO           0          0            0 1152385024      140672     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

F:\ORACLE\ORADATA\ENCYNEW\UNDOTBS01.DBF                                        

         3 UNDOTBS1                       1.1660E+10    1423360 AVAILABLE      

           3 YES 3.4360E+10    4194302          640 1.1659E+10     1423232     

ONLINE                                                                         

FILE_NAME                                                                      

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         

---------- ------------------------------ ---------- ---------- ---------      

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     

------------ --- ---------- ---------- ------------ ---------- -----------     

ONLINE_                                                                        

-------                                                                        

F:\ORACLE\ORADATA\ENCYNEW\USERS01.DBF                                          

         4 USERS                            41943040       5120 AVAILABLE      

           4 YES 3.4360E+10    4194302          160   40894464        4992     

ONLINE                                                                         

27 rows selected.

----------------------

SQL> run

  1* select tablespace_name, sum(bytes) from dba_free_space group by tablespace_name

TABLESPACE_NAME                SUM(BYTES)                                      

------------------------------ ----------                                      

TS_AUD                          270991360                                      

SYSAUX                           73269248                                      

UNDOTBS1                       1.1618E+10                                      

ENCY_INDICES                   5063639040                                      

GDN_DATOS                       420675584                                      

GDN_INDICES                     352321536                                      

USERS                            40566784                                      

SYSTEM                          876609536                                      

ENCY_DATOS                     6082985984                                      

9 rows selected.

No, we haven't.

I agree with SSDs to improve write performance on a CSE. My primary site is on SQLServer and has 113M dobj and 191M dasc. Removing old subsets and ensuring developers scope their subsets correctly is key to download performance. Don't take out procedures in Full Expansion and only take what you need. Also to help download performance we tweaked one of the indexes on DASC ... IASC2 - adding ASSOC_CHG_STATUS to the index

My secondary site is on Oracle and I'll get some stats for that one later this week. I'm going to tweak this index there to see if it improves performance (not running SSDs there and download performance is currently poor according to the developers).

It also doesn't hurt to ensure that you run stats/re-orgs every weekend. When we don't do that on SQLServer CSE we get a large performance hit and it is very obvious for checkouts (they go from 3-7mins to 15-30mins for just a few ABs/PSteps).

Hi,

When you say that removing old subsets is a good idea, what do you exactly mean?: To override not needed subsets or to delete full subset definitions (even if they are not checked out)?

Good! we are expecting to hear about your results with IASC2 modification.

Just for the sake of comparison, on your Oracle non-ssd server, is also the query

select ... from dobj where obj_id = ?

your top reason for elapsed time on the DB?

Havent had a chance to do testing on the oracle CSE last week ... however, to answer your query about subsets ...

YES, having lots of subset definitions in a CSE (whether checked out or otherwise) does impact download performance as the download processes 'seems' to transverse through them to check for downgrades.When we go through a process to clean up old subsets ... it speeds up downloads by a few seconds (for us now).

Oh, this oracle CSE has 61M in dobj and 101M in dasc ... what size subset do you want me to test with?

This is surprising for me. I expected the download process to ignore non checked out subsets. But It seems this is not the case then. We will try to do clean up of the subsets we have, which are quite a lot.

Regarding the test with the index: It is more or less the same. We could scale the seen improvement over smaller or bigger subsets. Just donwnload whatever the subset you want and compare before & after the index is applied.

Thanks!

Ok ... I had a chance to do some testing and collect data on a Gen85 based Oracle CSE

Specs: Server 2012R2/8Gb RAM/Xeon E5-2670v3@2.3GHz (1 socket / 4 cores)

CSE MD and Oracle DB services restarted between tests.

Index tests were done via a drop and recreate ... then a RUNSTATS

Test Data:

DBA Subset  = Approx 36 Subj Areas, 1 TD - Default/Modify

PSteps and ABs Subset =   4 Psteps, 19 ABs - Default/Modify

Small Model = approx. 18K objects

Medium Model = approx. 1.7M objects

No optimisation - object cache - 1,000,000

Checkout DBA Subset - 0:04:30

Checkout PSteps and ABs - 0:06:44

Model Copy (small) - 0:01:19

Model Copy (medium) - 2:32:58

No optimisation - object cache - 10,000,000

Checkout DBA Subset - 0:04:30

Checkout PSteps and ABs - 0:05:47

Model Copy (small) - 0:01:14

Model Copy (medium) - 2:01:59

Add IASC2 index - new column - object cache 1,000,000

Checkout DBA Subset - 0:03:47

Checkout PSteps and ABs - 0:06:42

Add IASC2 index - new column - object cache 10,000,000

Checkout DBA Subset - 0:03:22

Checkout PSteps and ABs - 0:06:53

Model Copy (small) - 0:01:19

Model Copy (medium) - 2:07:14

I missed the model copy tests with the object cache at 1M and the expanded index. However, from this you can conclude that there is some benefit of setting the object cache higher than the default of 1M. The timings did improve greatly if I ran the job again ... restarting the services every-time ensures that these results were not improved by any caching or buffering whether in Gen or in the DBMS.

Looking at the figures here I am not happy with the model copy performance for a medium model on this Oracle server. At my SQLServer site we have a model with 6M objects that used to take 4 hours to copy until the server was upgraded (2012R2,  64Gb RAM and 3.5GHz CPU) to have SSDs, the index and change of the object cache. Now it copies in under 2 hours. However, the checkout performance is about on par with the SQLServer. This developers at the Oracle site clean up their old subsets, while they are tardy in cleaning subsets at the SQLServer site.

Other Oracle-ish DBA things to do is to check that your stats are up-to-date. Not having played with Oracle for a while, it looks like 11g has a lot of automated maintenance built in that SQLServer does not have.

You can check when last stats were done via (can filter by owner to only get CSE tables)

     select owner, table_name, last_analyzed from all_tables order by last_analyzed DESC

And the schedule via:

     select * from dba_autotask_client where client_name like '%auto%stats%';

I hope these observations help someone out there in the Gen community.

Thanks Aldred,

Interesting that taking the Object Cache > 1M does still help. I probably have never had large enough models to verify that

On the Oracle v SQL Server side I have always found SQL Server to provide reasonably good performance on an OOTB install on a basic machine where you have no ability to tweak the DB and its files for IO.

Aside from the stats being up to date I strongly suspect it is IO impacting Oracle as DariusPanahy has already indicated.

Since the first CSE benchmarking we did mid 90s the data/index distribution using either manual/RAID + disk speed seems to have been the constant theme where performance can be greatly improved.

"For example, the top query had a very low elapsed time (by itself). But since it was repeated millions of times, it was on the top of the elapsed time, with almost 50% of all global elapsed time of the examined period. This was the query that fetches 1 row from dobj filtering by obj_id."

For this particular query was there a reason why it took so much CPU? Was it getting a large number of disk i/o? In tests we did here at our

site we found that query got almost no disk i/o and it's cpu time was not egregious.

It spending most of the time on I/O. Also, that query is on the top of physical reads (unoptimized)

Is this a host encyclopedia or a client/server encyclopedia?

It is CSE Encyclopedia

what database ? is it oracle or sqlserver

Oracle 11.2.0.3.0

Also, may be someone has used partitioning on DOBJ or DASC? Is that a good Idea? Which criteria could be good for partitioning those tables?