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

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???? :rolleyes:

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. :rolleyes:

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

Quote:

---

Originally Posted by slimdave

Oh, you'd better be sure that you need that distinct as well

---

LIKE...LIKE...LIKE...LIKE...LIKE...perhaps they should scrap everything and start over. :rolleyes:

Step # Step Name
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]

You can add a hint (NO_USE_MERGE) to disable merge join cartesian, if your oracle release 10g.

The hint, USE_NO_MERGE is not available in 9i, so you can try USE_HASH or USE_NL hint to avoid merge join.