Home » RDBMS Server » Performance Tuning » Gathering Statistics of Partitioned table (Oracle 11g )
Gathering Statistics of Partitioned table [message #663712] Thu, 15 June 2017 02:26 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All ,

We have table A with INTERVAL(DAY wise) Partition on TIMESTAMP column. Whenever data is inserted into the table with sysdate as timesatmp a new partitioned will be created. i.e we have one partition for one working day. There is control either to merge or to drop existing old partitions. In production environment its just keep on increasing the number of partitions on that table .

This table is having 4 millions records 500 partitions and it is taking 45 minutes to gather statistics on production

I am suspecting the number of partitions is impacting the gathering statistics why because there some other table which are having the more data & less number of partitions than A ,and taking less time to gather statistics.

Please correct me if I am wrong .

Thanks
SaiPradyumn.
Re: Gathering Statistics of Partitioned table [message #663713 is a reply to message #663712] Thu, 15 June 2017 02:31 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Almost certainly correct, there will be overhead. Is there a reason you are partitioning so aggressively for so few records?

There is the option of incremental statistics if that suits your data though.
Re: Gathering Statistics of Partitioned table [message #663714 is a reply to message #663713] Thu, 15 June 2017 02:45 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Thanks Roachcoach for your quick response.

There is no certain reason why we had implemented DAY-INTERVAL Partition on that table at the time of initial development.
As we have some performance issue on one particular job which is having gathering statistics of that table A , now we are analyzing the issue thoroughly .

Thanks
SaiPradyumn
Re: Gathering Statistics of Partitioned table [message #663726 is a reply to message #663714] Thu, 15 June 2017 05:11 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Partitioning such a small table may have some unexpected (and possibly undesired) side affects. For example, a full table scan may be indirect rather than direct because no one partition is large enough to trigger direct read. How about indexes? Are they local or global?
In general, I would say that a table of that size would usually be better off unpartitioned. Easy to convert: merge them, and modify the interval.
Re: Gathering Statistics of Partitioned table [message #663728 is a reply to message #663726] Thu, 15 June 2017 05:51 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi John ,

Thanks for your inputs.
As per the table definition there are two local indexes.
Initially ow we are planing to merge all those partitions to TWO partitions.

On going possible solutions

1: To merge that merge job on weekly basis
or
2:To change the interval as INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))

Please suggest which one is feasible solution ?

Thanks
SaiPradyumn



Re: Gathering Statistics of Partitioned table [message #663730 is a reply to message #663728] Thu, 15 June 2017 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Initially ow we are planing to merge all those partitions to TWO partitions.
Why?
And why a partitioned table?

Re: Gathering Statistics of Partitioned table [message #663731 is a reply to message #663730] Thu, 15 June 2017 07:43 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel ,

If I merge all partitions lowest boundary will contains most of the data.I just want to keep minimum number partitions in the table .
That's why kept as TWO

Thanks
SaiPradyumn
Re: Gathering Statistics of Partitioned table [message #663732 is a reply to message #663731] Thu, 15 June 2017 07:45 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I just want to keep minimum number partitions in the table .
The minimum is one.
Re: Gathering Statistics of Partitioned table [message #663733 is a reply to message #663731] Thu, 15 June 2017 07:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Thu, 15 June 2017 05:43
Hi Michel ,

If I merge all partitions lowest boundary will contains most of the data.I just want to keep minimum number partitions in the table .
That's why kept as TWO

Thanks
SaiPradyumn
BTW the absolute minimum number of partitions is ONE.
Can you post test case that shows any measurable benefit that partitioned table provides over non-partitioned table?

Re: Gathering Statistics of Partitioned table [message #663735 is a reply to message #663733] Thu, 15 June 2017 08:41 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Through out of the application no where we are using this table with respect to the partitioned key column.
In this case there is no use of partition also. If we make partitioned table as NON-Partitioned table we don't know the impact.
and there is no specific reason why I didn't use the ONE.

This is problem in production and we are searching for feasible work around .

Please suggest.

Thanks
SaiPradyumn
Re: Gathering Statistics of Partitioned table [message #663736 is a reply to message #663735] Thu, 15 June 2017 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You have posted NOTHING of substance!


We don't know OS name or version.

We don't know DDL to create table or associated indexes.

We don't how data gets inserted (with or without APPEND hint).

Why haven't you traced the problem to actually see where time is being spent?
ALTER SESSION SET SQL_TRACE=TRUE.

You have a mystery & provided us no real clues.
Re: Gathering Statistics of Partitioned table [message #663739 is a reply to message #663735] Thu, 15 June 2017 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
This is problem in production and we are searching for feasible work around .
Make it non partitioned.

Re: Gathering Statistics of Partitioned table [message #663753 is a reply to message #663739] Fri, 16 June 2017 00:58 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All ,

Thanks for your support to understand the problem.

Hi Blackswan ,

Following are answers for your questions

Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE	11.2.0.4.0	Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production


Table Definition
DROP TABLE  TABLE_A;
  CREATE TABLE "TABLE_A" 
   (	"BRANCH" VARCHAR2(3 CHAR) NOT NULL ENABLE, 
	"ZYZ_COMPRESS_TRUNC_ID" VARCHAR2(20 CHAR) NOT NULL ENABLE, 
	"ZYZ_COMPRESS_TRUNC_VERSION" NUMBER NOT NULL ENABLE, 
	"INTERNAL_REF_NUM" VARCHAR2(20 CHAR), 
	"INCOMING_SOURCE_REF" VARCHAR2(20 CHAR), 
	"INCOMING_SOURCE_VERSION" NUMBER, 
	"ZYZREFERENCE" VARCHAR2(20 CHAR), 
	"ZYZVERSION" NUMBER, 
	"ACTIONTYPE" NUMBER, 
	"DEAL_TYPE" VARCHAR2(10 CHAR), 
	"CUST_NUM" VARCHAR2(20 CHAR), 
	"DEALDATE" DATE, 
	"VALUEDATE" DATE, 
	"SPOTWH" VARCHAR2(15 CHAR), 
	"PROD_CODE_ID" VARCHAR2(500 CHAR), 
	"CCYCODE" VARCHAR2(3 CHAR), 
	"AMOUNT1" NUMBER, 
	"CASUAL_NETTER_FLAG" NUMBER(1,0), 
	"PRMYRECEIVE_ACCOUNTNO" VARCHAR2(20 CHAR), 
	"CCYCODE2" VARCHAR2(3 CHAR), 
	"AMOUNT" NUMBER, 
	"PRMYPAY_ACCOUNTNO" VARCHAR2(20 CHAR), 
	"CLSELIGIBLEFLAG" NUMBER(1,0), 
	"CREDITCHECK" NUMBER(1,0), 
	"DEALER" VARCHAR2(12 CHAR), 
	"PROD_CODE" VARCHAR2(4 CHAR), 
	"   CROSSREF" VARCHAR2(20 CHAR), 
	"SWAP_INDICATOR" VARCHAR2(2 CHAR), 
	"CROSS_REF" VARCHAR2(20 CHAR), 
	"USI_NUMBER" VARCHAR2(32 CHAR), 
	"USI_PREFIX" VARCHAR2(10 CHAR), 
	"JURISDICTION" VARCHAR2(200 CHAR), 
	"UTI_CREATOR" VARCHAR2(50 CHAR), 
	"OUR_UTI" VARCHAR2(60 CHAR), 
	"THEIR_UTI" VARCHAR2(100 CHAR), 
	"CCYTYPE" VARCHAR2(7 CHAR), 
	"CONF_STATUS" VARCHAR2(1 CHAR), 
	"AFFIRM_STATUS" VARCHAR2(1 CHAR), 
	"RECOVERY_ENABLED" NUMBER, 
	"SOURCE" VARCHAR2(20 CHAR), 
	"STL_USRDFE_COL3" VARCHAR2(20 CHAR), 
	"QUEUE_REASON" VARCHAR2(2000 CHAR), 
	"COMPRESSED" VARCHAR2(1 CHAR), 
	"NETTERFLAG" VARCHAR2(1 CHAR), 
	"DVP_FLAG" VARCHAR2(20 CHAR), 
	"WRK_USRDFE_COL1" VARCHAR2(20 CHAR), 
	"TIMESTAMP" DATE NOT NULL ENABLE, 
	"INCOMING_SOURCE" VARCHAR2(3 CHAR) DEFAULT NULL, 
	"CLEARINGID" VARCHAR2(40 CHAR), 
	"CLEARINGHOUSE" VARCHAR2(18 CHAR), 
	"CLEARINGFLAG" NUMBER(1,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_CMASTER" 
  PARTITION BY RANGE ("TIMESTAMP") INTERVAL (NUMTODSINTERVAL(1,'DAY')) 
 (PARTITION "SYS_P29642"  VALUES LESS THAN (TO_DATE(' 2015-09-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_CMASTER" , 
 PARTITION "SYS_P29682"  VALUES LESS THAN (TO_DATE(' 2015-09-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING 
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_CMASTER" )  ENABLE ROW MOVEMENT ;

  CREATE INDEX  "IDX_TABLE_A_01" ON  "TABLE_A" ("BRANCH", "ZYZ_COMPRESS_TRUNC_ID", "ZYZ_COMPRESS_TRUNC_VERSION") 
  PCTFREE 10 INITRANS 40 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_STPINDEX"  LOCAL
 (PARTITION "SYS_P29642" 
  PCTFREE 10 INITRANS 40 MAXTRANS 255 LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_STPINDEX" , 
 PARTITION "SYS_P29682" 
  PCTFREE 10 INITRANS 40 MAXTRANS 255 LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_STPINDEX" ) ;

  CREATE UNIQUE INDEX "TABLE_A_PK" ON "TABLE_A" ("BRANCH", "INTERNAL_REF_NUM", "ZYZVERSION", "TIMESTAMP") 
  PCTFREE 10 INITRANS 60 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_CMASTER"  LOCAL
 (PARTITION "SYS_P29642" 
  PCTFREE 10 INITRANS 60 MAXTRANS 255 LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_CMASTER" , 
 PARTITION "SYS_P29682" 
  PCTFREE 10 INITRANS 60 MAXTRANS 255 LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TS_CMASTER" ) ;

:

Data gets inserted with the help of following index from the DB.
   INSERT /*+ PARALLEL(TABLE_A 4) */

There is possibility to get the data from application also .

In production environment at what time exactly you want me to keep the SQL_TRACE as true
Possible cases
1: Online insertion
2: Data Base procedure insertion
3: Gathering statistics of that time

Hi Michel,
What should be minimum of records in a table, which is required for partition.


Thanks
SaiPradyumn
Re: Gathering Statistics of Partitioned table [message #663756 is a reply to message #663753] Fri, 16 June 2017 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
What should be minimum of records in a table, which is required for partition.

This is not a question of number of rows, it is a question of how you use the table and its data.

Database Concepts
Chapter 4 Partitions, Views, and Other Schema Objects
Section Overview of Partitions

Re: Gathering Statistics of Partitioned table [message #663760 is a reply to message #663756] Fri, 16 June 2017 07:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>In production environment at what time exactly you want me to keep the SQL_TRACE as true

trace the session where you want to see where time is being spent.

How many rows in TABLE_A?
What is average row length?
What is current table size?
How much free space inside TABLE_A?
Re: Gathering Statistics of Partitioned table [message #663782 is a reply to message #663760] Mon, 19 June 2017 01:21 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Blackswan/Michel

Thanks for your information,
As of now the table is hiving 3 millions .
in order to get answers for the remaining questions, do we need to go for the SQL TRACE as TRUE .
I will try to get the answers

Thanks
SaiPradyumn
Re: Gathering Statistics of Partitioned table [message #663783 is a reply to message #663782] Mon, 19 June 2017 02:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
in order to get answers for the remaining questions
Which ones?

Re: Gathering Statistics of Partitioned table [message #663784 is a reply to message #663783] Mon, 19 June 2017 04:40 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Following Questions asked Blackswan

What is average row length?
What is current table size?
How much free space inside TABLE_A?

Thanks
SaiPradyumn
Re: Gathering Statistics of Partitioned table [message #663785 is a reply to message #663784] Mon, 19 June 2017 04:45 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Average row length can be got from user_tables.
size from user_segments
Re: Gathering Statistics of Partitioned table [message #663786 is a reply to message #663784] Mon, 19 June 2017 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah OK, you do not need to activate trace for this.
1) is in USER_TABLES once you have gathered statistics
2) is in USER_SEGMENTS (sum of all partitions)
3) can be gotten using DBMS_SPACE.SPACE_USAGE and UNUSED_SPACE procedures

Re: Gathering Statistics of Partitioned table [message #663788 is a reply to message #663786] Mon, 19 June 2017 05:22 Go to previous message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks for your continuous support :

Following are the answers

1) is in USER_TABLES once you have gathered statistics

256

2) is in USER_SEGMENTS (sum of all partitions)

46945665024 bytes

Query to calculate :

SELECT SUM(bytes) siz ,
  SUM(DS.BLOCKS)
FROM user_SEGMENTS ds,
  user_TAB_PARTITIONS dts
WHERE DS.PARTITION_NAME = DTS.PARTITION_NAME
AND DTS.TABLE_NAME      ='TABLE_A' ;




3) can be gotten using DBMS_SPACE.SPACE_USAGE and UNUSED_SPACE procedures.

Will try for this

Thanks
SaiPradyumn
Previous Topic: query issue with execute immediate
Next Topic: While creating of M-View, lock the SYS.OBJ$ table
Goto Forum:
  


Current Time: Thu Mar 28 17:08:23 CDT 2024