I’ve been meaning to post a couple of comments about the set role command for several months – and a few days ago someone came on to the OTN Database Forum with the exact problem that I had been meaning to write about (except that it turned out that there was an even more important problem [thread subsequently deleted, unfortunately] causing the issue).
Try executing the following SQL (if you have an account that’s allowed to use the plustrace role).
set role plustrace, plustrace, plustrace, plustrace, plustrace;
Then go and look for the following SQL statements in your library cache (v$sql).
select privilege#,level from sysauth$ connect by grantee# = prior privilege# and privilege# > 0 start with grantee# = :1 and privilege# > 0 select max(nvl(option$,0)) from sysauth$ where privilege#=:1 connect by grantee# = prior privilege# and privilege# > 0 start with (grantee#=:2 or grantee#=1) and privilege# > 0 group by privilege#
Depending on your version of Oracle, the hash_value for the statements will be (respectively):
10g 2747636884 2195418186 9i 4143084494 3785480933 8i 502510949 1498920852
(In 10g, the old_hash_value will match the 9i hash_value, but the hash_value is the efficient query option).
You will find that every time you run the set role command I’ve shown, the other two statements will execute five times (once for each of the roles I’ve set – even though it’s actually the same role set five times).
Be very careful what you do with the set role command. I have seen a couple of web-based systems coded to run a statement of this type as a precursor to every other call they make to the database – with the effect that the two recursive statements account for several percent of the total CPU used by the application.