Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2005
    Posts
    39

    Unanswered: Performance Problem

    Hello All,

    The below mentioned query is a part of one trigger. When i execute this query directly, it is is taking fraction of seconds, but when I am trying to save the data through oracle forms, which in turn invokes triggers and then this query, then it is
    taking time.

    I am putting trace on the session in which I am trying to save data
    through forms and then with the help of tkprof I see that this query
    is taking time.


    SELECT A.FPL_ID,A.PZUG_ID,A.PZV_KEY, A.EINBRUCHZEIT,A.STRECKE_ID,
    A.BST_REIHENFOLGE,COUNT(*),B.ZN FROM
    PV_BSTKETTE A,PPL_ZUG B WHERE (A.PZUG_ID = B.PZUG_ID ) GROUP BY A.FPL_ID, A.PZUG_ID,A.PZV_KEY, A.EINBRUCHZEIT,A.STRECKE_ID, A.BST_REIHENFOLGE,B.ZN HAVING COUNT(*) > 1


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.00 0.00 0 0 0 0
    Execute 19 0.00 0.00 0 0 0 0
    Fetch 19 9.74 27.13 10910 274360 1045 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 39 9.74 27.13 10910 274360 1045 0

    Misses in library cache during parse: 0
    Optimizer goal: CHOOSE
    Parsing user id: 90 (recursive depth: 1)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 FILTER
    1570350 SORT GROUP BY
    1570331 NESTED LOOPS
    120593 TABLE ACCESS FULL PPL_ZUG
    1570331 TABLE ACCESS BY INDEX ROWID PV_BSTKETTE
    1690905 INDEX RANGE SCAN (object id 42088)

    Index on PPL_ZUG :

    INDEX_NAME UNIQUENES COLUMN_NAME
    ------------------------------ --------- ------------------------------
    FK1_PZUG_I NONUNIQUE FPL_ID

    PZUG_PK UNIQUE PZUG_ID

    PZUG_UK UNIQUE FPL_ID
    UNIQUE ZN


    Index on PV_BSTKETTE :


    INDEX_NAME UNIQUENES COLUMN_NAME
    ------------------------------ --------- ------------------------------
    PZVK_BSF_FK_I NONUNIQUE STRECKE_ID
    NONUNIQUE BST_REIHENFOLGE

    PZVK_BST_FK_I NONUNIQUE BST_ID

    PZVK_PK UNIQUE PZVK_ID

    PZVK_PZV_FK_I NONUNIQUE PZUG_ID
    NONUNIQUE PZV_KEY

    Will appreciate any pointer to reduce the time.

    Thanks, Raj

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since the query run fast & the trigger runs slow, then the problem is in the trigger & you won't gain much by tweaking the SQL in this query.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Maybe if you tell us what you are trying to do, we can help you. Your query doesn't make any sense. You are not tying to anything in the trigger. Please paste in the trigger.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Is it a Forms trigger or database trigger?

Posting Permissions

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