Miguel De Belliz, October 09, 2006 - 11:27 am UTC
Miguel De Belliz, October 09, 2006 - 11:40 am UTC
A reader, October 10, 2006 - 3:50 pm UTC
can you explain what exactly do you want to convey... ?
What I felt from ur answer is we should go for PL/SQL, JAVA and bla bla but should not use cursors.
Is it ?
2nd thing... IF we dont use cursors then why cursors have been invented and where those are used for ?
Cursors are easy to implement, person is not required to have knowledge of Java/C...
Pls. execuse me if I wrote / ask something silly...
October 10, 2006 - 8:20 pm UTC
IF you can do it in a single sql statement, by all MEANS DO IT THAT WAY. do not waste time, energy, cpu cycles writing procedural code that will run slower than regular SQL.
If you cannot do it in a single sql statement - then use plsql....
cursors were invented for when you cannot do it in a single sql statement - read the "mantra" above, it does NOT say "thou shall never use procedural code", it says "only do procedural code when thou MUST"
Correct.... But One minute Pls.!!!
Star Nirav, October 10, 2006 - 8:28 pm UTC
Agreed but let me ask you one question wrt Hogging Queries.
I read somewhere in oracle site that for performance, we need to kill queries from OS (kill command in AIX).
My question is, Sir, if a query is required some CPU resource, it will take and once it completed, will release the CPU. Correct...? So why should we kill from OS level..? Cant we tune from oracle itself... ?
And to find out the most expensive queries, what things we need to keep in mind and what to do to avoid completely in future... ? Can we restrict that user from OEM ?
Can you share the script or link with me pleaseeeee ???
Sometimes, we also need to see the execution plan and in that we need to check that whether FTS is a culprit here or not... ?
Correct me if i am wrong.... Thanks
October 10, 2006 - 8:33 pm UTC
you did not read that on this oracle site, please don't say "i read somewhere out there on the internet (and if it is published it must be true)..."
If you think you read something somewhere, point to it and let us see it in context, you may have misread or misinterpreted what it was saying.
I would NOT recommend killing queries, no.
Yeah... May be correct.
Star Nirav, October 10, 2006 - 8:43 pm UTC
>> you may have misread or misinterpreted what it was saying. <<
Seems that correct, as I have not made that as bookmark and could not remember that whole link. Anyways, forget that part please and tell me if you not recommend to killing queries then what should I do to stop the queries which hogging my servers cpu. Dev. team is saying that they are tuning those queries at their best level, but still it is chocking the cpu and mine HDD (I am having SAN).
Waiting for your anticipation...
October 11, 2006 - 8:00 am UTC
You can use the resource manager (see admin guide) to partition out the resources of the machine when the server gets near 100% utilized. For example, you could put a policy in place that says "if your query is going to take more than one minute, we won't even start it" or "if machine is getting maxed out on cpu, you will only get 20% of the cpu - leaving 80% for the rest of us" and so on
You can use profiles (see admin guide) to limit the aggregate amount of resources a user or call to the database can use - that is, you can limit cpu per session, cpu per call, IO's and so on.
Reinventing the wheel?
Miguel De Belliz, October 11, 2006 - 9:07 am UTC
I repeat that I do not know about oracle and this was the reason of mi question to Mr. Kyte.
But is basic that using data bases we must to tray to resolve the problem in the "server side".
When I submit a query I give the desicion to resolve it to the server because the server going to resolve the problem, in general, in the correct way. ¿The cost is greatest than using cursors? I do not know but is the best.
When I use ADO connection to access to a data base. ¿Is most effective to use a store procedure in the server side that return a result or, in other case, to submit a query for each record? The answer is simple.
And ¿Why the cursor was invented? In the same way that was invented the shovel and the Caterpillar. If I going to remove a hill can use a shovel, is cheap but mean a lot of work and time.
Mr. Kyte, based in your response I make two procedures. One query based and the other based in cursor. The first was a lot faster than the query. Is enough to me.
Thanks a lot again.
Alex, October 16, 2006 - 3:11 am UTC
Speaking about boss. In one of my project I have got the dispute as well with my boss about using cursors while loading tables. He had similuar approach and wants me to follow it. But I changed it to single SQL statements. In fact, it was a performance booster from 10-15 hours to some minutes, but it cost me many frustrating disputes with my boss during developing.
October 16, 2006 - 7:50 am UTC
just use the term "slow by slow" in the future - it really makes the point....
Why do you want to do "slow by slow" processing, databases were born to do set operations...
Tom's Mantra and Nothing but the Mantra
Robert, October 19, 2006 - 10:00 am UTC
>> I have a pretty simple mantra when it comes to developing database software:
ahh been loving that very mantra of yours since 5 years back...
Miguel...time to fire that boss there's always greener pasture. You ain't going far with that guy.
The anti-database creed
Duke Ganote, November 01, 2006 - 8:54 am UTC
This is the creed I've heard for years from some database managers and DBAs:
* No use of innovative features in the database means less blame for the database team when the application encounters bugs.
* Minimizing work allocated to the database means less blame when the application fails to perform.
1. Only keyed reads of tables are permitted.
2. The application code does all joins, ESPECIALLY outer joins.
3. The application code must be out of the database to make the database clean and fast.
4. Never use any feature that hasn't been proven since Tom Kyte started working with Oracle version 5.1.5c ($99 single user version for DOS on 360 KB floppy disks).
"You can always tell the pioneers: they're the ones with arrows in their backs."
And that's a fact.
November 01, 2006 - 8:55 am UTC
wow, that is like "backwards day" isn't it.
In order to minimize work on the database, you have to use database features. The less of the feature set in general you use, the more work the database has to do overall.
The anti-database creed additions...
Jmv, November 01, 2006 - 10:09 am UTC
4a) Foreign keys are only defined within the application, none within the database. (not sure about availability in 5.1.5c though)
4b) Dimensions same as 5 above.
5) The database is not broke, so don't patch or upgrade it.
6) 1 schema per db
6a) 1 db per instance
6b) 1 set of Oracle executables per instance
6c) Multiple instances per box
7) Test backups or recovery procedures...why?
November 01, 2006 - 11:15 am UTC
you just listed half of my database worst practices material!
The anti-database creed
Duke Ganote, November 01, 2006 - 10:39 am UTC
And of course the infamous:
8. Keep your DBHR close to 100% to guarantee good performance!
9. Employ a squad of expensive DBAs to monitor and protect the database from any innovative usage.
The anti-database creed
Tim, November 01, 2006 - 1:16 pm UTC
Tried to resist - couldn't. Had to join in.
10) Cold backups are the only way to go.
11) Bind variables? Waste of developer time.
12) Sequence objects? Why bother - select max(id) works great.
Creed of the worst practitioners
Duke Ganote, November 01, 2006 - 1:29 pm UTC
November 01, 2006 - 6:17 pm UTC
More for the anti-database creed....
Jmv, November 01, 2006 - 1:53 pm UTC
Oh the temptation....restraint...it is overwhelming....
13) Everything in 1 big table
13a) columns like description1, userdefined1, and filler1... all char(2000) (where 1 is incremented and the resulting column added upon "need")
13b) each column indexed
13c) non partitioned
14) Trunc and reload table during every batch window
November 01, 2006 - 6:18 pm UTC
13 - hit in the presenation (along with 11 from above of course)
13b - in the "you probably do not need configuration management section"
are you tryin I shoot me????
Miguel De Belliz, November 01, 2006 - 2:02 pm UTC
you destroyed my illusions men!
I am working in Argentina thinking that in other place of the world there are people who work ideally!
I ask Tom about queries versus cursors and OK! Queries are better!
So what! In all the wide world there are DBAs saying :
Why do you like to use MY DATABASE? Take some data and go to Access!
Do you like to create transitory tables? Are you crazy? NO. You have permissions to use select and is enough!
Indexes???? why do you need another indexes?
Is this true?
Damn world already nothing has sense.
... more creed
Leo Mannhart, November 01, 2006 - 3:57 pm UTC
15) for sure there are silver bullets but only a top consultant knows them so hurry to hire one
16) if you dig deep enough you'll find this famous fast=true initialization parameter (but this is not a normal underscore parameter as everybody knows them now; I bet it is one of this famous double underscore-dollar parameters, probably __$fast=true, but nobody will really tell it once he/she knows it). This must be true by the fact that Jonathan Lewis has one of these new SQL*Plus binary where, after he enters the command "go faster", a query will runfaster by a order of magnitude.
Anti-Database Creed contributions for Database Worst practices...
Jmv, November 02, 2006 - 8:58 am UTC
Thanks for the link!
I did not know you had something like this going already, should have figured as much though.
I really have to get better at checking both AskTom and your blog. AskTom is available easily at work, while your blog was blocked. A link to the presentation would also be helpful.
Perhaps I may yet have a contribution or two.
November 02, 2006 - 9:08 am UTC
click on my files tab above and sort by created by from big to small, it is a recent upload.
Database Worst Pratices....
Jmv, November 02, 2006 - 12:34 pm UTC
The slide on "...just whip things together..." sadly speaks volumes for only being 3 images!!
Creed vs mere list of Worst Practices
Duke Ganote, November 03, 2006 - 1:59 pm UTC
I think the best part of your "worst practices" presentation
is exactly what I was trying to capture in the Creed: Statements of WHY the 'worst practices' happen, the thinking behind them!
WHY just use VARCHAR2 instead of other datatypes? "It's so much easier"
WHY just code literals into SQL strings? "It's so much easier"
Everyone is pressed for completing their "piece of the pie", yet gotta be 'blameless' when the inevitable glitches show up. Oh, maybe I'm suboptimizing the overall system, but as long as my piece functions and got done on time....
November 04, 2006 - 12:09 pm UTC
but the problem is (and this I go through in the talk) that the premise that they are easier is provably FALSE.
It is harder to NOT bind
It is harder to use varchar2 for everything
because of all of the inevitable downstream problems they create.
Their "piece of the pie"...
Jmv, November 09, 2006 - 11:07 am UTC
I agree, though not everybody is capable of stepping back from their "piece of the pie", and looking at the bigger picture to see those "downstream problems".
As may have been stated in other threads, some people are content to say, for a variety of reasons, things like:
- "Get it done now, to 'proof' it out for..." from a developer perhaps
- "Add it to the issues list.." from the tech lead
- "Redress that during warranty..." from the team lead
- "That can be an enhancement request..." project manager
- "Fix that in the next update/fixpack/version..." product manager
- "Performance of xxxx bites...." end user
- "Wow nice code..." Support person
- "Why is it running this way..." DBA
The "Fix" to this is two part in my mind. The first is education, such as this site, wherein a problem is posed, examined, solutions provided and address some of the "downstream problems". Such that those "downstream problems" can be avoided by knowing the "why" of it.
The second is all the viewers out there reading it, commenting on it and learning from it, trying to make their lives, environment and/or company better for knowing the "why" of it.
It should go without saying, but is worth repeating, that Tom does an excellent job of this.
Here's to wishing I could have seen the live version of the presentation!
A reader, July 09, 2010 - 11:03 am UTC
I want to READ the first line in your mantra to as shown below:
->You should do it in a single SQL statement in pl/sql (in a pl/sql block/procedure/function) if at all possible with out using any Procedural logic/language features.
If you cannot do it in a single SQL statement, do it in PL/SQL.
If you cannot do it in PL/SQL, try a Java stored procedure.
If you cannot do it in Java, do it in a C external procedure.
If you cannot do it in a C external procedure, you might want to seriously think about why it is you need to do it.
Is it correct to say ? because while developing a software - we should always put the SQL in pl/sql only - but we should try to avoid the procedural logic. Can you please correct me if I am wrong ?
July 09, 2010 - 4:49 pm UTC
I like the sql in a stored procedure because then I can find it, fix it, make it better. If you (the developer that doesn't like sql in the first place, that finds it a burden) hide it in your code, I cannot find it, fix it.
So, if in plsql, I can at least see it.
Then, try to do as much as you can in sql, do not for example do this:
for x in ( select * from t1 ) loop
insert into t2 values x;
Just do this:
insert into t2 select * from t1;
don't do procedurally what we can efficiently (more efficiently actually) do in a single sql statement
that is what I'm trying to say there.
Any case where pl/sql is faster than SQL
Sutaria Chirayu, July 09, 2010 - 7:36 pm UTC
Just curious.. have you ever encountered a scenario.. or could there be a scenario where PL/SQL could be more faster than SQL.
July 19, 2010 - 7:58 am UTC
yes, there are rare cases.
Never say Never
Never say Always
I Always say....
In almost all cases - doing as much as you can in a single sql statement will outperform (greatly) the equivalent slow by slow procedural code.
There are of course rare exceptions to this - very rare and generally very unique and somewhat complex (so it is not straightforward to give you a cookbook that says "in this case, do this instead of sql")
re: Any case where pl/sql is faster than SQL
Duke Ganote, July 09, 2010 - 8:59 pm UTC
Any? Maybe due to wonky statistics so the optimizer choses a woefully suboptimal plan. Basically you can only beat the optimizer when you know more than it does. Very few people consistently win at blackjack; almost all lose. Think of the optimizer as "the house"; it's hard to beat!
When explicit cursors are necessary ?
A reader, July 12, 2010 - 7:06 am UTC
I have been your fan since long time.
Normally I also prefer implicit cursor; it seems that there may be rare situations where explicit cursors can be required.
The reason which drawn me to this question was a question asked by a team member. That was - “When explicit cursors are required?” OR “When should we go for an explicit cursor?”
I gave few answers –
1 ) To pass data through cursor via an out parameter (SYS_REFCURSOR).
2 ) To perform – Select for update …
3 ) To write a file, which can be used by other interface systems.
I could not go on further. Can you give some information on – when exactly we need explicit cursors in real time applications?
July 19, 2010 - 10:29 am UTC
you need to use an explicit cursor when:
a) you need to use a ref cursor, ref cursors have to be explicit
b) you want to array process data using bulk collect (when the bulk collecting done by implicit cursors is not sufficient)
for example of (b):
for x in ( select * from t )
utl_file.put_line( x.... );
that code DOES NOT need to array fetch, we already do it for you (10g and above - 100 rows at a time). The above code should use an implicit cursor as I have shown. However:
for x in (select * from t) /* this is bad code */
.... process row ....
update t set ... where t.pk = X.PK;
that code would want to use an EXPLICIT cursor so we could array fetch 100 rows - process 100 rows - array update 100 rows, eg:
open c; -- for select * from t
fetch c bulk collect into l_data_array limit 100;
l_eof := sql%notfound;
for i in 1 .. l_data_array.count
... process ...
forall i in 1 .. l_data_array.count update t set ...
where t.pk = l_data_array.pk(i);
exit when l_eof;
and that is it, select for update shouldn't be in your list - nor should "write a file"
Where current of... ?
Kim Berg Hansen, July 20, 2010 - 6:26 am UTC
You answered the previous commentary, that "select for update" should not be on his list. And you had an example of bad practice of updating row-by-row in a for loop.
Does that mean that you generally disapprove of the "where current of" syntax?
I would guess that is why the previous commenter believed a "select for update" needed an explicit cursor since "where current of" is not possible (AFAIK) with an implicit cursor.
Anyway I tend to agree, just curious to confirm whether you like "where current of" or not :-)
July 23, 2010 - 7:05 am UTC
... Does that mean that you generally disapprove of the "where current of" syntax? ..
yes, for any update of any size other then a single row or two.
it (where current of) would make sense for a row or two, but not for a large bulk update.
they asked about "for update", which in general is used to lock a stable set of rows and MAY or MAY NOT be used with "where current of".
ops$tkyte%ORA10GR2> create table t ( x int , y int );
2 for x in ( select rowid rid, x, y from t for update )
4 update t set y = 2 where rowid = x.rid;
5 end loop;
PL/SQL procedure successfully completed.
is identical to "where current of", where current of is syntactic sugar for "where rowid = ?" - in fact, if you trace a where current of, you'll see we really just added rowid to your select and use it in the where clause.
A reader, September 21, 2010 - 6:59 am UTC
September 21, 2010 - 4:04 pm UTC
devu mani, April 23, 2014 - 5:28 am UTC
what is a full form of cusror?