Home » RDBMS Server » Performance Tuning » DB performance problem or "bad" SQL query (Oracle 10g)
DB performance problem or "bad" SQL query [message #599449] Thu, 24 October 2013 11:20 Go to next message
rc3d
Messages: 186
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

Elapsed: 00:13:55.10


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 #599450 is a reply to message #599449] Thu, 24 October 2013 11:27 Go to previous messageGo to next message
BlackSwan
Messages: 22810
Registered: January 2009
Senior Member
please try again on EXPLAIN PLAN utilizing details provided below

http://www.orafaq.com/wiki/Explain_Plan

are statistics current for all involved tables & indexes
icon5.gif  Re: DB performance problem or "bad" SQL query [message #599451 is a reply to message #599449] Thu, 24 October 2013 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Re: DB performance problem or "bad" SQL query [message #599458 is a reply to message #599449] Thu, 24 October 2013 12:04 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2375
Registered: May 2013
Location: World Wide on the Web
Senior Member
Regarding your SQL, you are using LOWER function. Is a function based index created? I would rather handle the case sensitivity during loading itself instead of putting pressure on the optimizer.
Re: DB performance problem or "bad" SQL query [message #599460 is a reply to message #599458] Thu, 24 October 2013 12:13 Go to previous messageGo to next message
rc3d
Messages: 186
Registered: September 2013
Location: Baden-Württemberg
Senior Member
Lalit Kumar B wrote on Thu, 24 October 2013 12:04
Regarding your SQL, you are using LOWER function. Is a function based index created? I would rather handle the case sensitivity during loading itself instead of putting pressure on the optimizer.


no there is no function based index. Does it make sense? Because we have only one script which uses LOWER on hostname column. The script runs every 14 days.

 cat output.24102013.18h.csv
[...]
Elapsed: 00:13:37.33

Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |    31 |  2015 |   296 |
|   1 |  TABLE ACCESS BY INDEX ROWID| RSS_USER       |     1 |    37 |     6 |
|   2 |   NESTED LOOPS              |                |    31 |  2015 |   296 |
|   3 |    TABLE ACCESS FULL        | MXONLINE_TEST1 |    23 |   644 |   168 |
|   4 |    INDEX RANGE SCAN         | RSS_USER_IX_6  |    28 |       |     1 |
------------------------------------------------------------------------------

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
    2983295  recursive calls
          0  db block gets
    3045617  consistent gets
        743  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


SQL> desc MXONLINE_TEST1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OS_FQDN                                            VARCHAR2(120)
 OS_OS                                              VARCHAR2(60)
 OS_OSVERSION                                       VARCHAR2(60)
 OS_VIRTUAL                                         VARCHAR2(60)
 OS_IP                                              VARCHAR2(60)
 HDW_NODENAME                                       VARCHAR2(60)
 HDW_LOCATION                                       VARCHAR2(60)
 HDW_PRODUCER                                       VARCHAR2(60)
 HDW_MODEL                                          VARCHAR2(60)
 ALIAS_FQDN                                         VARCHAR2(60)
 ALIAS_IP                                           VARCHAR2(60)
 ALIAS_NETWORKADDRESSTYPE                           VARCHAR2(60)
 HWLIFECYCLE                                        VARCHAR2(60)
 MAINTAINER                                         VARCHAR2(60)
 APPLICATION                                        VARCHAR2(60)
 IMPORTED                                           DATE

SQL> desc UAM_RSS_USER_ODSSRV
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 HOST_CN                                   NOT NULL VARCHAR2(511 CHAR)
 RSS_NAME                                  NOT NULL VARCHAR2(32 CHAR)
 RSS_TYPE                                  NOT NULL VARCHAR2(12 CHAR)
 HOST_NAME                                          VARCHAR2(4000)
 HOST_DESCRIPTION                                   VARCHAR2(4000)
 ODS_CUSTOMER                                       VARCHAR2(4000)


Re: DB performance problem or "bad" SQL query [message #599461 is a reply to message #599458] Thu, 24 October 2013 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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.

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 Smile

Re: DB performance problem or "bad" SQL query [message #599465 is a reply to message #599461] Thu, 24 October 2013 12:43 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2375
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 Smile


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 Smile


Re: DB performance problem or "bad" SQL query [message #599476 is a reply to message #599460] Thu, 24 October 2013 13:32 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2375
Registered: May 2013
Location: World Wide on the Web
Senior Member
rc3d wrote on Thu, 24 October 2013 22:43

 
Note
-----
   - 'PLAN_TABLE' is old version



Fix this first.
Re: DB performance problem or "bad" SQL query [message #599567 is a reply to message #599476] Sat, 26 October 2013 02:53 Go to previous messageGo to next message
rc3d
Messages: 186
Registered: September 2013
Location: Baden-Württemberg
Senior Member
Lalit Kumar B wrote on Thu, 24 October 2013 13:32
rc3d wrote on Thu, 24 October 2013 22:43

 
Note
-----
   - 'PLAN_TABLE' is old version



Fix this first.


I will look with our DBA next week. I don't have the rights (no access to $ORACLE_HOME) to fix this:

drop table plan_table;
@$ORACLE_HOME/rdbms/admin/utlxplan.sql
Re: DB performance problem or "bad" SQL query [message #599693 is a reply to message #599567] Mon, 28 October 2013 05:02 Go to previous messageGo to next message
cookiemonster
Messages: 10961
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 #599698 is a reply to message #599693] Mon, 28 October 2013 05:30 Go to previous messageGo to next message
Roachcoach
Messages: 1206
Registered: May 2010
Location: UK
Senior Member
That's a lot of recursive calls. Your stats also look suspiciously out for the amount of gets going on compared to the bytes in the plan, maybe they're ok....but I'd check
Re: DB performance problem or "bad" SQL query [message #599700 is a reply to message #599476] Mon, 28 October 2013 05:32 Go to previous messageGo to next message
rc3d
Messages: 186
Registered: September 2013
Location: Baden-Württemberg
Senior Member
Lalit Kumar B wrote on Thu, 24 October 2013 13:32
rc3d 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 #599701 is a reply to message #599700] Mon, 28 October 2013 05:34 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Check the stats on the tables are up to date, apply the fixes I suggested and then retry the plan.
Re: DB performance problem or "bad" SQL query [message #599702 is a reply to message #599701] Mon, 28 October 2013 05:34 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
And post the sql of the view
Re: DB performance problem or "bad" SQL query [message #599721 is a reply to message #599702] Mon, 28 October 2013 06:39 Go to previous messageGo to next message
rc3d
Messages: 186
Registered: September 2013
Location: Baden-Württemberg
Senior Member
cookiemonster wrote on Mon, 28 October 2013 05:34
And 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 Go to previous messageGo to next message
cookiemonster
Messages: 10961
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 Go to previous messageGo to next message
rc3d
Messages: 186
Registered: September 2013
Location: Baden-Württemberg
Senior Member
cookiemonster wrote on Mon, 28 October 2013 05:34
Check 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 #599730 is a reply to message #599725] Mon, 28 October 2013 07:06 Go to previous messageGo to next message
rc3d
Messages: 186
Registered: September 2013
Location: Baden-Württemberg
Senior Member
cookiemonster wrote on Mon, 28 October 2013 06:57
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.


Sorry. I don't understand Embarassed
This function written by an external consultant. Can you provide better code?
Re: DB performance problem or "bad" SQL query [message #599732 is a reply to message #599730] Mon, 28 October 2013 07:20 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2375
Registered: May 2013
Location: World Wide on the Web
Senior Member
From where in the code the table MXONLINE_TEST1 is being accessed? I couldn't figure it out anywhere.

[Updated on: Mon, 28 October 2013 07:21]

Report message to a moderator

Re: DB performance problem or "bad" SQL query [message #599735 is a reply to message #599730] Mon, 28 October 2013 07:36 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
rc3d wrote on Mon, 28 October 2013 12:06
cookiemonster wrote on Mon, 28 October 2013 06:57
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.


Sorry. I don't understand Embarassed
This function written by an external consultant. Can you provide better code?


The function is passed a rowid that is used in a select.
The select is pointless as you can just pass add_info_master as a parameter instead of the rowid.
So change the function and view accordingly.
Re: DB performance problem or "bad" SQL query [message #599736 is a reply to message #599732] Mon, 28 October 2013 07:37 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Lalit Kumar B wrote on Mon, 28 October 2013 12:20
From 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 #599751 is a reply to message #599736] Mon, 28 October 2013 10:43 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
also what datatype is rss_user.add_info_master and what columns are in the index RSS_USER_IX_6?
Re: DB performance problem or "bad" SQL query [message #599823 is a reply to message #599751] Tue, 29 October 2013 04:57 Go to previous messageGo to next message
rc3d
Messages: 186
Registered: September 2013
Location: Baden-Württemberg
Senior Member
cookiemonster wrote on Mon, 28 October 2013 10:43
also what datatype is rss_user.add_info_master and what columns are in the index RSS_USER_IX_6?


ADD_INFO_MASTER is CLOB. RSS_USER_IX_6 = column RSS_TYPE.

SQL> desc rss_user
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RSS_USER_NAME                             NOT NULL VARCHAR2(511 CHAR)
 RSS_NAME                                  NOT NULL VARCHAR2(32 CHAR)
 RSS_TYPE                                  NOT NULL VARCHAR2(12 CHAR)
 LEAF_USER_NAME                                     VARCHAR2(511 CHAR)
 OE_FULL_NAME                                       VARCHAR2(255 CHAR)
 RSS_USER_KEY_NAME                                  VARCHAR2(511 CHAR)
 RSS_USER_NAME__LC                                  VARCHAR2(511 CHAR)
 RSS_NAME__LC                                       VARCHAR2(32 CHAR)
 RSS_TYPE__LC                                       VARCHAR2(12 CHAR)
 DEF_UG_NAME                                        VARCHAR2(255 CHAR)
 DEF_UG_NAME__LC                                    VARCHAR2(255 CHAR)
 OE_FULL_NAME__LC                                   VARCHAR2(255 CHAR)
 ADMIN                                     NOT NULL VARCHAR2(24 CHAR)
 ADMIN__LC                                          VARCHAR2(24 CHAR)
 REVOKED                                   NOT NULL NUMBER(11)
 REVOKED__LC                                        NUMBER(11)
 REVOKE_REASON                                      VARCHAR2(10 CHAR)
 REVOKE_REASON__LC                                  VARCHAR2(10 CHAR)
 REVOKE_TIME                                        DATE
 REVOKE_TIME__LC                                    VARCHAR2(14 CHAR)
 LOCKED                                             VARCHAR2(1 CHAR)
 SUSPENDED                                          VARCHAR2(1 CHAR)
 CLASS                                              VARCHAR2(30 CHAR)
 PASSWORD                                           VARCHAR2(32 CHAR)
 NEW_PASSWORD                                       VARCHAR2(32 CHAR)
 PASSWORD_LIFE                                      VARCHAR2(10 CHAR)
 PASSWORD_SYNC                             NOT NULL NUMBER(11)
 PASSWORD_EXPIRATION                                DATE
 USER_ID                                   NOT NULL VARCHAR2(20 CHAR)
 UPD_DEFGRP_ACTION                                  VARCHAR2(4 CHAR)
 UPD_TIME                                           DATE
 SYNC_TIME                                          DATE
 STATUS                                             NUMBER(11)
 PENDING_ACTION                                     VARCHAR2(12 CHAR)
 DOWNLOAD_MARK                             NOT NULL NUMBER(11)
 CHECKSUM                                           VARCHAR2(8 CHAR)
 INSERT_TRANS                                       NUMBER(11)
 PENDING_TRANS                                      NUMBER(11)
 ADD_INFO_MASTER                                    CLOB
 ADD_INFO_IX_1                                      VARCHAR2(255 CHAR)
 ADD_INFO_IX_2                                      VARCHAR2(255 CHAR)
 ADD_INFO_IX_3                                      VARCHAR2(255 CHAR)
 ADD_INFO_IX_4                                      VARCHAR2(30 CHAR)
 ADD_INFO_IX_5                                      VARCHAR2(30 CHAR)
 ADD_INFO_IX_6                                      VARCHAR2(30 CHAR)

SQL> SELECT * FROM  USER_IND_COLUMNS WHERE INDEX_NAME = 'RSS_USER_IX_6';

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
--------------- ------------- ----------- ----
RSS_USER_IX_6                  RSS_USER
RSS_TYPE
              1            36          12 ASC
Re: DB performance problem or "bad" SQL query [message #599827 is a reply to message #599823] Tue, 29 October 2013 05:06 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
What does this give:
SELECT count(*) FROM rss_user
WHERE rss_user.rss_type = 'XXXSRV';

Re: DB performance problem or "bad" SQL query [message #599837 is a reply to message #599827] Tue, 29 October 2013 06:18 Go to previous messageGo to next message
rc3d
Messages: 186
Registered: September 2013
Location: Baden-Württemberg
Senior Member
cookiemonster wrote on Tue, 29 October 2013 05:06
What does this give:
SELECT count(*) FROM rss_user
WHERE rss_user.rss_type = 'XXXSRV';



3560


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.
Re: DB performance problem or "bad" SQL query [message #599841 is a reply to message #599837] Tue, 29 October 2013 06:30 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
rc3d wrote on Tue, 29 October 2013 11:18
cookiemonster wrote on Tue, 29 October 2013 05:06
What does this give:
SELECT count(*) FROM rss_user
WHERE rss_user.rss_type = 'XXXSRV';



3560


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 #599846 is a reply to message #599449] Tue, 29 October 2013 06:49 Go to previous messageGo to next message
rc3d
Messages: 186
Registered: September 2013
Location: Baden-Württemberg
Senior Member
thank you cookiemonster for your analysis.

the ERD/data model can't be changed. I now understand that's a ****** decision to store so much fields in a single CLOB. This is a product we bought. If we would change it, the Windows front-end and much much more will be broken.

As of PL/SQL. It's on my TODO list. First I wish to pass SQL expert Oracle exam. I am junior, I am Oracle guy in training. Sorry.
Re: DB performance problem or "bad" SQL query [message #599856 is a reply to message #599846] Tue, 29 October 2013 09:54 Go to previous messageGo to next message
cookiemonster
Messages: 10961
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.
Re: DB performance problem or "bad" SQL query [message #599958 is a reply to message #599856] Wed, 30 October 2013 07:35 Go to previous messageGo to next message
michael_bialik
Messages: 602
Registered: July 2006
Senior Member
Post TKPROF
Re: DB performance problem or "bad" SQL query [message #599977 is a reply to message #599958] Wed, 30 October 2013 11:40 Go to previous messageGo to next message
rc3d
Messages: 186
Registered: September 2013
Location: Baden-Württemberg
Senior Member
michael_bialik wrote on Wed, 30 October 2013 07:35
Post TKPROF


http://www.orafaq.com/wiki/TKProf

I need to ask our DBA, because TKProf is in $ORACLE_HOME/bin
Re: DB performance problem or "bad" SQL query [message #599985 is a reply to message #599977] Wed, 30 October 2013 12:25 Go to previous message
Michel Cadot
Messages: 59203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can do it with your client installation, assuming you have install the tools and you can get the trace file on your PC.

Previous Topic: Please help me on tune this query
Next Topic: Tiff Images and Oracle
Goto Forum:
  


Current Time: Tue Sep 23 22:26:48 CDT 2014

Total time taken to generate the page: 0.11257 seconds