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

Thread: database buffer cache

  1. #1
    Join Date
    Feb 2005
    Posts
    31

    database buffer cache

    hi,

    i'm using oracle 8.1.7,
    i run a statistic query,and i found that the recursive calls is very high.do i have to increase the dictionary cache? and how do i do it!

    i have another thing, i rebuild indexes on 5 tables that i often load data into it, but also on these tables, i have an index tablename_R on field O_ROWID [defined as integer in all tables].i have slowness in loading, do i rebuild also these indexes?

    thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Do you have performance problem with that SQL and you found the recursive is the issue. From my experience i came with lot of trace files where recursive SQL time is significant compared to non recursive. Can you post your tkprof output.

    Regrading rebuilding, could not understood your question properly.
    http://www.perf-engg.com
    A performance engineering forum

  3. #3
    Join Date
    Feb 2005
    Posts
    31

    dictionary cache

    hi,
    actually i ran the following:
    SELECT n.name, s.value FROM v$statname n, v$sysstat s WHERE n.statistic# = s.statistic# ORDER BY n.class, n.name;
    ....
    ....
    recursive calls 3008903
    ....
    ....

    and then i looked into result, that's why i think that i have to increase the dictionary cache size.

    regarding indexes, i have for each table [ex:emp]:
    index name unique column position index owner
    emp_1 nonunique id 1 oper
    emp_r nonunique o_rowid 1 oper


    and o_rowid in each table defined as integer.
    what i did, is rebuild to emp_1 for all tables, and the performance still the same.does oracle 8.1.7 enforce creation of such index tablename_r for each table to use it internally? i.e. i don't know if i use this index, i'm thinking if i rebuild this indexes too.
    thanks in advance
    Oracle,Unix Administrator

  4. #4
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    I suggest run the statspack before you change.

  5. #5
    Join Date
    Feb 2005
    Posts
    31

    Unhappy

    hi again,
    i'm a bit new in administration,kinldy can you more clarify about statspak!

    thanks
    Oracle,Unix Administrator

  6. #6
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    v$ views are good for analysis, but do not come to a conclusion from this. I would recommend to look at the statspack report as earlier suggested and if you do not have any performance issues, then forget this.

    If your one query or transaction is taking more time, trace that transaction. Verify the trace file and you will come to know where the bottleneck is. Just rebuilding the indexes might not serve your purpose.
    http://www.perf-engg.com
    A performance engineering forum

  7. #7
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Statspack is a Oracle tool, which will record the snapshots and using these snapshots you can prepare report, which will help you in analyzing DB performance.

    Do a google search, you will find lot of docs.
    http://www.perf-engg.com
    A performance engineering forum

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cannot query v$sysstat like that, you need to know that the values shown in sysstat are values since the database has been started, if your database has been running for a month then 3000000 is probably not as large as you think

  9. #9
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by fawazf16
    i'm a bit new in administration,kinldy can you more clarify about statspak!
    Sure. Read the doc spdoc.txt in the below path:

    ORACLE_HOME/rdbms/admin

    and implement the statspack, if errors out get back to forum.

    Good luck

  10. #10
    Join Date
    Feb 2005
    Posts
    31
    thank you all.
    i will check how to trace the query and look into the spdoc.txt
    Oracle,Unix Administrator

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