Authorization Check Failed ! Multiple Child Cursors…..

Recently, got an oppurtunity to work on a Mutex related issue causing database to halt. While the issue started from April 2013, the frequency of this increased since September 2013. This is a Banking Customer and every month, they add few Branches into the system. With the Branches, the concurrency also goes up. This increase in concurrency could be one of the reason of frequent Library Cache Mutex Issue.

In this case, the issue was found to be due to high number of Child Cursors. The top 4 queries, based on number of child cursors, had around 30k child cursors. From the v$sql_shared_cursor, the reason for these many childs was AUTH_CHECK_MISMATCH, which stands for “Authorization Check Mismatch”. Further investigation revealed this to be an Implementation issue.

The Bank has around 580 Branches. For Scalability purpose, the Application Team has implemented 290 Branches on One Database and other 280 Branches on another Database. These are named as BDB1 and BDB2. For each of the Branches, they have individual schema. Therefore, in BDB1 and BDB2 they have 290 and 280 schemas respectively. Each Schema has it’s own table, procedure, functions etc…The name of the tables, their columns etc are same as well across each of these schema’s. For example, each of the schema has a table USER_SESSION_LOGIN_DTL with same number and names of the columns. They only differ in data stored within each of these tables. Further, each schema has around 800+ tables. Therefore, a query executed against Branch 1 is executed against Branch 2, due to the textual similarity, they match in HASH Values and therefore share a common parent. The logic at the application level is that if a user connects to the application, the Branch Name is returned based on which, he or she connects to the schema (based on the branch name) and executes the application level business trasaction. Another user from another branch will open the same module but will connect to different schema. Therefore, while the queries are textually same for each of the Branches, since the underlying objects are different, we see multiple child cursors being populated.

In my view, this is an inefficient Database Design Issue. This will also have a serious manageability issue, as any change in the business logic, will have to be replicated across all the schemas. Any Optimization, i.e. creation of a New Index, will have to be replicated across each of the Schemas. This list can go on…

This is very easy to demonstrate.

## This pl/sql will create 300 Users with names as F1, F2, F3 and so on....

declare
l_statement	varchar2(100);
begin
 for i in 1..300
 loop
   l_statement:='drop User F'||i||' cascade';
   execute immediate l_statement;
   l_statement:='Create User F'||i||' identified by F'||i;
   execute immediate l_statement;
   l_statement:='grant dba to F'||i;
   execute immediate l_statement;
 end loop;
end;
/
## Next we create a Table EMP in each of these schemas. In this case, I am inserting same data into these tables.

declare
l_statement	varchar2(100);
begin
 for i in 1..300
 loop
   execute immediate 'alter session set current_schema=F'||i;
   l_statement:='create table emp as select * from scott.emp';
   execute immediate l_statement;
 end loop;
end;
/
## Next let us execute a query on each of the emp schemas (randomly). These will be 30000 queries with different Bind Variables.

declare
l_emp		number;
l_num		number;
l_sch		number;
l_statement	varchar2(1000);
l_ename		varchar2(32);
begin
 for i in 1..30000
 loop
   l_num:=round(dbms_random.value(1,14),0);
   l_sch:=round(dbms_random.value(1,300),0);
   execute immediate 'alter session set current_schema=F'||l_sch;
   select empno into l_emp from (
     select empno, row_number() over(order by empno) rno from scott.emp)
   where rno=l_num;
   l_statement := 'select /*+ vivek1 */ ename from emp where empno=:b1';
   execute immediate l_statement into l_ename using l_emp;
 end loop;
end;
/

Once the queries are executed, we can query v$sql_shared_cursor to check for the number of child cursors.

SQL> SQL> @sql_text
Enter value for 1: select /*+ vivek1
old   2: from v$sqlarea where sql_text like '&1%'
new   2: from v$sqlarea where sql_text like 'select /*+ vivek1%'

SQL_ID        SQL_TEXT                                           BUFFER_GETS EXECUTIONS VERSION_COUNT PLAN_HASH_VALUE
------------- -------------------------------------------------- ----------- ---------- ------------- ---------------
1c9w6s580jtpd select /*+ vivek1 */ ename from emp where empno=:b        5403       1801            30      3956160932
              1

SQL> select count(*) from v$sql_shared_cursor where sql_id='1c9w6s580jtpd';

  COUNT(*)
----------
      8084

SQL> select sql_id, child_number, AUTH_CHECK_MISMATCH, reason from v$sql_shared_cursor where sql_id='1c9w6s580jtpd' and rownum<=5;

SQL_ID        CHILD_NUMBER A REASON
------------- ------------ - --------------------------------------------------------------------------------
1c9w6s580jtpd           94 Y 9437Authorization Check f
1c9w6s580jtpd           95 Y 9537Authorization Check f
1c9w6s580jtpd           96 Y 9637Authorization Check f
1c9w6s580jtpd           97 Y 9737Authorization Check f
1c9w6s580jtpd           98 Y 9837Authorization Check f

For 30000 Executions, we have 8000 Child Cursors. This block was executed from a Single Session and no one else working. Imagine the impact of such an implementation in a highly concurrent environment.

About these ads

About Vivek Sharma
I am an Oracle Professional from Mumbai (India). I blog on the issues that I feel is Interesting for my readers. These are all my real life examples, which I hope, you would find interesting. Comments are always a welcome.

9 Responses to Authorization Check Failed ! Multiple Child Cursors…..

  1. Ranjit Nagi says:

    Hi vivek,

    Thanks for sharing this experience. Given that application is poorly designed, what could be the resolution to overcome the hugh number of child cursor?

    Below might help?
    1) Limit number of child cursor through _cursor_obsolete_threshold parameter
    2) Use public synonyms (but of-course, this could eliminate the need to replicate tables through all schemas)

    Regards
    Ranjit

  2. vishith says:

    so what is the resolution to this issue??

  3. Amos says:

    Vivek, thank you very much for sharing with us another great article. So in this case, what’s your suggestion to avoid this mutex problem?

    • Vivek Sharma says:

      I missed to mention about the solution that was recommended to resolve the issue. In case of a production case, one of the function, along with the 3 queries (called within the functions), were the issue. Therefore, the function was created in a new schema and these 3 tables were consolidation in this new schema. Required privileges were granted to all the other schemas accessing this function and these tables. This ensured that only one object is referenced everytime it is accessed.

      Regards
      Vivek

  4. Onkar Nath T says:

    Nice post Vivek. Due to this very issue, we implemented VPD for our clients. We have more than 1000 clients who use our application and we store most of the client’s data in same table except for those who insist separate schema and ready to pay for it. It is much more simple and maintenance is really easy.

  5. Vijay Sehgal says:

    Vivek,

    Trust you are fine.

    As always a good read.

    Warm Regards,
    Vijay Sehgal.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 125 other followers