| SQL Tune [message #429917] |
Fri, 06 November 2009 05:09  |
|
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 |
|
|
| Re: SQL Tune [message #429923 is a reply to message #429917] |
Fri, 06 November 2009 05:35  |
JRowbottom
Messages: 5362
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?
|
|
|
| Re: SQL Tune [message #429925 is a reply to message #429923] |
Fri, 06 November 2009 05:50 |
|
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]
|
|
|
| Re: SQL Tune [message #429928 is a reply to message #429925] |
Fri, 06 November 2009 06:06 |
|
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.
|
|
|
| Re: SQL Tune [message #429930 is a reply to message #429925] |
Fri, 06 November 2009 06:07 |
JRowbottom
Messages: 5362
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?
|
|
|
| Re: SQL Tune [message #429932 is a reply to message #429930] |
Fri, 06 November 2009 06:19 |
|
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.
|
|
|
| Re: SQL Tune [message #429949 is a reply to message #429932] |
Fri, 06 November 2009 08:04 |
JRowbottom
Messages: 5362
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?
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
