SAP Knowledge Base Article - Preview

3228587 - sp_dropuser is slow after upgrade from SP03 PL10 to SP04 PL02 - SAP ASE 16.0

Symptom

  • We recently upgraded from SAP ASE 16.0 SP03 PL10 to SAP ASE 16.0 SP04 PL02. The installmaster script was executed as part of the upgrade.
  • We do a IBM hardware flash copy of a database to a reporting side.  We then run sp_dropuser on all users (~10,000) in the reporting side and then add in the extracted users we had before the flash process. 
  • In SP03 PL10, dropping all users would take 15-20 minutes to run, and in SP04 PL02, it takes 50-60 minutes to run. Is this expected behavior?  
  • Apparently, there is a difference in the sp_dropuser code between SP03 and SP04:

ASE 16.0 SP03

       if (user_id() != 1)
                begin
                        execute @status2 = sp_aux_checkroleperm "sa_role",
                                @nullarg, @nullarg, @gp_enabled output

                if (@status2 != 0)
                        begin
                                /*
                                ** proc_role() will raise permission errors
                                ** and send audit records to the audit trail.
                                */
                                select @dummy = proc_role("sa_role")
                                select @dummy = proc_role("sso_role")
                                return (1)
                        end
                end

ASE 16 SP04

                execute @status2 = sp_aux_checkroleperm "sa_role",
                        @nullarg, @nullarg, @gp_enabled output

                if (@status2 != 0 and (user_id() != 1))
                begin
                        /*
                        ** proc_role() will raise permission errors
                        ** and send audit records to the audit trail.
                        */
                        select @dummy = proc_role("sa_role")
                        select @dummy = proc_role("sso_role")
                        return (1)
                end


Read more...

Environment

SAP Adaptive Server Enterprise (ASE) 16.0 SP04 PL02

Product

SAP Adaptive Server Enterprise 16.0

Keywords

performance, speed, fast, installmaster, query, plan, cache, cached, procedure , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.