Slow running query after upgrading to Oracle 10.2.0.4

10-11  Source: Network gathering  Views:1 

Advertisement
Hello
I had posted a query in Database Upgrade section , but all in vian as i have had no replies . So i am posing this in general section as i can now confirm with the exact query issue .
1. I upgraded my oracle instance windows 64 bit from 10.2.0.1 to 10.2.0.4
2. All is good except when i use SUM or MAX in queries i get a very slow performance below is the complete example
Your help is much appreciated .
Regards
Harshad
=====================================================================================================
Example.
=====================================================================================================The query runs on a single table of 275,000,000 records, partitioned by Feed_Id in 53 partitions:-
SELECT *
FROM Fact_Life
WHERE Life_Id = 11555269
AND Feed_Id = 2;
This returns 18 rows in sub-second response time.
Explain Plan for it:-
SELECT *
FROM Fact_Life
WHERE Life_Id = 11555269
AND Feed_Id = 2;
Statement Id=3 Type=INDEX
Cost=938 TimeStamp=11-06-09::11::25:55
(1) SELECT STATEMENT ALL_ROWS
Est. Rows: 66 Cost: 940
(4) PARTITION RANGE SINGLE
Est. Rows: 66 Cost: 940
(3) TABLE TABLE ACCESS BY GLOBAL INDEX ROWID RDS.FACT_LIFE [Analyzed]
Blocks: 2,765,931 Est. Rows: 66 of 275,979,985 Cost: 940
(2) INDEX INDEX SKIP SCAN RDS.PIX_FTLE_1 [Analyzed]
Est. Rows: 3 Cost: 938
However, when the query employs an aggregate function:-
SELECT MAX(Cap_Period_Id)
FROM Fact_Life
WHERE Life_Id = 11555269
AND Feed_Id = 2;
This takes anything from 200-400 seconds to return.
Explain plan for it:-
SELECT MAX(CAP_Period_Id)
FROM Fact_Life
WHERE Life_Id = 11555269
AND Feed_Id = 2;
Statement Id=3 Type=PARTITION RANGE
Cost=7 TimeStamp=11-06-09::11::26:50
(1) SELECT STATEMENT ALL_ROWS
Est. Rows: 1 Cost: 7
(5) SORT AGGREGATE
Est. Rows: 1
(4) FIRST ROW
Est. Rows: 66 Cost: 7
(3) PARTITION RANGE SINGLE
Est. Rows: 66 Cost: 7
(2) INDEX INDEX RANGE SCAN (MIN/MAX) RDS.PIX_FTLE_1 [Analyzed]
Est. Rows: 66 Cost: 7
However when the query is run with a different Life_Id for the same partition:-
SELECT MAX(Cap_Period_Id)
FROM Fact_Life
WHERE Life_Id = 11555275
AND Feed_Id = 2;
Returns sub-second.
Explain plan for it:-
SELECT MAX(CAP_Period_Id)
FROM Fact_Life
WHERE Life_Id = 11555275
AND Feed_Id = 2;
Statement Id=3 Type=PARTITION RANGE
Cost=7 TimeStamp=11-06-09::11::29:29
(1) SELECT STATEMENT ALL_ROWS
Est. Rows: 1 Cost: 7
(5) SORT AGGREGATE
Est. Rows: 1
(4) FIRST ROW
Est. Rows: 66 Cost: 7
(3) PARTITION RANGE SINGLE
Est. Rows: 66 Cost: 7
(2) INDEX INDEX RANGE SCAN (MIN/MAX) RDS.PIX_FTLE_1 [Analyzed]
Est. Rows: 66 Cost: 7
Is this a problem with b-tree indexes? The Life_Id is allocated from a sequence and there are 67,535,682 rows in the partition. The stats are up to date.
=====================================================================================================
=====================================================================================================
Hello Randolf,
Sorry for the late reply was down with flue and could not mail , as mentioned i below is the DDL for PIX_FTLE_1 & PIX_FTLE_2 . I have used TOAD to extract the script.
As i cant send the both the DDL in single post i will send it in 2 post , There is a limit of 30000 characters in sigle post .
Thanks once again for your help .
Regards
Harshad
CREATE INDEX RDS.PIX_FTLE_1 ON RDS.FACT_LIFE
(FEED_ID, CAP_PERIOD_ID, LIFE_ID, ISO_CURRENCY_ID, LIFE_SCD_ID)
TABLESPACE RDS_TABLESPACE01
INITRANS 2
MAXTRANS 255
LOGGING
GLOBAL PARTITION BY RANGE (FEED_ID) (
PARTITION HA1 VALUES LESS THAN (2)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1225M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION NU6 VALUES LESS THAN (3)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 2761M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION FP1 VALUES LESS THAN (4)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 971M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION PRU VALUES LESS THAN (5)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 993M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION GAC VALUES LESS THAN (6)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 622M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION NU3 VALUES LESS THAN (7)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 2136M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION LEG VALUES LESS THAN (8)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 847M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION RSC VALUES LESS THAN (9)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 144M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION SW1 VALUES LESS THAN (10)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 80M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION PRI VALUES LESS THAN (11)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 34M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION BAL VALUES LESS THAN (12)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 128M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION NA1 VALUES LESS THAN (13)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION MID VALUES LESS THAN (14)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION EGS VALUES LESS THAN (15)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION ARK VALUES LESS THAN (16)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 1088M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION SKA VALUES LESS THAN (17)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 136M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION NU1 VALUES LESS THAN (18)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 196M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION SK2 VALUES LESS THAN (19)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 9M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION CU1 VALUES LESS THAN (20)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 11M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION DLI VALUES LESS THAN (21)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION AEG VALUES LESS THAN (22)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION COM VALUES LESS THAN (23)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION CU2 VALUES LESS THAN (24)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION CUL VALUES LESS THAN (25)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION EAG VALUES LESS THAN (26)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION FP2 VALUES LESS THAN (27)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION HAG VALUES LESS THAN (28)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION HAL VALUES LESS THAN (29)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION JRA VALUES LESS THAN (30)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION LEL VALUES LESS THAN (31)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION MGA VALUES LESS THAN (32)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION MGM VALUES LESS THAN (33)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION NU2 VALUES LESS THAN (34)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION PEG VALUES LESS THAN (35)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION RLM VALUES LESS THAN (36)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION RL2 VALUES LESS THAN (37)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION RL3 VALUES LESS THAN (38)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION RL4 VALUES LESS THAN (39)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION RL5 VALUES LESS THAN (40)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION BG4 VALUES LESS THAN (41)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION BG5 VALUES LESS THAN (42)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION RS2 VALUES LESS THAN (43)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION RS3 VALUES LESS THAN (44)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION RS4 VALUES LESS THAN (45)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION SA1 VALUES LESS THAN (46)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION SAF VALUES LESS THAN (47)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION SCA VALUES LESS THAN (48)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION SL1 VALUES LESS THAN (49)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION SL2 VALUES LESS THAN (50)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION SPI VALUES LESS THAN (51)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION SWP VALUES LESS THAN (52)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION VDL VALUES LESS THAN (53)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 240M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION ZUR VALUES LESS THAN (54)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION AR3 VALUES LESS THAN (55)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 360M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
PARTITION ZZZ VALUES LESS THAN (MAXVALUE)
NOLOGGING
NOCOMPRESS
TABLESPACE RDS_TABLESPACE01
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
NOPARALLEL;
Related articles