|
|
|
|
|
|
| Author |
Message |
sybrandb
*nix forums beginner
Joined: 13 Jul 2006
Posts: 22
|
 Posted: Wed Jul 19, 2006 3:22 pm Post subject:
Re: Optimisation of a sql query
|
|
|
Abhijat wrote:
| Quote: | Hi group,
I have a simple create table query.
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT *
FROM rd_at2.AUTH_MAY06 A LEFT JOIN
dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
GROUP BY B.MCC_CTGY_KEY, B.SLS_GRP_LVL_6_NM
HAVING A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09";
The data table AUTH_MAY06 is of size 25 GB and the other table is about
500MB!
I have two questions regarding the performance tuning of this query.
1. What should be the order of having clause so that the processing
time is decreased ?
2. Is there another way out (ex. using some options in oracle/sas)
which would further decrease the processing speed (assuming we have
fixed the "having" issue, if any)?
|
1 No version. How do you expect help
2 No explain plan. How do you expect help
3 How did you establish there is a 'having issue'? Did you look in a
crystal ball, or went to your work on a broom stick?
4 Did you check where your bottlenecks are located? Disk? Logwr?
Database archiving like hell? If you didn't do any of those things, how
do you expect anyone can help you out.
--
Sybrand Bakker
Senior Oracle DBA |
|
| Back to top |
|
 |
Brian Peasland
*nix forums Guru
Joined: 04 Apr 2006
Posts: 301
|
| Posted: Wed Jul 19, 2006 4:08 pm Post subject:
Re: Optimisation of a sql query
|
|
|
Abhijat wrote:
| Quote: | Hi group,
I have a simple create table query.
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT *
FROM rd_at2.AUTH_MAY06 A LEFT JOIN
dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
GROUP BY B.MCC_CTGY_KEY, B.SLS_GRP_LVL_6_NM
HAVING A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09";
The data table AUTH_MAY06 is of size 25 GB and the other table is about
500MB!
I have two questions regarding the performance tuning of this query.
1. What should be the order of having clause so that the processing
time is decreased ?
2. Is there another way out (ex. using some options in oracle/sas)
which would further decrease the processing speed (assuming we have
fixed the "having" issue, if any)?
|
Why are you performing a GROUP BY when there is no aggregate/group
functions in your SELECT clause? Are you sure you do not mean ORDER BY?
If that is the case, then realize that there is not true ordering of
rows in a table so the ORDER BY is unnecessary.
HTH,
Brian
--
===================================================================
Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown |
|
| Back to top |
|
|
Charles Hooper
*nix forums addict
Joined: 09 Jul 2006
Posts: 51
|
| Posted: Wed Jul 19, 2006 4:21 pm Post subject:
Re: Optimisation of a sql query
|
|
|
Abhijat wrote:
| Quote: | Hi group,
I have a simple create table query.
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT *
FROM rd_at2.AUTH_MAY06 A LEFT JOIN
dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
GROUP BY B.MCC_CTGY_KEY, B.SLS_GRP_LVL_6_NM
HAVING A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09";
The data table AUTH_MAY06 is of size 25 GB and the other table is about
500MB!
I have two questions regarding the performance tuning of this query.
1. What should be the order of having clause so that the processing
time is decreased ?
2. Is there another way out (ex. using some options in oracle/sas)
which would further decrease the processing speed (assuming we have
fixed the "having" issue, if any)?
|
HAVING is resolved after the GROUP BY operation. This is helpful when
trying to do something like this: SUM(MATERIAL_COST) > 10000. This
means that the GROUP BY operation must be performed before the
restrictions on the HAVING can be applied, hence a full table scan of
the 25GB is likely being performed, along with an attempted GROUP BY of
that 25GB of data, an a possible sort operation.
WHERE is resolved prior to a GROUP BY operation, and should be used
when possible to restict data before it enters the GROUP BY operation.
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT *
FROM rd_at2.AUTH_MAY06 A LEFT JOIN
dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
WHERE A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09"
GROUP BY B.MCC_CTGY_KEY, B.SLS_GRP_LVL_6_NM;
I personally prefer the SQL89 syntax of left outer joins, which are
less complicated to decipher when more than two tables are involved:
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT
*
FROM
rd_at2.AUTH_MAY06,
dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
WHERE
A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR(+)
AND A.RSPNS_CDE = '00'
AND A.TST_ACCT_CDE = ''
AND A.AUTH_TRAN_CDE IN ('0110','0120')
AND A.AUTH_PROC_1_CDE = '09'
GROUP BY
B.MCC_CTGY_KEY,
B.SLS_GRP_LVL_6_NM;
"SELECT * FROM" does not seem approproiate for use with "GROUP BY".
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. |
|
| Back to top |
|
|
Mladen Gogala
*nix forums Guru
Joined: 11 Mar 2005
Posts: 948
|
| Posted: Wed Jul 19, 2006 4:35 pm Post subject:
Re: Optimisation of a sql query
|
|
|
Abhijat wrote:
| Quote: | Hi group,
I have a simple create table query.
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT *
FROM rd_at2.AUTH_MAY06 A LEFT JOIN
dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
GROUP BY B.MCC_CTGY_KEY, B.SLS_GRP_LVL_6_NM
HAVING A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09";
The data table AUTH_MAY06 is of size 25 GB and the other table is about
500MB!
I have two questions regarding the performance tuning of this query.
1. What should be the order of having clause so that the processing
time is decreased ?
2. Is there another way out (ex. using some options in oracle/sas)
which would further decrease the processing speed (assuming we have
fixed the "having" issue, if any)?
|
1) What are you grouping/having? There is nothing to group: no sum,
average, count or anything like that.
2) Are you table indexed? With the condition like
A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
I would expect both involved columns to have indexes.
3) Are the tables analyzed? Do all the columns mentioned in the query
have histograms?
4) Do you have an execution plan? Database will tell you "master, I have
a cunning plan". Then, you have to revise that plan and approve it.
It's known as the Baldrick mechanism. Cunning plans are visible in
V$SQL_PLAN table.
5) Are you really, really sure that you need @#$%! ANSI syntax? Every
patchset installation notes mention bugs of @#$%! ANSI joins and CBO
combination. What are you trying to create? An application that will
suck equally on every platform? That's what "portable SQL" is for.
--
Mladen Gogala
http://www.mgogala.com |
|
| Back to top |
|
|
Abhijat
*nix forums beginner
Joined: 19 Jul 2006
Posts: 4
|
| Posted: Wed Jul 19, 2006 5:49 pm Post subject:
Re: Optimisation of a sql query
|
|
|
Hi Sybrandb,
You are right and I must confess, I am kind of novice in this field.
I should have looked into the options. Here is my explanation to each
of your query.
Since I m working in SAS, I will provide you the answers with respect
to it.
1 No version. How do you expect help
Ans - SAS version 9.1
2 No explain plan. How do you expect help
Ans. I don't know how do we get explain plan in SAS.
3 How did you establish there is a 'having issue'? Did you look in a
crystal ball, or went to your work on a broom stick?
Ans. I concluded to this issue because I had a rough idea that the
execution of a sql query starts from the last mapping, hence, we should
ideally give the biggest filter at the bottom moving upwards ! Please
correct me if I m wrong here.
4 Did you check where your bottlenecks are located? Disk? Logwr?
Database archiving like hell? If you didn't do any of those things,
how do you expect anyone can help you out.
Ans. The disk space is enough and so is the processor speed. In fact,
its not in my hands to alter them as well. I have to work in the given
disk space (which is enough) and processor speed.
Please let me know if the provided suffices.
Thanks and Regards,
Abhijat.
sybrandb wrote:
| Quote: | Abhijat wrote:
Hi group,
I have a simple create table query.
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT *
FROM rd_at2.AUTH_MAY06 A LEFT JOIN
dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
GROUP BY B.MCC_CTGY_KEY, B.SLS_GRP_LVL_6_NM
HAVING A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09";
The data table AUTH_MAY06 is of size 25 GB and the other table is about
500MB!
I have two questions regarding the performance tuning of this query.
1. What should be the order of having clause so that the processing
time is decreased ?
2. Is there another way out (ex. using some options in oracle/sas)
which would further decrease the processing speed (assuming we have
fixed the "having" issue, if any)?
1 No version. How do you expect help
2 No explain plan. How do you expect help
3 How did you establish there is a 'having issue'? Did you look in a
crystal ball, or went to your work on a broom stick?
4 Did you check where your bottlenecks are located? Disk? Logwr?
Database archiving like hell? If you didn't do any of those things, how
do you expect anyone can help you out.
--
Sybrand Bakker
Senior Oracle DBA |
|
|
| Back to top |
|
|
fitzjarrell@cox.net
*nix forums Guru
Joined: 19 Jul 2005
Posts: 444
|
| Posted: Wed Jul 19, 2006 6:04 pm Post subject:
Re: Optimisation of a sql query
|
|
|
Abhijat wrote:
| Quote: | Hi Sybrandb,
You are right and I must confess, I am kind of novice in this field.
I should have looked into the options. Here is my explanation to each
of your query.
Since I m working in SAS, I will provide you the answers with respect
to it.
1 No version. How do you expect help
Ans - SAS version 9.1
2 No explain plan. How do you expect help
Ans. I don't know how do we get explain plan in SAS.
3 How did you establish there is a 'having issue'? Did you look in a
crystal ball, or went to your work on a broom stick?
Ans. I concluded to this issue because I had a rough idea that the
execution of a sql query starts from the last mapping, hence, we should
ideally give the biggest filter at the bottom moving upwards ! Please
correct me if I m wrong here.
4 Did you check where your bottlenecks are located? Disk? Logwr?
Database archiving like hell? If you didn't do any of those things,
how do you expect anyone can help you out.
Ans. The disk space is enough and so is the processor speed. In fact,
its not in my hands to alter them as well. I have to work in the given
disk space (which is enough) and processor speed.
Please let me know if the provided suffices.
Thanks and Regards,
Abhijat.
|
Not really, no. We don't care which SAS version you're using, what
Sybrand asked for (and you have yet to provide) is the Oracle
release/version information, such as 9.2.0.6, 10.2.0.2, etc.
SAS has nothing, really, to do with this issue outside of how the
schema objects were created. Also, your query is ... wrong ... in many
ways, including using GROUP BY and HAVING without any aggregate values
in your SELECT statement. This query should be rewritten using WHERE:
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT *
FROM rd_at2.AUTH_MAY06 A LEFT JOIN
dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
WHERE A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09";
GROUP BY and HAVING have no place in this query of yours; of course
SELECT * isn't the most intelligent of choices for this query, either.
Place the columns you want in the SELECT list explicitly to avoid
ambiguous column errors and to prevent duplicate data in your record
(NOVUS_MRCH_NBR contains the same data as NOVS_MRCH_NBR, so why have
both in the same record?).
Explain plan is fairly straightforward:
explain plan
set statement_id = 'myid' for
SELECT *
FROM rd_at2.AUTH_MAY06 A LEFT JOIN
dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
WHERE A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09";
Presuming you're using 9i or later you would then:
select *
from table(dbms_xplan.display);
and output the results. When you generate your explain plan you'll see
how the query executes. And what problems, if any, my be present.
David Fitzjarrell |
|
| Back to top |
|
|
Abhijat
*nix forums beginner
Joined: 19 Jul 2006
Posts: 4
|
| Posted: Wed Jul 19, 2006 8:47 pm Post subject:
Re: Optimisation of a sql query
|
|
|
Hi Sybrand/David/Brian/Charles/Mladen,
First of all many thanks for your valuable thoughts and invaluable
time.
I understand my mistake and below I m going to mention the steps that I
am going to take to correct. Please look into it too.
My actual code doesn't contain select *, it has fixed selections, hence
what David is mentioning about select * is automatically taken care of.
About using group by and having, I have to confess that the query I
have mentioned here is slightily different from the actual query. The
actual query does contain selections like "SUM(var1)".
The actual query that I intend to execute is like this
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT
DISTINCT B.MERCHANT, "WITHOUT AQUIRER" AS MODEL, "DISCOVER" AS
NEW_ISSUER,"DISCOVER" AS ISSUER,
B.MCC, "MAY06" AS TIME, COUNT(A.ACCT_NBR) AS CO_TRANSACTIONS,
SUM(A.AUTH_AMT) AS AUTHORIZED ,
SUM(A.ADDTNL_AMT) AS CASHOVER , (CALCULATED AUTHORIZED - CALCULATED
CASHOVER ) AS MERCHANDISE
FROM rd_at2.AUTH_MAY06 A LEFT JOIN dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_v2 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
GROUP BY B.MCC, B.MERCHANT
HAVING A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09";
So now that I m using aggregated functions, please suggest the best way
to improve my code. Shall I go ahead with the where clause as suggested
by you all or anything else. Please suggest with regards to the fact
that the AUTH_MAY06 is about 25 GB in size and the other table is about
500 MB.
A quick note to Sybrand and David: I guess I could not make myself very
clear in my last mail. I am not working on Oracle tables. I am working
in SAS and working with SAS datasets. Hence, the version still remains
to be SAS 9.1. Please help with regards to this.
Once again thanks to all for your overwhelming response.
Regards,
Abhijat
Mladen Gogala wrote:
| Quote: | Abhijat wrote:
Hi group,
I have a simple create table query.
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT *
FROM rd_at2.AUTH_MAY06 A LEFT JOIN
dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_V1 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
GROUP BY B.MCC_CTGY_KEY, B.SLS_GRP_LVL_6_NM
HAVING A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09";
The data table AUTH_MAY06 is of size 25 GB and the other table is about
500MB!
I have two questions regarding the performance tuning of this query.
1. What should be the order of having clause so that the processing
time is decreased ?
2. Is there another way out (ex. using some options in oracle/sas)
which would further decrease the processing speed (assuming we have
fixed the "having" issue, if any)?
1) What are you grouping/having? There is nothing to group: no sum,
average, count or anything like that.
2) Are you table indexed? With the condition like
A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
I would expect both involved columns to have indexes.
3) Are the tables analyzed? Do all the columns mentioned in the query
have histograms?
4) Do you have an execution plan? Database will tell you "master, I have
a cunning plan". Then, you have to revise that plan and approve it.
It's known as the Baldrick mechanism. Cunning plans are visible in
V$SQL_PLAN table.
5) Are you really, really sure that you need @#$%! ANSI syntax? Every
patchset installation notes mention bugs of @#$%! ANSI joins and CBO
combination. What are you trying to create? An application that will
suck equally on every platform? That's what "portable SQL" is for.
--
Mladen Gogala
http://www.mgogala.com |
|
|
| Back to top |
|
|
Sybrand Bakker
*nix forums Guru
Joined: 03 Apr 2005
Posts: 1766
|
| Posted: Wed Jul 19, 2006 9:17 pm Post subject:
Re: Optimisation of a sql query
|
|
|
On 19 Jul 2006 13:47:00 -0700, "Abhijat" <ursabhijat@gmail.com> wrote:
| Quote: | A quick note to Sybrand and David: I guess I could not make myself very
clear in my last mail. I am not working on Oracle tables. I am working
in SAS and working with SAS datasets. Hence, the version still remains
to be SAS 9.1. Please help with regards to this.
|
SAS is not a database, and needs a database. If the database you are
using is not Oracle, why are you posting this here? The advice you are
getting will be inevitably geared at Oracle, and the possibilities and
impossibilities of the optimizer Oracle provides. I don't think anyone
here has any SAS knowledge.
Also, if the affected table has been properly indexed, and the HAVING
clause is not being misused to get a non-performant sql-statement, the
size of the table doesn't matter at all.
In summary: we still don't know anything, and chances are you are
using Oracle and don't know anything about it. In that case it will be
very difficult to help you out, as we won't go on asking for
information forever, and we are not looking over your shoulder.
--
Sybrand Bakker, Senior Oracle DBA |
|
| Back to top |
|
|
Joel Garry
*nix forums Guru
Joined: 24 Mar 2005
Posts: 1044
|
| Posted: Wed Jul 19, 2006 9:17 pm Post subject:
Re: Optimisation of a sql query
|
|
|
Abhijat wrote:
| Quote: |
A quick note to Sybrand and David: I guess I could not make myself very
clear in my last mail. I am not working on Oracle tables. I am working
in SAS and working with SAS datasets. Hence, the version still remains
to be SAS 9.1. Please help with regards to this.
|
This is an Oracle group. It would be silly to ask non-Oracle questions
here. If you are getting the data from an Oracle database, please tell
us the version.
In Oracle, only very old software versions (or very obscure situations)
care what order the SQL is in. Oracle will use statistics on the
tables to find the best access method for the data. That is the plan
everyone is talking about - before it even runs, you can get a pretty
good idea what the plan will be, although when the query actually runs
Oracle may indeed use a different plan. Again, we can help you figure
out what is actually going on if you are getting the data from an
Oracle database. If you expect different databases to execute the same
SQL the same way, you are in for a rude awakening.
And Mladen is wrong about one thing, portable SQL will not suck equally
on every database. It will likely suck extremely badly on some
databases, but only suck very badly on others.
jg
--
@home.com is bogus.
http://www.ravenblack.net/random/surreal/shakespeare.html |
|
| Back to top |
|
|
Charles Hooper
*nix forums addict
Joined: 09 Jul 2006
Posts: 51
|
| Posted: Wed Jul 19, 2006 10:56 pm Post subject:
Re: Optimisation of a sql query
|
|
|
Abhijat wrote:
| Quote: | Hi Sybrand/David/Brian/Charles/Mladen,
First of all many thanks for your valuable thoughts and invaluable
time.
I understand my mistake and below I m going to mention the steps that I
am going to take to correct. Please look into it too.
My actual code doesn't contain select *, it has fixed selections, hence
what David is mentioning about select * is automatically taken care of.
About using group by and having, I have to confess that the query I
have mentioned here is slightily different from the actual query. The
actual query does contain selections like "SUM(var1)".
The actual query that I intend to execute is like this
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT
DISTINCT B.MERCHANT, "WITHOUT AQUIRER" AS MODEL, "DISCOVER" AS
NEW_ISSUER,"DISCOVER" AS ISSUER,
B.MCC, "MAY06" AS TIME, COUNT(A.ACCT_NBR) AS CO_TRANSACTIONS,
SUM(A.AUTH_AMT) AS AUTHORIZED ,
SUM(A.ADDTNL_AMT) AS CASHOVER , (CALCULATED AUTHORIZED - CALCULATED
CASHOVER ) AS MERCHANDISE
FROM rd_at2.AUTH_MAY06 A LEFT JOIN dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_v2 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
GROUP BY B.MCC, B.MERCHANT
HAVING A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09";
So now that I m using aggregated functions, please suggest the best way
to improve my code. Shall I go ahead with the where clause as suggested
by you all or anything else. Please suggest with regards to the fact
that the AUTH_MAY06 is about 25 GB in size and the other table is about
500 MB.
Once again thanks to all for your overwhelming response.
Regards,
Abhijat
|
Here are appropriate uses of HAVING - note the aggregate in the HAVING:
SELECT
NAME,
BLOCK_SIZE,
SUM(BUFFERS)
FROM
V$BUFFER_POOL
GROUP BY
NAME,
BLOCK_SIZE
HAVING
SUM(BUFFERS) > 0;
SELECT
SQL_TEXT,
SUM(ONEPASS_EXECUTIONS) ONEPASS_CNT,
SUM(MULTIPASSES_EXECUTIONS) MPASS_CNT
FROM
V$SQL S,
V$SQL_WORKAREA WA
WHERE
S.ADDRESS = WA.ADDRESS
GROUP BY
SQL_TEXT
HAVING
SUM(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0;
SELECT
SUBSTR(SQL_TEXT, 1, 60),
COUNT(*)
FROM
V$SQLSTATS
WHERE
EXECUTIONS < 4
GROUP BY
SUBSTR(SQL_TEXT, 1, 60)
HAVING
COUNT(*) > 1;
You will want to eliminate as many rows/records as soon as possible in
the select statement - this does not necessarily refer to the order of
the items in the WHERE clause, as Oracle will often change the order of
the items in the WHERE clause when trying to find the most efficient
way to process the SQL statement. Since your HAVING clause still does
not include a COUNT(), SUM(), MIN(), MAX(), etc., you do not need the
HAVING. Instead, move those items currently in the HAVING to a WHERE
clause, as has been suggested by several people already. The WHERE
clause will remove the unnecessary rows/records before those rows are
passed on to the grouping (and possible sorting operation for Oracle
releases before 10.2). Once you have the WHERE and HAVING clauses
fixed, you can start tuning the SQL statement.
The tuning method varies depending on the version of Oracle that
contains the SAS data, the initialization parameters that are in effect
for the database, the indexes that are present for the database tables,
etc. Please excecute the following query and post the results:
SELECT
NAME,
VALUE
FROM
V$PARAMETER
ORDER BY
UPPER(NAME);
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. |
|
| Back to top |
|
|
Mladen Gogala
*nix forums Guru
Joined: 11 Mar 2005
Posts: 948
|
| Posted: Wed Jul 19, 2006 11:28 pm Post subject:
Re: Optimisation of a sql query
|
|
|
On Wed, 19 Jul 2006 14:17:44 -0700, joel garry wrote:
| Quote: | And Mladen is wrong about one thing, portable SQL will not suck equally
on every database. It will likely suck extremely badly on some
databases, but only suck very badly on others.
|
I stand corrected. Your are right.
--
http://www.mgogala.com |
|
| Back to top |
|
|
Brian Peasland
*nix forums Guru
Joined: 04 Apr 2006
Posts: 301
|
| Posted: Wed Jul 19, 2006 11:43 pm Post subject:
Re: Optimisation of a sql query
|
|
|
| Quote: | My actual code doesn't contain select *, it has fixed selections, hence
what David is mentioning about select * is automatically taken care of.
About using group by and having, I have to confess that the query I
have mentioned here is slightily different from the actual query. The
actual query does contain selections like "SUM(var1)".
|
So the previous posts were all a waste of everyone's time because you
did not post the *actual* SQL statement?!?!?!
| Quote: | The actual query that I intend to execute is like this
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT
DISTINCT B.MERCHANT, "WITHOUT AQUIRER" AS MODEL, "DISCOVER" AS
NEW_ISSUER,"DISCOVER" AS ISSUER,
B.MCC, "MAY06" AS TIME, COUNT(A.ACCT_NBR) AS CO_TRANSACTIONS,
SUM(A.AUTH_AMT) AS AUTHORIZED ,
SUM(A.ADDTNL_AMT) AS CASHOVER , (CALCULATED AUTHORIZED - CALCULATED
CASHOVER ) AS MERCHANDISE
FROM rd_at2.AUTH_MAY06 A LEFT JOIN dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_v2 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
GROUP BY B.MCC, B.MERCHANT
HAVING A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09";
|
Move the HAVING clause to the WHERE clause instead:
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT
DISTINCT B.MERCHANT, "WITHOUT AQUIRER" AS MODEL, "DISCOVER" AS
NEW_ISSUER,"DISCOVER" AS ISSUER,
B.MCC, "MAY06" AS TIME, COUNT(A.ACCT_NBR) AS CO_TRANSACTIONS,
SUM(A.AUTH_AMT) AS AUTHORIZED ,
SUM(A.ADDTNL_AMT) AS CASHOVER , (CALCULATED AUTHORIZED - ALCULATED
CASHOVER ) AS MERCHANDISE
FROM rd_at2.AUTH_MAY06 A LEFT JOIN p_ab.NOVUS_MCC_CORP_HQ_LOOKUP_v2 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
WHERE A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09"
GROUP BY B.MCC, B.MERCHANT;
HTH,
Brian
--
===================================================================
Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown |
|
| Back to top |
|
|
Bob Jones
*nix forums Guru Wannabe
Joined: 28 Jun 2005
Posts: 265
|
| Posted: Wed Jul 19, 2006 11:57 pm Post subject:
Re: Optimisation of a sql query
|
|
|
| Quote: | And Mladen is wrong about one thing, portable SQL will not suck equally
on every database. It will likely suck extremely badly on some
databases, but only suck very badly on others.
|
Non-portable SQL has no chance to suck on other databases at all. |
|
| Back to top |
|
|
Abhijat
*nix forums beginner
Joined: 19 Jul 2006
Posts: 4
|
| Posted: Thu Jul 20, 2006 6:46 am Post subject:
Re: Optimisation of a sql query
|
|
|
Guys,
Thanks a lot for your time , comments , suggestions and remarks!
I think there are couple of things about a couple of things that I can
safely conclude from here.
1. I should not post a query of SAS related stuff on oracle group,
even if its finally a sql query that I ve been talking about.
2. I should post the actual query coz u never know what important stuff
are u missing while u r modifying your query.
3. Having clause is a waste here. I should go ahead with where clause
as suggested by almost everybody.
Once again, thanks a lot for your time and concern.
Sincere apologies to all of you to provide you with the wrong query in
my first mail and wasting your time.
I ll try look for a sas group in google now.
Thanks and Regards,
Abhijat.
Brian Peasland wrote:
| Quote: | My actual code doesn't contain select *, it has fixed selections, hence
what David is mentioning about select * is automatically taken care of.
About using group by and having, I have to confess that the query I
have mentioned here is slightily different from the actual query. The
actual query does contain selections like "SUM(var1)".
So the previous posts were all a waste of everyone's time because you
did not post the *actual* SQL statement?!?!?!
The actual query that I intend to execute is like this
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT
DISTINCT B.MERCHANT, "WITHOUT AQUIRER" AS MODEL, "DISCOVER" AS
NEW_ISSUER,"DISCOVER" AS ISSUER,
B.MCC, "MAY06" AS TIME, COUNT(A.ACCT_NBR) AS CO_TRANSACTIONS,
SUM(A.AUTH_AMT) AS AUTHORIZED ,
SUM(A.ADDTNL_AMT) AS CASHOVER , (CALCULATED AUTHORIZED - CALCULATED
CASHOVER ) AS MERCHANDISE
FROM rd_at2.AUTH_MAY06 A LEFT JOIN dp_ab.NOVUS_MCC_CORP_HQ_LOOKUP_v2 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
GROUP BY B.MCC, B.MERCHANT
HAVING A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09";
Move the HAVING clause to the WHERE clause instead:
CREATE TABLE dp_ab.CASH_OVER_SUMMARY_1_AUTH AS
SELECT
DISTINCT B.MERCHANT, "WITHOUT AQUIRER" AS MODEL, "DISCOVER" AS
NEW_ISSUER,"DISCOVER" AS ISSUER,
B.MCC, "MAY06" AS TIME, COUNT(A.ACCT_NBR) AS CO_TRANSACTIONS,
SUM(A.AUTH_AMT) AS AUTHORIZED ,
SUM(A.ADDTNL_AMT) AS CASHOVER , (CALCULATED AUTHORIZED - ALCULATED
CASHOVER ) AS MERCHANDISE
FROM rd_at2.AUTH_MAY06 A LEFT JOIN p_ab.NOVUS_MCC_CORP_HQ_LOOKUP_v2 B
ON A.NOVUS_MRCH_NBR = B.NOVS_MRCH_NBR
WHERE A.RSPNS_CDE = "00"
AND A.TST_ACCT_CDE = ""
AND A.AUTH_TRAN_CDE IN ("0110","0120")
AND A.AUTH_PROC_1_CDE = "09"
GROUP BY B.MCC, B.MERCHANT;
HTH,
Brian
--
===================================================================
Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net
Remove the "nospam." from the email address to email me.
"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown |
|
|
| Back to top |
|
|
Joel Garry
*nix forums Guru
Joined: 24 Mar 2005
Posts: 1044
|
| Posted: Thu Jul 20, 2006 2:03 pm Post subject:
Re: Optimisation of a sql query
|
|
|
Bob Jones wrote:
| Quote: | And Mladen is wrong about one thing, portable SQL will not suck equally
on every database. It will likely suck extremely badly on some
databases, but only suck very badly on others.
Non-portable SQL has no chance to suck on other databases at all.
|
And that's a bad thing?
Bob, I work daily with portable code. I deal with unnecessary problems
daily with portable code. In fact, it is quite lucrative, as few
people understand both the semantics of the code (it's not SQL, but
rather a 4GL that generates SQL for OCI) and the issues on the Oracle
side, and how to deal the frustrations involved in not being able to
fully utilize Oracle. It actually does perform amazingly well, if you
ignore the lockups due to a non-Oracle locking and concurrency model,
and having to manually fix flags in the data when things go south, and
ignore some pretty poor programming by high priced consultants. I
guess you could say that about any cross-db enterprise software, eh?
At least this one gives customers all the source - which means any site
will likely have some verrrrrrrry strange code.
jg
--
@home.com is bogus.
The woman who mistook her hat for a horse:
http://www.signonsandiego.com/teaserimages/060720delmarhat.jpg |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Thu Dec 04, 2008 2:33 am | All times are GMT
|
|
Loan | Mortgage Calculator | Teen Chat | Loan | Credit Cards
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
»
Server
