Thanks for the question, Rajneesh.
Asked: April 28, 2020 - 8:00 am UTC
Last updated: April 29, 2020 - 8:24 am UTC
Version: oracle 19c
Viewed 1000+ times
You Asked
Hello TOM,
If possible ,what kind of resources can be managed at schema level, I mean we can set limit or total % to each schema from total available at DB level?
Example- how much space is consumed by tables in different schemas. And assign quotas per schema.
Why I am asking this question:
I am exploring what are possible benefits of having separate layer/a dedicated schema for each layer in ETL (extract , transform and load)?
I mean one dedicated layer for staging and a dedicated schema/layer for final target tables?
I was wondering if this will offer better database resource management because each layer is having different data processing needs and hence need different amount of resource.
Thanks,
Rajneesh
and Connor said...
Resource manager is your key piece of technology here.
Resource control is done by plan directives based on consumer groups. Plenty of examples on AskTOM if you search for those terms.
You *could* map consumer groups to schemas, but you have total flexiblity here.
For example, I've had customers (all ultimately in the same schema) get place in a consumer group for adhoc search (so we can time out expensive queries) and a different consumer group for standard OLTP operations.
You can use Resource Manager to meet whatever needs you have to carve up the resource piece. If you want to slice and dice by extract, transform and load, then thats totally possible and just for you to define which processes/sessions get assigned or switched into which consumer groups.
The choice is yours.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment