Bewarse Talk Discussion Board: <b>Oracle tuning SQL statements</b>

Bewarse Talk Discussion Board Archives-2010 Cine Talk - Reviews, Gossips, Insider Info etc. Archive through March 12, 2010 Oracle tuning SQL statements

< Previous Next >

Author

Message

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12882
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 3:23 pm:

Kirikiri:
also, temporarily, you can add another condition to exclude those records that have more than one.

Try playing. Its fun and this is how you will learn.

let us know if you are in a hurry. otherwise, spend time fixing it.

good luck.

ya...ade chosthunna mama..rendu enduku vasthunnayo chosthunna..ee company and costcenter ki rendu org id lu unna records unnay..aa orgs info antha same except the org id...oka column object version number okati 1 undi and inkokadaniki 3 undi..adokkate difference. may be aa column use chesukovachu anukunta nenu..kani anni company, costcenters ki ilage different object version numbers unnayo ledo chodali...aa rendu oke aame create chesindi..indake project manager vachindi..chosindi...idelagu ani sare aame ni adugutha ani vellindi mari.

Kirikiri
Pilla Bewarse
Username: Kirikiri

Post Number: 345
Registered: 09-2007
Posted From: 63.226.59.105

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 2:59 pm:

Ravanabrahma:
ok ardamaipoyindi le. ee employee numbers ki, two rows vasthunnayo mundu kanukkunta..avi < 10 unte vaatini thesesi process chestha..or vatiki enduku 2 rows vasthunnayo chosi danni fix chestha

also, temporarily, you can add another condition to exclude those records that have more than one.

Try playing. Its fun and this is how you will learn.

let us know if you are in a hurry. otherwise, spend time fixing it.

good luck.

NTR Amar Rahe... The ONE and The ONLY ONE...

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12881
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 2:32 pm:

ok ardamaipoyindi le. ee employee numbers ki, two rows vasthunnayo mundu kanukkunta..avi < 10 unte vaatini thesesi process chestha..or vatiki enduku 2 rows vasthunnayo chosi danni fix chestha

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12880
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 2:27 pm:

naku problem ardam ayyindi. edo oka record ki update ki oka column ki 2 values vasthunnay..appudu ee exception raise avuthundi. appudu aa exception lo ee error ni update chesthunna aa record ki..kani idi inka migilina records annitiki ee error message set chesi padesthundi inka processing aapesi...ela handle cheyyalabha idi

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12879
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 2:20 pm:

420:
Bemmi bedar , pump the SQL query here .. have u ever heard of

a) Analyzing statistics
b) Indexes
c) Parallel Execution
etc .. and for ur ORA error ..have you not used IN , ANY clause ?

update xxhr_i188_employee_stg es
set per_processed_flag = 'E'
,per_error_message = 'Multiple Employees with same employee number in the system'
where per_processed_flag = 'N'
and exists (
select nvl(ppf.employee_number,ppf.npw_number)
from per_all_people_f ppf
,per_assignments_f paf
where paf.person_id = ppf.person_id
and nvl(ppf.effective_end_date,sysdate) >= sysdate
and nvl(paf.effective_end_date,sysdate) >= sysdate
and nvl(ppf.employee_number,ppf.npw_number) = es.global_emp_id
group by nvl(ppf.employee_number,ppf.npw_number)
having count(*) > 1
)
and global_emp_id = nvl(p_employee_number,global_emp_id)
;

ee query slow ga run avuthundi...kani indaka cheppina error vere query ki vasthunnattu undi. chostha adi

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12878
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 2:19 pm:

Kirikiri:
are you trying to update in a query?
If you are, looks like, you have a join where some rows on LHS have more than one matching RHS rows (in layman terms)

Make sure you have exactly one row on RHS by either adding more conditions or by adding disticnt condition

oh..thanks mama...sare chostha idedoo...aa error ayina records lone oka 5 unchesi run chesthe error ledu...so edo konnitiki ee error vasthe idi chala records ni update chesthundi anukunta. sare chostha mama idedo.

420
Celebrity Bewarse
Username: 420

Post Number: 9362
Registered: 12-2006
Posted From: 85.150.252.173

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 1:16 pm:

Ravanabrahma:
'Pre-ValidationRA-01427: single-row subquery returns more than one row'

Bemmi bedar , pump the SQL query here .. have u ever heard of

a) Analyzing statistics
b) Indexes
c) Parallel Execution
etc .. and for ur ORA error ..have you not used IN , ANY clause ?

Knit India,do not split India !

Kirikiri
Pilla Bewarse
Username: Kirikiri

Post Number: 344
Registered: 09-2007
Posted From: 63.226.59.105

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 1:09 pm:

Ravanabrahma:
oka 4000 employee records unnay table lo. program run chesthe oka 2400 records ki column error message tho fill avuthundi....aa 2400 lo oka 5 records unchi anni delete chesi run chesthe bagane process avuthunnay records....ekkada problem chachindo

anni records ki ee message error ga vasthundi 'Pre-Validation:-ORA-01427: single-row subquery returns more than one row'

are you trying to update in a query?
If you are, looks like, you have a join where some rows on LHS have more than one matching RHS rows (in layman terms)

Make sure you have exactly one row on RHS by either adding more conditions or by adding disticnt condition

NTR Amar Rahe... The ONE and The ONLY ONE...

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12876
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 12:47 pm:

oka 4000 employee records unnay table lo. program run chesthe oka 2400 records ki column error message tho fill avuthundi....aa 2400 lo oka 5 records unchi anni delete chesi run chesthe bagane process avuthunnay records....ekkada problem chachindo

anni records ki ee message error ga vasthundi 'Pre-Validation :-O

RA-01427: single-row subquery returns more than one row'

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12875
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 12:42 pm:

Symbol_of_king:
teliayakapoyina parledu ...where clause loo vunna columns meeda indexes vunnayo ledoo toad lo chudu...lekapothe create chesi explain run chesi cost taguthondi emo chudu taggakapothe indexes ni drop cheseyi....

there is process to create on what and not on what...kani teliayakapoyina create chesi explain plan run chesthe cost taggithe avasaram lekapothe indeex avasram ledu

nenu ippudu pl/sql developer vaduthunna..toad kadu..ee costs, indexs concept ardam kaaledu mama....ee pl/sql developer lo koda undi explain plan..chostha aagu.

Symbol_of_king
Yavvanam Kaatesina Bewarse
Username: Symbol_of_king

Post Number: 1995
Registered: 12-2005
Posted From: 160.109.63.190

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 12:37 pm:

teliayakapoyina parledu ...where clause loo vunna columns meeda indexes vunnayo ledoo toad lo chudu...lekapothe create chesi explain run chesi cost taguthondi emo chudu taggakapothe indexes ni drop cheseyi....

there is process to create on what and not on what...kani teliayakapoyina create chesi explain plan run chesthe cost taggithe avasaram lekapothe indeex avasram ledu :-)

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12874
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 12:23 pm:

Symbol_of_king:
first statastics recent gaa update ayyayo ledoo chudu next explain run cheyyi...ekkuva time cost paduthundoo telusthundi..

taruvatha query nee rebuild cheyyadam kuduruthundi emo chudu.... lekapothe indexes chudu ...lekapothe hints emi ayina pettalaoo chudu

evi kakkundaa konni general concepts vuntayi like DWH lo aythe local indexes for partioned tables lekapothe global indexes...better to have materialized views etc etc

}

ee indexes etc theledu mama...chostha anni. nerchukovali elagaina.

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12873
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 12:22 pm:

Kirikiri:
Looks like it is a batch. So, for now, it is ok.

But, you definitely will have to learn qury performance tuning.

Best way to start is on internet. google and you will get a lot of info.

If you prefer books instead, you can start with:

http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Millsap/dp/059600527X/r ef=sr_1_8?ie=UTF8&s=books&qid=1268331233&sr=8-8

http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/007222729 X/ref=sr_1_7?ie=UTF8&s=books&qid=1268331233&sr=8-7

Let me know if you need more.

These books may have mode advanced info also that includes hardware setup etc... but, its a good start.

chala chala thanks mama..

Symbol_of_king
Yavvanam Kaatesina Bewarse
Username: Symbol_of_king

Post Number: 1994
Registered: 12-2005
Posted From: 160.109.63.190

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 12:17 pm:

first statastics recent gaa update ayyayo ledoo chudu next explain run cheyyi...ekkuva time cost paduthundoo telusthundi..

taruvatha query nee rebuild cheyyadam kuduruthundi emo chudu.... lekapothe indexes chudu ...lekapothe hints emi ayina pettalaoo chudu

evi kakkundaa konni general concepts vuntayi like DWH lo aythe local indexes for partioned tables lekapothe global indexes...better to have materialized views etc etc}

Kirikiri
Pilla Bewarse
Username: Kirikiri

Post Number: 343
Registered: 09-2007
Posted From: 63.226.59.105

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 12:16 pm:

Ravanabrahma:
ante ee program everyday morning 6 ki run ayyela set cheyyali once antha ayyaka...oka 4 mins pattinattundi motham complete avvataniki...naku ee indexs etc thelev mama..ekkada chadavali veeti gurunchi. manchi document unda nee daggara...avanni ardam chesukunta.

Looks like it is a batch. So, for now, it is ok.

But, you definitely will have to learn qury performance tuning.

Best way to start is on internet. google and you will get a lot of info.

If you prefer books instead, you can start with:

http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Millsap/dp/059600527X/r ef=sr_1_8?ie=UTF8&s=books&qid=1268331233&sr=8-8

http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/007222729 X/ref=sr_1_7?ie=UTF8&s=books&qid=1268331233&sr=8-7

Let me know if you need more.

These books may have mode advanced info also that includes hardware setup etc... but, its a good start.

NTR Amar Rahe... The ONE and The ONLY ONE...

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12871
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 11:38 am:

Onlyniru3:
bemmam maaya comedy baaga setunnav ga.. naaku neevu Oracle pdf's ampavvv tuning vi...

nenekkada pampanu. avi pl/sql vi ayi untay.

Onlyniru3
Yavvanam Kaatesina Bewarse
Username: Onlyniru3

Post Number: 2195
Registered: 08-2009
Posted From: 59.164.50.62

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 11:36 am:

Ravanabrahma:
ante ee program everyday morning 6 ki run ayyela set cheyyali once antha ayyaka...oka 4 mins pattinattundi motham complete avvataniki...naku ee indexs etc thelev mama..ekkada chadavali veeti gurunchi. manchi document unda nee daggara...avanni ardam chesukunta.

bemmam maaya comedy baaga setunnav ga.. naaku neevu Oracle pdf's ampavvv tuning vi...

http://i46.tinypic.com/2wbzul0.gif

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12869
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 11:34 am:

Kirikiri:
Online app or batch?

Batch ayite vaake.

Online ayite UNACCEPTABLE.

Did you check you have proper indexes?
Did you check if you have proper join statement?

ante ee program everyday morning 6 ki run ayyela set cheyyali once antha ayyaka...oka 4 mins pattinattundi motham complete avvataniki...naku ee indexs etc thelev mama..ekkada chadavali veeti gurunchi. manchi document unda nee daggara...avanni ardam chesukunta.

Kirikiri
Pilla Bewarse
Username: Kirikiri

Post Number: 342
Registered: 09-2007
Posted From: 63.226.59.105

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 11:31 am:

Ravanabrahma:

Online app or batch?

Batch ayite vaake.

Online ayite UNACCEPTABLE.

Did you check you have proper indexes?
Did you check if you have proper join statement?

NTR Amar Rahe... The ONE and The ONLY ONE...

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12868
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 11:30 am:

Fubar:
pills ga, clean and green job baane chestunaav

Fedex
Kurra Bewarse
Username: Fedex

Post Number: 975
Registered: 01-2010
Posted From: 151.151.16.8

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 11:28 am:

enti nitesh message egirindha

Fubar
Yavvanam Kaatesina Bewarse
Username: Fubar

Post Number: 1180
Registered: 11-2006
Posted From: 74.83.227.89

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 11:27 am:

pills ga, clean and green job baane chestunaav

B L A H

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12864
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 11:19 am:

evary cheyyaleda performance tuning..nayakesh ayiteh seppesevadu. emayyado endo :-(

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12863
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 11:12 am:

3 mins 13 seconds pattindi...ok naa aa time?

Ravanabrahma
Bewarse Legend
Username: Ravanabrahma

Post Number: 12862
Registered: 06-2004
Posted From: 169.143.0.103

Rating: N/A
Votes: 0 (Vote!)

Posted on Thursday, March 11, 2010 - 11:11 am:

oka program run chesthunte chala sepu paduthundi..idendra babu program lo thappu undi ani log statements petta..oka chota log display chestham aagipoyindi..aa next unna sql query ni separate ga run chesthe chala sepu run avutha undi..ippudu aa statement fast ga run avvataniki emi cheyyali..explain plan..oracle tuning untundi kada..adi ela? nannu interviews lo chala mandi adigaru idi.

Administration