DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Performane problem PROD versus PREPROD

  1. #1
    Join Date
    Jun 2001
    Posts
    32

    Performane problem PROD versus PREPROD

    ORACLE 9.2
    Solaris 2.8
    BLOCKS : 8k


    We have 2 databases, the production and the pre-pod.
    The pre-pod database is refreshed periodically with the data
    from prod using inport/export dumps.

    Lately, I have come across a performance problem on the production database.

    A Full scan of one particular table takes 32 minutes versus 9 minutes
    on the pre-prod , when the machine on the pre-prod is normally slower than
    the prod one.

    The prod table has more rows than the one on the pre-prod (144718411 versus 121815630).
    For me that should not account for the difference.

    I thought that maybie , I had chained rows on the production database.
    So, to prove my assumption, I ran those queries that I am listing with results both on the pre-prod
    and the prod :

    QUERY 1 :

    SQL> select a.name, b.value
    from v$statname a, v$mystat b
    where a.statistic#=b.statistic#
    and name like 'table%';

    PRE-PROD

    NAME VALUE
    ------------------------------------------------- ----------
    table scans (short tables) 5
    table scans (long tables) 0
    table scans (rowid ranges) 0
    table scans (cache partitions) 0
    table scans (direct read) 0
    table scan rows gotten 3
    table scan blocks gotten 3
    table fetch by rowid 85
    table fetch continued row 0
    table lookup prefetch client count 0

    10 rows selected.

    Elapsed: 00:00:00.00

    PROD


    NAME VALUE
    ---------------------------------------------------- ----------
    table scans (short tables) 5
    table scans (long tables) 0
    table scans (rowid ranges) 0
    table scans (cache partitions) 0
    table scans (direct read) 0
    table scan rows gotten 3
    table scan blocks gotten 3
    table fetch by rowid 26
    table fetch continued row 0
    table lookup prefetch client count 0

    10 rows selected.

    Elapsed: 00:00:00.04

    QUERY 2


    SQL> select count(*)
    from contrat1.fa_usage_detail
    where id_type_identifiant is not null; (One of the first columns in the definition of the table)

    PRE-PROD

    COUNT(*)
    ----------
    121815630

    Elapsed: 00:09:15.54

    PROD

    COUNT(*)
    ----------
    144718411

    Elapsed: 00:31:36.26

    QUERY 3


    To verify that "table fetch continued row" is still 0

    SQL>select a.name, b.value
    from v$statname a, v$mystat b
    where a.statistic#=b.statistic#
    and name like 'table%';

    PRE-PROD

    NAME VALUE
    ---------------------------------------------------- ----------
    table scans (short tables) 12
    table scans (long tables) 21
    table scans (rowid ranges) 0
    table scans (cache partitions) 0
    table scans (direct read) 0
    table scan rows gotten 121815633
    table scan blocks gotten 4990985
    table fetch by rowid 1268
    table fetch continued row 0
    table lookup prefetch client count 0

    10 rows selected.

    Elapsed: 00:00:00.03

    PROD



    NAME VALUE
    --------------------------------------------------- ----------
    table scans (short tables) 10
    table scans (long tables) 25
    table scans (rowid ranges) 0
    table scans (cache partitions) 0
    table scans (direct read) 0
    table scan rows gotten 144718414
    table scan blocks gotten 6167092
    table fetch by rowid 1269
    table fetch continued row 0
    table lookup prefetch client count 0

    10 rows selected.

    Elapsed: 00:00:00.07

    QUERY 4


    SQL> select count(*)
    from contrat1.fa_usage_detail
    where id_catalogue is not null;

    This is the last column of the table which was added recently, and was updated.

    I wanted to see the value of "table fetch continued row" increase to show me
    the chained rows :

    PRE-PROD

    COUNT(*)
    ----------
    17355117

    Elapsed: 00:09:29.09

    PROD


    COUNT(*)
    ----------
    41099556

    Elapsed: 00:33:33.13

    QUERY 5


    To verify that "table fetch continued row" increased on the prod and not on the pre-prod

    SQL>select a.name, b.value
    from v$statname a, v$mystat b
    where a.statistic#=b.statistic#
    and name like 'table%';


    PRE-PROD


    NAME VALUE
    ---------------------------------------------------- ----------
    table scans (short tables) 19
    table scans (long tables) 42
    table scans (rowid ranges) 0
    table scans (cache partitions) 0
    table scans (direct read) 0
    table scan rows gotten 243631263
    table scan blocks gotten 9981967
    table fetch by rowid 2669
    table fetch continued row 0
    table lookup prefetch client count 0

    10 rows selected.

    Elapsed: 00:00:00.03

    PROD

    NAME VALUE
    ------------------------------------------------------ ----------
    table scans (short tables) 15
    table scans (long tables) 50
    table scans (rowid ranges) 0
    table scans (cache partitions) 0
    table scans (direct read) 0
    table scan rows gotten 289436825
    table scan blocks gotten 12334181
    table fetch by rowid 2516
    table fetch continued row 1
    table lookup prefetch client count 0

    10 rows selected.

    Elapsed: 00:00:00.12

    We seem to have only one chained row which could not
    account for the difference in response time.

    I would like to add that the problem is only on this table.

    All other queries on the other tables are faster in prod than in pre-prod.

    I would also add that I ran the queries several times on the prod and on the pre-prod(when they were not busy)
    with similar results.I am showing here the results of just one run.

    If anybody have any ideas about what the problem might be, what I am doing wrong in my tests, or
    what other queries I should run , You're welcome to share your ideas.

    QUERY 6


    SQL> select blocks,chain_cnt,avg_row_len,num_rows
    from dba_tables
    where owner ='CONTRAT1'
    and table_name ='FA_USAGE_DETAIL';

    PRE-PROD


    BLOCKS CHAIN_CNT AVG_ROW_LEN NUM_ROWS
    ---------- ---------- ----------- ----------
    4994116 0 286 122240740

    Elapsed: 00:00:00.02
    [B]
    PROD[/B]



    BLOCKS CHAIN_CNT AVG_ROW_LEN NUM_ROWS
    ---------- ---------- ----------- ----------
    6167089 0 293 144698310

    Elapsed: 00:00:00.02

    Thanks in advance.

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I guess the HW setup and configurations make much difference in response time.

    You did not tell us the server details of prod and pre-prod.

    Are your sure both init.ora parameters are same.

    Tamil

  3. #3
    Join Date
    Jun 2001
    Posts
    32
    Quote Originally Posted by tamilselvan
    I guess the HW setup and configurations make much difference in response time.

    You did not tell us the server details of prod and pre-prod.

    Are your sure both init.ora parameters are same.

    Tamil
    both init.ora are the same.
    The prod has 4 pocessors and the pre-prod 2.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    what are the explain plans

  5. #5
    Join Date
    Jun 2001
    Posts
    32
    Quote Originally Posted by davey23uk
    what are the explain plans
    Both of them do a full scan of the table.

    IF I go through an index, it takes a couple of seconds in both cases.

  6. #6
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Whats the HWM on the table in both environments?
    Assistance is Futile...

  7. #7
    Join Date
    Jun 2001
    Posts
    32
    Quote Originally Posted by waitecj
    Whats the HWM on the table in both environments?
    What is the command to get the HWM?

  8. #8
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Assuming that both tables are analyzed:

    Code:
    --Find the total number of blocks allocated to the segment (Thus the HWM)
    SELECT BLOCKS
    FROM   DBA_SEGMENTS
    WHERE  SEGMENT_NAME = your_table;
    
    -- To find the number of blocks that are empty (how many blocks your are reading during a FTS for no reason)
    SELECT EMPTY_BLOCKS
    FROM   DBA_TABLES
    WHERE  ]TABLE_NAME = your_table;
    Someone else is bound to come up with simpler apporaches for this (possibly using DBMS_SPACE or something), but this will work just fine.
    Assistance is Futile...

  9. #9
    Join Date
    Jun 2006
    Posts
    259
    Turn on tracing: and run tkprof against the trace output after running the two querries).
    "alter session set sql_trace=true "

    What is the o/s reporting for I/O stats during each querry?

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    check if preprod is doing FTS parallely.. on the other hand in prod serially..

    Abhay
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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