Printable View
There is no problem with this part. As I told earlier that the whole query executes in less than 2 seconds if I use select count(*) instead of selectQuote:
Originally Posted by hrishy
. I am not able to figure out why specifying the column list causes the query to take so long in fetching the results. Is it a known issue? I could understand that it has to bring column data, so more I/O is required but this more time could amount to 6 hours is not understandable.
Hi
Do you mind using a MV and showing me the results ?
Hrishy, I know you may be right. But believe me, this query on which you are asking for MV executes in less than a second. Moreover, there is a wild search '%813982%' which is not the same in every query created by the user. So, MV would help only in one particular case. Moreover, I need approval to create an MV for which I need to give the reason and I have no valid reason so far.Quote:
Originally Posted by hrishy
However, I have seen that the problem is with the select list but I don't know where. Anyways, this query has made me crazy.
It will made you crazy, because it's not picking correct index.
Do your own analysis and see why it's not picking correct index.
MY thought is if you execute this query with FULL hint it'll finish much faster.
Here since it's picking wrong index it's doing lot of buffer gets and scanning memory is CPU intensive.
Can you execute this query with FULL hint and get back to us with the autotrace details.
Did you answer when was the last time statistics gathered and how do you excatly gather statistics.
Also do provide the wait event details.
It is indeed picking the right index.Quote:
Originally Posted by malay_biswal
See the query below:
SELECT TO_CHAR(A.ID) A118,A.ASSET_GROUP_XID A2,A.ACL_ID A106,A.OWNER A4,CONVERT(A.NAME,'WE8MACROMAN8') A1,
A.FIRST_VERSION_CREATED_BY A15,LPAD(LTRIM(TO_CHAR(A.FIRST_VERSION_DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A16,
LPAD(LTRIM(TO_CHAR(A.DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A18,CONVERT(A.CHECKOUT_LOCATION,'WE8MACROMAN8') A24,
TO_CHAR(A.ASSET_SIZE) A7,A.CHECKED_OUT_BY A23,A.ARCHIVE_STATUS A27,A.STORAGE_CHANGE_REQUEST A110,A.HAS_HISTORY A74,
TO_CHAR(A.CONTAINED_IN_COUNT) A73,TO_CHAR(A.CONTAINER_OF_COUNT) A72,TO_CHAR(A.RELATED_WITH_COUNT) A71,
CONVERT(A.FILE_TYPE,'WE8MACROMAN8') A12,CONVERT(A.FILE_CREATOR_APPLICATION,'WE8MACROMAN8') A13,
CONVERT(A.FILE_EXTENSION,'WE8MACROMAN8') A9,TO_CHAR(A.REVISION) A21,A.IS_CURRENT_VERSION A109,TO_CHAR(A.FAMILY_ID) A112,
LPAD(LTRIM(TO_CHAR(A.FILE_DATE_CREATED,'SYYYYMMDDHH24MISS')),15,'+') A11,
LPAD(LTRIM(TO_CHAR(A.FILE_DATE_LAST_MODIFIED,'SYYYYMMDDHH24MISS')),15,'+') A10
FROM MAM_ASSETS A WHERE ((A.ACL_ID=65536) AND (A.ASSET_GROUP_XID=120) AND (A.IS_CURRENT_VERSION=1) AND
A.ID IN ( SELECT A.ID ASSET_ID FROM MAM_ASSETS A
WHERE (A.ID IN (SELECT SV65539.ID FROM MAM_ATTR_STRING_VALUES SV65539 WHERE
(SV65539.LOWER_CASE_STRING_VALUE LIKE
LOWER(CONVERT('%813982%','WE8MSWIN1252','WE8MACROMAN8'))) AND
(SV65539.ATTRIBUTE_XID=65539))) ) )
ORDER BY A.ASSET_SIZE;
The indexes on columns ACL_ID, ASSET_GROUP_XID AND IS_CURRENT_VERSION are not picked as there are more than 10 percent rows corresponding to the values of these columns.
The index on ID column is picked as the nested query returns less than 2 percent of rows. The name of index is AST_PK.
The index on ATTRIBUTE_XID column used in innermost query is also used and name of index is ATRVALSTR_ATRDEF_FK_I.
See the execution plan below:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1118 Card=86 Bytes=15738)
1 0 SORT (ORDER BY) (Cost=1118 Card=86 Bytes=15738)
2 1 NESTED LOOPS (Cost=1107 Card=86 Bytes=15738)
3 2 NESTED LOOPS (Cost=1088 Card=918 Bytes=44064)
4 3 SORT (UNIQUE) (Cost=1072 Card=918 Bytes=38556)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'MAM_ATTR_STRING_VALUES' (TABLE) (Cost=1072 Card=918 Bytes=38556)
6 5 INDEX (RANGE SCAN) OF 'ATRVALSTR_ATRDEF_FK_I' (INDEX) (Cost=819 Card=367018)
7 3 INDEX (UNIQUE SCAN) OF 'AST_PK' (INDEX (UNIQUE))
8 2 TABLE ACCESS (BY INDEX ROWID) OF 'MAM_ASSETS' (TABLE) (Cost=1 Card=1 Bytes=135)
9 8 INDEX (UNIQUE SCAN) OF 'AST_PK' (INDEX (UNIQUE))
However, there is one area that I have literally found problematic after using events '10046 trace name context forever, level 12'. See the highlighted part from the excerpt of the formatted trace file below:
Rows Row Source Operation
------- ---------------------------------------------------
4 SORT ORDER BY (cr=217990536 pr=5509 pw=0 time=1737749501 us)
4 MERGE JOIN SEMI (cr=217990536 pr=5509 pw=0 time=1743457434 us)
19740 TABLE ACCESS BY INDEX ROWID MAM_ASSETS (cr=217952929 pr=5508 pw=0 time=22406421915 us)
443784 NESTED LOOPS (cr=217939831 pr=197 pw=0 time=29382428995 us)
424043 INDEX FULL SCAN AST_PK (cr=915 pr=0 pw=0 time=424066 us)(object id 2624)
19740 BITMAP CONVERSION TO ROWIDS (cr=217938916 pr=197 pw=0 time=29438106950 us)
19740 BITMAP AND (cr=217938916 pr=197 pw=0 time=29436670545 us)
424043 BITMAP CONVERSION FROM ROWIDS (cr=1273083 pr=79 pw=0 time=10021178 us)
424043 INDEX RANGE SCAN AST_PK (cr=1273083 pr=79 pw=0 time=7913561 us)(object id 2624)
424043 BITMAP CONVERSION FROM ROWIDS (cr=26333520 pr=102 pw=0 time=3238424511 us)
22005947710 INDEX RANGE SCAN AST_ASG_FK_I (cr=26333520 pr=102 pw=0 time=4114630 us)(object id 3043)
424043 BITMAP CONVERSION FROM ROWIDS (cr=190332313 pr=16 pw=0 time=26184714435 us)
1626309602 INDEX RANGE SCAN AST_CURRENT_VERSION_I (cr=190332313 pr=16 pw=0 time=3919662 us)(object id 3062)
4 SORT UNIQUE (cr=37607 pr=1 pw=0 time=1482279 us)
48 TABLE ACCESS BY INDEX ROWID MAM_ATTR_STRING_VALUES (cr=37607 pr=1 pw=0 time=3467157 us)
361416 INDEX RANGE SCAN ATRVALSTR_ATRDEF_FK_I (cr=825 pr=1 pw=0 time=2384 us)(object id 30474)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 5509 0.55 35.60
latch: cache buffers chains 3 0.00 0.00
SQL*Net message from client 2 0.36 0.68
I am not able to find why is it wasting so much of time here. What is this BITMAP operation and why is it performed?
You can set this in session level and execute to see if bitmap conversion is happening.
_b_tree_bitmap_plans = false
However i think problem is with NL join, did you try excuting query with FULL hint?
This "LOWER(CONVERT('%813982%','WE8MSWIN1252','WE8MACROMAN8')))" is where it is doing bitmap conversion 367018 many times, so lets c after setting _b_tree_bitmap_plans = false what's happening.
BTW, how long it was taking to execute before and what changes done after which this query taking long time?
That I suppose is a hidden parameter. I will try to do it.Quote:
Originally Posted by malay_biswal
Yes, I tried it full hint and it took around 5 and a half hours then. No changes were made to this query. There is a wizard at application level that lets the users form any query.
Earlier, this query should have executed in less than 2 minutes if customer didn't complain before. Even if I remove all columns in select list and use count(*), then this query executes in 2 seconds. Then if I start from 1 st column of select list and start adding columns one by one (by looking into the query posted above), the query starts taking long when I reach one particular column. If I think that it is the problematic column and use only this column in select list, even then the query will again start executing in 2 seconds. So, I am not able to drill down the cause.
Just another question: Do the indexes on columns present in select list have anything to do with faster query retrieval? I think it's no but I have seen change in execution plan if I create index on column of select list and this column is not present in where clause.
Finally, some good news at the end . Setting this hidden parameter has worked. Now, the query executes in 50 seconds.Quote:
Originally Posted by malay_biswal
I will test this setting for a period of two days before keeping it permanently.
Malay, I almost forgot to thank you. Thanks a ton.
The following links have more information about this hidden parameter. It's a bug kind.0
http://blogs.oracle.com/theshortensp...le_parame.html
https://support.oracle.com/CSP/main/...tFamily=Oracle
Description of this parameter:
PARM_NAME CURRENT_VAL DEFAULT_VAL DESCR
-------------------- ------------ ------------ ------------------------------------------------------------
_b_tree_bitmap_plans TRUE TRUE enable the use of bitmap plans for tables w. only B-tree ind
exes
SQL> select distinct index_type from dba_indexes;
INDEX_TYPE
---------------------------
CLUSTER
DOMAIN
FUNCTION-BASED NORMAL
IOT - TOP
LOB
NORMAL
6 rows selected.
There is no bitmap index. So, I think that I am on the right track.
That's a good news and you are welcome.