Bad running queries [message #442255] |
Sat, 06 February 2010 02:19  |
Hitman11
Messages: 94 Registered: October 2009 Location: norway
|
Member |
|
|
Hi,
May i know something about bad running query? can someone explain about it with few examples?
Thanks
|
|
|
|
Re: Bad running queries [message #442257 is a reply to message #442256] |
Sat, 06 February 2010 03:01   |
Hitman11
Messages: 94 Registered: October 2009 Location: norway
|
Member |
|
|
Bad query.
Queries like development type and testing queries are not to perform in production database.
How to determine the queries are development type and testing type queries?
Because there could be impact if we run such testing queries in production database.
And how to identify those queries in database ?
Please confirm.
Thanks
|
|
|
|
|
|
Re: Bad running queries [message #442269 is a reply to message #442260] |
Sat, 06 February 2010 05:20   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Place a security guard behind each developer, maybe.
Or just don't give the developers the user/password for the production database?
Of course, when you can't trust your own developers, then it might already be to late to save anything.
[Updated on: Sat, 06 February 2010 05:21] Report message to a moderator
|
|
|
Re: Bad running queries [message #442292 is a reply to message #442255] |
Sat, 06 February 2010 10:46   |
wakula
Messages: 150 Registered: February 2008 Location: Poland
|
Senior Member |
|
|
I believe that you are asking wrong questions.
Do you want a bad query example?
If you have 2 or more different environments and you try to run same query on both - then it is most likely that the performance will be different. Please refer to the optimization guide (it is somewhere as sticky or whatever).
In Oracle 10 the default optimizer is cost based optimizer. It "knows" what data are in the table and then it "can" make the best decision on "how to access the data". But sometime it won't know the correct information and this way it might decide that "index is bad and it is better to skip it". Often such decision might be good (index should not be used always - it depends on many factors) but sometime it might be bad.
If you want us to tell you what is wrong with your particular environment - show us what is the problem. Show it - don't describe.
A joke about describing problems as a bonus for the people who have read through all my post:
A friend of mine is working in a big company. He is creating software which is used by the employees. One day some VIP person called him (phone call) to diagnose some issue with the printer. He asked about every detail. After ~5 minutes he was aware that the monitor screen is black - he asked to check all the cables and whether the computer is turned on. After ~15 minutes he asked this VIP person to check the power cord again. Then he got a reply "Do I really need to? It is difficult because it is dark there cause we don't have electricity since the morning...".
So remember - don't describe the issue - show it to us.
|
|
|
|
Re: Bad running queries [message #442448 is a reply to message #442259] |
Mon, 08 February 2010 01:55  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
If the question is about part of code that can be activated in development database but not in production, this could be done with conditional code. Something like the following.
The code of the procedure is the following one:
create or replace procedure p is
begin
$if $$development $then
dbms_output.put_line('Development version');
$end
dbms_output.put_line ('Processing');
end;
When you create or compile it with the default settings (that is in production database) then execute it, you get:
SQL> create or replace procedure p is
2 begin
3 $if $$development $then
4 dbms_output.put_line('Development version');
5 $end
6 dbms_output.put_line ('Processing');
7 end;
8 /
Procedure created.
SQL> exec p;
Processing
PL/SQL procedure successfully completed.
In development database you activate the conditional compilation:
SQL> alter session set plsql_ccflags='development:true';
Session altered.
SQL> alter procedure p compile;
Procedure altered.
SQL> exec p;
Development version
Processing
PL/SQL procedure successfully completed.
Regards
Michel
|
|
|