Re: question on EXPLAIN_PLAN

2002-02-07 Thread kevin wang 

  Hi, Rafiq
  Thank you for your advise, and thanks everyone, the handsome guys, reply
me.
  You are right, it seems like a High Water Mark problem, after lots of
other try,
  finally I drop that table and re-create that table, copy data, build PK
index,
  analyzed in same way as before, and test the performance,
  the SQL statement is as fast as we expected.
  The "select count(*) from table_name" is still slower than the "good guy",
but much faster than before.
  As a problem, it is fixed now.

  But I still don't understand how can the stupid HWM make such a slow-down
on performance?
  I did lots of tests, I am sure there is nothing to do with
analyze,SGA,session_wait,resource.
  On both two databases, that table and its PK index use one extent on
different tablespace on different datafile.
  Actually, the table in the good performance database has the same HWM as
the bad performance buy.
  I mean, re-create table can fix the problem, but, the problem is not only
with the HWM.
  I will do some further investigation.

  thanks a lot,

  Kevin Wang



- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, February 06, 2002 2:18 PM


> Try to reduce High Water Mark on your table by
> creating as select a backup table
> truncate original table
> insert into original table from backup table...
>
> This is based on your info for full table scan...Please also rebuild
indexes
> on that table and analyze table if you are on COST BASED..
>
> HTH,
> Regards
> Rafiq
>
>
>
>
> Reply-To: [EMAIL PROTECTED]
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Date: Wed, 06 Feb 2002 13:45:44 -0800
>
> Hi, Bill
>
> You are right, there is really something to do with the index.
> I drop&re-created that PK index, it became much faster, but after 5
minutes,
> it became slow again, and I am sure no records insert/delete/update
> happened. I drop&re-created that indexes again, nothing happened this
time,
> it is still slow!
> It is really weird!
>
> And I found on the bad performance database,  a simplest query: select
> count(*) from table_namewill take 4 seconds!
> the explain_plan said it used cost-optimizer, using fast_full_index_scan
on
> that PK index,
> what should I do with the index? I already tried to re-created the PK
index.
>
> The bad performance database is on a super-box, 4 CPUs and more memory.
the
> good performance one is on a normal box.
>   And the table and index is analyzed at same way.
>
>   thanks a lot for your reply.
>
>
>Kevin Wang
>
>
>
>- Original Message -
>From: Bill Zakrzewski
>To: kevin wang
>Sent: Wednesday, February 06, 2002 12:38 PM
>Subject: Re: question on EXPLAIN_PLAN
>
>
>Kevin,
>
>Have these indexes been rebuilt recently?  If not, you may want to
> consider rebuilding the indexes.  Not sure if this is your issue, but if
you
> have inserted/deleted records from your tables over time, the index levels
> may have grown as well.
>
>~
>Bill Zakrzewski
>Senior Consultant
>Intactus Technology, Inc.
>
>  - Original Message -
>  From: kevin wang
>  To: LazyDBA.com Discussion
>  Sent: Wednesday, February 06, 2002 2:43 PM
>  Subject: question on EXPLAIN_PLAN
>
>
>Hi, guys
>
>The problem belows is really make me confused and gave me big
> trouble, is there someone can give me some hlep?
>
>I have two databses, same version(oracle 8.1.6),same O/S(win2000),
> same schema structure, different data(but small difference of size).
>and even exactly same explain_plan of my sql query.
>But on one database, the cardinality of one PK index access upon
one
> table is 27(cost=2,card=27,bytes=756) (table rows 263758)
>and the other is 11706 (cost=3,card=11706,bytes=199002)( table
> rows 351173).
>so, on one DB the sql query took 300ms, one the other, it took 5
> seconds!
>
>Any advise is highly appreciated.
>
>thanks,
>
>Kevin Wang
>Database Administrator
>Vivonet Canada Inc.
>
>
>
>
>
>
>
> MOHAMMAD RAFIQ
>
>
> _
> Chat with friends online, try MSN Messenger: http://messenger.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mohammad Rafiq
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
>

Re: question on EXPLAIN_PLAN

2002-02-06 Thread kevin wang 

Yes, Mike, I analyzed the table and PK index on the two databases at the
same way,
it seems that there is something wrong with the PK index,
the" select count(*) from table_name " query took 4 seconds, and only 335199
rows atcually.
it use fast_full_index scan of the PK index, and I re-created the PK index,
same thing. that's very bad.

it is Oracle 8.1.6 on win2000.

thanks for reply.

Kevin Wang


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, February 06, 2002 1:55 PM


> Kevin,
>
> Have you analyzed the tables on both databases? Card is the CBO's estimate
> of the number of rows it will process.
>
> Mike
>
>
>
> >From: "kevin wang" <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> >Subject: question on EXPLAIN_PLAN
> >Date: Wed, 06 Feb 2002 11:43:38 -0800
> >
> >   Hi, guys
> >
> >   The problem belows is really make me confused and gave me big trouble,
> >is there someone can give me some hlep?
> >
> >   I have two databses, same version(oracle 8.1.6),same O/S(win2000),
same
> >schema structure, different data(but small difference of size).
> >   and even exactly same explain_plan of my sql query.
> >   But on one database, the cardinality of one PK index access upon one
> >table is 27(cost=2,card=27,bytes=756) (table rows 263758)
> >   and the other is 11706 (cost=3,card=11706,bytes=199002)( table
rows
> >351173).
> >   so, on one DB the sql query took 300ms, one the other, it took 5
> >seconds!
> >
> >   Any advise is highly appreciated.
> >
> >   thanks,
> >
> >   Kevin Wang
> >   Database Administrator
> >   Vivonet Canada Inc.
> >
> >
> >
>
>
> _
> Chat with friends online, try MSN Messenger: http://messenger.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mike Killough
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: kevin wang
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: question on EXPLAIN_PLAN

2002-02-06 Thread kevin wang 



Hi, Bill
 
You are right, there is really something to do with 
the index. 

I drop&re-created that PK index, it became much 
faster, but after 5 minutes, it became slow again, and I am sure no records 
insert/delete/update happened. I drop&re-created that indexes again, nothing 
happened this time, it is still slow!
It is really weird!    
 
And I found on the bad performance database,  
a simplest query: select count(*) from table_name    will take 4 
seconds!
the explain_plan said it used cost-optimizer, using 
fast_full_index_scan on that PK index, 
what should I do with the index? I already tried to 
re-created the PK index.
 
The bad performance database is on a super-box, 4 
CPUs and more memory. the good performance one is on a normal 
box.
 And the table and index is analyzed at 
same way.
 
 thanks a lot for your 
reply.
 
 
  Kevin Wang  
 

  - Original Message - 
  From: 
  Bill Zakrzewski 
  
  To: kevin wang 
  Sent: Wednesday, February 06, 2002 12:38 
  PM
  Subject: Re: question on 
  EXPLAIN_PLAN
  
  Kevin,
   
      Have these indexes been 
  rebuilt recently?  If not, you may want to consider rebuilding the 
  indexes.  Not sure if this is your issue, but if you have 
  inserted/deleted records from your tables over time, the index levels may have 
  grown as well.
   
  ~Bill ZakrzewskiSenior 
  ConsultantIntactus Technology, Inc.
  
- Original Message - 
From: 
    kevin wang 

To: LazyDBA.com Discussion 
Sent: Wednesday, February 06, 2002 2:43 
PM
Subject: question on EXPLAIN_PLAN

  Hi, guys
 
  The problem belows is really make me 
confused and gave me big trouble, is there someone can give me some 
hlep?
  
  I have two databses, same version(oracle 
8.1.6),same O/S(win2000), same schema structure, different data(but 
small difference of size).
  and even exactly 
same explain_plan of my sql query.  
  But on one database, the cardinality of 
one PK index access upon one table is 27(cost=2,card=27,bytes=756) (table 
rows 263758)
  and the other is 11706 
(cost=3,card=11706,bytes=199002)( table rows 351173).  
  so, on one DB the sql query took 300ms, 
one the other, it took 5 seconds!
 
  Any advise is highly 
appreciated.
 
  thanks, 
 
      Kevin Wang  Database 
Administrator  Vivonet Canada Inc.  
 

question on EXPLAIN_PLAN

2002-02-06 Thread kevin wang 



  Hi, guys
 
  The problem belows is really make me 
confused and gave me big trouble, is there someone can give me some 
hlep?
  
  I have two databses, same version(oracle 
8.1.6),same O/S(win2000), same schema structure, different data(but small 
difference of size).
  and even exactly same 
explain_plan of my sql query.  
  But on one database, the cardinality of one 
PK index access upon one table is 27(cost=2,card=27,bytes=756) (table rows 
263758)
  and the other is 11706 
(cost=3,card=11706,bytes=199002)( table rows 351173).  
  so, on one DB the sql query took 300ms, one 
the other, it took 5 seconds!
 
  Any advise is highly 
appreciated.
 
  thanks, 
 
  Kevin Wang  Database 
Administrator  Vivonet Canada Inc.  
 

Re: limit user CPU usage

2002-01-15 Thread kevin wang 

Thanks, Ron, I will try it.

Kevin  Wang


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, January 15, 2002 11:55 AM


Kevin,
You could try creating a profile with the cpu_per_call and
cpu_per_session set ta a particular value. It will limit the amount of
time in hundredths of a second to the user assigned to the profile. I
woul experiment with the values before I assigned it to a user. The
manual does not say it will act as a choke on cpu usage other than
time.
ROR mª¿ªm

>>> [EMAIL PROTECTED] 01/15/02 01:10PM >>>
Hi, Guys,

Is there some parameters or user-profile prometers in  Oracle can limit
user CPU usage?
I mean, make specific user or all users use less CPU, I has a SQL query
that use 100% CPU.
I know the SQL is bad, but before developer change it, Is there
something I can do?
Any suggestion will be highly appreciated.
My environment is Oracle8.1.6 on Win2000.

Thanks,

Kevin  Wang



--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: kevin wang
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

limit user CPU usage

2002-01-15 Thread kevin wang 




Hi, Guys,
 
Is there some parameters or user-profile prometers 
in  Oracle can limit user CPU usage?
I mean, make specific user or all users use less 
CPU, I has a SQL query that use 100% CPU.
I know the SQL is bad, but before developer 
change it, Is there something I can do?
Any suggestion will be highly 
appreciated.
My environment is Oracle8.1.6 on 
Win2000.
 
Thanks,
 
Kevin  Wang
 

Re: How to backup MTS database with RMAN

2001-12-21 Thread kevin wang 
Title: How to backup MTS database with RMAN




Hi, Hulmet,
I tried.  To use RMAN on MTS database 
environment, both database server machine and the RMAN machine(the machine you 
run RMAN on it) should use dedicated server mode instead of shared mode. It 
means the tnsnames.ora file on these two machines(or one machine) 
should
contain SERVER=DEDICATED line. Maybe only 
application box can use shared-mode(server=shared).
example: 
SHAGGY =  (DESCRIPTION 
=    (ADDRESS_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.25)(PORT = 
1521))    )    (CONNECT_DATA 
=  (SERVICE_NAME = 
SHAGGY)  (server = 
dedicated)    )  )
 
Kevin Wang

  - Original Message - 
  From: 
  Daiminger, Helmut 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Wednesday, December 19, 2001 5:25 
  AM
  Subject: How to backup MTS database with 
  RMAN
  
  Hi! 
  Since I'm pretty new to RMAN I have a rather basic 
  question: is it possible to backup databases running in multithreaded server 
  mode with RMAN? Or does this have to be dedicated server mode?
  $ rman target sys/@kpmgi rcvcat rman/x@admserv 
  Recovery Manager: Release 8.1.7.2.0 - 
  Production 
  RMAN-06005: connected to target database: 
  KPMGI (DBID=3995384462) RMAN-06008: 
  connected to recovery catalog database 
  RMAN> register database; 
  RMAN-03022: compiling command: 
  register RMAN-03023: executing 
  command: register RMAN-08006: 
  database registered in recovery catalog RMAN-03023: executing command: full resync RMAN-03026: error recovery releasing channel 
  resources RMAN-00571: 
  === RMAN-00569: === ERROR MESSAGE STACK 
  FOLLOWS === RMAN-00571: 
  === RMAN-07005: error during channel cleanup 
  RMAN-07004: unhandled exception during 
  command execution on channel default RMAN-10035: exception raised in RPC: ORA-19550: cannot use 
  backup/restore functions while using dispatcher RMAN-10031: ORA-19550 occurred during call to 
  DBMS_BACKUP_RESTORE.CFILEUSECURRENT RMAN-03008: error while performing automatic resync of recovery 
  catalog RMAN-07004: unhandled 
  exception during command execution on channel default RMAN-10035: exception raised in RPC: ORA-19550: 
  cannot use backup/restore functions while using dispatcher RMAN-10031: ORA-19550 occurred during call to 
  DBMS_BACKUP_RESTORE.CFILEMAKEANDUSESNAPSHOT 
  Do I just have to take out mts_dispatchers = 
  "(protocol=TCP)" from the init.ora file? 
  This is 8.1.7.2.0 on Sun Solaris. 
  Thanks, Helmut

Re: How to backup MTS database with RMAN

2001-12-19 Thread kevin wang 
Title: How to backup MTS database with RMAN



Hi, Hulmet,
I tried.  To use RMAN on MTS database 
environment, both database server machine and the RMAN machine(the machine you 
run RMAN on it) should use dedicated server mode instead of shared mode. It 
means the tnsnames.ora file on these two machines(or one machine) 
should
contain SERVER=DEDICATED line. Maybe only 
application box can use shared-mode(server=shared).
example: 
SHAGGY =  (DESCRIPTION 
=    (ADDRESS_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.25)(PORT = 
1521))    )    (CONNECT_DATA 
=  (SERVICE_NAME = 
SHAGGY)  (server = 
dedicated)    )  )
 
Kevin Wang
 
 
 

  - Original Message - 
  From: 
  Daiminger, Helmut 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Wednesday, December 19, 2001 5:25 
  AM
  Subject: How to backup MTS database with 
  RMAN
  
  Hi! 
  Since I'm pretty new to RMAN I have a rather basic 
  question: is it possible to backup databases running in multithreaded server 
  mode with RMAN? Or does this have to be dedicated server mode?
  $ rman target sys/@kpmgi rcvcat rman/x@admserv 
  Recovery Manager: Release 8.1.7.2.0 - 
  Production 
  RMAN-06005: connected to target database: 
  KPMGI (DBID=3995384462) RMAN-06008: 
  connected to recovery catalog database 
  RMAN> register database; 
  RMAN-03022: compiling command: 
  register RMAN-03023: executing 
  command: register RMAN-08006: 
  database registered in recovery catalog RMAN-03023: executing command: full resync RMAN-03026: error recovery releasing channel 
  resources RMAN-00571: 
  === RMAN-00569: === ERROR MESSAGE STACK 
  FOLLOWS === RMAN-00571: 
  === RMAN-07005: error during channel cleanup 
  RMAN-07004: unhandled exception during 
  command execution on channel default RMAN-10035: exception raised in RPC: ORA-19550: cannot use 
  backup/restore functions while using dispatcher RMAN-10031: ORA-19550 occurred during call to 
  DBMS_BACKUP_RESTORE.CFILEUSECURRENT RMAN-03008: error while performing automatic resync of recovery 
  catalog RMAN-07004: unhandled 
  exception during command execution on channel default RMAN-10035: exception raised in RPC: ORA-19550: 
  cannot use backup/restore functions while using dispatcher RMAN-10031: ORA-19550 occurred during call to 
  DBMS_BACKUP_RESTORE.CFILEMAKEANDUSESNAPSHOT 
  Do I just have to take out mts_dispatchers = 
  "(protocol=TCP)" from the init.ora file? 
  This is 8.1.7.2.0 on Sun Solaris. 
  Thanks, Helmut