Home » RDBMS Server » Performance Tuning » Performance Tuning SQL Queries (Oracle 11i Queries)
Performance Tuning SQL Queries [message #516598] Tue, 19 July 2011 08:22 Go to next message
petersdj
Messages: 1
Registered: July 2011
Junior Member

Hello:

I am having issues performance tuning sql scripts that I am creating. I want to know how to add indexing and any other methods to better execute my scripts. I think my biggest problem is big tables and full table scans. Please advise if you can advise how i can apply coding to my existing sql statements. Below is an example of a query i created that take long execution times..




SELECT
o.attribute5 "Theater",
o.attribute4 "CUIC" ,
o.name "Contract Operating Unit",
h.SHORT_DESCRIPTION "Short Description",
okh.contract_number "Contract Number",
okh.contract_number_modifier "Contract Number Modifier",
okh.start_date "Contract Start Date",
okh.end_date "Contract End Date",
okh.currency_code "Currency",
okh.estimated_amount "Total Amount",
okh.USER_ESTIMATED_AMOUNT "Comments",
okh.attribute13 "Auto Consolidate",
H.DESCRIPTION "Contract Instructions",
h.comments "Conbil Text",
h.cognomen "User",
hv.STATUS "Status Code",
hv.PARTY_NUMBER "Party Number",
hv.PARTY_NAME,
BT.SITE_USE_CODE,
BT.LOCATION "BT Location",
ST.SITE_USE_CODE,
ST.LOCATION "IA Location Header",
hps.PARTY_SITE_NUMBER "IA Location Line",
OKL.STS_CODE "Line Status",
okl.line_number line,
oksl.line_number subline,
to_char(okl.line_number)||'.'||to_char(oksl.line_number)"Subline",
OKSL.STS_CODE "Subline Status",
d.ETS_COST_CENTER,
o2.name "IB Country Org Unit",
oki.object1_id1 product_number,
cii.SERIAL_NUMBER "Unisys Serial Number",
cii.EXTERNAL_REFERENCE "Vendor Serial Number",
cii.INSTANCE_TYPE_CODE "Product Type",
cii.ACCOUNTING_CLASS_CODE "Accounting Classification",
cii.INSTANCE_USAGE_CODE,
cii.LOT_NUMBER "Lot Number",
cii.ATTRIBUTE2 "Coverage Status/Unisys Status",
DECODE(cii.ATTRIBUTE2,'01','MA - Maintenance'
,'02','LE - Lease'
,'03','WU - Warranty Upgrade'
,'04','IN/UE - Internal Use Equipment'
,'05','RE - Repair'
,'06','OP - Open'
,'07','DC - Data Center'
,'08','LN - Loan'
,'09','WA - Warranty'
,'10','DE - Delete',cii.ATTRIBUTE2) "Unisys Status Description",

cii.ATTRIBUTE3 "Unisys Usage",
cii.ATTRIBUTE5 "IB Load Identifier",
cii.ATTRIBUTE8 "Conversion Control",
cii.ATTRIBUTE9 "Conversion Reference",
cii.ATTRIBUTE7 "SW Product Type",
cii.ATTRIBUTE11 "SW Distr Type",
cii.ATTRIBUTE12 "URC",
cii.ATTRIBUTE14 "SCN",
msi.segment1 STYLE,
msi.DESCRIPTION,
cii.INSTALL_DATE "Installation Date",
oksl.START_DATE "Item Start Date",
oksl.END_DATE "Item End Date",
cii.QUANTITY,
cii.UNIT_OF_MEASURE,
oksl.PRICE_UNIT,
oksl.PRICE_NEGOTIATED,
cv.name "Line Name",
cv.description "Line Coverage Description"


from
apps.okc_k_headers_b okh,
APPS.OKC_K_HEADERS_V H,
apps.okc_k_lines_V okl,
apps.okc_k_lines_V oksl,
apps.okc_k_items oki,
apps.csi_item_instances cii,
apps.hz_party_sites hps,
apps.mtl_system_items_b msi,
apps.hr_all_organization_units o,
apps.hr_all_organization_units o2,
apps.oks_headers_v hv,
apps.oks_ent_headers_v e,
APPS.RA_SITE_USES_MORG ST,
APPS.RA_SITE_USES_MORG BT,
apps.oks_coverages_v cv,
APPS.OKC_K_LINES_B_DFV D,
APPS.OKC_K_HEADERS_V H2

WHERE 1=1 --oki.cle_id = okl.id
and cii.location_id = hps.party_site_id
and cii.last_vld_organization_id = msi.organization_id
and msi.inventory_item_id = cii.inventory_item_id
and cii.instance_id = oki.object1_id1
and oki.cle_id = oksl.id
and oksl.cle_id = okl.id
and oksl.dnz_chr_id = okl.chr_id
and okl.chr_id = okh.id
--and hv.PARTY_NUMBER = 'HX0001530516'
and okh.contract_number = '29933'
--and (msi.segment1 like 'NXP%' or msi.segment1 like 'CSP1%' or msi.segment1 like 'NX 10%' or msi.segment1 like 'CS 10%')
--and (cii.SERIAL_NUMBER in ('93081701386','93083701461','93083701463','93084700528','93084700530','93084700532','93091800228','93091800376','7H041101424','9N020 201577','9N020201575','7H041101528','7H041101142','7H030301970','93063801180','93064000478','7H050700698','7H050600180','7H054600434' ,'7H042800594','7H031401100','7H031401076','93083600262','93083500176','93083400010','93084500374','93084500370','93063100572','93073 100592','7H033702280','93062900268','93082700367','93062900266','93062800274','93062800270','7H034100042','7H033901813','7H043100404' ,'7H043100402','7H030601170','7H030601168','93062800276','9N023200654','9N020300761','93063800950','9N025102458','93084200132','93071 600830','93073400432','93073400394','7H054800080','93080400904','93074700268','93073801172','93073801168','93074301437','93074301417' ,'93073300024','93073300022','93072101588','93072101586','93072101576','93072101572','7H043500642','7H043301170','7H052100992','7H052 100982','7H052100974','7H051900863','7H051900855','7H051101498','7H051101496','7H051101494','7H051101490','7H051101488','7H051101484' ,'7H051101482','7H051101478','7H051101476','7H051101474','7H051101470','7H043701016','7H043201389','7H043200102','7H043100850','7H043 100846','7H043001081','7H043001079','7H043001075','7H043001071','7H043001069','7H043001067','7H043001065','7H043001061','7H043001059' ,'7H043001055','7H043001051','7H043000885','7H034101124','7H033700894','7H033001078','7H033001076','93082601396','93082601392','93082 601390','93082601386','93082501237','93072700270','93072700266','93071301392','93071301388','93072101214','93063600632','7H034101542' ,'7H032600864','93065100014','93074600396','93072501582','93072501540','93092800216','93062100322','93071000222','93071000220','93071 000200','93083000371','93083000272','9N024300906','93061600782','7H050100969','93082900531','93083400020','93083400038','93083400008' ,'93082000621','93082000619','93073100598','93091200338','93091200330','7H030501214','7H052000448','7H040200092','7H052000508','9N024 001098','93072501304','93072501302','93072501300','7H031900870','93082400562','93082400560','93062200982','93062200980','7H053500822' ,'7H053401360','7H053300142','7H053300136','7H042000438','7H042000416','93062100264','7H045001025','7H045001011','7H040501000','93062 000560','93062000562','93062201868','93062400746','93062400748','93061800856','93061900228','93061902286','93062000554','93062000558' ,'93062400998','93062401000','93062401024','93062401026','93062401030','93062400752','93062400866','93062400872','93062400878','93062 400980','7H040200818','7H040601464','7H041100822','7H041601226','7H041601276','7H032500460','93064400608','7H033401188','7H033701268' ,'7H033901270','7H050500990','9N023100512','9N024300158','9N024300398','7H033300424','7H031100730','7H031800860','7H031800864','7H032 601794','7H050100971','7H050101837','7H052100140','7H052700641','7H052900301','7H043200779','7H043301238','7H043600038','7H043701190' ,'93062400590','7H052200759','7H053201043','7H053801458','7H052401027','7H042700524','93061900292','93062400588','7H043100942','93063 601464','93063601466','93063601468','93063701370','93063701376','93062900772','93063101478','93063501386','93063501388','93063501392' ,'93064800648','93064800650','93064802756','93065000498','7H042700120','93063701382','93064701826','93064800616','93064800618','9N023 501263','7H050400868','7H050400866','7H050400864','7H044901429','7H044901335','7H044301342','7H043300962','7H045001575','7H052700629' ,'7H041900684','7H041900658','7H041501060','7H040500250','93074300846','93073600160','93071000330','93063001634','9N024201648','93061 600446','93091300096','93091300078','7H034301334','93071600010','93063801470','93072900502','93071800246','93081900037','7H045002527' ,'7H045002467','7H042600490','7H042600488','93071700402','93083000116','93082700345','7H052300738','7H051701234','93062301108','9N024 300436','9N024300428','7H052701370','7H053801448','7H052900615','7H052600810','93082900787','93082101193','93082101187','93082101185' ,'9N021701542','9N021701310','7H033802162','7H032301096','7H054001055','7H051301819','7H054500038','7H041801314','7H042600486','7H045 100295','7H042700508','7H053900644','7H041900140','93071300860','93071300854','93062501356','93083100536','93074900827','93073500325' ,'93073500323','9N020600004','93070600018','93062900554','93064904687','93063200068','93063000826','93062601168','9N025101276','9N025 101274','7H052600870','7H034801061','7H044901138','7H052600856','93065301632','7H034801061','7H041700822','7H041700820','7H044300958' ,'7H044100362','93062601080','7H042800826','7H041201722','7H050801350','7H040401308','7H041200726','93090900202','93062000637','7H052 900509','7H052900505','7H053300108','7H035102325','7H033400350','7H031800280','7H031800264','7H031700236','7H030300122','7H042101396' ,'7H033500468','7H033101224','7H031800270','7H030301978','93093000094','93092600442','93091400026','93091400032','7H054800906','7H054 800880','7H033600468','7H033500462','93072000804','7H043501120','7H043501114','7H043301242','93081100859','93081100853','93080300016' ,'93080300014','7H050200038','7H045200402','7H045101130','7H045101124','7H045100825','93071500182','93071500114','93072500230') OR
--cii.EXTERNAL_REFERENCE in ('93081701386','93083701461','93083701463','93084700528','93084700530','93084700532','93091800228','93091800376','7H041101424','9N020 201577','9N020201575','7H041101528','7H041101142','7H030301970','93063801180','93064000478','7H050700698','7H050600180','7H054600434' ,'7H042800594','7H031401100','7H031401076','93083600262','93083500176','93083400010','93084500374','93084500370','93063100572','93073 100592','7H033702280','93062900268','93082700367','93062900266','93062800274','93062800270','7H034100042','7H033901813','7H043100404' ,'7H043100402','7H030601170','7H030601168','93062800276','9N023200654','9N020300761','93063800950','9N025102458','93084200132','93071 600830','93073400432','93073400394','7H054800080','93080400904','93074700268','93073801172','93073801168','93074301437','93074301417' ,'93073300024','93073300022','93072101588','93072101586','93072101576','93072101572','7H043500642','7H043301170','7H052100992','7H052 100982','7H052100974','7H051900863','7H051900855','7H051101498','7H051101496','7H051101494','7H051101490','7H051101488','7H051101484' ,'7H051101482','7H051101478','7H051101476','7H051101474','7H051101470','7H043701016','7H043201389','7H043200102','7H043100850','7H043 100846','7H043001081','7H043001079','7H043001075','7H043001071','7H043001069','7H043001067','7H043001065','7H043001061','7H043001059' ,'7H043001055','7H043001051','7H043000885','7H034101124','7H033700894','7H033001078','7H033001076','93082601396','93082601392','93082 601390','93082601386','93082501237','93072700270','93072700266','93071301392','93071301388','93072101214','93063600632','7H034101542' ,'7H032600864','93065100014','93074600396','93072501582','93072501540','93092800216','93062100322','93071000222','93071000220','93071 000200','93083000371','93083000272','9N024300906','93061600782','7H050100969','93082900531','93083400020','93083400038','93083400008' ,'93082000621','93082000619','93073100598','93091200338','93091200330','7H030501214','7H052000448','7H040200092','7H052000508','9N024 001098','93072501304','93072501302','93072501300','7H031900870','93082400562','93082400560','93062200982','93062200980','7H053500822' ,'7H053401360','7H053300142','7H053300136','7H042000438','7H042000416','93062100264','7H045001025','7H045001011','7H040501000','93062 000560','93062000562','93062201868','93062400746','93062400748','93061800856','93061900228','93061902286','93062000554','93062000558' ,'93062400998','93062401000','93062401024','93062401026','93062401030','93062400752','93062400866','93062400872','93062400878','93062 400980','7H040200818','7H040601464','7H041100822','7H041601226','7H041601276','7H032500460','93064400608','7H033401188','7H033701268' ,'7H033901270','7H050500990','9N023100512','9N024300158','9N024300398','7H033300424','7H031100730','7H031800860','7H031800864','7H032 601794','7H050100971','7H050101837','7H052100140','7H052700641','7H052900301','7H043200779','7H043301238','7H043600038','7H043701190' ,'93062400590','7H052200759','7H053201043','7H053801458','7H052401027','7H042700524','93061900292','93062400588','7H043100942','93063 601464','93063601466','93063601468','93063701370','93063701376','93062900772','93063101478','93063501386','93063501388','93063501392' ,'93064800648','93064800650','93064802756','93065000498','7H042700120','93063701382','93064701826','93064800616','93064800618','9N023 501263','7H050400868','7H050400866','7H050400864','7H044901429','7H044901335','7H044301342','7H043300962','7H045001575','7H052700629' ,'7H041900684','7H041900658','7H041501060','7H040500250','93074300846','93073600160','93071000330','93063001634','9N024201648','93061 600446','93091300096','93091300078','7H034301334','93071600010','93063801470','93072900502','93071800246','93081900037','7H045002527' ,'7H045002467','7H042600490','7H042600488','93071700402','93083000116','93082700345','7H052300738','7H051701234','93062301108','9N024 300436','9N024300428','7H052701370','7H053801448','7H052900615','7H052600810','93082900787','93082101193','93082101187','93082101185' ,'9N021701542','9N021701310','7H033802162','7H032301096','7H054001055','7H051301819','7H054500038','7H041801314','7H042600486','7H045 100295','7H042700508','7H053900644','7H041900140','93071300860','93071300854','93062501356','93083100536','93074900827','93073500325' ,'93073500323','9N020600004','93070600018','93062900554','93064904687','93063200068','93063000826','93062601168','9N025101276','9N025 101274','7H052600870','7H034801061','7H044901138','7H052600856','93065301632','7H034801061','7H041700822','7H041700820','7H044300958' ,'7H044100362','93062601080','7H042800826','7H041201722','7H050801350','7H040401308','7H041200726','93090900202','93062000637','7H052 900509','7H052900505','7H053300108','7H035102325','7H033400350','7H031800280','7H031800264','7H031700236','7H030300122','7H042101396' ,'7H033500468','7H033101224','7H031800270','7H030301978','93093000094','93092600442','93091400026','93091400032','7H054800906','7H054 800880','7H033600468','7H033500462','93072000804','7H043501120','7H043501114','7H043301242','93081100859','93081100853','93080300016' ,'93080300014','7H050200038','7H045200402','7H045101130','7H045101124','7H045100825','93071500182','93071500114','93072500230'))
--and hv.STATUS in ('ACTIVE', 'SIGNED', 'ENTERED')
--and OKL.STS_CODE in ('ACTIVE', 'SIGNED', 'ENTERED')
--and OKSL.STS_CODE in ('ACTIVE', 'SIGNED', 'ENTERED')
and h.ID = okh.ID
and okh.id = e.contract_id
and okl.chr_id = e.contract_id
and e.ship_to_site_use_id = ST.site_use_id
and e.bill_to_site_use_id = BT.site_use_id
and okh.authoring_org_id = o.organization_id
and cii.last_vld_organization_id = o2.organization_id
and hv.contract_id = h.ID
and okl.id = cv.SERVICE_CLE_ID
AND oksl.ROW_ID = D.ROW_ID
and h2.id = h.id

group by

okh.contract_number,
OKH.ID,
oki.object1_id1 ,
cii.SERIAL_NUMBER,
cii.EXTERNAL_REFERENCE,
msi.segment1 ,
oksl.START_DATE,
oksl.END_DATE,
oksl.PRICE_NEGOTIATED,
OKL.STS_CODE,
OKSL.STS_CODE,
d.ETS_COST_CENTER,
okl.line_number ,
oksl.line_number ,
hps.party_site_number ,
o.attribute5,
o.attribute4,
o.name,
o2.name,
h.SHORT_DESCRIPTION,
okh.contract_number,
okh.contract_number_modifier,
okh.start_date,
okh.end_date,
okh.currency_code,
okh.estimated_amount,
okh.USER_ESTIMATED_AMOUNT,
okh.attribute13,
H.DESCRIPTION,
h.comments,
h.cognomen,
hv.STATUS,
hv.PARTY_NUMBER,
hv.PARTY_NAME,
ST.LOCATION,
ST.SITE_USE_CODE,
BT.LOCATION,
BT.SITE_USE_CODE,
cv.name,
cv.description,
oksl.PRICE_UNIT,
oksl.PRICE_NEGOTIATED,
cii.QUANTITY,
msi.DESCRIPTION,
cii.ATTRIBUTE2,
cii.ATTRIBUTE3,
cii.ATTRIBUTE8,
cii.LOT_NUMBER ,
cii.INSTALL_DATE ,
cii.ACCOUNTING_CLASS_CODE,
cii.ATTRIBUTE9,
cii.ATTRIBUTE12,
cii.INSTANCE_USAGE_CODE,
cii.UNIT_OF_MEASURE,
cii.ATTRIBUTE14,
cii.ATTRIBUTE5,
cii.INSTANCE_TYPE_CODE,
cii.ATTRIBUTE11,
cii.ATTRIBUTE7,
to_char(okl.line_number)||'.'||to_char(oksl.line_number)





Re: Performance Tuning SQL Queries [message #516600 is a reply to message #516598] Tue, 19 July 2011 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define "long".

For all performances question, read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Performance Tuning SQL Queries [message #516617 is a reply to message #516600] Tue, 19 July 2011 11:24 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And don't post a query with large chunks commented out, it makes it really hard to see what is and isn't being used.
Previous Topic: DBMS_UTILITY
Next Topic: Sql Query comsuming time
Goto Forum:
  


Current Time: Thu Mar 28 07:17:03 CDT 2024