Forum Stats

  • 3,759,224 Users
  • 2,251,514 Discussions
  • 7,870,541 Comments

Discussions

Stats Gather issue

Hey all,


Oracle 19c standalone

I have a developer who has created a table thats about 20G and has about 1500 to 1600 partitions and sub-partitions and 21 indexes. Stats gather fails on this table 99% of the time or takes days to complete.

Is there a good/better way to collect on this  or should he look at the design, i feel like 20G isnt that big but i feel like its all the partitions. Im not a developer so i dont know maybe this is normal


I tried

BEGIN dbms_stats.gather_table_stats(ownname=>'CAT', TABNAME=>'ORDER_SEARCH', estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE, degree =>32, cascade=>true); END;

--

BEGIN dbms_stats.gather_table_stats(ownname=>'CAT', TABNAME=>'ORDER_SEARCH', estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE, degree =>32, cascade=>false); END;

Thanks

Dave

Tagged:

Best Answer

  • T1DSoldier
    T1DSoldier Member Posts: 60 Blue Ribbon
    Accepted Answer

    How do i check if the index is local, I dont believe it is

    select INDEX_NAME,INDEX_type ,GLOBAL_STATS from dba_indexes where table_name like 'ORDER_SEARCH'

    they all say index_type normal and global_stats yes, except for the ctx one thats index_type domain and global_stats no

    I did check select * from dba_ind_partitions where index_owner='CAT' and INDEX_NAME in (select INDEX_NAME from dba_indexes where table_name like '%ORDER_SEARCH');

    and it did pull back 6969 rows

    he is partitioning on company, as long as you put the right filter on the queries return fast but if you try to order and not filter its not good.

    PARTITION BY LIST (COMPANY) AUTOMATIC

    SUBPARTITION BY LIST (COMPANY_ID)

    (  PARTITION COMPANY_P1 VALUES ('BINGO_INC')

       (SUBPARTITION VALUES (DEFAULT)

    Im not a developer so I try not to get in their design but the partitions seem excessive but i dont know if its my lack of not knowing the application. I have asked about the amount of partitions a few times but he answers every question but that on

Answers

  • EdStevens
    EdStevens Member Posts: 28,470 Gold Crown

    or should he look at the design

    Can't say without seeing the current design.

  • T1DSoldier
    T1DSoldier Member Posts: 60 Blue Ribbon
    Accepted Answer

    How do i check if the index is local, I dont believe it is

    select INDEX_NAME,INDEX_type ,GLOBAL_STATS from dba_indexes where table_name like 'ORDER_SEARCH'

    they all say index_type normal and global_stats yes, except for the ctx one thats index_type domain and global_stats no

    I did check select * from dba_ind_partitions where index_owner='CAT' and INDEX_NAME in (select INDEX_NAME from dba_indexes where table_name like '%ORDER_SEARCH');

    and it did pull back 6969 rows

    he is partitioning on company, as long as you put the right filter on the queries return fast but if you try to order and not filter its not good.

    PARTITION BY LIST (COMPANY) AUTOMATIC

    SUBPARTITION BY LIST (COMPANY_ID)

    (  PARTITION COMPANY_P1 VALUES ('BINGO_INC')

       (SUBPARTITION VALUES (DEFAULT)

    Im not a developer so I try not to get in their design but the partitions seem excessive but i dont know if its my lack of not knowing the application. I have asked about the amount of partitions a few times but he answers every question but that on

  • T1DSoldier
    T1DSoldier Member Posts: 60 Blue Ribbon

    Youre saying you let your DBA design how you want your data accessed? If they dont know the queries used to access the data how can they determine a useful index. I cant say I know of many dbas telling a developer index or partitioning structure on their tables, maybe we are looking at it differently.

    Oracle generates the partitions "AUTOMATIC" and each partition has at least 4 rows so I guess so.

  • EdStevens
    EdStevens Member Posts: 28,470 Gold Crown

    Youre saying you let your DBA design how you want your data accessed?

    A proper design is NOT based on the needs of a specific query or report. The actual table design is based on the business relationship of the various data elements. This is called 'data normalization' and the ideal is to design to Third Normal Form. (look it up). To this end, table design should be a joint effort of the DBA and the developer/business analyst.

    Things like additional indexing, partitioning, etc. are introduced to address specific issues as they are discovered. For instance, a table may start out as a simple heap table, but a given performance issue then indicates that partitioning is a proper solution. The needs of a frequently executed query may indicate an additional index should be helpful - after weighing the factor that each additional index introduces a performance penalty on insert, update, and delete.

    And arching over this is that design and development should be a joint venture between developers and DBAs.

  • T1DSoldier
    T1DSoldier Member Posts: 60 Blue Ribbon

    well this has got me nowhere close to my question. I think Ed is the closes about being a joint effort, John you sure are riding high on your soapbox.

    Anyways, I give developers suggestions and let them pick their own journey but I'm guessing every shop has their own way of doing things

  • EdStevens
    EdStevens Member Posts: 28,470 Gold Crown

    I think Ed is the closes about being a joint effort, John you sure are riding high on your soapbox.

    Actually, over the years I've seen that John and I see pretty much eye to eye on most things. We just describe things differently as we respond to questions.