| Gathering Statistics of Partitioned table [message #663712] |
Thu, 15 June 2017 02:26  |
 |
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 #663726 is a reply to message #663714] |
Thu, 15 June 2017 05:11  |
John Watson
Messages: 8964
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 #663753 is a reply to message #663739] |
Fri, 16 June 2017 00:58 |
|
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 #663788 is a reply to message #663786] |
Mon, 19 June 2017 05:22 |
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
