1

Topic: Requests with involvement of table X$KSOLSFTS hang up

A row of requests to system dictionaries hangs up - read billions lines in a cycle hours (or days). It was possible to clarify that it happens if in request participates in quality of object X$KSOLSFTS. Actually even simple request to this table too never :

SQL_ID 2ds2515540rbx, child number 0
-------------------------------------
select count (*) from X$KSOLSFTS
Plan hash value: 1642168290
-------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU) | E-Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 312 (100) | |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | FIXED TABLE FULL | X$KSOLSFTS | 545K | 312 (100) | 0:00:01 AM |
-------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / X$KSOLSFTS@SEL$1
Outline Data
-------------
/ * +
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE (' 11.2.0.4 ')
DB_VERSION (' 11.2.0.4 ')
ALL_ROWS
OUTLINE_LEAF ("SEL$1")
FULL ("SEL$1" "X$KSOLSFTS" "SEL$1")
END_OUTLINE_DATA
*/

Somebody faced the such?

2

Re: Requests with involvement of table X$KSOLSFTS hang up

Valergrad;
https://support.oracle.com/knowledge/Oracle Database Products/2044146_1.html?

3

Re: Requests with involvement of table X$KSOLSFTS hang up

xtender;
Saw it. But like this patch is on 11.2.0.4 by default?

4

Re: Requests with involvement of table X$KSOLSFTS hang up

Actually a situation 1-v-1 as at this guy:
http://www.anbob.com/archives/2812.html

5

Re: Requests with involvement of table X$KSOLSFTS hang up

Slow Performance When Querying V$SEGSTAT or GV$SEGSTAT Views Doc Id 1532624.1

6

Re: Requests with involvement of table X$KSOLSFTS hang up

There are many duplicate entries in the fixed table X$KSOLSFTS on the particular node causing the scan of the table to take a long time.
If the number of rows in V$SEGSTAT is extremely high and the following returns rows then you are most likely experiencing this bug
select statistic#, count (*) from V$SEGSTAT
group by ts#,obj#,dataobj#, statistic#
having count (*)> 1