| DB performance problem or "bad" SQL query [message #599449] |
Thu, 24 October 2013 11:20  |
 |
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
|
Senior Member |
|
|
Hi
I am not DBA. My job to programm SQL and Shell code. Now my question: is our db very slow or the SQL code "bad"?
SQL code
SELECT 'DELETE rss_user WITH rss_user_name = "'
|| LDAP.HOST_CN
|| '" , rss_name = "LDAP" , rss_type = "LDAP";'
FROM (SELECT (SUBSTR (LOWER (ALIAS_FQDN),
1,
INSTR (LOWER (ALIAS_FQDN), '.') - 1))
AS HOSTNAME
FROM TEST1
WHERE ALIAS_NETWORKADDRESSTYPE = 'Cluster-IP (Cluster-Member)'
AND (SUBSTR (LOWER (ALIAS_FQDN),
1,
INSTR (LOWER (ALIAS_FQDN), '.') - 1))
IS NOT NULL) CLUSTERNAME_MXO
INNER JOIN
(SELECT HOST_NAME, HOST_CN
FROM UAM_RSS_USER_VIEW
WHERE HOST_NAME IS NOT NULL) LDAP
ON CLUSTERNAME_MXO.HOSTNAME = LDAP.HOST_NAME;
execution time
explain plan
see Toad screenshot
result:
> wc -l output.24102013.17h.csv
23 clustername_delete.24102013.17h.csv
[Updated on: Thu, 24 October 2013 11:23] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: DB performance problem or "bad" SQL query [message #599465 is a reply to message #599461] |
Thu, 24 October 2013 12:43  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 24 October 2013 22:51
Quote:Regarding your SQL, you are using LOWER function. Is a function based index created?
How will this help in anyway to speed up the query?
Or, in your way of posting:
Now just think about SUBSTR.
Sorry, i did not notice the entire expression. What I meant was, may be OP has a regular index, but due to the function, index won't be used.
Quote:I would rather handle the case sensitivity during loading itself instead of putting pressure on the optimizer.
And what would be the rule for the case oof "Cluster-IP (Cluster-Member)"?
This is YOUR try. Good luck, and keep up the work!
No issue, man. Either you would share or just learn here 
Actually, the function is on column ALIAS_FQDN.
>SUBSTR (LOWER (ALIAS_FQDN)
And, "Cluster-IP (Cluster-Member) " is in another column ALIAS_NETWORKADDRESSTYPE.
Thanks for the good words
|
|
|
|
|
|
|
|
| Re: DB performance problem or "bad" SQL query [message #599693 is a reply to message #599567] |
Mon, 28 October 2013 05:02 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The lowers in this bit don't do anything useful and can be removed:
AND (SUBSTR (LOWER (ALIAS_FQDN),
1,
INSTR (LOWER (ALIAS_FQDN), '.') - 1))
IS NOT NULL) CLUSTERNAME_MXO
and this bit:
SUBSTR (LOWER (ALIAS_FQDN),
1,
INSTR (LOWER (ALIAS_FQDN), '.') - 1))
AS HOSTNAME
could be more simply written as:
LOWER(SUBSTR(ALIAS_FQDN,
1,
INSTR (ALIAS_FQDN, '.') - 1
)
)
AS HOSTNAME
|
|
|
|
|
|
| Re: DB performance problem or "bad" SQL query [message #599700 is a reply to message #599476] |
Mon, 28 October 2013 05:32 |
|
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
|
Senior Member |
|
|
Lalit Kumar B wrote on Thu, 24 October 2013 13:32rc3d wrote on Thu, 24 October 2013 22:43
Note
-----
- 'PLAN_TABLE' is old version
Fix this first.
new stats:
Elapsed: 00:13:02.72
Execution Plan
----------------------------------------------------------
Plan hash value: 259219601
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41 | 2665 | 331 (2)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| RSS_USER | 2 | 74 | 7 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 41 | 2665 | 331 (2)| 00:00:04 |
|* 3 | TABLE ACCESS FULL | MXONLINE_TEST1 | 23 | 644 | 168 (2)| 00:00:03 |
|* 4 | INDEX RANGE SCAN | RSS_USER_IX_6 | 36 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ALIAS_NETWORKADDRESSTYPE"='Cluster-IP (Cluster-Member)' AND
SUBSTR(LOWER("ALIAS_FQDN"),1,INSTR(LOWER("ALIAS_FQDN"),'.')-1) IS NOT NULL)
4 - access("RSS_USER"."RSS_TYPE"='ODSSRV')
filter("FUNC_RSS_USER"(ROWIDTOCHAR(ROWID),'__98__ODSSRV_host=') IS NOT NULL
AND SUBSTR(LOWER("ALIAS_FQDN"),1,INSTR(LOWER("ALIAS_FQDN"),'.')-1)="FUNC_RSS_USER"(ROW
IDTOCHAR(ROWID),'__98__ODSSRV_host='))
Statistics
----------------------------------------------------------
2873014 recursive calls
0 db block gets
2932990 consistent gets
747 physical reads
0 redo size
3771 bytes sent via SQL*Net to client
480 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23 rows processed
|
|
|
|
|
|
|
|
| Re: DB performance problem or "bad" SQL query [message #599721 is a reply to message #599702] |
Mon, 28 October 2013 06:39 |
|
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
|
Senior Member |
|
|
cookiemonster wrote on Mon, 28 October 2013 05:34And post the sql of the view
little bit obfuscated because of Google:
CREATE OR REPLACE FORCE VIEW ESSDB.UAM_RSS_USER_XXXSRV
(
HOST_CN,
RSS_NAME,
RSS_TYPE,
HOST_NAME,
HOST_DESCRIPTION,
XXX_CUSTOMER
)
AS
SELECT rss_user.rss_user_name,
rss_user.rss_name,
rss_user.rss_type,
func_rss_user (ROWID, '__98__XXXSRV_host=') host_name,
func_rss_user (ROWID, '__98__XXXSRV_description=') host_description,
func_rss_user (ROWID, '__98__XXXSRV_customer=') XXX_customer
FROM rss_user
WHERE rss_user.rss_type = 'XXXSRV';
CREATE OR REPLACE FUNCTION ESSDB."FUNC_RSS_USER" (id rowid,tag varchar2 default 'ID=') return varchar2 is
long_tmp long(32000);
mail_start number;
mail_end number;
tag_length number;
begin
tag_length:=length(tag);
select add_info_master into long_tmp
from rss_user where rowid=id;
mail_start:=instr(long_tmp,tag)+tag_length;
if mail_start=tag_length
then return null;
end if;
mail_end:=instr(substr(long_tmp,mail_start),';')-1;
return substr(long_tmp,mail_start,mail_end);
end;
/
|
|
|
|
| Re: DB performance problem or "bad" SQL query [message #599725 is a reply to message #599721] |
Mon, 28 October 2013 06:57 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
OK - that function is probably the cause of most of the pain here.
Why on earth are you passing a rowid to the function instead of add_info_master?
Requerying the row in rss_user the main query already has is a completely pointless waste of CPU cycles.
|
|
|
|
| Re: DB performance problem or "bad" SQL query [message #599729 is a reply to message #599701] |
Mon, 28 October 2013 07:04 |
|
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
|
Senior Member |
|
|
cookiemonster wrote on Mon, 28 October 2013 05:34Check the stats on the tables are up to date, apply the fixes I suggested and then retry the plan.
new stats (no improvement in elapsed time):
Elapsed: 00:13:02.56
Execution Plan
----------------------------------------------------------
Plan hash value: 259219601
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 41 | 2665 | 331 (2)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| RSS_USER | 2 | 74 | 7 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 41 | 2665 | 331 (2)| 00:00:04 |
|* 3 | TABLE ACCESS FULL | MXONLINE_TEST1 | 23 | 644 | 168 (2)| 00:00:03 |
|* 4 | INDEX RANGE SCAN | RSS_USER_IX_6 | 36 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("ALIAS_NETWORKADDRESSTYPE"='Cluster-IP (Cluster-Member)' AND
SUBSTR(LOWER("ALIAS_FQDN"),1,INSTR(LOWER("ALIAS_FQDN"),'.')-1) IS NOT NULL)
4 - access("RSS_USER"."RSS_TYPE"='ODSSRV')
filter("FUNC_RSS_USER"(ROWIDTOCHAR(ROWID),'__98__ODSSRV_host=') IS NOT NULL
AND LOWER(SUBSTR("ALIAS_FQDN",1,INSTR("ALIAS_FQDN",'.')-1))="FUNC_RSS_USER"(ROWIDTOCHA
R(ROWID),'__98__ODSSRV_host='))
Statistics
----------------------------------------------------------
2873062 recursive calls
0 db block gets
2932996 consistent gets
1061 physical reads
0 redo size
3771 bytes sent via SQL*Net to client
480 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
23 rows processed
|
|
|
|
|
|
|
|
|
|
| Re: DB performance problem or "bad" SQL query [message #599736 is a reply to message #599732] |
Mon, 28 October 2013 07:37 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Lalit Kumar B wrote on Mon, 28 October 2013 12:20From where in the code the table MXONLINE_TEST1 is being accessed? I couldn't figure it out anywhere.
I imagine it's test1 in the original query.
@rc3d - if you're going obfuscate table names, do it consistently, or don't bother, it just confuses matters otherwise.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: DB performance problem or "bad" SQL query [message #599841 is a reply to message #599837] |
Tue, 29 October 2013 06:30 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
rc3d wrote on Tue, 29 October 2013 11:18cookiemonster wrote on Tue, 29 October 2013 05:06What does this give:
SELECT count(*) FROM rss_user
WHERE rss_user.rss_type = 'XXXSRV';
That's 100 times what the explain thinks, so the stats are still out, but that isn't the main problem
The reason why this is slow is because of the data model.
You're joining CLUSTERNAME_MXO.HOSTNAME to LDAP.HOST_NAME
CLUSTERNAME_MXO.HOSTNAME is this:
SUBSTR (LOWER (ALIAS_FQDN),
1,
INSTR (LOWER (ALIAS_FQDN), '.') - 1)
LDAP.HOST_NAME is this:
func_rss_user (ROWID, '__98__XXXSRV_host=') host_name
And that function does something similar to the top bit code.
So for every row in CLUSTERNAME_MXO WHERE ALIAS_NETWORKADDRESSTYPE = 'Cluster-IP (Cluster-Member)' oracle has to compute the top substr/instr
For every row in rss_user WHERE rss_user.rss_type = 'XXXSRV' oracle calls a function that re-queries the same row to get add_info_master and then does substr and instrs on that to get a particular fragment.
And then oracle joins the two results.
That is never, ever going to be fast.
Matters could be improved a little bit by removing the select from the function as I already suggested. They could probably be improved a bit more by removing the function all together and doing the necessary instrs and substrs directly in the view (or use a regexp in the view, I'm sure some of the others could make suggestions, I'm rubbish at them)).
But if you really want it to be fast you need to fix the data model so that the data you are joining on is in its own column, rather than stored as a fragment of a clob.
rc3d wrote on Tue, 29 October 2013 11:18
can you please post new code for "CREATE OR REPLACE FUNCTION ESSDB."FUNC_RSS_USER""
this function is used by 90% of all SQL scripts. It's a production system and I have Zero PL/SQL knowhow. I need to be with caution, because don't wish to get fired.
If 90% of sql scripts are using that function to pull data from the clob then your data model is even worse than I thought.
You need to split that clob out into proper columns.
As for supplying new code, I can, but if you really don't know enough PL/SQL to change 1 parameter and remove 1 select from a function then you don't know enough to check I've supplied working code and if I make a mistake you're getting fired anyway.
Are there not people at your company who are capable of making that change?
Plus if you're being asked to tune sql that makes use of functions then you need to learn PL/SQL. A simple (really simple) change like this would be a good place to start.
|
|
|
|
|
|
| Re: DB performance problem or "bad" SQL query [message #599856 is a reply to message #599846] |
Tue, 29 October 2013 09:54 |
cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Considering the function consists of:
a) a select statement,
b) a single basic IF statement,
c) some calls to length, instr and substr - functions that all behave exactly the same in pl/sql as they do in sql,
I'd have to say you're not really trying.
Seriously - what in the function do you not understand? cause most of it is actually sql and the remainder is as basic as it gets and does exactly what it says.
Now in this case you can't change the function - it's apparently used in too many places and you would have to change every single call to it.
But you could very easily create a copy of the function with a new name and apply the following simple changes:
Change the id parameter to be of the same type as add_info_master and change it's name to something apropriate (p_add_info_master for example).
Then there's a select that populates long_tmp with the value of add_info_master, remove that and replace with a line of code that sets long_tmp to the value of the parameter.
That's literally all you need to do to the function, then you just need to change the view query to pass add_info_master instead of rowid.
Alternatively - since you're more comfortable with sql, ditch the function entirely and change the view query to extract the necessary parts of the clob using instr and substr( or reg_exp) in the view query itself.
|
|
|
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
