DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: peformance tunning

  1. #1
    Join Date
    Jan 2003
    Posts
    141

    peformance tunning

    Hi,
    One of our developer is running a procedure ( which does a select and updates and inserts based on certain criteria). The insert is happening for every 10,000 records.Total records inserted will be 100000 in production which is taking 6 hrs.

    the same procedure takes 3 hrs in development. This development environment is refreshed with the copy of production with the daily hotbackup.

    In development it runs faster when compared to prod?? any ideas.

    Thanks,

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    do all the inserts in one go, use bulk loading

    but you need to trace the executions to see the differences between prod and test

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

  4. #4
    Join Date
    Jan 2003
    Posts
    141
    The same procedure is taking less time in development??

    Thanks

  5. #5
    Join Date
    Jan 2003
    Posts
    141
    My dbms_stats is running during the same time???

    Starts at 5.00AM and Finishes at 9.00 AM

    exec dbms_stats.gather_schema_stats(OWNNAME => 'SYSADM', CASCADE => TRUE);


    procedure had processed 80,000 rows (approx. 80,000 rows in 5.5 hours).
    as soon as my dbms_stats finishes
    a total of 146,000 rows (approx 66,000 rows in 45 minutes).
    Last edited by castlerock; 05-17-2006 at 12:45 PM.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you obviously missed the posts that said 'trace it'

  7. #7
    Join Date
    Jan 2003
    Posts
    141
    My dbms_stats is running during the same time???

    Starts at 5.00AM and Finishes at 9.00 AM

    exec dbms_stats.gather_schema_stats(OWNNAME => 'SYSADM', CASCADE => TRUE);


    thanks, will start the trace ...

  8. #8
    Join Date
    Jan 2003
    Posts
    141
    Now it took 2 Hrs to run in prod. The only difference now is the dbms_stats is not running in prod while executing.

    Is DBMS_STATS an issue ??

    Its dbms_stats take 5 hrs to finish. The same procedure used to run in 10 mins and instead of dbms_stats we used to have analyze


    Trace file output:
    ----------------

    OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 242 0.07 0.08 0 0 0 0
    Execute 326935 42.71 163.55 61778 7876 1046370 146287
    Fetch 200736 996.55 6247.29 5260835 151250052 0 182953
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 527913 1039.34 6410.93 5322613 151257928 1046370 329240

    Misses in library cache during parse: 14


    OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 32 0.00 0.00 0 0 0 0
    Execute 44 0.00 0.00 0 0 0 0
    Fetch 110 0.00 0.22 66 162 0 69
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 186 0.01 0.23 66 162 0 69

    Misses in library cache during parse: 9

    242 user SQL statements in session.
    32 internal SQL statements in session.
    274 SQL statements in session.
    0 statements EXPLAINed in this session.
    ********************************************************************************
    Trace file: sa_prod.trc
    Trace file compatibility: 9.02.00
    Sort options: default

    0 session in tracefile.
    242 user SQL statements in trace file.
    32 internal SQL statements in trace file.
    274 SQL statements in trace file.
    64 unique SQL statements in trace file.
    531344 lines in trace file.

  9. #9
    Join Date
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Dude, WTF???

    Hmmmm, let me see I've got a batch of 1mil records that I have to load up. I wonder if I should run STATS at the same time?

    Somebody git me a stick....
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

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