Help needed to make oracle retrival faster....
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Help needed to make oracle retrival faster....

  1. #1
    Join Date
    May 2007
    Posts
    3

    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

  2. #2
    Join Date
    Jan 2007
    Posts
    231
    Without giving any details no could solve your problem..

    In general GUI takes time when comparing to command mode.

  3. #3
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  4. #4
    Join Date
    May 2007
    Posts
    3
    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???

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  6. #6
    Join Date
    May 2007
    Posts
    3
    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 -- --

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you should probably get rid of certesian join - nasty

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    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);
    this space intentionally left blank

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Oh, you'd better be sure that you need that distinct as well
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width