Re: HELP ME!!!! PLEASE!!! before i leap off a bridge. Im waiting days for info...

mike wrote:

> okay ill explain the problem: Its taking years to get reports back
> using a report writer. First let me explain the situation. Im new at
> this place.
> I have no control over the DB. No db admin, no Bstat, no nothing, just
> looking.
>
> A. Here is what im hoping someone can tell me:(OH THAT LOOKS TOTALLY
> WHACKED OUT, DUH FIX THAT!!!)
>
> 1. I write reports, I have no control over Oracle. I dont have sysadm
> to it.
> 2. I can't adjust my SQL statements besides running stored procs.
> 3. Reports are taking days to get back, yeah I said DAYS, small ones
> hours.
> 4. The server is a Xeon dual 500, 1 gig of ram, multiple drives in
> raid.
> 5. NT 4.0 latest patches, running Oracle and a imaging system.
> 6. Memory is sometimes maxing out, while cpu utilization is running
> around 40%
> 7. Database is very simple some 5 tables. with the main running 13
> million rows
> with about 500 char size, others are fairly small
> ranging(1000-400,000) rows.
> Indexes are all on single colums. A ton of those.
> 8. I cant do a bstat or statspack or tell you buffer hit rates... etc.
> 9. I can get into DBA studio
> Here is what DBA tells me: (version 8.1.7.00)
> 1. Shared Pool : 43 Meg
> 2. Buffer Cache: 39 Meg
> 3. Large Pool : 0
> 4. Java Pool : 19 Meg
> 5. Number of Concurrent Users : 49
> 6. Sort Area Size : 128 K
> 10. I know im doing large sorts/groupings.
> 11. Some reports are doing full table scans, some not, even the nots
> are taking
> hours. The fulls are days.
> 12. Can i do a bstat without admin or higher security?
>
> Of course im not a Oracle officianado.
>
> here is the Oracle server init props:
>
> background_dump_dest = %RDBMS80%\trace
> compatible = 8.1.7
> control_files = "F:\DB\DATABASE\ctl1db.ora"
> control_files = "H:\DB\DATABASE\ctl3db.ora"
> control_files = "G:\DB\DATABASE\ctl2db.ora"
> db_block_buffers = 10000
> db_block_size = 4096
> db_domain = WORLD
> db_file_multiblock_read_count = 16
> db_files = 1024
> db_name = DB
> distributed_transactions = 5
> global_names = TRUE
> instance_name = db
> job_queue_interval = 10
> job_queue_processes = 0
> log_archive_dest_1 = "LOCATION=d:\oracle\ora81\RDBMS"
> log_buffer = 8192
> log_checkpoint_interval = 10000
> max_dump_file_size = 10240
> open_links = 4
> optimizer_mode = CHOOSE
> parallel_max_servers = 5
> processes = 59
> remote_login_passwordfile = BLAHBLAH
> service_names = database.world
> shared_pool_size = 45534336
> sort_area_size = 131072
> timed_statistics = TRUE
> user_dump_dest = %RDBMS80%\trace
>
> Does anything stick out like a sore thumb as totally blatantly
> wrong?????????????????????????????
>
> help me please... :)

What sticks out as a sore thumb to me is that a report writer is posting this and not the DBA.

There are a thousand things that could be wrong. Others undoubtedly will comment on them. But I suspect the problem is that the DBA isn't the one who took the initiative to solve the problem before it got to this point.

Daniel Morgan Received on Thu May 16 2002 - 12:56:59 CDT