Re: dc_used_extents ,dc_free_extents and dc_histogram_defs

.. And there used to be all these dc_ parameters that one could set, giving the dba control over the dictionary cache, which was not a part of the shared pool. And then came Oracle V7, with the shared_pool_size, wresting that control. Regards

Re: ** can two processes use the same rollback extent

And to further refine that statement and IIRC, a transaction will not use an extent E, that already has an active transaction in it, when the transaction has filled up the previous extent S, and is now looking to wrap into a new extent E. However, a fresh transaction can always be allocated space

How do I corrupt a raw datafile?

In the process of testing my rman recovery scripts? How do I corrupt a datafile created on a raw volume? DD, CAT, VI, RM ? Thanks in Advance Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services--

RE: How do I corrupt a raw datafile?

Thats precisely what I did. Logged in as Oracle. 1. Tried dd if=/dev/null of=/dev/vx/rdsk/.../tools_01 2. cat sqlnet.log /dev/vx/rdsk/.../tools_01 3. echo Mary has a little bambi /dev/vx/rdsk/.../tools_01 All ran successfully. I see the timestamp on the raw volume

Re: What is a local write wait?

Thanks KG. There were underlying OS I/O issues. The SA is looking to fix the same. Regards Raj __ Rajesh L. Rao DCI - Triad, Oracle DBA IBM Global Services, JPMC Account Phone: 516 5746065 E-mail: [EMAIL PROTECTED]

What is a local write wait?

Was creating an index with a degree of 4, and in unrecoverable manner? There were few waits for an event called local write wait. Can anyone shed more light on this wait? Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat

Re: Using ' in Update statement

The one thing that I missed in my reply were the ;-) signs. I say hear hear to Mladen and Jared. My attempt at sarcasm fell flat like the Elle show. I was going thru my latest acquistion of Effective Oracle by Design by Tom Kyte, and was unduly influenced by the goodies he spoon fed me ;-).

RE: RAC and Standby Dr

Thanks for the reply, Pete. We recommended EMC SRDF to keep the production and DR in sync, but the budget cannot provide for it. Now, with a standby DR, what I am scared of is, it would take me say 15 minutes tops, to activate the standby in case of a production site failure. But then, making

RAC and Standby Dr

Folks, A project team here is flirting with the idea of having standby databases for the two production RAC nodes. The two standy nodes will be at a DR site. Any gotchas with this configuration? Regards Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET:

RE: Using ' in Update statement

You guys are mean !! Tom Kyte would have given me 10 ways of writing the statement, would have traced every one of them under different versions and on different platforms, pointed out the number of logical reads, elapsed time, et all, and told me which one is better. Regards Raj

Re: FIRST_ROWS hints

Very Much expected behaviour. Your increase in the db_block_size from 8 to 16, is making the optimizer choose full table scans, where applicable, since you have to scan through only half the number of blocks now. A nested loop will starting throwing the initial results faster. A sort merge join

OT - Microsoft Security Bulletin MS03-040 (828750)

Microsoft issued this critical update on the 3rd, which I found out about, when I did a Windows Update from my IE.

Re: Multiple Datafiles and performance?

Tim, Indexed access is a purely sequential activity from an I/O standpoint, putting aside the reality that a buffer cache exists. First, we access the root block of the index and read its contents in order to know where to perform the next I/O (i.e. a branch block). Then we read that branch

Re: Multiple Datafiles and performance?

Tim, For arguments sake, the I/O steps that you mention is for a single user. Assume thousands of users, in which case, everyone would be hitting the same disk volume. Whereas, if they were spread, the I/O would be spread across 2 different volumes. Having said that, I dont recommend spreading

Re: RE: why does block cleanout incur redo?

Excerpts from one of my notes, collated from excellent papers or websites by Steve Adams, Jonathan Lewis, and KGopal. The first block of every rollback segment, also called the segment header, has a structure called the transaction table, which keeps track of all the active transactions in

RE: last column in a table is added with a DEFAULT

I know, with earlier version of Oracle, columns likely to have null values were placed at the end of the table, to save on some minuscle bytes of space. If the null columns fall between columns with data values, then one byte is used for their storage. Else, they require no storage. But no,

Re: Corrupt Inactive RedoLog Goup.

Sorry, I skipped mentioning that, but we did try it, for the members. Got an ORA-01514: error in log specification. No such log. ORA-01517: log member: 'F:\ORACLE\ORADATA\TCP1\LOG3TCP1.ORA' When we used the group number, we got an:

Corrupt Inactive RedoLog Goup.

Members, Production 8.1.6.3 database on Windows NT 4.0. We noticed an entry in the alert log, citing that a log group could not be archived since the 25th of July.. We have 3 log groups with 2 members each. I notice that the 3rd redolog group has a status of Inactive with Archived=No. This group

cursor_sharing=SIMILAR and 9i

A quick update. Having multiple ORA-600 errors thrown at me randomly when I try to do a select from any of the V$ tables. Not always. 3 out of 5 attempts spew out ORA-600's and while this is in progress, the database freezes for a few seconds. Oracle Support says its coz of the

Re: elapsed_time in 9i v$sqlarea

Thanks Mladen. So, I was looking at the wrong place. Since I was referring to the column elapsed_time in v$sqlarea, I expected to find it documented under v$sqlarea. I did not have the foresight or the hindsight to look at v$sql : -) Thanks Raj -- Please see the official ORACLE-L FAQ:

elapsed_time in 9i v$sqlarea

What's elapsed_time in v$sqlarea? I believe this was introduced in Oracle 9i. What is the unit for it? Strangely, the Oracle 9i Server Reference manual seems to have missed this altogether. I executed a query, the execution time of which was approximately 9 seconds. But when I look up v$sqlarea,

elapsed_time in 9i v$sqlarea

What's elapsed_time in v$sqlarea? I believe this was introduced in Oracle 9i. What is the unit for it? Strangely, the Oracle 9i Server Reference manual seems to have missed this altogether. I executed a query, the execution time of which was approximately 9 seconds. But when I look up v$sqlarea,

Re: buffer busy waits and v$filestat

Thanks, Mladen, for that helpful quick reply. The reason I ask is, one of our clients claims that they see a intermittent database performance degrade (queries get timed out), during times when the RMAN backup runs. Using an OS monitoring tool, they see a spike in I/O. The RMAN backups have 4

buffer busy waits and v$filestat

Folks, Say a session issues a read request, and finds another session already reading the block into the buffer cache. If this session waits N ms on a buffer busy waits event, does this N ms of wait get added to the read times in v$filestat? Or is the readtim in v$filestat purely physical I/O?

Friday Humor - RMAN

Excerpt from the 9i RMAN Guide : If you specify DEVICE TYPE DISK, then you must back up to random access disks. You can make a backup on any device that can store an Oracle datafile: in other words, if the statement CREATE TABLESPACE tablespace_name DATAFILE 'filename' works, then 'filename' is

RE: SORT_AREA_SIZE question

Since we are talking of Sort area sizes here, I tried to collect some statistics on the amount of memory used by each dedicated Oracle connection. As I understand it, the PGA is allocated in chunks upto the SORT_AREA_SIZE. Initially, I just started a session, and noted down the memory used using

RE: SORT_AREA_SIZE question

Thanks Dennis. My question was not about the memory release, but more of the way memory is allocated to the PGA. Given that Oracle allocates memory in small chunks to the PGA until it approaches the sort area size, if I run a huge sort, should I expect the PGA memory to increase, until it reaches

Re: SORT_AREA_SIZE question

Thanks for the reply. I did use the Metalink Note to calculate pmap, minus the shared memory, et all. The problem with pmap is how do I make out what portion of this memory is UGA, and PGA? So, I look at v$sysstat. Anyway, hope to pursue this test in a much more detailed manner, when I have the

RE: AQ, RAC and delay

With MPCD=0, Is your primary wait event shown as on-going reading of SCN to complete in the statspack reports? Oracle Support insisted we set it to 7 coz of this. Raj

Some rollback doubts

From the docs : Many active transactions can write concurrently to a single rollback segment--even the same extent of a rollback segment; however, each data block in a rollback segment's extent can contain information for only a single transaction. 1) Now, when oracle writes to the last extent in

RE: Some rollback doubts

Thanks KG, for your reply. Well, it whets my appetite for more. This surprises me. All the documentation I have read so far tells me that multiple transactions cannot be write to the same rollback segment data block. My first question was basically well, if many transactions can write to an

RE: HP OpenView

I tried monitoring some of my databases with it. The interface was crappy. But management insisted we use it since we pay for it. Eventually, found it much easier to write my own unix scripts and send all alerts and warnings to a file on a server, which is then monitored by Openview. Raj

Re: Parallel Query Server died

How about writing an eulogy now for ORA-600 [ 12235 ]: Oracle process has no purpose in life? Joe Testa

RE: use of reverse key index,cost based optimizer

Assume an index on employee number. The number is assigned sequentially, and as such, the rightmost index leaf block would always be used. A possible hot block. A reverse key index can avoid this. Also, assume when an employee retires or quits, the record is deleted. But the space freed within

max_commit_propagation_delay

OPS 8.0.6.3 on Sun Nodes. We have this currently set to 0, and Oracle has recommended that we leave it at the default of 700, while making a general recommendation to improve the performance of this database. But from what I have gathered so far from Metalink, this would not suit us, since,

Re: max_commit_propagation_delay

Thanks for the update Jonathan. The recommendation was made by Oracle Support, after they noticed a lot of on going read of SCN to complete waits from a system dump. Almost 40% of the waits. In some of my statspack report, I even see this waits accouting for 70% of the total. Setting this

9i RAC - GFS

Attached in a quote from an Oracle Rep, asking us to avoid Global file systems on Sun Cluster nodes for 9i RAC. It is very clear that binaries and/or datafiles on GFS are not supported today. The reason for this is the proxy I/O that GFS is based on can be a real performance and scalability

Re: 2k and 8k block size

My question was specific to RAC: With a larger block size, is one not increasing the chances of pinging for the blocks? Or I dont need to worry about it with Cache fusion? A new worry for me now is that, though the block size was changed from 2K to 8K, the recommendation was to keep the SGA the

Re: 2k and 8k block size

The purpose of this feature, I believe, is to accomodate transportable tablespace restores, so that it is possible to say,transport a tablespace from a 2K block size database, to a 4k block database. Raj

2k and 8k block size

An 8.0.6 ops database being migrated to 9i RAC database on Solaris 8. The 8.0 database had an block size of 2k. Multiblock read count is 8. This is an OLTP database, all transactions almost always work on a single row. None of the row sizes exceed the block size. Hardly any chained or migrated

2k and 8k block size

An 8.0.6 ops database being migrated to 9i RAC database on Solaris 8. The 8.0 database had an block size of 2k. Multiblock read count is 8. This is an OLTP database, all transactions almost always work on a single row. None of the row sizes exceed the block size. Hardly any chained or migrated

Using Soft Links for Datafiles

Resending. Any takers? - Forwarded by Rajesh Rao/JPMCHASE on 02/07/03 11:51 AM - Rajesh

RE: SQL*Net Connection under-performing

Vivek, I am wondering why do you have load balancing turned on for BBY01, when you have just one listener? Whats the MTS_DISPATCHER set to? Raj

Using Soft Links for Datafiles

We are in the process of creating a 9.2 RAC database. And we are being forced to follow a standard whereby, all files are to be in a directory structure, say /local. No standards have yet been defined, if one were using raw volumes. And since we will be using raw volumes for the datafiles, to

RE: simple question on DDL

Well, then even Tom's vague on this one. DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions. Well, then even an insert statement makes changes to DD objects. Ain't locks taken then? DDL Starts by commiting. Why not declare a

Re: over-normalized?

How many join table operations do you perform, in most of the queries? As more tables are added to the join, you take a performance hit? Plus, all the space for the indexes on the additional tables? An update could end up having to write to multiple tables. So, I guess, you have to walk the tight

Re: Slow database, too MANY buffers???

Wouldnt this indicate hot blocks? How do I deduce from this, I have a large value for db_block_buffers? Thanks Raj Jonathan Lewis

Re: over-normalized?

A valid point. But say, what if an primary key, such as, employee number has to be changed, or reused? Aaaah!!! Forget it. Typed that in just for arguments sake ;-) Thanks Raj

Re: CBO not using the index even though it is faster when hinted ....

How do you know an index scan would be faster? Did you get the query to use an index? What hint? Do you have the tracing stats for both with and without an index? First, you dont have a where clause filter on an indexed column? If all the columns in the select were indexed, maybe u could have

Shared Pool - How much free memory?

I have this application which issues a lot of dynamic SQL's with literals, for each customer who logs in. And on querying v$sgastat, I see 21Mb of free memory. Does shared_pool_reserved_size show up as free memory in v$sgastat? If not, I might have some further questions? Thanks Raj -- Please

Re: simple question on DDL

That raises another doubt. For an simple insert statement, could also update the UET$ or FET$ tables? So, if the purpose was to preserve all changes to the data dictionary, What's different between OBJ$, COL$ and these space management tables? Thanks Raj

RE: simple question on DDL

Thanks Dan. The gist of your response was that all changes to the data dictionary are immediately commited. Seems to make sense to me. Maybe, thats one reason why one cannot free space below the high water mark. Coz changes to UET$ has been committed, even though the data was rolled back. I sent

Re: from Non-Archive-Mode to Archive-Mode

Arup, My initial reaction was that you were suggesting setting the parameter to know the frequency of your log switches. But now to clearly understand your point of view, and for my curiousity, how does one derive the amount of redo log generated in bytes using the SCN Numbers? Thanks Raj

RE: Database Verification

Just use RMAN to backup your databases. It will warn you about corruptions. Raj Stephane

Re: from Non-Archive-Mode to Archive-Mode

Arup, From your past replies to the list, I am surprised to hear you say this. If you have placed the init.ora parameter log_checkpoints_to_alert set to TRUE, then your alert log file will have lines when the redo log group switch fom one to the other. Regards Raj

Re: temp space

Chris, Add the following in your init.ora. event = 1652 trace name errorstack level 3. And check the user dump destination after u encounter the error. In case, downtime is not an option, or you cannot set it at the individual session levels , another way of doing it is by coding for a

RE: Caught YAPPing

Thank You for your response Cary. You say The important thing is that you have to segment your transactions into different lists. I did convey something similar to the requestor. I have now put the ball back in their court, by telling them to identify some crucial functions or precise statements

Re: trying to order date chronologically

Sebastian, I dont know if I understood this right, for this seems to be quite a simple task. Or maybe I am overlooking something very obvious. Select to_char('date field','MMDD') from Mytable order by 1 Unless you dont want the date field displayed in that format in your report, but still

Caught YAPPing

Hello Everyone, Is there anything, called an Average Response Time for an Oracle database? I got a request from one of the application owners as under : Snip If I can get a log of AVERAGE response time for 1/2 hour periods starting at 9am and running thru 5 pm I would be in heaven. If I

RE: Database tracking

Statspack is all good to troubleshoot problems. If my boss had his way, I would be looking at the statspack reports every 15 minutes. But for me to be proactive as a DBA, I need bells and whistles. A user just executed a query which does 14 full table scans. Other than the monitoring tools

RE: RMAN backup - basic Qs

To better answer the original question, my understanding of this difference is as under: Why do you put the tablespace in backup mode during normal hot backups without RMAN? This is to avoid backing up split blocks. Say, you have a database with a block size of 16K. And that a block is in the

RE: encrypted user/passwd connection

http://documents.iss.net/literature/DatabaseScanner/reports/oracle/OraPolicy.pdf Sony kristanto

Re: BCHR Tuning

I too think the BCHR has its place, as a problem indicator. It can tell me theres something wrong with my database. Say, I have this database performing well, the users are happy, the BHR is mostly at 90%, and now it suddenly shoots down to 70%, or it suddenly increases to 98. Somethings amiss.

Re: Question On High Parse to execute ratio

My colleagues call it CURSER_SHARING :))) Igor Neyman

RE: encrypted user/passwd connection

All oracle passwords are encrypted is not a true statement. Failed login attempts, are retried by sending the password in an unencrypted format. Atleast, until 8.1.7. To avoid which, ORA_ENCRYPT_LOGIN variable and DBLINK_ENCRYPT_LOGIN parameter (for retried attempts across database link) should

Re: FULL TABLE SCAN?

1. Set optimizer_mode to RULE. 2. Make sure all statements have a WHERE clause. 3. Dont use functions in the equality clauses. 4. Create an index on each and every column you have in the database. Take my advice. I dont use it anyway :))) Raj

Re: Long-running PL/SQL function (long)

Cherie, If network bandwidth is the bottleneck, the use of Fast Refreshable snapshots will be a great help. Whereby you only pull the rows that have changed since the last refresh across to the primary. Raj

RE: encrypted user/passwd connection

adisys.com To: [EMAIL PROTECTED] cc: Rajesh Rao/JPMCHASE@CHASE January 07, Subject: RE: encrypted user

Re:Snapshot too old

Headed for home. So, in short, theres delayed block counts, which could be responsible too. Raj dgoulet@vicr.

Simple Query for Week number

Hey Folks, Looking for the simplest of queries to find the week number, given a date. For example, 19th December 2002 falls in the 3rd week, whereas 19th November, 2002, falls in the 4th week. Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL

RE: Simple Query for Week number

Dan, Thanks. The W works just fine. What I was looking for. Phew!!! I almost wrote a PL/SQL for this ; Fink, Dan

Re: Simple Query for Week number

Thanks Yechiel. I missed that. That is a life saver. Yechiel

RE: password

Its not an update of dba_users. Its an Alter user identified by values OldPassword, which is also what an export/import uses. Raj Bob

Re: Finding User sessions = idle 30 min??

Denham, How about using status='INACTIVE' and last_call_et 30 minutes? Regards Raj Denham Eva

Re: imp table by table

And if the table already exists, look to disable the constraint, and then enable them after the import. Raj Yechiel

RE: AQ query from dequeuw procedure not using index -URGENT

Shaleen, The use of FIRST_ROWS hint with an order by clause is ridiculous. The hint is ignored. Raj John Kanagaraj

Re: How are rollback segments assigned?

Thanks Jared, thats not what I see. I have 20 rollback segments, and at no point in time, there were more than 4 transactions in the rollback segments. In fact, I happened to see it a couple of minutes ago. select usn, xacts from v$rollstat showed me 0 in 19 rollback segments, and 2 in one of

RE: LGWR using lots of CPU time, low CPU usage

Debi, A log file sync event usually indicates that the application is probably committing too often, and LGWR cannot keep pace with it. You might be experiencing contention or I/O issues, on the disks where the redo log files are placed. Moving your redo log files away from RAID 5 is a step in

How are rollback segments assigned?

Hello Friends, Oracle Parallel Server 8.0.6.2.0 on Solaris 2.6 Nodes We were having some locking and rollback issues, and I set up some scripts to alert me in case there are more than 4 transactions in the rollback segments, and more than 1 in any of the rollback segments. Select sum(xacts)

Transactions not getting commited :-(

Hello All, 8.0.6.2.0 Parallel Server on Solaris 2.6 nodes. We have intermittent outages, once every 2 days, here on a production database. What happens is that all DML transactions dont get committed or rolled back, and start filling up the transaction slots in the rollback segments. On a normal

RMAN backup

Hello All, Is resizing the datafiles the only way of reducing the size of an RMAN full backup? Oracle Version 8.0.6. We take RMAN hot backups to disk, and the size of the backup has grown considerably. There's one large table which we were considering truncating. But looks like that would not

Re: How-To or Good Practices on Code Releases

And have a similarly tested and signed off rollback strategy in place. An immediate rollback, as well as a rollback strategy after n number of days. Raj One attachment (0k)

RE: How-To or Good Practices on Code Releases

As an example, something that yours truly was involved with, and still have the scars to show for it. A migration from a lower version of Oracle, to a higher version, on a completely new server. The scripts ran fine, and the implementation plan worked fine. However, the application started

RE: How-To or Good Practices on Code Releases

I also have been burnt by an simple alter table add column default string script. A pretty simple script. The developers tested it, the QA team too approved it. Was expected to run in a matter of minutes. The problem was the data in production was 100 times over. Missed out on volume testing.

RE: How-To or Good Practices on Code Releases

Thats Utopia for us. Production DBAs have no access to Development and QA and Test boxes. The application developers code for everything, they test it, send it to QA, who perform the implementation on the QA databases. We only review the scripts, after it has been approved by QA, and do some

Re: Solaris vs Windows 2000

Everything good comes at a price. And never trust any system, where to shut down, you got to click on start ;-) Regards Raj Steve Perry

Re: SID and DB_NAME

Simply speaking, consider SID to be the name for the various background process (DBWR, LGWR, et all) and the memory structures (SGA, PGA). A SID needs to be associated with a database, which would be the DB_NAME. The SID and DB_NAME need not be the same. They have to be different in an Oracle

Re: ALTER SESSION SET EVENTS '...'

Mike, These events are generally set at the advice of Oracle Support, or when the DBA needs to do some tracing. You might not find these in the docs, but Metalink should have notes on them. Raj

Re: Location of Trace file

If you follow OFA, it works for NT ;-) Tom Pall

RE: [Q] wield create view error, need help?

Grant privileges on v_$sysstat dist cash

Re: Full Import/tablespace sizes

Mike, While not necessary, it would be a good idea to precreate the tablespaces. Else, for a clean import the first time, you would need to ensure that the directory structure for the datafiles exists, you have the permissions to create files at the OS level, ensure that files dont already exist

Re: Find Queries by a group of users

Johnson, Auditing, atleast in 8i, wont help. Maybe, if its not an overhead, you could have a logon trigger that enables tracing for those users. Raj

Re: Opinions on BR scenarios

If the database crash causes media failure of your archive destination or your online log destination, then you can only recover the database upto the last archivelog availability, and you would need to open the database with resetlogs. This is true for both scenarios. Assuming no media

Re: standby database question.

That would be the manual mode. Recover standby database. With 8i, and the multiple archive destinations, you have the sustained recovery mode. Recover managed standby database. A sustained recovery mode will lock your telnet window, and will be dedicated to doing just the recovery. But then, one

RE: Opinions on BR scenarios

Dont you mean complete recovery? You could then perform an incomplete recovery until 02:10:01 - 02:10:59 or 02:21:01 - 02:21:59, etc. Fink, Dan

RE: Opinions on BR scenarios

Dan, What you intend to say is that if the database crashed at say 2:15:30, you can do an incomplete recovery upto say 2:10:30, right? Why cant I do a complete recovery ? or what stops me from doing an incomplete recovery until 2:15:25? Raj

Re: DB monitoring using SNMP MIBs

Not sure, Though it does run queries against the database, does the OEM Agent not use MIB? One attachment (0k) Jamadagni,

RE: oraperf comment

To summarise, the goal should be to spead I/O evenly across the devices. Right? Raj Markham, Richard

Template for Space Estimates

- Forwarded by Rajesh Rao/CHASE on 10/17/02 05:15 PM - Rajesh Rao

Template for Space Estimates

I need to estimate disk space sizing requirements for a new database server. Does anyone have a document that I can send forth to the developers, asking for inputs from them, about the objects, the growth patterns, et all? Or any links? Thanks Raj -- Please see the official ORACLE-L FAQ:

  1   2   3   >