RE: Are you using Resource Manager in 9.2? URGENT

Raj, Not really. I just wanted to know people's experience in general with Resource Manager on 9.2. I'm not sure that it's being used much. You are the only person who responded to my email. Perhaps not many people are running on 9.2 (especially in production). We ended up turning it

Leccotech DB*Expert Users - Response Time versus Elapsed Time

I know that a few list users are using DB*Expert SQL tuning tool by Leccotech. We are using the latest version on a 9.2.0.1 database. On occassion, I have seen that when we do a batch run of optimized SQL to find the fastest SQL we see a fairly wide disparity between elapsed time and response

Followup: Leccotech DB*Expert Users - Response Time versus Elapsed Time

Cherie_Machler @gelco.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent

Are you using Resource Manager in 9.2? URGENT

We are just ramping up a version 9.2 OLTP database on Sun Solaris 2.6. We didn't see any issues with a small number of users but we are seeing a lot of resource manager waits with a large number of external clients. We are wondering if perhaps we should turn Resource Manager off. It defaults

Where are the scripts located to create the SCOTT test objects?

I have a user that is using the SCOTT test tables on one of our dev databases. He is apparently missing the DEPT table. Where are the scripts that create the SCOTT tables? I tried looking through the on-line documentation and Metalink to no avail. Can anyone point me in the right

RE: Where are the scripts located to create the SCOTT test objects?

Kevin, Thanks. Just what the Dr. ordered. Cherie Kevin Tsay

Re: Orawomen

Dennis, I think that the article is correct that girls and teens are sensitive to the geek stigma factor.Even in our 10-year-old Girl Scout troop, the girls are already concerned about their images and not being too dorky. We have many more girls vote for activities involving animals and

RE: Orawomen

Robin, Yes, there is already declining enrollment in Girl Scouts in general and in the older age ranges as the kids get into Middle School or Junior High. The Girl Scouts organization is very sensitive to having activities be driven by what the girls want to do, not just what the leaders think

Long-running PL/SQL function (long)

Our developers sent me a function which is running quite long to see if I could give them any advice. It is written in PL/SQL for version 9.2.0.1 of Oracle on Sun Solaris. It is going across a database link. It reads tables in one database and loads a new table in a datamart table on another

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

Cherie_Machler @gelco.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: Long-running PL/SQL function (long) om

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

Raj, Just got this code this morning so I haven't had a chance to run a trace yet. Will have to schedule that for tonight. Yes, code is being run on the source DB. Yes, ACCT_PAY_TYPE is less than 2000 rows. You are the second person who has recommended a PL/SQL table. I will run a test

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

Stephane, Thanks for your reply. I don't think it's a possibility to get another resource to rewrite the PL/SQL. Our shop is more java-based and PL/SQL is not our developers language of choice. Do you mean to move as many statements inside of the loop to outside of the loop as possible? If

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

Cherie_Machler @gelco.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: rootcc: Subject: Long-running PL/SQL function (long

Re: Happy Holidays!!

Rachel, I suspect that there are many more than ten female subscribers to the list. However, I'd have to agree that there are at least a dozen women who post somewhat regularly. Going to our local Oracle User's Group meeting, there are about 25% women in attendance at the meeting when I look

Re: Happy Holidays

Kevin, Thanks to you (and also to Lisa) for offering to share your scripts. Every little bit helps. Cherie Machler - close to leaving for vacation Oracle DBA Gelco Information Network

Function-based Index

I have the following statement that I would like to create a function-based index for: SELECT MIN(tran_dt) FROM ach_tran WHERE cnfr_no = :b1 I'd like the index to include columns cnfr_no and tran_dt (in that order). The examples I've seen are just single-column indexes. Is it possible to

RE: converting to the cost based optimizer

Raj, Can you elaborate on the process that you used to determine the optimal value for db_file_multiblock_read_count? We are doing some performance testing on a new generation of our product with some new databases. We increased our database block size from 8k to 16k (on Sun Solaris 2.6).

RE: converting to the cost based optimizer

Raj, I'm just curious whether you also have reset your optimizer_index_ parameters in these databases where you have changed and/or are considering changing the db_file_multiblock_read_count? In particular, has it been set on the production database where you set db_file_multiblock_read_count

RE: But I *want* to use RBO!

Last I saw, version 9.0.1.x is scheduled to be desupported in June of '03. I haven't seen any desupport date for Rev2 of 9i which is 9.2.0.1. We've upgraded from 9.0.1 to 9.2.0 in order to avoid any issues with desupport before we even get our product to market which we've been developing on 9i.

RE: DBMS_STATS.GATHER_DATABASE_STATS analyzes SYS and SYSTEM inde

Connor, What version of Oracle was this coded for? Thanks, Cherie Connor

Re: Goldilocks repost, please?

For what it's worth, we use 128k, 4m, and 128m. Those numbers were taken from the paper How to Stop Defragmenting and Start Living. Can't remember the author but I'm sure someone will chime in as it's a well-known paper. Cherie Machler Oracle DBA Gelco Information Network

Re: * Oracle 11i DBA position in Wisconsin..

Baraboo is in a fairly rural area more than an hour from Madison or from Milwaukee. Neither of those cities are a vast metropolis. Not everyone is keen to live in the country. Plus I would imagine that a DBA job in this part of the country wouldn't pay a massive salary. However, the cost of

RE: Another Character Set Problem

The utility is called csscan for character set scan. It runs at the unix prompt with additional parameters (some optional and some not) much as export or import would. Before you use it the first time, you need to run csminst.sql from $ORACLE_HOME/rdbms/admin to set up the work tables, etc.

Re: Another Character Set Problem

Scott, You can only use the alter database command to move from one character set that is a strict superset of your current character set. You can find lists of superset relationships on Metalink. Search on something like character set conversion and superset or UTF8 and WE8ISO8859P1. You

Rerunning CSSCAN character set scan utility on same database

With the end-goal of doing a character set conversion from US7ASCII to UTF8, I have run csminst.sql to create work tables and then run the CSSCAN utility from rdbms/admin on my test database. We have written a C program to identify and alter exceptional characters identified by the CSSCAN

RE: dbms_utility and dbms_stat difference

Barbara, I can't speak for dbms_utility but dbms_stats is supposed to generate statistics at both the partition level and at the table level for partitioned tables, which analyze does not do. These are supposed to be better quality statistics. Be aware that there are bugs related to

Is port 1575 the standard for any Oracle product?

Is port 1575 the standard port number for any particular Oracle product? Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX:

RE: SOME SOLUTIONS!

Dennis, The sales critter could give better pricing for Leccotech's SQL*Expert. For a SWAG I believe it's less than a $1000 per seat and I believe that is sold with a per-database component to the pricing. I think there are also discounts when you buy more copies. It was well worth what we

How to use pipe-delimited SQL*Plus output with a long datatype

One of our developers wants to create a reusable report where the pipe-delimited output generated by SQL*Plus would be sent off and used as input into another process. The problem is the remarks field which is of type long. It causes ORA-00997 errors. If I take the pipe-delimits off, the

Re: How to use pipe-delimited SQL*Plus output with a long datatype

Thanks to those who recommended the relatively new set colsep command. It did work in this report. Thanks again, Cherie Machler Oracle DBA Gelco Information Network

Creating sequences on the DUAL table?

I have a request from one of our developers to create two new sequences on the DUAL table. This seems like a bad idea to me. I've never had such a request in the past. I asked for clarification on why this is needed and I didn't get a lot of details yet. Is this something that is standard

RE: Creating sequences on the DUAL table?

Dan, Haven't heard the why yet but I think it may be just a poorly worded request. I suspect he wants the sequences created on another table and just wants to be able to select from dual. Maybe he never realized that the sequences were actually created on another table.Maybe he actually

RE: Creating sequences on the DUAL table?

Kevin, Thanks for the suggestion on select statements. I will pass them on. I think it's just a misunderstanding on his part as to where the sequences are actually created. Apparently he only uses them in a select from dual so he thinks they reside there. Cherie

RE: Creating sequences on the DUAL table?

Mladen, Thanks for everyone's response on this. I think that this is just a misperception on the part of the developer. I took the phrase verbatim from his change request. I suspect that he has only ever used sequences in the SELECT from DUAL statement so he thinks that they are actual

When is the next patchset for 9.2.0.1 coming out?

Gene, What patchsets are currently available for 9iR2 (9.2.0.1)? I'm not aware that any are available. Does anyone know when the next patchset is expected to be released for 9.2.0.1? Thanks, Cherie Machler Oracle DBA Gelco Information Network

OEM Intelligent Agent and port numbers - How can I assign/configure a port

We have a third-party application that needs to use the SNMP agent associated with OEM. They want to be able to get to it via a particular port. We have a port assigned to each database, based on which Oracle version it is running. However, as far as I know, there is not a particular port

Re: Exporting stats for a production server to a dev/test server

Jack, Were your old statistics generated with the ANALYZE command? If yes, were you able to export ALL of the old statistics with the export option of DBMS_STATS before you actually analyzed with DBMS_STATS for the first time? If yes, after exporting them, did you delete the old statistics

RE: Upgrade from 9.0.1.2 - NOPE!

Does anyone have the bug number on this security bug? Thanks, Cherie Machler Oracle DBA Gelco Information Network Connor

Should we upgrade from 9.0.1.2 to 9.2?

We have a couple of new applications in development that are moving to QA. These databases are currently running 9.0.1.2 on Sun Solaris 2.6. We've been pretty happy with this version to this point but are at a pivotal spot in our product development cycle where we have a narrow window in which

Re:Should we upgrade from 9.0.1.2 to 9.2?

Dick, Thanks for your comments. So, the bottom line is that you are recommending moving from 9.0.1.2 to 9.2? Cherie [EMAIL PROTECTED]

Re: Urgent - Query Optimization

What version of Oracle and operating system/type are you using.Is this the Clarify help desk application? If it is Clarify, you can contact their support and open a case with them. They have some additional indexes, etc. that they recommend that helped us quite a bit with our Clarify

RE: Analyze running twice as long after upgrade to 8.1.7.2

Paula, Yes, it is a partitioned table. I am planning on switching over to analyzing it in parallel but first the application owners want to know WHY it's taking twice as long to analyze since the upgrade to 8.1.7.2. What is different about ANALYZE that would cause it to run so much longer.

RE: Analyze running twice as long after upgrade to 8.1.7.2

John, Thanks for this advice. I will try to get this data tonight on the updated version. We do not have the old version around anymore to try this out. Cherie

RE: Analyze running twice as long after upgrade to 8.1.7.2

Jack, Thanks for this insight. I'll check this out. We did not change our sort_area_size. However, I suspect there have been changes in the default method that Oracle is using sort_area_size. Perhaps I need to increase sort_area_size? I have a test database I can try it out on and see.

Re: Upgrade from 8.1.5 to 9.2

Igor, In the 9.2 upgrade manual, there should be a section in the front about preparing to upgrade and selecting your upgrade path.There should be a table that shows which versions you can upgrade from.Does that table list 8.1.5 as a supported version to migrate directly to 9.2? Cherie

Re: Nologging index tablespaces?

Dennis, We have set particular indexes to nologging when building them. These are indexes that we drop every night for our warehouse load. It is a hassle because whenever we clone this database to our QA box, those nologged indexes get corrupt and we have to rebuild them. Takes us three

Analyze running twice as long after upgrade to 8.1.7.2

We just upgraded our data warehouse from version 8.0.4 to version 8.1.7.2 of Oracle. We run on Sun Solaris 2.6 vith Veritas Quick I/O. We do an analyze compute nightly with a 10% estimate of our large, main fact table. Before the upgrade, the analyze ran for 45 minutes. Since the upgrade,

Why is Parallel Query Running after upgrading to 8.1.7.2

I have just upgraded my 8.0.4 database to 8.1.7.2. Previously, parallel query would only run on objects that a DEGREE set 1. I just checked and all of my tables and indexes have DEGREE set to 1. So are most of my queries now running in parallel? I just checked my init.ora file and I haven't

Re: Why is Parallel Query Running after upgrading to 8.1.7.2

Tim, Thanks for replying on a weekend. After doing some reading, I discovered that parallel_threads_per_cpu is set to the default of 2 on our database. With 8 CPUs, we are seeing 16 parallel threads on these queries, when they do go to parallel (a few of them do not). I experimented with

Re: Oracle DBA with 8i through 11i Experience Needed in Columbus,

Columbus, Ohio is not a real big market.I'm not sure what market rates are there but I would assume that they are not top of the line. Cherie Machler With in-laws that live in Columbus

Is this a good upgrade path from 8.0.4 to 8.1.7.2?

We are preparing to do an upgrade of our Data Warehouse on Sun Solaris from version 8.0.4.0.0 to 8.1.7.2. We've done a number of upgrades in the past but this is the first time we are going from 8.0.4 to 8.1.7. Following is the basic, high level plan: 1. Upgrade from 8.0.4 to 8.1.7.0 (Oracle

Re: Partitions

Lee, I've been doing a lot of work with reorging partitioned tables and splitting them. I'm on Oracle version 8.0.4 and 2.6 of Sun Solaris. I don't believe that you need to unload your data. You should be able to create your new tablespaces to the correct size. I believe you plan on

Re: RMAN error registering database

Danny, Many RMAN errors will show a hit when searching Metalink. I found the following related document on Metalink when I searched with the RMAN-10031 error: fact: Oracle Enterprise Edtion 8.1.7 fact: Recovery Manager (RMAN) symptom: Unable to register a database

RE: partition tables

Jack, Thanks for these great hints. I have not seen lots of these before and they have given me lots of ideas. Here's a question that you've induced: How do you determine what a good value is for INITRANS? What are the downsides of setting it too high? Thanks, Cherie Machler Oracle DBA

RE: Statistical sampling and representative stats collection

Larry, Thanks for taking the time to write this lengthy reply. I've never seen this particular information on histograms anywhere. This has been very informative. Cherie Machler Oracle DBA Gelco Information Network

Re: Legato oracle module.

Mike, We did create another RMAN catalog on a small alternate host. We back up our main catalog. Yes, it is redundant but if you're going to go to all the work of getting RMAN working, you might as well go the extra distance and do this so you're covered. Cherie Machler Oracle DBA Gelco

RE: Statistical sampling and representative stats collection

I have had some really good experiences with using histograms. They didn't always produce the improvements that I expected but in many cases, I saw 10 times, 100 times, or even 1,000 faster execution times after adding histograms. I don't have the specifics, but these were cases where the data

Re: Diagnose Slow System

Tim, I'm curious why you say that utlbstat/utlestat should be run from svrmgrl and not sqlplus. I've not heard/read that before. Cherie Machler Oracle DBA Gelco Information Network

RE: I/O EVENTS

Cary, Thanks for this meaty example and especially for the analysis afterwards. Real-life examples like this are so much more revealing and insightful than any amount of theoretical discussion. We could learn so much if once a month someone would post the statistics and output from their

Re: Statistical sampling and representative stats collection

Jack, What version are you on? Are you able to utilize the gather_stale option. That way you would not only be optimizing the amount you are estimating but the interval between analyzing. Of course, that option of DBMS_STATS is not available on older versions. Cherie Machler Oracle DBA

Re: Statistical sampling and representative stats collection

Jack, Isn't the problem with this concept that it doesn't take into consideration how skewed the data is? Statistically significance would be relevant to perfectly distributed data but wouldn't you need a higher percentage of data for significance in more highly skewed data? Just something

Outstanding bugs in 8.1.7.2

I've been hunting around OTN and can't remember where to find this. I'd like to see a list of all outstanding bugs that are present in version 8.1.7.2 of EE. Is there one convenient place to find these or am I going to have to compile a list from multiple other versions. Thanks, Cherie

RE: Outstanding bugs in 8.1.7.2

Is there anyway to download the readme file without downloading the whole product set for 8.1.7.3? Thanks, Cherie Jesse, Rich

RE: Dba_tab_modifications question

Chris, Do you know anything about monitoring and gathering stale statistics on table partitions? I am able to monitor and gather stale statistics on partitioned tables at the table level but don't seem to be doing so at the partition level. I can't figure out how to alter my partitions to put

RE: reset database

We've found that when we use RMAN to recover an exact clone of a database with the same name as the original to an alternate host (perhaps as test database or fallback database while upgrading) we have to reset the database in the RMAN catalog if both databases are in the same RMAN catalog. This

RE: Dba_tab_modifications question

Chris, Actually, sometimes I want to be able to just gather statistics for a single stale partition. In my date-based partitioning, usually only the most recent partition has data changes in it. The older partitions do not change at all. It would surely be nice to monitor on a

Unsetting OPTIMAL in rollback segments

We are getting the following error in our alert log for a database where we are doing some unusually large transactions: Wed May 15 03:26:22 2002 Failure to extend rollback segment 27 because of 1581 condition FULL status of rollback segment 27 set. On Metalink I've found a couple of

Re: Unsetting OPTIMAL in rollback segments

Rob, Just what the Dr. ordered. Many thanks, Cherie Robert Pegram

Re: Oracle Diagnostic and Tuning Packs

Peter, I guess it depends on whether you have any other monitoring and/or performance tuning tools that you use.If you do, there may be quite a bit of duplication of features provided.If you don't have any other automated tools that provide performance monitoring and tuning

Re: Datawarehousing help

Rachel, Are you licensed for Oracle Designer? We put the estimated row counts for each table into Designer and it produces a nice report showing sizing estimates per table or index, tablespace, and database. Pretty painless. You still need to add on extra space for archives, exports,

YAPP / Oraperf / STATSPACK - Optimal SQL linesize and pagesize

When I try to use YAPP to analyze my STATSPACK report, I get an error message which states that my SQL is wrapping or too long. It says that I should set my pagesize and linesize correctly. The report looks o.k. to the naked eye. I looked all over their website but I don't see anywhere that

Re: ERD generation tool

Both Designer and ERwin can reverse engineer enough information from an existing database to generate an ERD. Cherie Machler Oracle DBA Gelco Information Network

RE: No Rows Deadlock and PCTFREE, INITRANS on 8.1.7.2

Waleed, I will run a script and check for missing indexes. Thanks, Cherie Khedr,

RE: What block size are you using for your new 9i data warehouse?

Gaja, Thanks for this enlightening and helpful reply. Cherie Gaja Krishna

Re: index status misleading?

Gene, Perhaps the indexes weren't owned by MYOWNER. Accidentally owned by a different userid? Cherie Machler Oracle DBA Gelco Information Network Gurelei

Re: index status misleading?

Is this a partitioned index? If so, then there is no status shown in dba_indexes for that index. You'll have to look for index status in dba_ind_partitions. Cherie

No Rows Deadlock and PCTFREE, INITRANS on 8.1.7.2

We are testing an upgrade of our warehouse from 8.0.4 to 8.1.7.2. While testing our nightly load job that runs in two simultaneous streams (ascending and descending), we have been getting deadlock errors. The trace file shows the the rows waited on are no rows. In response to a TAR I opened

What block size are you using for your new 9i data warehouse?

We are building a new version 9.0.1 data warehouse on Sun Solaris 2.6 migrating to Solaris 2.8. We will be using striped disk that is striped using Veritas Volume Manager on EMC disk. The datawarehouse will be about 200 Gig. It will be written to throughout the day. To this point, almost

RE: No Rows Deadlock and PCTFREE, INITRANS on 8.1.7.2

Stephane, No, this is not an IOT table. This is a TX type of lock on insert. Thanks for your reply. Cherie Stephane

RE: No Rows Deadlock and PCTFREE, INITRANS on 8.1.7.2

Waleed, There used to be a single process that loaded the data. It ran too long and the application owners split the job out into two process. One started at one end and loaded data in ascending order and the other started at the other end and loaded in descending order. Both jobs run at

Re: No Rows Deadlock and PCTFREE, INITRANS on 8.1.7.2

Jonathan, Following is section of the trace file generated. It is a TX lock. I'm not sure whether it is mode 4. How can I tell? We upgraded using the migration script, not export/import. During the nightly loads we have always dropped the indexes on the foreign keys on this large fact

RE: Reorganize table

Is the table partitioned? Also, do you have any money for reorg tools? What sort of maintenance window do you have to complete this reorg? Can you negotiate for a larger-than-normal maintenance window? Cherie

RE: SQL statement with hints or without hints

Raj, I don't remember what version you said you were using but you might want to investigate stored outlines. If you have a full-size test environment, you could save away the current RBO-based execution plans, switch to CBO and gradually get rid of your RBO-based stored outlines by tuning

RE: SQL Tuning - How to avoid TOCHAR function against a date

Ron, That's an idea. Easy to implement and test. I'll give it a try tonight to see if it helps. It is a small table. Cherie Ron Rogers

SQL Tuning - How to avoid TOCHAR function against a date

I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based

RE: SQL Tuning - How to avoid TOCHAR function against a date

John, I will test it out. Thanks for your helpful recommendation. Cherie John Hallas

RE: SQL Tuning - How to avoid TOCHAR function against a date

Iain, I will do some tests. Theoretically, yes, a range scan should be better than a full table scan. Thanks for your helpful recommendation. Cherie

RE: SQL Tuning - How to avoid TOCHAR function against a date

Tom, It is probably too late for this original design but it is not too late for a new data warehouse that is in development. Jared has made a recommendation for better date columns that may help eliminate these problems. I have forwarded that table design on to the application owner.

Re: Re[2]: SQL Tuning - How to avoid TOCHAR function against a date

Bob, Thanks for your reply. I am testing this afternoon. Cherie Robert

Re: Long-running SQL

Stephane, Thanks for your reply. I am going to check into setting the nulls with the application owner to see if that is an acceptable solution. I am unable to create a function-based index as this is a version 8.0.4 database. Thanks, Cherie

Long-running SQL

We have a statement that I feel takes too long to run in a nightly data load. The table it runs against has 386,000 records. It runs for about 10 seconds on average. We're only loading about 50,000 records a night but this statement is running during the majority of the 9-hour load time.

RE: Long-running SQL

Jerome, Yes, this data is not normalized. You are correct. I am not sure why the table was denormalized like this but it is not an option to change the underlying table at this point in time. The application is in the process of being redesigned but in the meantime, I have to keep the

RE: Long-running SQL

Lisa, I'm not positive that it is actually using the index. This load ran last night so I was not able to trace as it happened. Following is the explain plan that Precise monitoring tool states that it should have used: Count (stopkey) Table access (by index rowid)

RE: Long-running SQL

Dennis, When you partitioned the table, how did you determine what the limits on the partition should be? In this case, there are not always values for the other columns of the key so I'm not sure if we could partition on the full key. Cherie

RE: Long-running SQL

Robert, Thanks for your informative reply. Cherie -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists

Impact of striping on index range scans?

What is the impact of striping on index range scans? Since we striped our disk that our indexes are located on, we have been showing more than thirty times the number of rows read during index range scans. How does disk striping (in our case using Veritas volume manager) affect range scans?

Deadlock issues in 8.1.7.2

Is anyone having deadlocking issues with the 8.1.7.2 release of Oracle? If you are, how serious are these errors? What are the consequences of the deadlock error messages.How long does it actually take to clean up the deadlocks? Does one of the transactions roll back? Does it then reissue

Difference in number of records returned using same SQL statement and same

List, There is a report that I tuned last November. It worked great for months. Then a couple of weeks ago we started moving some of our indexes to disks that had been logically striped with Veritas volume manager.I rebuilt all of the indexes for a couple of partitioned tables and

Re: local partition index question

Yes, that is true. I've seen that to be the case in our warehouse database. Cherie Machler Oracle DBA Gelco Information Network oracle dba

Re: Cost vs Rule

Bill, There are some good notes on Metalink about why CBO avoids using an index when one is available. I'll see if I can find a note number but you might try searching on index and optimizer. Sometimes you need to modify the code in order to get better performance under CBO. Can you test

  1   2   3   >