Hi, I have task to collect table statistics at partition level instead of table level statistics. Table created with range and interval partition. I know using dbms_stats.gather_table_stats collect stats at table, could you pls help collections stats at partition level and what parameters need to pass.
Hi For gathering statistic in partition level use this script:
SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> '''||OWNER||''', TABNAME => '''||TABLE_NAME||''' , partname => ''' ||PARTITION_NAME|| ''' ,granularity => ''PARTITION'' ,CASCADE => TRUE, DEGREE => 6);' FROM DBA_TAB_STATISTICS WHERE OWNER = 'VAHID' and (STALE_STATS='YES' or stale_stats is NULL) and partition_name is not NULL and subpartition_name is NULL;
Dear Basu degree=>6, It means degree of parallelism on your database in fact your database will gather statistic by 6 thread as parallel.
stale_status, It means statistic status of your objects in database for example if your stale_status files be YES therefore it shows your objects's statistics needs to gather. I hope be useful.
Hi, I am executing below statement dynamically I.e truncating table partition Execute immediate 'alter table '||temp||'truncate partition for'||'(some_value)'
Getting below error "partition extended table name syntax is disallowed in this context. If I hard card table name its working fine , but I don't want to hard code table name. Pls let me know how to fix this ??
Thank you so much ..I missed space after table name hence error.
Vahid ,
I have one more question I crated ranger interval partition table to capture monthly data ..initial partition 'xyz' by mistake captured 2 months data starting date range given wrong , ex less than 20181201 instead of 20181101,later partitions created by range so my question can i update or alter table to set initial range value back to 20181101 and for 20181201 , system automatically creates for this.
Basu, If you made a mistake you should split the wrong partition by below script ALTER TABLE sales SPLIT PARTITION sales_Q4_2007 INTO ( PARTITION sales_Q4_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','dd-MON-yyyy')), PARTITION sales_Q1_2008 VALUES LESS THAN (TO_DATE('01-APR-2008','dd-MON-yyyy')), PARTITION sales_Q2_2008 VALUES LESS THAN (TO_DATE('01-JUL-2008','dd-MON-yyyy')), PARTITION sales_Q3_2008 VALUES LESS THAN (TO_DATE('01-OCT-2008','dd-MON-yyyy')), PARTITION sales_Q4_2008);
Hi , I am collecting partitions statistics inside loop ..just giving first partition name with 'auto' option. Before gathering statistics for each partition enabling incremental true on the table ..my question, is it collect statistics every time or on first call only.
Hi, I have requirement to write sql query based on input produce below result, plz help me to write query. If input '1y1m' return 13 If input '2y1m' return 25 If input '1y5m' return 17 Elsif input '1y' return 12 Elsif input '2y' return 24 Elsif input '5y' return 60 Elsif input '5m' return 5 Elsif input '7m' return 7
Hi,
ReplyDeleteI have task to collect table statistics at partition level instead of table level statistics.
Table created with range and interval partition.
I know using dbms_stats.gather_table_stats collect stats at table, could you pls help collections stats at partition level and what parameters need to pass.
Hi
DeleteFor gathering statistic in partition level use this script:
SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> '''||OWNER||''', TABNAME => '''||TABLE_NAME||''' , partname => '''
||PARTITION_NAME|| ''' ,granularity => ''PARTITION'' ,CASCADE => TRUE, DEGREE => 6);'
FROM DBA_TAB_STATISTICS
WHERE OWNER = 'VAHID'
and (STALE_STATS='YES' or stale_stats is NULL)
and partition_name is not NULL and subpartition_name is NULL;
Really appreciate your suggestion.Just would like know what is that degree=>6 and stale_status parameters ?
ReplyDeleteDear Basu
Deletedegree=>6, It means degree of parallelism on your database in fact your database will gather statistic by 6 thread as parallel.
stale_status, It means statistic status of your objects in database for example if your stale_status files be YES therefore it shows your objects's statistics needs to gather. I hope be useful.
Hi,
ReplyDeleteI am executing below statement dynamically I.e truncating table partition
Execute immediate 'alter table '||temp||'truncate partition for'||'(some_value)'
Getting below error "partition extended table name syntax is disallowed in this context.
If I hard card table name its working fine , but I don't want to hard code table name.
Pls let me know how to fix this ??
Hi,
ReplyDeleteYou can use below query but be careful about truncate your partitions.
select 'ALTER TABLE ' || table_owner || '.'|| table_name || ' truncate partition '|| partition_name || ' UPDATE INDEXES;'
from dba_tab_partitions;
Thank you so much ..I missed space after table name hence error.
ReplyDeleteVahid ,
I have one more question I crated ranger interval partition table to capture monthly data ..initial partition 'xyz' by mistake captured 2 months data starting date range given wrong , ex less than 20181201 instead of 20181101,later partitions created by range so my question can i update or alter table to set initial range value back to 20181101 and for 20181201 , system automatically creates for this.
Basu,
DeleteIf you made a mistake you should split the wrong partition by below script
ALTER TABLE sales SPLIT PARTITION sales_Q4_2007 INTO
( PARTITION sales_Q4_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','dd-MON-yyyy')),
PARTITION sales_Q1_2008 VALUES LESS THAN (TO_DATE('01-APR-2008','dd-MON-yyyy')),
PARTITION sales_Q2_2008 VALUES LESS THAN (TO_DATE('01-JUL-2008','dd-MON-yyyy')),
PARTITION sales_Q3_2008 VALUES LESS THAN (TO_DATE('01-OCT-2008','dd-MON-yyyy')),
PARTITION sales_Q4_2008);
Thank you and really appreciate your help !
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHi ,
ReplyDeleteI am collecting partitions statistics inside loop ..just giving first partition name with 'auto' option.
Before gathering statistics for each partition enabling incremental true on the table ..my question, is it collect statistics every time or on first call only.
Hi,
ReplyDeleteI have requirement to write sql query based on input produce below result, plz help me to write query.
If input '1y1m' return 13
If input '2y1m' return 25
If input '1y5m' return 17
Elsif input '1y' return 12
Elsif input '2y' return 24
Elsif input '5y' return 60
Elsif input '5m' return 5
Elsif input '7m' return 7