Limiting Sessions I/O

[JChiappa]

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

[PAVB]

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!

[Bore]

Have a look at the resource manager. Not sure that it will help but still it worths to take a look

[tamilselvan]

in a 9ir2 EE dw-like db

Why do you want to restrict on number of LIO?

In a DW, this is common.

[reydp]

I know about managing users,profile and resources, but forgive my ignorance what does LIO here stands for?

[Kamkan]

I guess it represents Logical I/O

[reydp]

thanks.
if its logical IO, then the SQLs used should be the one to be optimized. That I think.

[JChiappa]

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

[dbaxchangedba]

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

[JChiappa]

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