CA Gen

Expand all | Collapse all

Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

julio.ezequiel.decastro02-05-2016 02:25 AM

Amit Dwivedi02-05-2016 02:37 AM

julio.ezequiel.decastro02-05-2016 02:50 AM

  • 1.  Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-04-2016 12:29 PM

    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.



  • 2.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-04-2016 01:14 PM

    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?



  • 3.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 02:18 AM

    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.



  • 4.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 02:32 AM

    Also, the number of DASC rows for each:

     

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

    4,912,224

     

     

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

    4,490,309



  • 5.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 03:17 AM

    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?



  • 6.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 03:56 AM

    For example, on this same week:

     

    SLM_HTL HOTEL_01_01_ID00:33:05
    SLM_RAT_RATES_01_01_ID00:30:16
    SLM_CUS_CUSTOMER_01_01_ID00:29:27

     

    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?



  • 7.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Broadcom Employee
    Posted 02-05-2016 04:04 AM

    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



  • 8.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 04:12 AM

    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.



  • 9.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 06:30 AM

    >> 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

     

    physical reads cache                                           70278283
    db block gets from cache                                      229285994
    consistent gets from cache                                   6850682682

     

    >> 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?



  • 10.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 06:41 AM

    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.



  • 11.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 09:32 AM

    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.



  • 12.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Broadcom Employee
    Posted 02-05-2016 10:10 AM

    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;



  • 13.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Broadcom Employee
    Posted 02-05-2016 10:33 AM

    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.



  • 14.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-08-2016 03:15 AM

    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.



  • 15.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 10:29 AM

    No, we haven't.



  • 16.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-09-2016 07:24 PM

    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).



  • 17.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-10-2016 02:55 AM

    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?



  • 18.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-14-2016 10:53 PM

    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?



  • 19.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-15-2016 02:53 AM

    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!



  • 20.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-22-2016 11:22 PM

    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.



  • 21.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Broadcom Employee
    Posted 02-22-2016 11:49 PM

    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.



  • 22.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 10:42 AM

    "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.



  • 23.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-08-2016 03:22 AM

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

     

     

    Elapsed Time (s)Executions Elapsed Time per Exec (s) %Total%CPU%IO  SQL Id
    2,083.391,762,9470.0030.866.1594.26azcpnwzxxsm1t

     

    UnOptimized Read ReqsPhysical Read ReqsExecutionsUnOptimized Reqs per Exec%Opt%Total  SQL Id
    185,432185,4321,762,9470.110.0071.92azcpnwzxxsm1t


  • 24.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-04-2016 02:52 PM

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



  • 25.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 02:25 AM

    It is CSE Encyclopedia



  • 26.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Broadcom Employee
    Posted 02-05-2016 02:37 AM

    what database ? is it oracle or sqlserver



  • 27.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 02:50 AM

    Oracle 11.2.0.3.0



  • 28.  Re: Anyone has experience dealing with big encyclopedias? (50M dobj, 83M dasc)

    Posted 02-05-2016 08:18 AM

    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?