Limiting Sessions I/O
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Limiting Sessions I/O

  1. #1
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350

    Limiting Sessions I/O

    Hi, in a 9ir2 EE dw-like db, I want to limit ad-hoc end-users by LIOs - anyone doing more than n LIOs must be disconnected. I was thinking about PROFILEs limiting LOGICAL_READS_PER_SESSION, afaik they limit serial SQLs very well, but IF some SQL get paralelized, iirc the LIOs will be done by the parallel slaves, the PX coordinate session has done very little I/Os , so the limit will not be reached, right ? If so, what can I do in this release ?

    regards,

    Chiappa

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Resource Limits on PROFILES are your tool of choice.

    You know you app and you can run explain plans for the most used queries, even ad-hoc... then you set your values and see what happens

    We did that in our DW environment, it took six months of fine tunning but it's working great!

    Also, you can group your user in different profiles and assign different limitations

    Have fun!
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Have a look at the resource manager. Not sure that it will help but still it worths to take a look

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    in a 9ir2 EE dw-like db
    Why do you want to restrict on number of LIO?

    In a DW, this is common.

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    I know about managing users,profile and resources, but forgive my ignorance what does LIO here stands for?
    ---------------

  6. #6
    Join Date
    Jun 2006
    Location
    Chennai, INDIA
    Posts
    72
    I guess it represents Logical I/O

  7. #7
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    thanks.
    if its logical IO, then the SQLs used should be the one to be optimized. That I think.
    ---------------

  8. #8
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Yes, LIOs = Logical I/Os. Of course, these "ad-hoc" queries are consulting the DW tables doing groups and alike. Yes, we need to "block" acess to the real tables and give MVs to the end-users, take sqlplus from them and give Discover along with fixed optimized views , BUT in the meantime, they can consultdirectly the big ones, and I need to stop anyone who do not used the "right" where clauses and alike....
    And people, no, profiles do not "work" with PQ , I confirmed my guess with this test :

    ==> see, PQ is possible :

    system@DBDEV:SQL>explain plan set statement_id='P' for select count(*) from fco.SIA_RELACIONES_TICK_FCO;

    Explicado.

    system@DBDEV:SQL>@explain

    STATEMENT_ID
    ------------------------------
    P


    PLAN_TABLE_OUTPUT
    ------------------------------------------------------------------------------------------------------------

    ------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost | TQ |IN-OUT| PQ Distrib |
    ------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | 1773 | | | |
    | 1 | SORT AGGREGATE | | 1 | | | | | |
    | 2 | SORT AGGREGATE | | 1 | | | 38,00 | P->S | QC (RAND) |
    | 3 | INDEX FAST FULL SCAN| REL_INTERC_SERVICIO | 6305K| | 1773 | 38,00 | PCWP | |
    ------------------------------------------------------------------------------------------------------------

    PX Slave SQL Information (identified by operation id):
    ------------------------------------------------------

    2 - SELECT /*+ PIV_SSF */ SYS_OP_MSR(COUNT(*)) FROM (SELECT /*+ INDEX_RRS(A2 "REL_INTERC_SERVICIO") */ 0
    FROM "FCO"."SIA_RELACIONES_TICK_FCO" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A2) A1

    ==> this SQL does more than 70000 LIOs (I know this for a fact), so I will put an inferior limit :

    system@DBDEV:SQL>create profile PROF_MAX_LIO_10000 limit LOGICAL_READS_PER_SESSION 10000;

    Perfil criado.

    system@DBDEV:SQL>alter user fco profile PROF_MAX_LIO_10000;

    Usuário alterado.

    ==> OK, I will open two sessions for this fco user, one PQ-enabled and another not, and do the SQL :

    - first session...

    fco@DBDEV:SQL-1>select count(*) from fco.SIA_RELACIONES_TICK_FCO;

    -- and the second session ..

    fco@DBDEV:SQL-2>alter session disable parallel query;

    Sessão alterada.

    fco@DBDEV:SQL-2>select count(*) from fco.SIA_RELACIONES_TICK_FCO;


    ==> see, the session with PQ registered very few LIOs (the I/O was done by the PQ slaves), so it was NOT disconnected :

    fco@DBDEV:SQL-1>select count(*) from fco.SIA_RELACIONES_TICK_FCO;

    COUNT(*)
    ------------------
    6305638

    ==but see the session without pq :


    fco@DBDEV:SQL-2>select count(*) from fco.SIA_RELACIONES_TICK_FCO;
    select count(*) from fco.SIA_RELACIONES_TICK_FCO
    *
    ERRO na linha 1:
    ORA-02394: exceeded session limit on IO usage, you are being logged off


    see ?? So this is my problem, how to limit total LIOs ,ie, LIOs done by the session itself PLUS LIOs done by PQ slaves in behalf of the session....

    I don´t tested, but Resource manager, iirc, is not the answer, it will work in the same way, afaik...

    Regards,

    Chiappa

  9. #9
    Join Date
    Nov 2005
    Posts
    32
    In a DW, an ideal solution would be hide real tables and present data to end users through views and MVs. As a temporary solution, it looks like you are trying to restrict on the amount of IO that a user can do through ad-hoc queries through the use of resource governer a.k.a profiles. Since parallel queries are getting in your way of fairly implementing the logical_read_per_session profile, one of the things you could possibly do is disable parallel query at the session level either through logon trigger or through resource manager. Through trigger you could do this:

    create or replace trigger on_logon
    after logon on database
    declare
    var_profilename varchar2(100);
    begin
    select profile into var_profilename from dba_users where username = upper(sys_context('userenv','session_user'));
    -- You've called the profile PROF_MAX_LIO_1000
    if var_profilename = 'PROF_MAX_LIO_1000'
    then
    -- User has been assigned the limitation and so disable parallel query
    execute immediate 'alter session disable parallel query';
    end if;

    end;
    /


    Using a trigger will not prevent users from using parallel hint or enabling parallelism through their own alter session command and so a more foolproof method would be to use resource manager and create a resource group called adhoc_group and assign it a plan directive for PARALLEL_DEGREE_LIMIT_P1 => 0. You can then assign your ad-hoc query users to this group while all other users go to the default OTHER_GROUPS.

    One other potential advantage of disabling parallel query for ad-hoc queries is that the optimizer could potentially favor index scans over full scans.

    Again, all this is for a temporary solution while you work on a more permanant solution......

    Good luck.....

    http://www.dbaxchange.com

  10. #10
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Nope, I can´t disable PQ for the users, without PQ the performance go down, for sure.... In absence of other answers, I will try limit time elapsed, session time, or something like that - a "bad" query, doing a lot of I/Os, surely will consume a loty of time....

    []s

    Chiappa

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width