| Author | Message | ||
     Ustad Side Hero Username: Ustad Post Number: 7840 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Nenu kuda oka sari kalisi ashirvadam tesukovali  Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Ustad Side Hero Username: Ustad Post Number: 7839 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Well said, he is the real oracle guru. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Airliner Junior Artist Username: Airliner Post Number: 311 Registered: 11-2011 Posted From: 136.223.19.65 Rating: N/A |
 keka candidate. open world or IOUG guruvu gari darsanam chesukovalsinde year ki oopalanna | ||
| Triggerblaster Comedian Username: Triggerblaster Post Number: 1936 Registered: 03-2008 Posted From: 140.98.210.243 Rating: N/A |
If oracle Ask tom for everything else ask God. | ||
| Ustad Side Hero Username: Ustad Post Number: 7838 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Will take a deep dive into AWR and ADDM reports. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Triggerblaster Comedian Username: Triggerblaster Post Number: 1935 Registered: 03-2008 Posted From: 140.98.210.243 Rating: N/A |
akkada problem reading kaadu inserting. When inserting server tires to index each record with huge data which is taking time. | ||
| Ustad Side Hero Username: Ustad Post Number: 7837 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Tom Vuncle rocks as usual. I am reading his book "Cost Based Fundamentals"...superb piece of work. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Entikaburlu Side Hero Username: Entikaburlu Post Number: 5954 Registered: 07-2011 Posted From: 67.247.83.224 Rating: N/A |
source has existing and non existing. when you left join existing get filtered out kadaa.. all opinions expressed are mine and only mine. not to be attributed to the other IP address sharers, my employer, or any other human, animal, robot or alien. | ||
| Airliner Junior Artist Username: Airliner Post Number: 310 Registered: 11-2011 Posted From: 136.223.19.65 Rating: N/A |
check disk IO performance, wait queue. EK garu cheppinattu physical data organization is key too. If you have index tablespace datafiles and actual table space datafiles on same disk there will be disk contention. Index read/write is the costliest I/O in database. | ||
| Sachin Megastar Username: Sachin Post Number: 27381 Registered: 04-2008 Posted From: 69.248.109.234 Rating: N/A |
left join sure naaa? annnai...monnoka left join ettaa sql chachipoyindi... asalu minus place lo left join ela vadatham...minus vaduthunadu ante he doesn't want to show few records... share ki gross ki thedaa theliyakapothenemi..daatar maaya box office disco lo iragadeesthaaadu... ki ki ki ... | ||
| Triggerblaster Comedian Username: Triggerblaster Post Number: 1934 Registered: 03-2008 Posted From: 140.98.210.243 Rating: N/A |
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUES TION_ID:1921387700346909109 | ||
| Triggerblaster Comedian Username: Triggerblaster Post Number: 1933 Registered: 03-2008 Posted From: 140.98.210.243 Rating: N/A |
Did you try append? | ||
| Ustad Side Hero Username: Ustad Post Number: 7836 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
I checked AWR and ADDM reports and CPU Time is the # 1 event. I think finding a way to bypass index (by dropping before insert) seems to be the quick fix for this problem. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Entikaburlu Side Hero Username: Entikaburlu Post Number: 5949 Registered: 07-2011 Posted From: 67.247.83.224 Rating: N/A |
are you using RAID5? where are the indexes and data stored? you have start looking at the physical organization of data now. all opinions expressed are mine and only mine. not to be attributed to the other IP address sharers, my employer, or any other human, animal, robot or alien. | ||
| Ustad Side Hero Username: Ustad Post Number: 7835 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Looks interesting, will try to find some more material and commands for RK index. Thanks! Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Ustad Side Hero Username: Ustad Post Number: 7834 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Already tried, parallel hint, append hint and nologging mode. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Entikaburlu Side Hero Username: Entikaburlu Post Number: 5947 Registered: 07-2011 Posted From: 67.247.83.224 Rating: N/A |
if you want try everything, try reverse key index http://www.dba-oracle.com/t_reverse_key_indexes_dml_insert.h tm all opinions expressed are mine and only mine. not to be attributed to the other IP address sharers, my employer, or any other human, animal, robot or alien. | ||
| Entikaburlu Side Hero Username: Entikaburlu Post Number: 5946 Registered: 07-2011 Posted From: 67.247.83.224 Rating: N/A |
try append hint.. all opinions expressed are mine and only mine. not to be attributed to the other IP address sharers, my employer, or any other human, animal, robot or alien. | ||
| Entikaburlu Side Hero Username: Entikaburlu Post Number: 5945 Registered: 07-2011 Posted From: 67.247.83.224 Rating: N/A |
Hmm.. is the index clustered? all opinions expressed are mine and only mine. not to be attributed to the other IP address sharers, my employer, or any other human, animal, robot or alien. | ||
| Ustad Side Hero Username: Ustad Post Number: 7832 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Yes. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Entikaburlu Side Hero Username: Entikaburlu Post Number: 5936 Registered: 07-2011 Posted From: 67.247.83.224 Rating: N/A |
exists badulu left join inka better. but he says bottlenexk is not select, it is in insert all opinions expressed are mine and only mine. not to be attributed to the other IP address sharers, my employer, or any other human, animal, robot or alien. | ||
| Sachin Megastar Username: Sachin Post Number: 27380 Registered: 04-2008 Posted From: 69.248.109.234 Rating: N/A |
already chalamandi cheppesinattunnaaaru... try bulk insert with commits... drop index and rebuild after load. minus badulu exists vaadithe better anukuntunnaaa share ki gross ki thedaa theliyakapothenemi..daatar maaya box office disco lo iragadeesthaaadu... ki ki ki ... | ||
| Entikaburlu Side Hero Username: Entikaburlu Post Number: 5928 Registered: 07-2011 Posted From: 67.247.83.224 Rating: N/A |
for kickers.. if you drop the index and insert, is insert flying? I think the problem is your records are not cleanly fitting into segments of your table space. it is either defragging or fragmenting all the time, or you do not have enough disk space. also check your temp space all opinions expressed are mine and only mine. not to be attributed to the other IP address sharers, my employer, or any other human, animal, robot or alien. | ||
| Entikaburlu Side Hero Username: Entikaburlu Post Number: 5927 Registered: 07-2011 Posted From: 67.247.83.224 Rating: N/A |
check if you insert only some columns first and then refresh other columns using update any better? insert all keys first and then update other columns using updates. see if any better.. all opinions expressed are mine and only mine. not to be attributed to the other IP address sharers, my employer, or any other human, animal, robot or alien. | ||
| Ustad Side Hero Username: Ustad Post Number: 7829 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
I narrowed down the problem to Insert being slow.
Only 1 index, i.e. PK index. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Entikaburlu Side Hero Username: Entikaburlu Post Number: 5925 Registered: 07-2011 Posted From: 67.247.83.224 Rating: N/A |
how are you doing the Minu function, using a left join or where exists? how is the Select performance without Insert? is it good? findout if the bottleneck is the select or insert. if the select is slow, change subquery for checking the existence of the row to use a left join. if insert itself is slow, you have to check your table spaces, and number of indexes. remove any duplicate indexes. check if you have any clusetered indexes. see if you can do away with the clustered indexes. all fails, think terms of writing a custom loader. all opinions expressed are mine and only mine. not to be attributed to the other IP address sharers, my employer, or any other human, animal, robot or alien. | ||
| Ustad Side Hero Username: Ustad Post Number: 7819 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Next week idi try chestanu. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Kaisersooze Moderator Username: Kaisersooze Post Number: 6021 Registered: 04-2009 Rating: N/A |
ok nenu repu call chestha vadiki | ||
| Just4fun Megastar Username: Just4fun Post Number: 25047 Registered: 10-2007 Posted From: 174.37.61.78 Rating: N/A |
Kaiser mama, aaditho matladi update cheyu ..12 mandi waiting | ||
| Kaisersooze Moderator Username: Kaisersooze Post Number: 6019 Registered: 04-2009 Rating: N/A |
iddhi kottu brother...faaat ani ayipothadhi...cursor loki bulk fetch chesi...insert cheyyi... | ||
| Ustad Side Hero Username: Ustad Post Number: 7818 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Good idea...worth trying. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Ustad Side Hero Username: Ustad Post Number: 7817 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
AWR report kooda choosanu...idi medium size query. Bulk collect option try cheyyaledu...adi okkati try cheyyali. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Ustad Side Hero Username: Ustad Post Number: 7816 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Yes, even ensured it by seeing multiple sessions in session browser. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Airliner Junior Artist Username: Airliner Post Number: 309 Registered: 11-2011 Posted From: 136.223.19.76 Rating: N/A |
How about direct path load using sql loader? | ||
| Kaisersooze Moderator Username: Kaisersooze Post Number: 6016 Registered: 04-2009 Rating: N/A |
explain plan run chesava? run chesi plan ela vundho chudu...and how big is the query? oke proc rasi update kottu.... | ||
| Airliner Junior Artist Username: Airliner Post Number: 308 Registered: 11-2011 Posted From: 136.223.19.76 Rating: N/A |
Did you enable parallel dml at system level before using the parallel hint? | ||
| Ustad Side Hero Username: Ustad Post Number: 7814 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Already have this as a backup option. If nothing else works then I will go with this solution. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Ustad Side Hero Username: Ustad Post Number: 7813 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
This data is driven by a date...every week we delete one week's worth of data and add one week. Therefore, number of records remain close to 250 million every week. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Ustad Side Hero Username: Ustad Post Number: 7812 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Stats are good...I think NO_INDEX will be helpful when you have issue with select but not insert into a table. BTW, I will give a shot, you never know. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Ustad Side Hero Username: Ustad Post Number: 7811 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
Already tried, not much change. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Ccdbrocks Junior Artist Username: Ccdbrocks Post Number: 122 Registered: 01-2012 Posted From: 76.173.223.196 Rating: N/A |
were stats up to date on those tables..?? also you may try no index hint | ||
| Goonda Hero Username: Goonda Post Number: 18627 Registered: 02-2007 Posted From: 199.82.243.103 Rating: N/A |
when ever you load the data, drop indexes and load.. this is common thing in load process of all DB's.. what they usually do is load the data to temp table with out any indexes and then rename the table to original table and run the statistics Sasibabu: If TDP loses next elechens, i will donate 10% of my salary to TDP Skywalker: Bala chiru type kadu.....narasimha swamy avataram etti posani gadi pegulu medalo esukuntadu | ||
| Maverick Legend Username: Maverick Post Number: 34756 Registered: 01-2008 Posted From: 184.150.236.67 Rating: N/A |
append with nologging should help Who is this DB member? | ||
| Maverick Legend Username: Maverick Post Number: 34754 Registered: 01-2008 Posted From: 184.150.236.67 Rating: N/A |
did u use append hint? Who is this DB member? | ||
| Gandhiguevara Megastar Username: Gandhiguevara Post Number: 24622 Registered: 10-2009 Posted From: 66.151.13.188 Rating: N/A |
 | ||
| Chitti_v2 Side Hero Username: Chitti_v2 Post Number: 3634 Registered: 01-2011 Posted From: 160.83.72.208 Rating: N/A |
nenu oracle exfert kaadu....naa fosts ni lite theesuko....curiosity tho aduguthunnaa...time unte reply ivvu ilaa insert chesukuntaa pothe table alaa increase aithe u have to partition it some time kadaa? what is ur enterprise standard for max records in an unpartitioned table? | ||
| Ustad Side Hero Username: Ustad Post Number: 7809 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
 Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Ustad Side Hero Username: Ustad Post Number: 7808 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
I already have this as a backup option. I am sure this is a very common scenario faced by DW people and DBAs. Want to know if there is some other technique or tuning methodology for this problem. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Ustad Side Hero Username: Ustad Post Number: 7807 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
No. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. | ||
| Maverick Legend Username: Maverick Post Number: 34752 Registered: 01-2008 Posted From: 184.150.236.67 Rating: N/A |
disable index during load and rebuild them later Who is this DB member? | ||
| Rajusk Hero Username: Rajusk Post Number: 17628 Registered: 02-2008 Posted From: 192.234.99.11 Rating: N/A |
problem antha ee PK undadam vallane...nenu jump.. sorry to divert the thread.. | ||
| Chitti_v2 Side Hero Username: Chitti_v2 Post Number: 3632 Registered: 01-2011 Posted From: 160.83.73.15 Rating: N/A |
partitions unnaayaa? | ||
| Ustad Side Hero Username: Ustad Post Number: 7804 Registered: 07-2008 Posted From: 65.161.188.11 Rating: N/A |
One of my sql script is taking 60 minutes to insert 4 million records into a 250 million record table. After going through AWR and other tuning exercises...I found that most of the time is spent on maintaining the index (PK index). Have you worked on a similar kindaa issue and Is there a hint of some technique I can use to make it run faster. pseudo code: insert into table A (select from table B and C minus (select from table A)); Table A has 250 million records. I already tried parallel hint but did not find much improvement. I have a workaround but it involves dropping index before inserting records which I want to keep as my last option. Fan of Fab 5: Sachin, Rahul, Saurav, Laxman and Kumble. |
