Re: Rules of thumb when creating Profiles?

What version of Oracle would this be?

And what O/S & platform?

Because if you've got 8i or higher, my strong advice would be to set the maximum number of permitted connections as you propose, and then leave everything else unlimited. Profiles are awfully big sledgehammers, and they don't just crack nuts, they mash them.

Point in case: I once had a profile where logical reads per session was set to some limit. A user fired off a big report, and obviously hit the limit. His session was terminated abruptly (because that's what profiles do), but this particular user thought maybe it was just Windows doing its usual thing, or a network fault... and so promptly logged back on and re-ran the entire query.... whereupon he was terminated once more... logged back on once more... and so the cycle continued for about 6 iterations before he got annoyed enough to ring for help. The net result of implementing this profile was not a limit on the amount of resources consumed, but a massive increase.

If you truly want to limit the resources used by users, then (with 8i or above) implement resource manager instead. Better, upgrade to 9i, and implement resource manager -because that has a very nice feature whereby a big report won't even start if its estimated execution time is bigger than some limit you set. That definitely *will* stop massive amounts of load on the database from even starting.

Regards
HJR "Jay" <jlee00_99_at_yahoo.com> wrote in message news:3ea71bf0.0303251659.397df304_at_posting.google.com...
> I am not a DBA, so any feedback from anyone who has experience in this
> subject matter would be greatly appreciated.
>
> I need to create a profile for users who will only be querying two
> tables. This query will only consist of an inner join, summation
> calculation, and a group by clause. At the most there will only be
> about 25,000 records returned from this query. I need to know what
> settings should I put for the items in the profile I plan to create
> based on this single query.
>
> I plan to only allow 1 session for this user account and an idle time
> of 30. I need to know what settings should I put for the other items
> in the profile based on the information on paragraph two on this post.
> I plan to reuse this profile for only users who will be running this
> query.
>
> I know I need to add the Resource_Limit parameter in my init*.ora file
>
> Thanks,
>
> Jay
Received on Tue Mar 25 2003 - 19:26:19 CST