| Author |
Message |
    
Pda
Bewarse Legend
Username: Pda
Post Number: 25959
Registered: 06-2006
Posted From: 129.138.250.120
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 6:00 pm: |
|
13:82 ante sana ekkuva.. patha query secs lo run ayinappudu aa table meeda aa time lo enni vunnayo sootte clear ga telisi poddi aithe. Edanna snapshot vunte aa table inko pre-production gani test lo gani Development database lo gani check sette better |
Poppy
Pilla Bewarse
Username: Poppy
Post Number: 9
Registered: 03-2009
Posted From: 216.245.209.205
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:59 pm: |
|
   |
Nayakudu
Bewarse Legend
Username: Nayakudu
Post Number: 11445
Registered: 05-2005
Posted From: 138.32.32.166
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:57 pm: |
|
peda baa fact_raw_vol_subdailt undi kada daniki chained cnt 13 million undi.. danemma
hasta la victoria siempre
|
Pda
Bewarse Legend
Username: Pda
Post Number: 25958
Registered: 06-2006
Posted From: 129.138.250.120
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:52 pm: |
|
20k aithe perfect.. ee madhya run ayyi ippudu avvatam ledu ante .. data recent ga baga change ayyi chained rows lachallo vunnaya..  25 mins vs few seconds ante chained rows okkate reason ayyi vundaka povachu |
Nayakudu
Bewarse Legend
Username: Nayakudu
Post Number: 11444
Registered: 05-2005
Posted From: 138.32.32.166
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:48 pm: |
|
peda baa aa USE_HASH lo tables only 20K rows untayii baa anthey...
hasta la victoria siempre
|
Nayakudu
Bewarse Legend
Username: Nayakudu
Post Number: 11443
Registered: 05-2005
Posted From: 138.32.32.166
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:47 pm: |
|
aa select sub quesries bagane run avuthayi baa individual gaa run chesthey... kani motham query as a whole run chesthey 25 mins paduthundi.. anthaku mundu few secs lo run ayyedi
hasta la victoria siempre
|
Pda
Bewarse Legend
Username: Pda
Post Number: 25957
Registered: 06-2006
Posted From: 129.138.250.120
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:39 pm: |
|
USE_HASH ante inko table enta vundi size.. Hash joins ki okka table aina chinnadi ayyi vundaali .. redu pedda tables meeda hash chette vonguntadi query |
Nayakudu
Bewarse Legend
Username: Nayakudu
Post Number: 11442
Registered: 05-2005
Posted From: 138.32.32.166
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:35 pm: |
|
aa table lo 82 million rows untayii baa.. oka bit map index untadi oka active column meeda.. aa active column meeda query rasa... denemma individual queries fat ga run avuthunnayi kani...
query paste chesthaa undu. appatiki hints petta anthaku mundu problem vasthey... bagane runa yyindi malla vongundi'
UPDATE /*+ INDEX(FACT_RAW_VOL_SUBDAILY PK_SUBDAILY_IDX) */ FACT_RAW_VOL_SUBDAILY
SET CURRENT_FLAG = '0'
WHERE
(CURRENT_FLAG = '1') AND
(METER_NUM, SOURCE_NAME, LCT) IN (
SELECT /*+ USE_HASH(A B) */ A.METER_NUM, A.SOURCE_NAME, A.LCT FROM
(SELECT /*+ INDEX(FACT_RAW_VOL_SUBDAILY IDX_FACT_ADJ_RVSD_CURRFLAG) */
FACT_RAW_VOL_SUBDAILY.SOURCE_NAME AS SOURCE_NAME, FACT_RAW_VOL_SUBDAILY.METER_NUM AS METER_NUM,
FACT_RAW_VOL_SUBDAILY.LCT AS LCT
FROM
FACT_RAW_VOL_SUBDAILY
WHERE
(FACT_RAW_VOL_SUBDAILY.CURRENT_FLAG = '1')) A,
(SELECT STG_SCADA_SUBDAILY_VOLUMES.SOURCE_NAME AS SOURCE_NAME,
TO_CHAR(STG_SCADA_SUBDAILY_VOLUMES.METER_NUM) AS METER_NUM,
STG_SCADA_SUBDAILY_VOLUMES.LCT AS LCT
FROM
STG_SCADA_SUBDAILY_VOLUMES
WHERE
STG_SCADA_SUBDAILY_VOLUMES.LOAD_DATE_TIME > (SELECT
TO_DATE(run_param_value, 'MM/DD/YYYY HH:MI:SS AM')
FROM
STG_INTERFACE_CTL
WHERE
interface_name = 'SUBDAILY_VOLUMES' AND
run_param_name = 'LAST_REFRESH_DATE')
AND STG_SCADA_SUBDAILY_VOLUMES.METER_NUM IN
(SELECT meter_num FROM d_org_hier)) B
WHERE A.METER_NUM = B.METER_NUM AND
A.LCT < B.LCT);
hasta la victoria siempre
|
Racchamass
Bewarse Legend
Username: Racchamass
Post Number: 17221
Registered: 01-2005
Posted From: 76.182.87.40
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:35 pm: |
|
nenu sesina application lo oka table 11GB unde summary table danni query sette 
People dont care how much you know until they know how much you care
|
Pda
Bewarse Legend
Username: Pda
Post Number: 25956
Registered: 06-2006
Posted From: 129.138.250.120
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:33 pm: |
|
9 GB aa okka table bongulodi .. naaku telavadi may be few hours. Index lu vunte inka ekkuva pattuddi.
Drop Index, migrate table, rebuild index for 9 GB table hours aithe guarantary |
Nayakudu
Bewarse Legend
Username: Nayakudu
Post Number: 11441
Registered: 05-2005
Posted From: 138.32.32.166
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:32 pm: |
|
kani ameki thelvadu yentha time paduthundooo offline thesthey.. table size vachhi 9 GIG undi baa.. so re org cheyyaniki yentha time paduthadi.. online lo kuda cheyochu anta kaka pothhey DB slow ga untadi annadi...
hasta la victoria siempre
|
Racchamass
Bewarse Legend
Username: Racchamass
Post Number: 17219
Registered: 01-2005
Posted From: 76.182.87.40
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:32 pm: |
|
People dont care how much you know until they know how much you care
|
Pda
Bewarse Legend
Username: Pda
Post Number: 25955
Registered: 06-2006
Posted From: 129.138.250.120
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:30 pm: |
|
//kaka pothey daniki yedo tablespace ni offline cheyyali re org cheyyali antundi.
mari offline chese cheyyali idi..yedo oka maintenance window vuntadi confirm ga appudu chettadi emo.. |
Pda
Bewarse Legend
Username: Pda
Post Number: 25954
Registered: 06-2006
Posted From: 129.138.250.120
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:29 pm: |
|
naaku artham ayyindi entante general ga OLTP one db block (8kb) = 2x OS block (4kb) = 1 disk read.
chained rows valla konchem data inko data block lo vuntundi.. so for chained rows you have to read 2 db blocks i.e 2 disk reads instead of one. performance Dgi poddi.. So query slow ga run avuddi .. so table ni reorganize sette performance peruguddi annamata |
Nayakudu
Bewarse Legend
Username: Nayakudu
Post Number: 11440
Registered: 05-2005
Posted From: 138.32.32.166
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:27 pm: |
|
aa ade cheppindi DBA pctfree ni increase chestha 10 undi ipudu 20 chestha ni.. kaka pothey daniki yedo tablespace ni offline cheyyali re org cheyyali antundi.. naa jobs yemo 24*7 run avutha untayiii.. yeto antha golaa
hasta la victoria siempre
|
Pda
Bewarse Legend
Username: Pda
Post Number: 25953
Registered: 06-2006
Posted From: 129.138.250.120
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:26 pm: |
|
chained rows ante aa row data extent lo kotha value saripokapothe inko extent lo load chesi ref. link pettuddi anta.. vatini avoid cheyyali ante data extent parameters change chesi aa table ni inko segment loki move cheyyali so that entire data would fit in same data extent.. aa link lo fix kooda ichadu gaa ..
ALTER TABLE row_mig_chain_demo MOVE
PCTFREE 20
PCTUSED 40
STORAGE (INITIAL 20K
NEXT 40K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 0);
idi dba ne chettadu anukunta.. aa settings values figure out cheyatam konchem kattame test chesukoni appudu prod lo pettali |
Nayakudu
Bewarse Legend
Username: Nayakudu
Post Number: 11437
Registered: 05-2005
Posted From: 138.32.32.166
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:20 pm: |
|
re org cheyyali antundi table nii yemitoo antha kalagapulagam laa undi
hasta la victoria siempre
|
Pda
Bewarse Legend
Username: Pda
Post Number: 25952
Registered: 06-2006
Posted From: 129.138.250.120
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:13 pm: |
|
tanks for link.. nenu eppudu vinaledu .. saduvuta bore kottinapudu |
Nayakudu
Bewarse Legend
Username: Nayakudu
Post Number: 11435
Registered: 05-2005
Posted From: 138.32.32.166
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:10 pm: |
|
ee link pampindi konchem chudavamma query ekkuva time run avuthundio yenduku ante
http://www.akadia.com/services/ora_chained_rows.html
 denemma comedy sami... 5 kalla jump andi malla.. monnati daka bagane undi.. hatathugaa queries dingu dingu antunnayi
hasta la victoria siempre
|
Nayakudu
Bewarse Legend
Username: Nayakudu
Post Number: 11434
Registered: 05-2005
Posted From: 138.32.32.166
Rating: N/A
Votes: 0 (Vote!) | | Posted on Wednesday, March 11, 2009 - 5:07 pm: |
|
ee chain_cnt ki siginificance yendi table lo.. DBA burra thinnadi...
hasta la victoria siempre
|
Archives - 2009