database buffer cache

[fawazf16]

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.

[malay_biswal]

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.

[fawazf16]

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

[dbasan]

I suggest run the statspack before you change.

[fawazf16]

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

thanks

[malay_biswal]

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.

[malay_biswal]

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.

[pando]

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

[dbasan]

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

[fawazf16]

thank you all.
i will check how to trace the query and look into the spdoc.txt