Home » RDBMS Server » Performance Tuning » Simple query is taking long time (Oracle 11G)
Simple query is taking long time [message #588913] Sat, 29 June 2013 13:23 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

The below query is taking long time.

SELECT   FS.*
  FROM   ORL.FAX_STAGE FS
         INNER JOIN
               ORL.FAX_SOURCE FSRC
            INNER JOIN
               GLOBAL_BU_MAPPING GBM
            ON GBM.BU_ID = FSRC.BUID
         ON UPPER (FSRC.FAX_NUMBER) = UPPER (FS.DESTINATION)
 WHERE       FSRC.IS_DELETED = 'N'
         AND GBM.BU_ID IS NOT NULL
         AND UPPER (FS.FAX_STATUS) ='COMPLETED';


this query is returning 1645457 records.

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------


-----------------------------------------------------------------------------------
| Id  | Operation           | Name                   | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                        |   625K|   341M| 45113   (1)|
|   1 |  HASH JOIN          |                        |   625K|   341M| 45113   (1)|
|   2 |   NESTED LOOPS      |                        |   611 | 14664 |    22   (0)|
|   3 |    TABLE ACCESS FULL| FAX_SOURCE             |  2290 | 48090 |    22   (0)|
|   4 |    INDEX RANGE SCAN | GLOBAL_BU_MAPPING_BUID |     1 |     3 |     0   (0)|
|   5 |   TABLE ACCESS FULL | FAX_STAGE              |  2324K|  1214M| 45076   (1)|
-----------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------


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

15 rows selected.


The distinct number of records in each table.

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

FAX_STATUS    COUNT(*)

BROKEN          10
Broken - New    9
Completed    2324493
New             20

SELECT is_deleted,COUNT(*)
FROM  FAX_SOURCE
GROUP BY IS_DELETED;

IS_DELETED	COUNT(*)
N	         2290
Y	         78


Total number of records in each table.

SELECT COUNT(*) FROM ORL.FAX_SOURCE FSRC-- 2368

SELECT COUNT(*) FROM ORL.FAX_STAGE--2324532

SELECT COUNT(*) FROM APPS_GLOBAL.GLOBAL_BU_MAPPING--9

To improve the performance of this query I have created the following indexes.

Functional based index on UPPER (FSRC.FAX_NUMBER) ,UPPER (FS.DESTINATION) and UPPER (FS.FAX_STATUS).
Bitmap index on FSRC.IS_DELETED.
Normal Index on GBM.BU_ID and FSRC.BUID.

But still the performance is bad for this query.
What can I do apart from this to improve the performance of this query.

Please help me .
Thanks in advance.
Re: Simple query is taking long time [message #588914 is a reply to message #588913] Sat, 29 June 2013 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 58313
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What can I do apart from this to improve the performance of this query.


Do not execute it; no one will read 1645457 records.

Regards
Michel
Re: Simple query is taking long time [message #588926 is a reply to message #588913] Sat, 29 June 2013 14:58 Go to previous messageGo to next message
John Watson
Messages: 4329
Registered: January 2010
Location: Global Village
Senior Member
Is this the same problem that you asked about in your other post today? The one where you ignored my suggestions?
The same answer: your statistics are wrong, and you need to replace your plan_table. Only then can you start tuning.
Re: Simple query is taking long time [message #588988 is a reply to message #588926] Mon, 01 July 2013 03:57 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

This query is different.But one table also used in this query.
I have done what you said but no improvement.

Please help me.

Thanks.
Re: Simple query is taking long time [message #588989 is a reply to message #588988] Mon, 01 July 2013 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 58313
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 29 June 2013 20:38
Quote:
What can I do apart from this to improve the performance of this query.


Do not execute it; no one will read 1645457 records.

Regards
Michel

Re: Simple query is taking long time [message #588991 is a reply to message #588988] Mon, 01 July 2013 04:09 Go to previous messageGo to next message
John Watson
Messages: 4329
Registered: January 2010
Location: Global Village
Senior Member
Until you create the correct plan_table, you are just wasting time.

Re: Simple query is taking long time [message #589002 is a reply to message #588991] Mon, 01 July 2013 06:37 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Check stats of APPS_GLOBAL.GLOBAL_BU_MAPPING and see if it's indexes also right.
And as John mention please fix the plan_table.

Out of plan table issue, I guess the most problem may comes from here "AND UPPER (FS.FAX_STATUS) ='COMPLETED'", even though you create a function base index on this, but it's not gonna work.
Re: Simple query is taking long time [message #589004 is a reply to message #589002] Mon, 01 July 2013 06:59 Go to previous messageGo to next message
cookiemonster
Messages: 10802
Registered: September 2008
Location: Rainy Manchester
Senior Member
Considering that 99.998% of records satisfy this predicate: "AND UPPER (FS.FAX_STATUS) ='COMPLETED'" oracle is never going to use the index on fax_status and nor should it.

I suspect the plan is as good as it gets, in which case the only way to speed it up is to get better hardware.
Re: Simple query is taking long time [message #589008 is a reply to message #589004] Mon, 01 July 2013 07:20 Go to previous messageGo to next message
Michel Cadot
Messages: 58313
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or to not query at all, as I said, which is the best solution.

Regards
Michel
Re: Simple query is taking long time [message #589010 is a reply to message #589008] Mon, 01 July 2013 07:23 Go to previous messageGo to next message
cookiemonster
Messages: 10802
Registered: September 2008
Location: Rainy Manchester
Senior Member
That assumes the output is going to screen or report somewhere, could just as easily be part of a process that modifies data.
Re: Simple query is taking long time [message #589013 is a reply to message #589010] Mon, 01 July 2013 07:27 Go to previous messageGo to next message
Michel Cadot
Messages: 58313
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
By a slow by slow process... could be.

Regards
Michel
Re: Simple query is taking long time [message #589074 is a reply to message #589013] Tue, 02 July 2013 00:09 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Try parallel if it's OLAP and if the cpu resource is not a shortage of OS.
Re: Simple query is taking long time [message #589273 is a reply to message #588913] Thu, 04 July 2013 00:05 Go to previous messageGo to next message
Kevin Meade
Messages: 1918
Registered: December 1999
Location: Connecticut USA
Senior Member
1) As John already indicated, your first big issue here is that you are using an old plan table. This is important because it means the query plan you are showing us is incomplete. For example, it does not contain a PREDICATE INFORMATION section. Without this, it is not possible to know the details of how oracle is using the access paths you have provided.

2) That said, you did one very smart thing in your post, you gave us some row counts that have meaning. You did this by creating what are called "COUNT QUERIES" and "FILTERING QUERIES". You might not know them by this name but you did good by providing them. For those who don't know these terms, look at the queries provided. An examination of these counts shows that the ESTIMATED CARDINALITIES in the query plan are very good. That suggests strongly that statistics are up-to-date and thus Oracle has estimated well the rows returned by each step in the query plan.

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

FAX_STATUS    COUNT(*)

BROKEN          10
Broken - New    9
Completed    2324493
New             20

SELECT is_deleted,COUNT(*)
FROM  FAX_SOURCE
GROUP BY IS_DELETED;

IS_DELETED	COUNT(*)
N	         2290
Y	         78


3) lastly the final row count of 1.6 million rows tells us that you want most of the data in your tables returned by this query. The COUNT QUERY on FAX_STAGE tells us this too. To that end the use of FULL TABLE SCAN on the 2.3 million row table is very good, not bad.

If indeed the row estimates are accurate as they appear to be, then the plan you have is the right plan and if you feel performance should be faster, then your problem is elsewhere than the query plan. So what do you do about it?

If we look at tuning as occurring in levels of work effort, then we might think of tuning levels as a way to categorize basic tuning such that each level becomes more difficult. In level one (1) we would use basic strategies for tuning. These strategies provide information to the optimizer in order to get you the right plan. Most OLTP style queries usually get fixed at this level of tuning. The strategies are:

A) statistics
B) indexing
c) query refactoring

But your query appears to have good stats, indexing does not matter given the FTS, and the query is so simple that a refactoring does not make sense at this point. Thus a level one tuning will not likely yield any joy for you. On the surface you appear to already have the right plan given your tables' physical structures. This means you need to go to a level two tuning.

Level two tuning involves more advanced strategies that go beyond what most Oracle DBAs and Developers deal with on a daily basis. Hence why it might be called level two. It includes things like:

A) Partitioning
B) Covering Indexes

Each of these strategies is designed to reduce I/O. Each is used under a specific set of circumstances and to be successful you need to map your circumstances to those of the strategy. Since the biggest amount of I/O for your query is in the FTS, we would focus there first. Unfortunately, once again the amount of data you are returning precludes any significant gain here. The basic idea of reducing I/O requires that there actually be I/O you are doing that you do not need to do. Said another way, to reduce I/O is to remove waste. But since you want 99% of the data in your tables, there is no waste in that there are few rows being fetched that we don't want. You are, based on your count query for the table FAX_STAGE, fetching 99.9% of the rows in that table. There is no I/O for you to eliminate from this query. Thus PARTIONING cannot eliminate most of the data by scanning only a small portion of your tables (cause you want most of the data), and a covering index won't work because you are doing a SELECT FS.* and thus cannot exclude most columns via a COVERING INDEX and thus reduce I/O with a index that is way smaller than the table. So I guess this would take us to a level 3 tuning which is real deep.

Level 3 tuning involves very advanced features of the database. These are those features that are routinely poorly understood and easy to mess up. They include:

A) Materialized Views
B) Bitmap-Join Indexes
C) Parallel Query
D) Memory Management for Hashing and Sorting
E) Keep Pool

These features are based on the idea of RESOURCE EXCHANGE through ENVIRONMENT MANIPULATION. These features allow you to exchange one of DISK SPACE or CPU CYCLES or MEMORY in order to get a faster result. It is the first step into the "throw more money at it" approach, where money in this case is your various system resources. Because you have a FTS going on, Parallel Query seems like a possible choice. Though two million rows is a borderline number of rows for this feature. Do not expect a miracle. You will likely only see a 2X or 3X improvement using PQ on this query.

But before you spend a lot of time on it you need to make sure that your query is actually where your time is going and not some other place like network latency or hardware failures. do a CREATE TABLE AS and get a wall clock timing. You may find your problem is not the database or the query.

LEVEL 1 TUNING (use information provided by basic features to get the right plan (the right plan usually minimizes overall use of I/O, CPU, MEMORY))
LEVEL 2 TUNING (use special features to reduce waste in I/O if there is any (there may not be any significant waste))
LEVEL 3 TUNING (change your environment to consume substantially more of some resource in exchange for time)

Good luck, Kevin

[Updated on: Thu, 04 July 2013 00:08]

Report message to a moderator

Re: Simple query is taking long time [message #589275 is a reply to message #589273] Thu, 04 July 2013 00:31 Go to previous messageGo to next message
snowball
Messages: 229
Registered: April 2006
Location: China
Senior Member

Hi, Kevin
One more from level one, if this is OLAP, may be HINT will be considered as gathering stats might need a long time. Smile
Very clear analyzing steps.
Cool.
Re: Simple query is taking long time [message #589343 is a reply to message #589275] Thu, 04 July 2013 08:55 Go to previous messageGo to next message
Kevin Meade
Messages: 1918
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes snowball, thanks for that note. Let me also add that:

These "tuning levels" are not anything set in stone. I only offer them as one possible way to organize thoughts about tuning. I am sure others have equallty good ways of organizing and categorizing their tuning activities.

Additionally as you point out, there are many other features that I have not mentioned that need placing somewhere in a mental organization. I would never try to capture them all. Indeed, how pompous would I be to assume I actually knew them all.

Lastly this is all just SQL tuning. We are not discussing any kind of system tuning, or design tuning, etc.

If we are looking at flaws or major omissions then I would add I missed CONSTRAINTS and DATABASE DESIGN in LEVEL 1 SQL TUNING. 3rd Normal Form design, Constraints, and Proper Data Types are some of the most basic and most messed up fundamentals I see every day. What a loss of information when these are ignored. Tisk Tisk.

Kevin

[Updated on: Thu, 04 July 2013 09:01]

Report message to a moderator

Re: Simple query is taking long time [message #589657 is a reply to message #589343] Tue, 09 July 2013 08:22 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi All,


I have created the following indexes.

CREATE INDEX ORL.IDX_DESTINATION_RAM ON ORL.FAX_STAGE(UPPER("DESTINATION"))
CREATE INDEX ORL.IDX_FAX_STATUS_RAM ON ORL.FAX_STAGE(LOWER("FAX_STATUS"))
CREATE INDEX ORL.IDX_UPPER_FAX_STATUS_RAM ON ORL.FAX_STAGE(UPPER("FAX_STATUS"))
CREATE INDEX ORL.IDX_BUID_RAM ON ORL.FAX_SOURCE(BUID)
CREATE INDEX ORL.IDX_FAX_NUMBER_RAM ON ORL.FAX_SOURCE(UPPER("FAX_NUMBER"))
CREATE BITMAP INDEX ORL.IDX_IS_DELETED_RAM ON ORL.FAX_SOURCE(IS_DELETED)


After creating the following indexes performance got improved.

But our DBA said that new BITMAP index at FAX_SOURCE table (ORL.IDX_IS_DELETED_RAM) can cause locks
on multiple rows if IS_DELETED column is in use. Please proceed with detailed tests.

I am sending the explain plan before creating indexes and after indexes has been created.

SELECT   FS.*
FROM   ORL.FAX_STAGE FS
                INNER JOIN
                ORL.FAX_SOURCE FSRC
               INNER JOIN
                   GLOBAL_BU_MAPPING GBM
                ON GBM.BU_ID = FSRC.BUID
             ON UPPER (FSRC.FAX_NUMBER) = UPPER (FS.DESTINATION)
WHERE       FSRC.IS_DELETED = 'N'
           AND GBM.BU_ID IS NOT NULL
           AND UPPER (FS.FAX_STATUS) =:B1;


--OLD without indexes

PLAN_TABLE_OUTPUT 
------------------------------------------------------------------------------------------------------------

Plan hash value: 3076973749

----------------------------------------------------------------------------------------------
| Id  | Operation           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                        |   141K|    85M| 45130   (1)| 00:09:02 |
|*  1 |  HASH JOIN          |                        |   141K|    85M| 45130   (1)| 00:09:02 |
|   2 |   NESTED LOOPS      |                        |   611 | 18330 |    22   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| FAX_SOURCE             |  2290 | 59540 |    22   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN | GLOBAL_BU_MAPPING_BUID |     1 |     4 |     0   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | FAX_STAGE              | 23245 |    13M| 45106   (1)| 00:09:02 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access(UPPER("FSRC"."FAX_NUMBER")=UPPER("FS"."DESTINATION"))
   3 - filter("FSRC"."IS_DELETED"='N')
   4 - access("GBM"."BU_ID"="FSRC"."BUID")
       filter("GBM"."BU_ID" IS NOT NULL)
   5 - filter(UPPER("FS"."FAX_STATUS")=SYS_OP_C2C(:B1))

21 rows selected.

--NEW with indexes.

PLAN_TABLE_OUTPUT   
------------------------------------------------------------------------------------------------------------------

Plan hash value: 665032407

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                          |  5995 |  3986K|  3117   (1)| 00:00:38 |
|*  1 |  HASH JOIN                       |                          |  5995 |  3986K|  3117   (1)| 00:00:38 |
|   2 |   NESTED LOOPS                   |                          |   611 | 47658 |    20   (5)| 00:00:01 |
|*  3 |    VIEW                          | index$_join$_002         |  2290 |   165K|    20   (5)| 00:00:01 |
|*  4 |     HASH JOIN                    |                          |       |       |            |       |
|*  5 |      HASH JOIN                   |                          |       |       |            |       |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------

|   6 |       BITMAP CONVERSION TO ROWIDS|                          |  2290 |   165K|     1   (0)| 00:00:01 |
|*  7 |        BITMAP INDEX SINGLE VALUE | IDX_IS_DELETED_RAM       |       |       |            |       |
|   8 |       INDEX FAST FULL SCAN       | IDX_BUID_RAM             |  2290 |   165K|     8   (0)| 00:00:01 |
|   9 |      INDEX FAST FULL SCAN        | IDX_FAX_NUMBER_RAM       |  2290 |   165K|    14   (0)| 00:00:01 |
|* 10 |    INDEX RANGE SCAN              | GLOBAL_BU_MAPPING_BUID   |     1 |     4 |     0   (0)| 00:00:01 |
|  11 |   TABLE ACCESS BY INDEX ROWID    | FAX_STAGE                | 23245 |    13M|  3096   (1)| 00:00:38 |
|* 12 |    INDEX RANGE SCAN              | IDX_UPPER_FAX_STATUS_RAM |  9298 |       |  2434   (1)| 00:00:30 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------


   1 - access(UPPER("DESTINATION")="FSRC"."SYS_NC00035$")
   3 - filter("FSRC"."IS_DELETED"='N')
   4 - access(ROWID=ROWID)
   5 - access(ROWID=ROWID)
   7 - access("FSRC"."IS_DELETED"='N')
  10 - access("GBM"."BU_ID"="FSRC"."BUID")
       filter("GBM"."BU_ID" IS NOT NULL)
  12 - access(UPPER("FAX_STATUS")=SYS_OP_C2C(:B1))

31 rows selected


Please confirm on the DBA comment.Is this bitmap index locks rows in my case.

Thanks.
Re: Simple query is taking long time [message #589658 is a reply to message #589657] Tue, 09 July 2013 08:38 Go to previous message
Kevin Meade
Messages: 1918
Registered: December 1999
Location: Connecticut USA
Senior Member
You can read up on bitmap indexes by doing a google search. Here is a quick take on my understanding.

Unless you are specifically requesting a lock on a row via SELECT FOR UPDATE... Then to lock a row you need to insert it or update it or delete it. So first off your DBA is talking about someone doing changes to the data not just querying it.

First we need some background. There are two kinds of locking (mostly anyway), ROW LEVEL LOCKING and BLOCK LEVEL LOCKING. Block level locking means that whenever you lock one row in a block, you lock all rows in that block because you lock the block, not what is in it.

A bitmap index works differently from a regular index. One of the differences is that (this is a little liberal in description) a bitmap index does block level locking. Thus whenever you insert/update/delete a row on a table, any affected bitmap index will lock the index block that holds the row you are working with. Doing this will lock all other rows on the block and prevent any other insert/update/delete against that block from different transactions. This does not affect readers of the data in Oracle, only writers.

So this may be what you DBA is referring to and thus why he/she is worried about creating such an index.

However, this is a great example of how not to use BITMAP indexes. BITMAP indexes are for FACT tables in a dimensional data model and they are never used alone. They are intended to be used in groups and normally you do not have both btree indexes and bitmap indexes on the same table.

My opinion is you should drop the bitmap index. It won't be of any value to you and looks like a wrong use of this feature.

Kevin
Previous Topic: Efficient Way to Alter Column For TDE.
Next Topic: Full Table scan - Query without where clause
Goto Forum:
  


Current Time: Wed Jul 09 18:31:37 CDT 2014

Total time taken to generate the page: 0.10444 seconds