OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » SQL Tune (Oracle 10g Database 10.2.0.4 + Oracle Client 9.2.0.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Tune [message #429917]

Fri, 06 November 2009 05:09

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Hi,

I am facing one performance issues in my below sql query. This query running with in 11 sec but sometime it's running more than 20 min. Please help on this.

Database Vesion 10 R2 (10.2.0.4) but SQL Query running from 9i R2

Thank you in advance.

select count(*) FROM STATEMENTS.STATEMENTS_BINS
   WHERE ROWID NOT IN  (SELECT MIN(ROWID) FROM STATEMENTS.STATEMENTS_BINS GROUP BY BIN, ORG, LOGO)
      OR ((BIN = 0 OR BIN = NULL) OR (ORG = 0 OR ORG = NULL) OR (LOGO = 0 OR LOGO = NULL));SQL>   2    3
 
  COUNT(*)
----------
         0
 
Elapsed: 00:00:10.41
 
Execution Plan
----------------------------------------------------------
Plan hash value: 894185258
 
--------------------------------------------------------------------------------
---------
 
| Id  | Operation             | Name            | Rows  | Bytes | Cost (%CPU)| T
ime     |
 
--------------------------------------------------------------------------------
---------
 
|   0 | SELECT STATEMENT      |                 |     1 |    18 | 72910   (1)| 0
0:14:35 |
 
|   1 |  SORT AGGREGATE       |                 |     1 |    18 |            |
        |
 
|*  2 |   FILTER              |                 |       |       |            |
        |
 
|   3 |    TABLE ACCESS FULL  | STATEMENTS_BINS |   656 | 11808 | 72910   (1)| 0
0:14:35 |
 
|*  4 |    FILTER             |                 |       |       |            |
        |
 
|   5 |     HASH GROUP BY     |                 |     7 |   126 | 72911   (1)| 0
0:14:35 |
 
|   6 |      TABLE ACCESS FULL| STATEMENTS_BINS |   656 | 11808 | 72909   (1)| 0
0:14:35 |
 
--------------------------------------------------------------------------------
---------
 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("ORG"=0 OR "ORG"=TO_NUMBER(NULL) OR "LOGO"=0 OR
              "LOGO"=TO_NUMBER(NULL) OR "BIN"=0 OR "BIN"=TO_NUMBER(NULL) OR  NOT
 EXISTS
 
              (SELECT 0 FROM "STATEMENTS"."STATEMENTS_BINS" "STATEMENTS_BINS" GR
OUP BY
 
              "BIN","ORG","LOGO" HAVING MIN(ROWID)=:B1))
   4 - filter(MIN(ROWID)=:B1)
 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    2545218  consistent gets
          0  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Report message to a moderator

Re: SQL Tune [message #429923 is a reply to message #429917]

Fri, 06 November 2009 05:35

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Quote:

Database Vesion 10 R2 (10.2.0.4) but SQL Query running from 9i R2

What does this mean?
Either the query is running on a 10g database or it isn't.

An alternative query that should get the same result, but only hit the table once is:

select sum(case when row_number() over (partition by BIN, ORG, LOGO) > 1 then 1
                when nvl(bin,0) = 0 then 1
                when nvl(org,0) = 0 then 1
                when nvl(logo,0)= 0 then 1
                else 0 end)
FROM   STATEMENTS.STATEMENTS_BINS

Can you post the plan from when the query runs slowly?

Report message to a moderator

Re: SQL Tune [message #429925 is a reply to message #429923]

Fri, 06 November 2009 05:50

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Database version 10.2.0.4 but end user executing sql query from 9i client connect to 10g database.

SQL> 
SQL> set timing on 
SQL> 
SQL> select sum(case when row_number() over (partition by BIN, ORG, LOGO) > 1 then 1
                when nvl(bin,0) = 0 then 1
                when nvl(org,0) = 0 then 1
                when nvl(logo,0)= 0 then 1
                else 0 end)
FROM   STATEMENTS.STATEMENTS_BINS
  2    3    4    5    6    7  
SQL> /
select sum(case when row_number() over (partition by BIN, ORG, LOGO) > 1 then 1
                     *
ERROR at line 1:
ORA-30483: window  functions are not allowed here


Elapsed: 00:00:00.00

Getting some issue. Please review your sql statement.

Thank you in advance.

[Updated on: Fri, 06 November 2009 05:51]

Report message to a moderator

Re: SQL Tune [message #429928 is a reply to message #429925]

Fri, 06 November 2009 06:06

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

 DELETE FROM STATEMENTS.STATEMENTS_BINS
   WHERE ROWID NOT IN
         (SELECT MIN(ROWID) FROM STATEMENTS.STATEMENTS_BINS GROUP BY BIN, ORG, LOGO)
      OR ((BIN = 0 OR BIN = NULL) OR (ORG = 0 OR ORG = NULL) OR
         (LOGO = 0 OR LOGO = NULL));
  COMMIT;

  
  UPDATE STATEMENTS.STATEMENTS_BINS
     SET BIN_LENGTH = LENGTH(BIN)
   WHERE BIN_LENGTH IS NULL;
  COMMIT;

We are trying to find duplicate records using the above two script. Here Delete statment taking long time. Instead delete i modified select statement.

As per plan report db consistent gets large; Is there any way to modify the above delete statment??

Thank you in advance.

Report message to a moderator

Re: SQL Tune [message #429930 is a reply to message #429925]

Fri, 06 November 2009 06:07

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Looks like your 9i client is doing some parsing - what is the client that your're using?

You can use this as a workround:

SELECT SUM(val)
FROM  (SELECT CASE WHEN row_number() OVER (PARTITION BY BIN, ORG, LOGO ORDER BY NULL) > 1 THEN 1
                   WHEN NVL(bin,0) = 0 THEN 1
                   WHEN NVL(org,0) = 0 THEN 1
                   WHEN NVL(logo,0)= 0 THEN 1
                   ELSE 0 END val
       FROM <TABLE>)

Hw often does the original query run slowly, and is there anything going on in the Db when this happens?

Report message to a moderator

Re: SQL Tune [message #429932 is a reply to message #429930]

Fri, 06 November 2009 06:19

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

SQL>
SQL> SELECT SUM(val)
  2  FROM  (SELECT CASE WHEN row_number() OVER (PARTITION BY BIN, ORG, LOGO ORDER BY NULL) > 1 THEN 1
                   WHEN NVL(bin,0) = 0 THEN 1
  3    4                     WHEN NVL(org,0) = 0 THEN 1
  5                     WHEN NVL(logo,0)= 0 THEN 1
  6                     ELSE 0 END val
  7  from STATEMENTS.STATEMENTS_BINS)
  8  /

  SUM(VAL)
----------
       374

Elapsed: 00:00:33.48

Execution Plan
----------------------------------------------------------
Plan hash value: 560992067

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |     1 |     3 | 73682   (1)| 00:14:45 |
|   1 |  SORT AGGREGATE      |                 |     1 |     3 |            |          |
|   2 |   VIEW               |                 |  1015 |  3045 | 73682   (1)| 00:14:45 |
|   3 |    WINDOW SORT       |                 |  1015 | 15225 | 73682   (1)| 00:14:45 |
|   4 |     TABLE ACCESS FULL| STATEMENTS_BINS |  1015 | 15225 | 73681   (1)| 00:14:45 |
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3934  consistent gets
        350  physical reads
          0  redo size
        516  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

Thanks for your prompt response.

>>Looks like your 9i client is doing some parsing - what is the client that your're using?

Yes. They are deleting duplicate records & updateing something daily jobs using batch script.

>>Hw often does the original query run slowly,

Only sometime running slowly.

Thank you in advance.

Report message to a moderator

Re: SQL Tune [message #429949 is a reply to message #429932]

Fri, 06 November 2009 08:04

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Well, that's got your consistent gets down by 3 orders of magnitude.

There's not a lot we can do without more information from you.

Quote:

what is the client that your're using?

Quote:

...and is there anything going on in the Db when this happens?

Quote:

Can you post the plan from when the query runs slowly?

Report message to a moderator

Re: SQL Tune [message #429952 is a reply to message #429949]

Fri, 06 November 2009 08:16

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

I am checking with end user. I'll update you all the details.

Thank you in advance.

Report message to a moderator

Previous Topic:	optimisation
Next Topic:	Oracle 10.2.0.4

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 18 07:45:26 CDT 2024