Re: latches and alter table freelist

willevolve <member27377_at_dbforums.com> wrote in message news:<2844605.1052210723_at_dbforums.com>...
> >MB, do you mean "reporting" instead of requiring?
> First Query save PK in FINDER, and second query is within prev. results.
> So, I have problem with table FINDER with huge dynamical changing number
> of rows. Freelist parameter must be number of users?
>
> >How do queries by users cause rows to appear in the FINDER table?
> in FINDER table I deploy previous results for each user.
>
> >What are you looking at that makes you think you have a latching
> >problem?
> I use Spotlight for Oracle, and I have waits for latches, but I do not
> understand why. Interesting is that even if I do not contact Oracle
> database alarm periodically occur?
>
> >I think that in order to get good advice you need to explain the
> >processing involved, at least at a high level, and possibly post some
> >query results related to the problem statistics.
> Queries are optimized and work well. I just tried to tune memory of my
> Oracle server for 40 users.
> INIT.ORA:
> compatible = "8.1.6"
> db_block_buffers = 35300
> db_block_checksum = TRUE
> db_block_lru_latches = 12
> db_block_size = 8192
> db_file_multiblock_read_count = 128
> db_files = 70
> db_name = "RMONDAY"
> distributed_transactions = 500
> global_names = TRUE
> instance_name = "RMONDAY"
> java_pool_size = "32768"
> job_queue_processes = 10
> large_pool_size = "0"
> log_archive_dest_1 = "location=C:\Oracle\oradata\RMONDAY\archive"
> log_archive_format = "%%ORACLE_SID%%T%TS%S.ARC"
> log_archive_start = TRUE
> log_buffer = 3145728
> log_checkpoint_interval = 0
> log_checkpoint_timeout = 0
> max_dump_file_size = "10240"
> max_enabled_roles = 30
> mts_dispatchers = "(protocol=TCP)"
> open_cursors = 400
> oracle_trace_collection_name = ""
> os_authent_prefix = ""
> parallel_max_servers = 5
> processes = 500
> remote_login_passwordfile = "EXCLUSIVE"
> service_names = "RMONDAY"
> shared_pool_reserved_size = "10223616"
> shared_pool_size = "102236160"
> sort_area_retained_size = 3219128
> sort_area_size = 3219128
> timed_statistics = TRUE

The init.ora parameters probably are not going to help much in looking at a latching problem. Latches are not my strong suit, but there are some posters on these boards that are very good with them. Still I have seen a couple of problems so which latches are you seeing waits on? Do they have latch children?

For only 40 users your buffer pool and distributed transactions seem high. Maybe a better number would be how many concurrent sessions does your db have to support? And what kind of db is it: OLTP vs Warehouse? What is the total size of the db?

Just taking a shot in the dark I would think that perhaps your finder table, if multiple users are accessing rows in the same block concurrently, needs to have more than the default transaction work areas preallocated. Multiple free lists might be in order, but I find Oracle does a pretty good job with them so just a couple should be enough.

Your compatiable is set to 8.1.6 so hopefully your db is 8.1.6. If it is higher I would set it to match the db.

HTH -- Mark D Powell -- Received on Tue May 06 2003 - 16:50:23 CEST