Home » RDBMS Server » Performance Tuning » performance issue with the query (Oracle 11G)
performance issue with the query [message #588865] Sat, 29 June 2013 01:58 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,
This query is fetching 500 records.

 SELECT 
 RECIPIENT_ID ,FAX_STATUS 
 FROM 
 FAX_STAGE WHERE LOWER(FAX_STATUS) like 'moved to%'

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

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |   159K|    10M|  2170   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID| FAX_STAGE           |   159K|    10M|  2170   (1)|
|   2 |   INDEX RANGE SCAN          | INDX_FAX_STATUS_RAM | 28786 |       |   123   (0)|
----------------------------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         21  consistent gets
          0  physical reads
          0  redo size
        937  bytes sent via SQL*Net to client
        375  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         19  rows processed


Total number of records in the table.
SELECT COUNT(*) FROM FAX_STAGE--3679418


Distinct reccords are low for this column.

SELECT DISTINCT FAX_STATUS FROM FAX_STAGE;
Completed
BROKEN
Broken - New
moved to - America
MOVED to - Australia
Moved to Canada and australia

Functional based index on FAX_STAGE(LOWER(FAX_STATUS))

stats are up to date.
Still the cost is high
How to improve the performance of this query.

Please help me.

Thanks in advance.
Re: performance issue with the query [message #588875 is a reply to message #588865] Sat, 29 June 2013 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Cost is meaningless, what is the time?

Buy faster hardware, remove concurrent workload, use Context option.

Regards
Michel
Re: performance issue with the query [message #588876 is a reply to message #588865] Sat, 29 June 2013 04:06 Go to previous messageGo to next message
John Watson
Messages: 4569
Registered: January 2010
Location: Global Village
Senior Member
First, you need to update your plan_table. So drop it, and run $ORACLE_HOME/rdbms/admin/utlxplan.
Second, you say the stat's are up to date, but they are wrong: the CBO expects 159k rows, but you are getting 500 rows. So analyze again, with estimate_percent=>100.
Third, how many rows are there for each distinct lower(fax_status) ?
Fourth, what is the clustering factor of the index?
Re: performance issue with the query [message #588966 is a reply to message #588875] Mon, 01 July 2013 02:17 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Michel,

How to use Context option.And on which column I should use it.

Thanks.
Re: performance issue with the query [message #588967 is a reply to message #588966] Mon, 01 July 2013 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know but Barbara may help you.
Do you want I move this topic to our "Text & interMedia" forum?

But before, investigate John's points, they are the most important things to first do.

Regards
Michel
Re: performance issue with the query [message #588968 is a reply to message #588876] Mon, 01 July 2013 02:26 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi John,

I have followed your suggestions.

Step1:
$ORACLE_HOME/rdbms/admin/utlxplan

'ORACLE_HOME' is not recognized as an internal or external command,
operable program or batch file.


Step2
EXEC dbms_stats.gather_table_stats('ORL','FAX_STAGE',ESTIMATE_PERCENT=>100,METHOD_OPT=>'for all indexed columns size auto',CASCADE=>True);
ANALYZE TABLE ORL.FAX_STAGE ESTIMATE STATISTICS SAMPLE 100 PERCENT for table for all indexed columns;


Step3:
SELECT DISTINCT LOWER(fax_status) FROM ORL.FAX_STAGE;

LOWER(FAX_STATUS)
broken          
broken - new   
completed   
new 
move to - america
move to - canada


Step4:

select index_name, clustering_factor
from all_indexes where index_name = 'INDX_FAX_STATUS_RAM';

INDX_FAX_STATUS_RAM  165139


Please help me.

Thanks in advance.

Re: performance issue with the query [message #588969 is a reply to message #588968] Mon, 01 July 2013 02:30 Go to previous messageGo to next message
John Watson
Messages: 4569
Registered: January 2010
Location: Global Village
Senior Member
Before attempting to tune SQL, you should learn how to run a script with SQL*Plus. You should also earn how to write a query that uses COUNT and GROUP BY.
At the moment, I see no point in proceeding.
---
Update, JW:

And one more thing: you should NEVER use the ANALYZE command. It is deprecated, and will break your histograms.

[Updated on: Mon, 01 July 2013 02:32]

Report message to a moderator

Re: performance issue with the query [message #588976 is a reply to message #588969] Mon, 01 July 2013 02:52 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi John,

I have executed from TOAD.

I have pressed F5

SELECT FAX_STATUS,count(*)
FROM fax_STAGE
GROUP BY FAX_STATUS;

FAX_STATUS    COUNT(*)

BROKEN          10
Broken - New    9
Completed    2324493
New             20
move to - america 250
move to - canada  250


What is the alternative query for this.

ANALYZE TABLE ORL.FAX_STAGE ESTIMATE STATISTICS SAMPLE 100 PERCENT for table for all indexed columns;


Please help me.

Thanks
Re: performance issue with the query [message #588980 is a reply to message #588976] Mon, 01 July 2013 03:27 Go to previous message
Michel Cadot
Messages: 59147
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at the description of dbms_stats in the documentation.

Regards
Michel
Previous Topic: Sql query is slow
Next Topic: Histograms issue During DB Upgrade.
Goto Forum:
  


Current Time: Sun Sep 21 11:09:49 CDT 2014

Total time taken to generate the page: 0.12319 seconds