Home » SQL & PL/SQL » SQL & PL/SQL » oracle tunning (oracle 10g, xp)
oracle tunning [message #570568] Mon, 12 November 2012 06:19 Go to next message
nirmal_oracle1978
Messages: 2
Registered: November 2012
Location: MUMBAI
Junior Member
In below query, there are million of record in a tables but order by 1 taking too much time. give me suggestion for fast retreiving for this query.


select * from (select a.*,rownum rnum from(select a.WORKITEMID,FCSS_tbRefId L_FCSS_tbRefId,FCSS_tbSrcSysIdUni L_FCSS_tbSrcSysIdUni,
FCSS_tbFCSSStatus L_FCSS_tbFCSSStatus,FCSS_tbOffice L_FCSS_tbOffice,FCSS_tbDrSBUId L_FCSS_tbDrSBUId,FCSS_tbDrBranchCode L_FCSS_tbDrBranchCode,
FCSS_tbDrAccNumber L_FCSS_tbDrAccNumber,dv.TASKID DEF_TASKID,dv.QUEUEID DEF_QUEUEID,dv.WORKITEMID DEF_WORKITEMID,dv.ITEMTYPEID DEF_ITEMTYPEID,
a.ACTION DEF_ACTION,a.MAKER_ID DEF_MAKERID,a.MAKER_DATE DEF_MAKERDATE,a.BSNS_DATE DEF_BSNSDATE,dv.STATUS AS DEF_STATUS from TB_TRANSACTION_DATA a,
OW_WORKITEM_INSTANCE dv WHERE a.WORKITEMID = dv.WORKITEMID AND dv.QUEUEID in(867,869,870,872,873,874,1191,1192,1193,1194,1195,1196,1197,1199,1200,
1201,1202,1203,1204,1205,1206,1207,1209,1210,1211,1212,1213,1214,1215,1217,1218,1219,1220,1221,1223,1224,1225,1226,1228,1229,1230,123 1,1233,1234,
1235,1236,1238,1240,1241,1242,1243,1244,1246,1247,1248,1250,1251,1252,1254,1255,1256,1257,1258,1259,1260,1262,1263,1264,1265,1266,126 7,1268,1269,
1270,1271,1272,1273,1274,1275,1276,1277,1278,1279,1282,1284,1285,1286,1287,1288,1289,1291,1292,1293,1294,1296,1297,1298,1300,1301,130 2,1303,1304,
1305,1306,1307,1376,1377,1402,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,1415,1416,1417,1418,1419,1420,1421,1422,142 3,1424,1425,
1426,1427,1428,1429,1430,1431,1432,1433,1434,1435,1436,1437,1438,1439,1440,1441,1442,1443,1444,1445) AND FCSS_tbSrcSysId = 'GPP'
union all
select
a.WORKITEMID,FCSS_tbRefId L_FCSS_tbRefId,FCSS_tbSrcSysIdUni L_FCSS_tbSrcSysIdUni,FCSS_tbFCSSStatus L_FCSS_tbFCSSStatus,FCSS_tbOffice
L_FCSS_tbOffice,FCSS_tbDrSBUId L_FCSS_tbDrSBUId,FCSS_tbDrBranchCode L_FCSS_tbDrBranchCode,FCSS_tbDrAccNumber L_FCSS_tbDrAccNumber,dv.TASKID
DEF_TASKID,dv.QUEUEID DEF_QUEUEID,dv.WORKITEMID DEF_WORKITEMID,dv.ITEMTYPEID DEF_ITEMTYPEID,a.ACTION DEF_ACTION,a.MAKER_ID DEF_MAKERID,
a.MAKER_DATE DEF_MAKERDATE,a.BSNS_DATE DEF_BSNSDATE,dv.STATUS AS DEF_STATUS from TB_TRANSACTION_HISTORY a, OW_WORKITEM_INSTANCE_HISTORY dv
WHERE a.WORKITEMID = dv.WORKITEMID AND dv.QUEUEID in(867,869,870,872,873,874,1402,1403,1404,1405,1406,1407,1408,1409,1410,1411,1412,1413,1414,
415,1416,1417,1418,1419,1420,1421,1422,1423,1424,1425,1426,1427,1428,1429,1430,1431,1432,1433,1434,1435,1436,1437,1438,1439,1440,1441 ,1442,1443,
1444,1445) AND FCSS_tbSrcSysId = 'GPP' order by 1) a where rownum <= 10) where rnum >= 1
Re: oracle tunning [message #570569 is a reply to message #570568] Mon, 12 November 2012 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

What you posted is unreadable.

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.

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

Regards
Michel


[Updated on: Mon, 12 November 2012 06:24]

Report message to a moderator

Re: oracle tunning [message #570574 is a reply to message #570568] Mon, 12 November 2012 07:11 Go to previous messageGo to next message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
As a gambling try: "order by taking too much" is sometimes a symptom of the optimizer_mode parameter being set to FIRST_ROWS. If this is the case consider modifying optimizer_mode to ALL_ROWS.
Re: oracle tunning [message #570681 is a reply to message #570574] Wed, 14 November 2012 03:45 Go to previous messageGo to next message
nirmal_oracle1978
Messages: 2
Registered: November 2012
Location: MUMBAI
Junior Member
I tried with modify the optimizer_mode but not any effect on the query, its again taking time.
give me more suggestions even query changes.

Thanks
Nirmal
Re: oracle tunning [message #570682 is a reply to message #570681] Wed, 14 November 2012 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 57612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 12 November 2012 13:23
Welcome to the forum.

What you posted is unreadable.

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.

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

Regards
Michel



Re: oracle tunning [message #570697 is a reply to message #570682] Wed, 14 November 2012 07:31 Go to previous message
mnitu
Messages: 140
Registered: February 2008
Location: Reims
Senior Member
You can't optimize something that you don't understand. In order to understand you have to collect and analyze additional information. So please follow the link indicated by Michel.
Previous Topic: Assistance with querying large table
Next Topic: Guide me in writing Case Statement
Goto Forum:
  


Current Time: Sat Apr 19 20:00:29 CDT 2014

Total time taken to generate the page: 0.62253 seconds