RE: Removing duplicate rows from a table !

Assuming that we have duplicate rows ( NOT duplicate keys ), you may try this. I have borrowed this from a friend of mine. We have used it successfully. Each of the columns in the table are included as predicates to confirm that we are deleting only the duplicate rows. delete from

RE: Huge optimization costs with 9.2

Yes. You have to bounce the database for this to take effect. -Original Message- Joan Hsieh Sent: Thursday, October 02, 2003 10:10 AM To: Multiple recipients of list ORACLE-L Govind, I will test it out today and post the updates, I should set optimize_feature_enable back to 9.2.0

RE: Huge optimization costs with 9.2

Joan, Can you post the query with the plan in 8.1.7 and 9.2; We ran into certain types of queries that had totally different execution plans and got work-arounds. Thanks, Govind -Original Message- Joan Hsieh Sent: Wednesday, October 01, 2003 11:55 AM To: Multiple recipients of list

RE: Huge optimization costs with 9.2

Joan, Can you post the query in question? Thanks, GOvind -Original Message- Joan Hsieh Sent: Wednesday, October 01, 2003 2:00 PM To: Multiple recipients of list ORACLE-L Malden, They all same. I didn't change any parameters after upgrade. The difference in the plans are one used all

RE: Huge optimization costs with 9.2

Can you try to generate the query plan with these settings? These are the 9i CBO Hidden parameters to generate 8.1.7 like query plans. alter session set _UNNEST_SUBQUERY = FALSE; alter session set _ORDERED_NESTED_LOOP = FALSE; alter session set _ALWAYS_SEMI_JOIN = off; explain plan for

RE: Problems creading a Index

Run the following sql statement to see whether there are duplicate entries. Chances are that you will find duplicates hence you get the above error. You may choose to remove the duplicates or create a non-unique index otherwise. select COMPANY, INVC_PREFIX, INVC_NUMBER,

RE: Problems creading a Index

Run the following sql statement to see whether there are duplicate entries. Chances are that you will find duplicates hence you get the above error. You may choose to remove the duplicates or create a non-unique index otherwise. select COMPANY, INVC_PREFIX, INVC_NUMBER, ITEM, count(*)

RE: DBMS_STATS and CBO

Prasad, We ran into the same problem when we did FOR ALL INDEXED COLUMNS using DBMS_STATS. Then we changed it to run against FOR ALL COLUMNS SIZE 1. Then CBO started to use the indexes. execute dbms_stats.gather_table_stats('owner','table',NULL,30,TRUE, 'FOR ALL COLUMNS SIZE

RE: DBMS_STATS and CBO

. Actually, I did 'FOR ALL COLUMNS SIZE 1' only. Here is the SQL I used : EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ('DISCLM',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',4,'ALL',TRUE); Best Regards, Prasad 860 843 8377 Govind.Arumugam@ alltel.com

Oracle user groups in Bangalore, INDIA

List gurus, Are there any oracle user groups in Bangalore, INDIA? My nephew is an upcoming Oracle DBA. He would like to participate in user meetings relevant to Oracle in Bangalore area. Any help will be greatly appreciated. Thanks, Govind -- Please see the official ORACLE-L FAQ:

RE: Parallel Query Server died

Shuan, Please let me know whether you are on 9i. When was the database bounced cleanly? After shutting down the oracle instance, check to make sure that no other orphaned background processes are present. ie. ps -ef | grep -i oracle should returnnothing. We got burntby this ORA-12805

RE: Listener Hangs

This is how you can cleanup listener.log w/o taking the listener down. You can copy the listener.log to an archive location, then do the following: cat "/dev/null" and redirect to listener.log file. This will zero the current file and allow other updates to the log from the listener to

Oracle 9.2.0.2 bugs

List, We recently upgraded our Production system to 9i and gotburnt by these bugs. Installed Patch List:2785282 [ Base Bug(s): 2442125 ] Bug # 2808431WRONG RESULTS FOR PARALLEL QUERY WITH HASH AND INDEX JOIN Bug # 2783229 Base bug : 2442125 INCORRECT DATA RETURNED FROM DATABASE WHEN

How to identify full table scans?

List, We use the following script to identify recent full table scans or full index scans. This result set will be used to identify the potential queries that could benefit by creating any new indexes or modify the existing index structure as needed. Our intention is to run this query against

RE: index rebuilding performance vs sort_area_size

We have not seen any performance gains after setting the sort_area_size in excess of 50Mb. We have set this as a standard in our re-indexing scripts to set this to 50Mb maximum. HTH. -Original Message- Sent: Monday, January 13, 2003 3:00 PM To: Multiple recipients of list ORACLE-L

RE: How to identify full table scans?

Title: RE: How to identify full table scans? This helps to identify the queries that could be tunedfor LIO and/or PIO from a SQL Tuning perspective. We can give this list to the development or application teams so that they could independently work off this list (hopefully!).

RE: Prevent FTS

Please check Jonathan Lewis book 'Practice Oracle 8i'. This book provides lot more details about these two optimizer related parameters that could significantly affect FTS situations. optimizer_index_caching optimizer_index_cost_adj The general 'harmless' guidelines could be setting 10 to

Parallel degree on tables and indexes

List, Is there a 'magic' number for num_rows that you use in order to set the parallel degree on tables and/or indexes? We were told to use parallel degree of 4 for tables having more than 100,000 rows. Some of the on-line queries are running in parallel mode thus making batch jobs to go

RE: New course for 9i - Dumps/Crashes from Oracle University

Title: RE: New course for 9i - Dumps/Crashes from Oracle University Oracle Corporation is conducting these 3 highly technical seminars. Each one of them is a full day class at a cost of $500 per class. http://education.oracle.com/web_prod-plq-dad/plsql/show_desc.redirect?dc=D12856GC10

RE: STATSPACK

STATSPACK related can be found under $ORACLE_HOME/rdbms/admin directory in these documents. Version 8.1.6 or prior ( must be 8i ) $ORACLE_HOME/rdbms/admin/statspack.doc Version 8.1.7 $ORACLE_HOME/rdbms/admin/spdoc.txt Prior to 8i: ( No statspack! ) $ORACLE_HOME/rdbms/admin/utlbstat.sql

BLEVEL on bit-mapped indexes

According tooracle documentation or metalink sources,if the BLEVEL were to be more than 4, it is recommended to rebuild the index. select index_name, blevel,decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL', 2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OKfrom dba_indexeswhere owner

RE: BLEVEL on bit-mapped indexes

According tooracle documentation or metalink sources,if the BLEVEL were to be more than 4, it is recommended to rebuild the index. select index_name, blevel,decode(blevel,0,'OK BLEVEL',1,'OK BLEVEL', 2,'OK BLEVEL',3,'OK BLEVEL',4,'OK BLEVEL','BLEVEL HIGH') OKfrom dba_indexeswhere

RE: Single-task message waits

This is what I got from metalink for an earlier case. 'single-task message' waits are Waiting for a client message, but in single task mode. Single task mode is when a process communicates directly with the SGA, as opposed to communicating through a background shadow process. even though you

RE: ORA-1653: unable to extend table - Why?

My experience yesterday was that dropping an index and trying to rebuild the same index failed ( even after coalescing the tablespace) since we need to wait for SMON to clean up the extents to make them available. I don't know how we make SMON process to coalesce the free space faster enough(

RE: Partition Info

You may use the following query will identify the 'highest' partition by date range. select partition_name, partition_position , high_value, tablespace_name from dba_tab_partitions where table_name = 'table_name' and table_owner = 'owner' and partition_position = ( select max(partition_position)

RE: Database up longer that host?

You may use following query will give you the uptime in hours and in minutes. select sysdate, startup_time, round( (sysdate - startup_time) *24*60 ,0 ) uptime_in_minutes, round( (sysdate - startup_time) *24 ,0) uptime_in_hours from v$instance SYSDATESTARTUP_TIME UPTIME_IN_MINUTES

RE: Table Locks

We run the following script every 20 minutes to identify the pending transactions ( to be committed) and notify the appropriate application group (online or batch ) to take action in consultation with the DBA group. We filter this by username since we have some convention for batch programs and