Alexander, April 21, 2010 - 11:01 am UTC
Never in my many years of reading this site have I read a post or question that I can relate to as much as John's. I swear we might be working for the same company.
I have looked all over the place to find documented "best practices" for exactly the reasons John states; We are mortal DBAs that often get as much respect as the janitors. For example, putting 1 instance per host, other than Tom, try finding an Oracle doc that says that's how Oracle is designed to work.
Another near impossible thing to prove to those people that make the decisions, is the work and dollars associated with supporting a piece of crap. Our hours and hours of extra work don't matter, we get a salary, done.
I feel I can apply this example with triggers do virtually everything I do that involves a design decision using Oracle. Obviously, we common folk would have a much tougher time finding a new job. But to be honest Tom, I'm starting to get the feeling this is how the world works, and it doesn't matter how many times I switch.....
great article
Wally, April 21, 2010 - 12:09 pm UTC
Thanks for your insight Tom.
Alexander, April 21, 2010 - 1:56 pm UTC
"everyone forgets that I was common folk."
Did people listen to you when your were?
April 21, 2010 - 2:26 pm UTC
many times, yes.
sometimes however, apparently they did not since I had to leave...
It is my firm belief that if you are in a situation that is just driving you crazy - it could be time to look elsewhere.
corporate
mahesh, April 21, 2010 - 11:42 pm UTC
Most of us work in corporate world, where efficiency, maintainability and cost savings are always not on priority.
I relate the corporate world to my family- when we went to mall, my three year old daughter asked for a toy, we tried hard to convince her- but she want it-so we purchased – knowing that it will cost and not suitable for her. But we do buy against our wish. Isn’t it the same?
Most important is to keep relationships in order than appropriate technology /design/solution/cost.
April 22, 2010 - 8:05 am UTC
... we tried hard to convince her- but she want it-so
we purchased – knowing that it will cost and not suitable for her. But we do
buy against our wish. Isn’t it the same?
...
no, because you do that out of affection - care - love even.
and you do this:
... where efficiency, maintainability and cost
savings are always not on priority. ...
out of "not smartness", care, consideration - has nothing to do with it.
The corporate world is where efficiency, maintainability and cost savings are paramount - if you want to stay in the corporate world.
... Most important is to keep relationships in order than appropriate technology
/design/solution/cost.
...
hah, but did you read the above - these are some defective, abusive relationships then.
Agree
Tom, April 22, 2010 - 9:10 am UTC
The reason we have to deal with this on a daily basis is due to the fact that many people in our fields don’t continue to keep up with the latest techniques and best practices by reading AskTom, studying books, benchmarking, learning from others…
Developers I work with tell me they don’t have time to learn and implement the techniques that we have learned from AskTom and his great books. They always say “yeah, yeah I know about AskTom”. I tell them that if they kept up with the best practices they would have plenty of time, due to the fact that they would not be writing as much code, spending as much time debugging… The battles continue and I am becoming exhausted with battling the entire enterprise. Like Tom has noted above I also may need to quit my current position due to the organizations’ rules.
Here are a few of the things that I am championing on behalf of my fellow Oracle developers:
1. Can we PLEASE have the “SELECT_CATALOG_ROLE” in the development environment so that we can see the V$tables …
2. Can the DW team stop using a self imposed rule that all code must do “bulk collect” as their only method for loading data (ignoring insert append no logging techniques. Create Table As).
3. Can the DW team update their general load APIs to stop imposing general rules that don’t make sense like “automatically building Bit Map indexes on every low cardinality column i.e date fields, numbers, everything that is low cardinality)
4. Why can’t we have enough space to get our job done? Space is cheap but they will not give us enough space to stage data in tables. We cannot have 2 copies of large tables in the staging area at the same time. Instead they are going to force us to use a slow by slow method.
5. I am in a constant battle with the Java guys, trying to convince them that SQL belongs in the back-end not the middle (I have a meeting next week with an architectural committee on this issue. If anyone has a good presentation to show them why code belongs in the back-end please post and I will incorporate into mine.)
6. I am constantly battling my fellow PL SQL developers over standards that hinder readability (schema. schema_name_table_name, schema. schema_package_name, all_tables end with _TBL, all procedures and functions in a single package for the whole application …), developers still utilizing cursors throughout their code for everything, minimal or no utilization of Case statements or Oracle Analytics.
7. I am constantly battling management over a “No talking with the end users rule”, how on earth are we ever going to get the applications right if we are severely restricted from communicating with the end user groups?
8. ….
I would have already quit but it seems to be this way everywhere I go! Career change?
Don't have time
Ben, April 22, 2010 - 9:28 am UTC
If you don't have time to do it right, you must have time to do it again.
Agree
Vic, April 22, 2010 - 9:41 am UTC
Why don't you get a copy of Tom's book or ebook 'Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions'
Tom goes into why and how and how not to develop applications. He goes into great detail as to why the database should 'do the work' I'm sure he won't mind if you quote him with his permission.
Follow Up
Tom, May 13, 2010 - 10:21 am UTC
As a follow up to my ranting above. I met with the Architects from one of the largest IT consulting companies in the world last week in Reston, VA. I was there to try to make them understand that SQL belongs in the Oracle database and not in the Java middle. I presented everything I had including many of the threads from Ask Tom. I was informed by the so-called “Architects” that SQL needs to be in the middle so that the applications may scale. This company is building hundreds of applications for the government with 0 sql in the Oracle database. I asked them to please call me when the performance starts to be a problem and I will come back to try to assist them once again.
The trouble with Triggers
Patrizia, May 20, 2010 - 5:45 am UTC
Hi Tom, this is my 'first time' about asking you. I tried to find your article about triggers, but i didn't. Please, can you link it ?
Thanks and regards
Patrizia
May 24, 2010 - 11:34 am UTC
Asking for trouble by using a trigger submit a job to avoid a mutating table challenge?
Hein, September 06, 2011 - 4:03 pm UTC
Is it reasonable to have a trigger submit a job to avoid a mutating table challenge for (low frequency) changes?
I'm working on an application with a table of 'people'. (naming changed to protect the innocent)
Columns are person_id, parent_id, family_id, name, and such, with GUIDs.
There was a performance problem with a frequently (millions/day) executed
query using CONNECT on the main table to report up/down hierarchy
for a given person within a family.
A real solution of course involves reducing those millions of executions
each day (most are done in the context of a COLLECTION ITERATOR PICKLER FETCH).
As a (temporary?) workaround I have put a 'helper' table in place which
materializes the immediate family structure for each person (up, and down)
This perfomed 30x faster, but of course adds the challenge to keep the helper table in sync.
There is a procedure to update the helper structure for a specified family
My initial, naive, attempt was to add an INSERT trigger to execute that update procedure.
Of course the generated "ORA-04091: table is mutating..."
My suggested solution for that is not to execute the helper update procedure directly,
but instead to submit a job to do so, when the insert transaction commits (once per day or less)
It seems to work.
Do you think this is a total hack, or reasonable usage of trigger and job?
Any and all feedback appreciated!
Regards,
Hein
CREATE OR REPLACE TRIGGER persons_helper_insert_trigger
AFTER INSERT ON persons_table
FOR EACH ROW
DECLARE
L_JOB number;
BEGIN
-- When the job below fails, information about the failure is recorded in a
-- trace file and the alert log. Oracle writes message number ORA-12012 and
-- includes the job number of the failed job.
--
DBMS_JOB.SUBMIT
(JOB => L_JOB,
WHAT => 'persons_helper_table_load ( hextoraw (''' || :new.family_id || '''));');
END;
/
September 07, 2011 - 8:31 am UTC
It seems to work.
Do you think this is a total hack, or reasonable usage of trigger and job?
Any and all feedback appreciated!
It seems reasonable - but I would change the way the job is submitted. right now, it is NOT bind friendly. I would do this:
declare
l_job number;
begin
dbms_job.submit( job => l_job, what => 'persons_helper_table_load( JOB );' );
insert into persons_helper_table_load_parms ( job_id, family_id, ... )
values ( l_job, :new.family_id, .... );
end;
and then in the persons_helper_table_load - you would have:
procedure persons_helper_table_load( p_job in number )
is
l_rec persons_helper_table_load_parms%rowtype;
begin
select * into l_rec from persons_helper_table_load_parms where job_id = p_job;
... your code ...
delete from persons_helper_table_load_parms where job_id = p_job;
end;
that way, you only submit ONE 'what' input - it is constant, always soft parses...
Trigger trouble
Patrick, December 28, 2011 - 3:45 pm UTC
hi
(don't know if this is good enough for the site)
some bad existing situations found in a legacy application:
influencing existing trigger :
begin
a_package.set_var(1); --set a package variable
update table a_table
set
where
a_package.set_var(0);
exception
when others then
a_package.set_var(0);
raise;
end;
trigger on a_table :
if a_package.get_var = 1 then
do_something;
else
do_normal;
end if;
Procedure is better: new input parameter i_a_parameter and test on it.
2. common use of mutating table via 3 triggers and a package pl/sql table.
years of problems with restart: pl/sql table filled in BR trigger didn't get rollbacked (thanks Tom for the tip in your book), so double entry.
3.Large (many columns) table updated in different processes : many mutating table entrys in the trigger to process the different cases : IF field x updated then mutating table action "process_x", ... If you read the trigger then you can not see the entire functional flow.
4. update table a, in trigger update cumul table, in trigger on that : update cumul table higher level table...
so procedure must only update table a, and the other tables are automatically updated. one can not program to use FORALL on the related tables. eg. do the processing of table a, postpone the updates of the cumul tables to do just before commit whith forall. It is also not possible to put an order on the cumul rows, to prevent deadlock.
5. "We have a problem we can't solve find but we think the triggers play ping-pong".
Triggers take away your freedom. It's harder to program new functionality
Trouble with Triggers | Asktom Column oracle magazine
Rajeshwaran, Jeyabal, January 16, 2018 - 9:44 am UTC
Team,
was reading this AskTom column from Oracle magazine online.
http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html it has references a couple of links to Tomkyte's blog, however we are unable to access those blog link.
could you help us with that contents?
tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.htmltkyte.blogspot.com/2005/08/part-ii-seeing-restart.htmltkyte.blogspot.com/2005/09/part-iii-why-is-restart-important-to.htmlAlso this link (asktom.oracle.com/tkyte/Mutate) referred in the online magazine is not working, could you help us with the right link?
January 16, 2018 - 1:03 pm UTC