-
Help needed to make oracle retrival faster....
Hello all,
Iam involved in a project which is been developed using J2EE and Oracle 9i as our database...
I got 2 servers ,Test and Production server.
The test server is a normal pc with low configuration which has only 1gb memory with single processor and my prodution server is a IBM machine with multiprocessor and 2 gb or memory..
My problem is when I was trying to retrive data in GUI its taking loats of time and its not populating all the information ,but when i deleted some of the data in Oracle , the retrival is quiet fast...
Can someone in this group suggest me some tips to make the oracle retrival
faster???(Can i do database tunning in oracle??)
If anyone is expert in tunning pls give me some tips to solve this problem...
Regards
Jack
-
Without giving any details no could solve your problem..
In general GUI takes time when comparing to command mode.
-
Hi
This sounds like "My car is broken. Please tell me how to proceed"
U should try first to investigate what's wrong and to have at least a clue what's going on. Then someone here could help
Regards
-
Ok guys what details you want ????
do u want my h/w configuration or my oracle configuration??
pls tell me what actually are you looking for???
-
Info Needed...
- Involved tables and indexes
- Offending query
What you want to do...
- Check if you have up-to-date perf stats
- Check Explain Plan for your query
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
here is my explain plan of my query,.............
Target:
CS22DB
Version: Oracle 9.2.0.1.0
Database: CS22DB
Schema: CS22USER
Date: 04-May-07 7:00:00 AM
SQL Statement:
SELECT DISTINCT ph.k_po_num, pd.supp_id supp_id, ph.po_status_flg po_status_flg,
cs_udf.cs_getdatefromjulian(ph.created_dt) created_dt,
ph.source_doc_type
FROM po_headers ph, po_details pd, item_dcs id
WHERE cs_udf.cs_trim(pd.item_id) = cs_udf.cs_trim(id.k_item_id)
AND pd.dc_id = id.k_dc_id
AND pd.k_comp_id = id.k_comp_id
AND id.owner_id IN (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12,
:13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26,
:27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40,
:41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54,
:55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68,
:69, :70, :71, :72, :73, :74, :75, :76, :77, :78, :79, :80, :81, :82,
:83, :84, :85, :86, :87, :88, :89, :90, :91, :92, :93, :94, :95, :96,
:97, :98, :99, :100, :101, :102, :103, :104, :105, :106, :107, :108,
:109, :110, :111, :112, :113, :114, :115, :116, :117, :118, :119,
:120, :121, :122, :123, :124, :125, :126, :127, :128, :129, :130,
:131, :132, :133, :134, :135, :136, :137, :138, :139, :140, :141,
:142, :143, :144, :145, :146, :147, :148, :149, :150, :151, :152,
:153, :154, :155, :156, :157, :158, :159, :160, :161, :162, :163,
:164, :165, :166, :167, :168, :169, :170, :171, :172, :173, :174,
:175, :176, :177, :178, :179, :180, :181, :182, :183, :184, :185,
:186, :187, :188, :189, :190, :191, :192, :193, :194, :195, :196,
:197, :198, :199, :200, :201, :202, :203, :204, :205, :206, :207,
:208, :209, :210, :211, :212, :213, :214, :215, :216, :217, :218,
:219, :220, :221, :222, :223, :224, :225, :226, :227, :228, :229,
:230, :231, :232, :233, :234, :235, :236, :237, :238, :239, :240,
:241, :242, :243, :244, :245, :246, :247, :248, :249, :250, :251,
:252, :253, :254, :255, :256, :257, :258, :259, :260)
AND pd.supp_id LIKE :261
AND ph.owner_id <> :262
AND id.active_flg = 'A'
AND ph.k_po_num = pd.k_po_num
AND ph.k_comp_id = pd.k_comp_id
AND ph.k_comp_id = :263
AND ph.active_flg = 'A'
AND pd.active_flg IN ('A', 'I')
AND ph.k_po_num LIKE :264
AND pd.item_id BETWEEN cs_udf.cs_nvlc(:265, pd.item_id) AND
cs_udf.cs_nvlc(:266, pd.item_id)
AND ph.created_by LIKE :267
AND pd.dc_id BETWEEN cs_udf.cs_nvlc(:268, pd.dc_id) AND
cs_udf.cs_nvlc(:269, pd.dc_id)
AND pd.eta_dt BETWEEN cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(
cs_udf.cs_to_date(:270, 'YYYYMMDD')), pd.eta_dt) AND
cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(cs_udf.cs_to_date(:271,
'YYYYMMDD')), pd.eta_dt)
AND ph.po_stage_flg LIKE :272
AND ph.po_status_flg LIKE :273
AND ph.created_dt BETWEEN cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(
cs_udf.cs_to_date(:274, 'YYYYMMDD')), ph.created_dt) AND
cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(cs_udf.cs_to_date(:275,
'YYYYMMDD')), ph.created_dt)
AND ph.close_dt BETWEEN cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(
cs_udf.cs_to_date(:276, 'YYYYMMDD')), ph.close_dt) AND
cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(cs_udf.cs_to_date(:277,
'YYYYMMDD')), ph.close_dt)
Optimizer Mode Used:
COST ALL ROWS (optimizer: CHOOSE)
Total Cost:
93
Execution Steps:
Step # Step Name
10 SELECT STATEMENT
9 SORT [UNIQUE]
8 CS22USER.PO_DETAILS TABLE ACCESS [BY INDEX ROWID]
7 NESTED LOOPS
5 MERGE JOIN [CARTESIAN]
2 CS22USER.PO_HEADERS TABLE ACCESS [BY INDEX ROWID]
1 CS22USER.PK_PO_HEADERS INDEX [RANGE SCAN]
4 BUFFER [SORT]
3 CS22USER.ITEM_DCS TABLE ACCESS [FULL]
6 CS22USER.PK_PO_DETAILS INDEX [RANGE SCAN]
Step # Description Est. Cost Est. Rows Returned Est. KBytes Returned
1 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index PK_PO_HEADERS. 2 10 --
2 This plan step retrieves rows from table PO_HEADERS through ROWID(s) returned by an index. 10 1 0.052
3 This plan step retrieves all rows from table ITEM_DCS. 79 15,333 449.209
4 This plan step sorts the buffer row source. 79 15,333 449.209
5 This plan step accepts two sets of rows and builds the set of all possible combinations of row pairs. The result set grows exponentially with the size of the row sets joined. 89 1 0.081
6 This plan step retrieves one or more ROWIDs in ascending order by scanning the B*-tree index PK_PO_DETAILS. 1 1 --
7 This plan step joins two sets of rows by iterating over the driving, or outer, row set (the first child of the join) and, for each row, carrying out the steps of the inner row set (the second child). Corresponding pairs of rows are tested against the join condition specified in the query's WHERE clause. 91 1 0.137
8 This plan step retrieves rows from table PO_DETAILS through ROWID(s) returned by an index. 2 1 0.056
9 This plan step accepts a row set (its only child) and sorts it in order to identify and eliminate duplicates. 93 1 0.137
10 This plan step designates this statement as a SELECT statement. 93 -- --
-
you should probably get rid of certesian join - nasty
-
Code:
... cs_udf.cs_trim(pd.item_id) = cs_udf.cs_trim(id.k_item_id)
...
Do whatever you can to reduce your use of PL/SQL functions, especially if you're going to apply them to values in the table like this.
-
Who wrote your schema, MC.Escher????
The "AND owner_id IN" might work better with a temporary table. You also mave way too many LIKE AND BETWEEN clauses. That this query even runs is a testament to the power of a modern database. My sugestion, total rewrite of the schema.
Code:
SELECT DISTINCT ph.k_po_num,
pd.supp_id supp_id,
ph.po_status_flg po_status_flg,
cs_udf.cs_getdatefromjulian(ph.created_dt) created_dt,
ph.source_doc_type
FROM
( SELECT k_po_num, k_comp_id, po_status_flg, created_dt, source_doc_type
FROM po_headers
WHERE k_comp_id = :263
AND active_flg = 'A'
AND k_po_num LIKE :264
AND ph.created_by LIKE :267
AND ph.po_stage_flg LIKE :272
AND ph.po_status_flg LIKE :273
AND ph.created_dt BETWEEN
cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(
cs_udf.cs_to_date(:274, 'YYYYMMDD')),
ph.created_dt)
AND cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(
cs_udf.cs_to_date(:275, 'YYYYMMDD')),
ph.created_dt )
AND ph.close_dt BETWEEN
cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(
cs_udf.cs_to_date(:276, 'YYYYMMDD')),
ph.close_dt)
AND cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(
cs_udf.cs_to_date(:277, 'YYYYMMDD')),
ph.close_dt)
) ph
INNER JOIN
( SELECT
FROM po_details
WHERE pd.active_flg IN ('A', 'I')
AND ph.owner_id <> :262
AND pd.supp_id LIKE :261
AND pd.item_id BETWEEN
cs_udf.cs_nvlc(:265, pd.item_id)
AND cs_udf.cs_nvlc(:266, pd.item_id)
) pd
ON ph.k_po_num = pd.k_po_num
AND ph.k_comp_id = pd.k_comp_id
AND pd.dc_id BETWEEN
cs_udf.cs_nvlc(:268, pd.dc_id)
AND cs_udf.cs_nvlc(:269, pd.dc_id)
AND pd.eta_dt BETWEEN
cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(
cs_udf.cs_to_date(:270, 'YYYYMMDD')), pd.eta_dt)
AND cs_udf.cs_nvln(cs_udf.cs_setdateinjulian(
cs_udf.cs_to_date(:271,'YYYYMMDD')), pd.eta_dt)
INNER JOIN
( SELECT
FROM item_dcs
WHERE active_flg = 'A'
AND owner_id IN
( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10,
:11, :12, :13, :14, :15, :16, :17, :18, :19, :20,
:21, :22, :23, :24, :25, :26, :27, :28, :29, :30,
:31, :32, :33, :34, :35, :36, :37, :38, :39, :40,
:41, :42, :43, :44, :45, :46, :47, :48, :49, :50,
:51, :52, :53, :54, :55, :56, :57, :58, :59, :60,
:61, :62, :63, :64, :65, :66, :67, :68, :69, :70,
:71, :72, :73, :74, :75, :76, :77, :78, :79, :80,
:81, :82, :83, :84, :85, :86, :87, :88, :89, :90,
:91, :92, :93, :94, :95, :96, :97, :98, :99, :100,
:101, :102, :103, :104, :105, :106, :107, :108, :109, :110,
:111, :112, :113, :114, :115, :116, :117, :118, :119, :120,
:121, :122, :123, :124, :125, :126, :127, :128, :129, :130,
:131, :132, :133, :134, :135, :136, :137, :138, :139, :140,
:141, :142, :143, :144, :145, :146, :147, :148, :149, :150,
:151, :152, :153, :154, :155, :156, :157, :158, :159, :160,
:161, :162, :163, :164, :165, :166, :167, :168, :169, :170,
:171, :172, :173, :174, :175, :176, :177, :178, :179, :180,
:181, :182, :183, :184, :185, :186, :187, :188, :189, :190,
:191, :192, :193, :194, :195, :196, :197, :198, :199, :200,
:201, :202, :203, :204, :205, :206, :207, :208, :209, :210,
:211, :212, :213, :214, :215, :216, :217, :218, :219, :220,
:221, :222, :223, :224, :225, :226, :227, :228, :229, :230,
:231, :232, :233, :234, :235, :236, :237, :238, :239, :240,
:241, :242, :243, :244, :245, :246, :247, :248, :249, :250,
:251, :252, :253, :254, :255, :256, :257, :258, :259, :260)
) id
ON pd.dc_id = id.k_dc_id
AND pd.k_comp_id = id.k_comp_id
AND cs_udf.cs_trim(pd.item_id) = cs_udf.cs_trim(id.k_item_id);
-
Oh, you'd better be sure that you need that distinct as well
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|