| Simple query is taking long time [message #588913] |
Sat, 29 June 2013 13:23  |
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 #588926 is a reply to message #588913] |
Sat, 29 June 2013 14:58  |
John Watson
Messages: 8960
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 #589004 is a reply to message #589002] |
Mon, 01 July 2013 06:59 |
cookiemonster
Messages: 13958
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 #589273 is a reply to message #588913] |
Thu, 04 July 2013 00:05 |
 |
Kevin Meade
Messages: 2103
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 #589343 is a reply to message #589275] |
Thu, 04 July 2013 08:55 |
|
Kevin Meade
Messages: 2103
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 |
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 |
|
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
