select catalog role

Printable View

08-29-2001, 03:19 PM
padma

Hi,

Is there any bad implication of assigning a select_catalog_role to a development dba to a production database. Basically, my production dba is not willing to assign this role to me as it involves v$ views. As a development dba, I am supposed to monitor the users and the performance of the sql statements etc, (not all the time. But if there was a problem.)

Please let me know if any of you know about any problem assigning this role to someone who does not have dba privilege on the database.

Any suggestions / solutions would be appreciated.

Thanks
08-29-2001, 03:29 PM
omegamark

The Senior DBA should have full confidence on you.But once you are appointed as DBA whether it is production database or development database I don't see a reason why are not give a DBA role (system userid).you should be asking the userid which has the DBA role .If not i will not logon to that production database itself as a normal user itself.
08-29-2001, 03:34 PM
raghud

We never had a problem . Some of our developers they do have the access , some are working on query optimization so they need to see some parameters and some v$view's . Basically they can't alter or recreate the system views or insert any rows into the system views ..Ask your Prod DBA for atleast one reason .. Being a supporting person you should get it ...
08-29-2001, 04:46 PM
padma

select catalog role

My production dba says assigning me 'select_catalog_role' could have potential performance issues on the database. Has anyone ever heard of such issues???
08-29-2001, 05:16 PM
sambavan

Hmmm, Interesting!!!

Did you make the How/Why/What question to him? Most of them uses the internal views. :p

Sam
08-30-2001, 01:39 AM
julian

Quote:

Originally posted by padma
Basically, my production dba is not willing to assign this role to me as it involves v$ views

That is not a correct work to do the job! Your Senior DBA has a lot to learn. I have full confidence in everyone working with me and I would never reject such a thing.
08-30-2001, 08:19 AM
jmodic

Re: select catalog role

Quote:

Originally posted by padma
My production dba says assigning me 'select_catalog_role' could have potential performance issues on the database. Has anyone ever heard of such issues???

Not that I would act the same as your DBA or that I'm on his side, I would say that the reason he is stating is totaly valid! Extensive use (or abuse) of some dictionary views can have serious impact on the database performance.

If this is a mission-critical production database and if he is not 100% confident in your DBA knowledge, I can totaly undertand his position... However if I were in his boots, I would first make sure you understand the implications of your catalog queries and if I would find out that there are some "white spots" in your DBA knowledge I would make sure you fill up those gaps and then I'd assign you the desired role.
08-30-2001, 08:44 AM
julian

Re: Re: select catalog role

[QUOTE]Originally posted by jmodic
[B]

Quote:

Originally posted by padma
Extensive use (or abuse) of some dictionary views can have serious impact on the database performance.

That's an overstatement. What do you mean: that nobody should use PATROL, who all the time makes those V$ queries? Come on!

I have tested running long selects with PATROL on and off (which runs quries over the DB). There is no significant difference. And I've done the test against an Oracle Parallel Serevr having hashed locks on SYSTEM! (PATROL having 2 session per instance).
08-30-2001, 01:40 PM
jmodic

Re: Re: Re: select catalog role

Quote:

Originally posted by julian

Quote:

Originally posted by jmodic
Extensive use (or abuse) of some dictionary views can have serious impact on the database performance.

That's an overstatement.[/B]

Might be in your eyes, not im mine. There are four words in mine statement that should be emphasized to put the things into right perspective: *extensive*, *abuse*, *some* and *can*.

Quote:

What do you mean: that nobody should use PATROL, who all the time makes those V$ queries?

No, I mean that any *inexperienced* (not to speak about experienced ones! ;)) DBA/developer is capable of *writing* a query based on some dictionary views and tables that can have a *huge* impact on your production database performance. Don't you belive me? Well, give me access to your database and I'll show it to you.

By no means I was refering to PATROL or some other monitoring/tuning/DBA tool. Such tools are packaged, so everything you do with them is perfectly controled and within the intended limits! I don't consider people who wrote PATROL to be *inexperienced* DBAs/developers! They new what they can stick in there and how to write that, and they also know what should not be there (although it is available in fixed views/tables) because of the impact it could have on the running database users. By "Extensive use (or abuse)" I ment self written (poorly written) queries.

And a remark to one of your previous posts:

Quote:

That is not a correct work to do the job! Your Senior DBA has a lot to learn. I have full confidence in everyone working with me and I would never reject such a thing.

I don't belive you are responsible for a mission critical production database with huge user base that has to run 24x7 with acceptable (read excelent) response times. If you are, then I can't understand how are you able to survive with such a liberal attitude. If you have full confidence in anyone working with you, are you saying you assign DBA role to all of them? After all, this would be the most obvious expression of your confidence in them ;).

I consider myself as a very open and liberal DBA when it comes to *development environment*. In fact, the majority of our developers have DBA role granted and I never experienced any so often mentioned DBAs : developers tensions or silent wars in my team. But when it comes to the production system, all the liberalism is forgoten - everyone has to have exatly those privileges granted to them that are needed to perform their tasks. No more and no less!! This is my DBA rule no.1 in production environment! That doesn't mean I'm the only one that have prety powerfull privileges (DBA role, for example) on the production database, but the ones that have such privileges have prooved themselves in the past that they know what they are doing. This is what I was talking about in my reply to the original question....

[Edited by jmodic on 08-30-2001 at 01:42 PM]
08-31-2001, 01:03 AM
julian

Re: Re: Re: Re: select catalog role

Quote:

I don't belive you are responsible for a mission critical production database with huge user base that has to run 24x7 with acceptable (read excelent) response times.

:-) Yes I run such a database (OPS). And not just one. And standby too. And how should I put this mildly: you sound very touchy..
08-31-2001, 04:31 AM
jmodic

Re: Re: Re: Re: Re: select catalog role

[QUOTE]Originally posted by julian
[B]

Quote:

And how should I put this mildly: you sound very touchy..

I'm sorry if you've got that impression. In the future I'll try to add some more smilies in my replies, two are obviously not enough.... ;)

Just out of curriosity, what would be your words if you wouldn't put this mildly (and I don't want to sound touchy again)? :D
08-31-2001, 06:30 AM
julian

Re: Re: Re: Re: Re: Re: select catalog role

Quote:

Just out of curriosity, what would be your words if you wouldn't put this mildly (and I don't want to sound touchy again)? :D

Take it easy, I am not trying to badger you :-)
I didn't have anything in mind.
On the other hand, I am impressed from what you write in this forum. You seem to have extremely deep knowledge about Oracle.
08-31-2001, 11:31 AM
sambavan

Folks,

We have the information that we need on this forum. I'm taking the liberty to close this forum. If this need to be reopen, a message to one of the moderators would do the work.

Sam