Home » Other » General » PERFSTATS (Oracle 10.2)
PERFSTATS [message #490203] Fri, 21 January 2011 14:54 Go to next message
chuckstoker
Messages: 10
Registered: January 2011
Junior Member
I have a copy of a VERY LARGE, OLTP database that is in READONLY mode. Logs are applied nightly to resync. This database, although not designed for it, is used to create data warehouse type reports. I need to determine performance metrics but am running into roadblocks due to it being readonly. I can really just gather info from the v$ tables.

AWR is obviously out of the question, but I was wondering if it was possible to modify the STATSPACK procedures to gather metrics and write them to a read/write database via a database link. Anybody ever try or am I way off base here?

Report message to a moderator

Re: PERFSTATS [message #490204 is a reply to message #490203] Fri, 21 January 2011 15:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
Writing through a database link means starting a read write transaction which is impossible in read only opened database.

Regards
Michel

Report message to a moderator

Re: PERFSTATS [message #490238 is a reply to message #490203] Sat, 22 January 2011 12:43 Go to previous messageGo to next message
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
Senior Member
Here's an idea: I don't know if it will work, because I can't test. Could you use the old utlbstat/utlestat scripts? Run them on a read/write database, edited so that you query the v$ views in your read only database through a database link?

Report message to a moderator

Re: PERFSTATS [message #490519 is a reply to message #490204] Tue, 25 January 2011 07:28 Go to previous messageGo to next message
chuckstoker
Messages: 10
Registered: January 2011
Junior Member
Thanks Michael.
Makes sense, I did not think of it at the transaction level

Report message to a moderator

Re: PERFSTATS [message #490520 is a reply to message #490519] Tue, 25 January 2011 07:29 Go to previous messageGo to next message
chuckstoker
Messages: 10
Registered: January 2011
Junior Member
utlb/utle was brought up as an alternative. I believe they have tried that in the past, but it really didn't give the stats they were looking for.(I'm the new guy on campus). I'll have to take a look at that alternative. thanks

Report message to a moderator

Re: PERFSTATS [message #490537 is a reply to message #490519] Tue, 25 January 2011 08:59 Go to previous messageGo to next message
chuckstoker
Messages: 10
Registered: January 2011
Junior Member
wait a minute..., I'm re-thinking this reply..,

I was proposing running the spcreate procedures in a R/W database and modifying it to read the V$ tables of the READONLY database via a dblink. Why would that create a r/w transaction in the readonly database? I still think it might work.

Report message to a moderator

Re: PERFSTATS [message #490559 is a reply to message #490537] Tue, 25 January 2011 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either a transaction is read only, either it is read write.
I doubt you can have it read write in a site and read only in another one but check it.

Regards
Michel

Report message to a moderator

Re: PERFSTATS [message #490560 is a reply to message #490559] Tue, 25 January 2011 10:30 Go to previous messageGo to next message
chuckstoker
Messages: 10
Registered: January 2011
Junior Member
You can.
This definitely works across a database link:

create table chuck as
select table_name from dba_tables@db_link where owner = 'xx';

I'm "guessing" I could alter the PERFSTAT.STATSPACK package to select information from the V$ tables across the database link as well.

Report message to a moderator

Re: PERFSTATS [message #490569 is a reply to message #490560] Tue, 25 January 2011 11:56 Go to previous message
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CREATE TABLE is a DDL, so internal transactions that are not managed in the same way. It is NOT an INSERT SELECT.
As I said, check it.

Regards
Michel

Report message to a moderator

Previous Topic: If my system can use timesten
Next Topic: how does stored procedure wok between timesten and oracle database
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 18 17:24:26 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.