View source for MemoryAnalyzer/OQL

== Object Query Language ==

Object Query Language is an SQL like language used by Memory Analyzer for exploring a heap dump. There is documentation in the [https://help.eclipse.org/2019-09/topic/org.eclipse.mat.ui.help/tasks/queryingheapobjects.html help] but this wiki allows newer features to be explained and discussed before the documentation is updated.

[[Category:Memory Analyzer]]

Simple

<syntaxhighlight lang="tsql">
SELECT * FROM java.lang.String
</syntaxhighlight>

Displays all String objects as a tree.

<syntaxhighlight lang="tsql">
SELECT s as String,s.value as "characters" FROM java.lang.String s
</syntaxhighlight>

Displays all String objects as a table.

<syntaxhighlight lang="tsql">
SELECT s as String,s.value as "characters", inbounds(s),inbounds(s).@length FROM java.lang.String s
</syntaxhighlight>

 <nowiki>
String                          |characters                                 |inbounds(s)| inbounds(s).@length
--------------------------------------------------------------------------------------------------------------
java.lang.String [id=0x22e58820]|char[] [id=0x22e60f50;length=16;size=48]   |[I@620f7a39|                   1
java.lang.String [id=0x22e59150]|char[] [id=0x22e62ff0;length=6;size=24]    |[I@1f7b8d59|                   1
java.lang.String [id=0x22e5b560]|char[] [id=0x22e6b730;length=537;size=1088]|[I@28551755|                   1
--------------------------------------------------------------------------------------------------------------
</nowiki>

There are two sorts of objects encountered with OQL, IObject which represent Java objects in the snapshot and regular
Java objects generated by OQL processing.

''java.lang.String [id=0x22e58820]'' is a IInstance representing a String from the snapshot.

''char[] [id=0x22e60f50;length=16;size=48]'' is an IPrimitiveArray representing a character array from the snapshot.

''[I@620f7a39'' is a regular Java integer array holding a several of ints which are the object IDs Memory Analyzer uses
to represent IObjects in the snapshot.

== OQL (Memory Analyzer) versus SQL (MAT/Calcite) ==

As well as the built-in OQL, there is an extension plug-in for MAT called [https://github.com/vlsi/mat-calcite-plugin MAT Calcite] which adds SQL processing

{| class="wikitable"
|-
!Topic
!OQL
!SQL
|-
|General syntax
|<syntaxhighlight lang="tsql">SELECT s FROM java.lang.String s</syntaxhighlight>
|<syntaxhighlight lang="sql">SELECT s.this FROM "java.lang.String" s</syntaxhighlight>
|-
|Built-in functions
|<syntaxhighlight lang="tsql">SELECT toString(s), classof(s), 
s.@objectAddress, s.@usedHeapSize, s.@retainedHeapSize
FROM java.lang.String s </syntaxhighlight>
|<syntaxhighlight lang="sql">SELECT toString(s.this),getType(s.this),
getAddress(s.this),shallowSize(s.this),retainedSize(s.this) 
FROM "java.lang.String" s</syntaxhighlight>
|-
|More functions
|<syntaxhighlight lang="tsql">SELECT h, h[0:-1].size(), h.table, 
h.table.@length, h.modCount, h.getField("modCount") 
FROM java.util.HashMap h</syntaxhighlight>
|<syntaxhighlight lang="sql">SELECT h.this,getSize(h.this),h.this['table'],
length(h.this['table']), h.this['modCount'], getField(h.this,'modCount') 
FROM "java.util.HashMap" h</syntaxhighlight>
|-
|Comments
|/* multi-line comment */
|/* multi-line comment */
|-
|Single line comment
|// comment
| -- comment
|-
|JOIN
|Simulated by [[#JOIN operations|JOIN Operations]] 
|Supported
|-
|LIMIT and OFFSET
|Simulated by [[#LIMIT and OFFSET|LIMIT and OFFSET]]
|Supported
|-
|ORDER BY
|Click on column headers to sort
|Supported
|-
|GROUP BY
|Can be simulated by [[#GROUP BY|GROUP BY]].
Also 'Java Basics > Group by Value'
query might help.
Also, if the row is backed by an object (the from clause
returned a list of objects) then the 'Group by' menu bar
option allows 'Group by classloader' and 'Group by package'.
|Supported
|-
|COUNT
|Can be simulated by [[#COUNT|COUNT]].
|Supported
|-
|MAX,MIN
|Not directly supported. Could be
simulated by clicking on a column name
and taking the top or bottom value.
|Supported
|-
|AVG,SUM
|Not supported
|Supported
|}

OQL/Calcite provides advanced SQL functions such as JOIN (INNER JOIN,LEFT JOIN,RIGHT JOIN,FULL JOIN), CROSS JOIN, GROUP BY, ORDER BY. Some can be simulated by OQL with a bit of work.

== Enhancements in November 2019 ==

Various updates and enhancements have been made to OQL under [https://bugs.eclipse.org/bugs/show_bug.cgi?id=552879 552879: OQL enhancements for sub-selects, maps, context providers, DISTINCT]. These are available from [https://www.eclipse.org/mat/snapshotBuilds.php snapshot builds] for testing and are subject to change. Please comment on the [https://bugs.eclipse.org/bugs/show_bug.cgi?id=552879 bug], [https://www.eclipse.org/forums/eclipse.memory-analyzer forum] or [https://dev.eclipse.org/mailman/listinfo/mat-dev development mailing list]

Some of the aims of these changes were to permit more complex queries such as:
* [https://www.eclipse.org/forums/index.php/t/1095354/ Does OQL permit map access]
* [https://www.eclipse.org/forums/index.php/t/1078960/ OQL nested list flattening and filtering]
* [https://www.eclipse.org/forums/index.php/m/1220103 OQL: Need help with query involving object array and sub-select] Find objects with children without references to the parent
* List maps with original map, key and value columns like the collections 'Hash Entries' query. 


=== SELECT DISTINCT ===

`DISTINCT` used to just operate on the results of a query if it returned objects rather than general select items.

<syntaxhighlight lang="tsql">SELECT DISTINCT OBJECTS classof(s) FROM "java.lang\.S.*" s</syntaxhighlight>

<syntaxhighlight lang="tsql">SELECT * FROM OBJECTS "java.lang\.S.*"</syntaxhighlight>

`DISTINCT` now also operates on SELECTs with select items. It uses the whole row considered as a list as the item to be considered as distinct. It also uses the optimization that the input FROM items are also considered as being distinct (either as ints/IObjects or more general `FROM` items).

<syntaxhighlight lang="tsql">SELECT DISTINCT classof(s) FROM "java.lang\.S.*" s</syntaxhighlight>

=== Sub SELECT with select items ===

sub-selects were permitted where the sub-select returned an object list.

<syntaxhighlight lang="tsql">SELECT v, v.@length FROM OBJECTS ( SELECT OBJECTS s.value FROM java.lang.String s  ) v</syntaxhighlight>

sub-selects are now also permitted which have select items.

<syntaxhighlight lang="tsql">SELECT v,v.s,v.val FROM OBJECTS ( SELECT s,s.value as val FROM java.lang.String s  ) v</syntaxhighlight>

<pre>
Row                                                                                |Object                          |Array
--------------------------------------------------------------------------------------------------------------------------------------------------------------
{s=java.lang.String [id=0x26ba8a30], val=char[] [id=0x26ba8a48;length=14;size=40]} |java.lang.String [id=0x26ba8a30]|char[] [id=0x26ba8a48;length=14;size=40]
{s=java.lang.String [id=0x26ba8998], val=char[] [id=0x26ba89b0;length=56;size=128]}|java.lang.String [id=0x26ba8998]|char[] [id=0x26ba89b0;length=56;size=128]
{s=java.lang.String [id=0x26ba8160], val=char[] [id=0x26ba8178;length=56;size=128]}|java.lang.String [id=0x26ba8160]|char[] [id=0x26ba8178;length=56;size=128]
{s=java.lang.String [id=0x26b9d390], val=char[] [id=0x26b9d3a8;length=15;size=48]} |java.lang.String [id=0x26b9d390]|char[] [id=0x26b9d3a8;length=15;size=48]
{s=java.lang.String [id=0x26b9d358], val=char[] [id=0x26b9d370;length=8;size=32]}  |java.lang.String [id=0x26b9d358]|char[] [id=0x26b9d370;length=8;size=32]
--------------------------------------------------------------------------------------------------------------------------------------------------------------
</pre>

The outer select processes the result of the sub-select row by row, with a single RowMap `Map` object representing the row. The key/value pairs are the sub-select items with the sub-select column names as the keys. If the keys are standard identifiers, i.e. generally alpha-numeric then attribute processing can be used as `v.s` rather than having to to `v.get("s2")` which can still be used, perhaps for column names with spaces.

The whole sub-select continues to return a `CustomTableResultSet` which is an `IResultTable` but this has been enhanced to also be a `List` of `RowMap` items. It is quite hard to operate in OQL on the whole result as if it is supplied to an outer select then the `Iterable` nature means it will be processed row by row.

<syntaxhighlight lang="tsql">SELECT * FROM OBJECTS ( SELECT s, s.value AS val FROM java.lang.String s ) v</syntaxhighlight>
<pre>
[{s=java.lang.String [id=0x26ba8a30], val=char[] [id=0x26ba8a48;length=14;size=40]}, {s=java.lang.String [id=0x26ba8998], val=char[] [id=0x26ba89b0;length=56;size=128]}, {s=java.lang.String [id=0x26ba8160], val=char[] [id=0x26ba8178;length=56;size=128]}, {s=java.lang.String [id=0x26b9d390], val=char[] [id=0x26b9d3a8;length=15;size=48]}, {s=java.lang.String [id=0x26b9d358], val=char[] [id=0x26b9d370;length=8;size=32]}, {s=java.lang.String [id=0x26b9d318], val=char[] [id=0x26b9d330;length=11;size=40]}, {s=java.lang.String [id=0x26b9d2e8], val=char[] [id=0x26b9d300;length=4;size=24]}, {s=java.lang.String [id=0x26b9c758], val=char[] [id=0x26b9c770;length=21;size=56]}, {s=java.lang.String [id=0x26b9c6c8], val=char[] [id=0x26b9c6e0;length=13;size=40]}, {s=java.lang.String [id=0x26b9c690], val=char[] [id=0x26b9c6a8;length=8;size=32]}, ...
</pre>

Shows the whole table as a list

==== LIMIT and OFFSET ====

SQL has LIMIT and OFFSET to choose only some of the items from the FROM clauses. This can be simulated in OQL.

<syntaxhighlight lang="tsql">SELECT eval((SELECT * FROM OBJECTS ( SELECT s, s.value AS val FROM java.lang.String s ) v))[3] FROM OBJECTS 0</syntaxhighlight>
<pre>
eval((SELECT * FROM OBJECTS ( SELECT s, s.value AS val FROM java.lang.String s  ) v ))[3]
-----------------------------------------------------------------------------------------
{s=java.lang.String [id=0x26b9d390], val=char[] [id=0x26b9d3a8;length=15;size=48]}
-----------------------------------------------------------------------------------------
</pre>
Processes the whole table as a select item.

This could be used to simulate SQL LIMIT and OFFSET clauses.

<syntaxhighlight lang="tsql">SELECT z.s FROM OBJECTS ( eval((SELECT s FROM "java.lang.String" s ))[10:29] ) z</syntaxhighlight>

This extracts 20 entries, skipping the first 10. Note the array slice processing, with the start and end offsets as 0-based but inclusive.

Compare with MAT Calcite (SQL)

<syntaxhighlight lang="sql">SELECT s.this from "java.lang.String" s limit 10 offset 20</syntaxhighlight>

=== Context Menu for object columns ===
If a column appears to hold heap objects, or lists or arrays of heap objects, then the context menu now offers a choice to process that column's item of the selected rows.

<syntaxhighlight lang="tsql">SELECT s AS String, s.value AS "Char array", inbounds(s) AS Inbounds FROM java.lang.String s</syntaxhighlight>
<pre>
String                          |Char array                               |Inbounds
--------------------------------------------------------------------------------------
java.lang.String [id=0x26ba8a30]|char[] [id=0x26ba8a48;length=14;size=40] |[I@6ad112de
java.lang.String [id=0x26ba8998]|char[] [id=0x26ba89b0;length=56;size=128]|[I@18a0721b
java.lang.String [id=0x26ba8160]|char[] [id=0x26ba8178;length=56;size=128]|[I@2ae2fa13
--------------------------------------------------------------------------------------
</pre>

Context Menu:
;SELECT ... s
:The entire row - based on the underlying object s. Copy OQL: <syntaxhighlight lang="tsql">SELECT s AS String, s.value AS "Char array", inbounds(s) AS Inbounds FROM OBJECTS 20798,20796,20793 s</syntaxhighlight>
;String
: Just the String item in column 'String'. Copy OQL: <syntaxhighlight lang="tsql">SELECT s AS String FROM OBJECTS 20798,20796,20793 s</syntaxhighlight>
;Char array
: Just the char array. Copy OQL: <syntaxhighlight lang="tsql">SELECT s.value AS "Char array" FROM OBJECTS 20798,20796,20793 s</syntaxhighlight>
;Inbounds
: All the inbounds as heap objects. Copy OQL: <syntaxhighlight lang="tsql">SELECT inbounds(s) AS Inbounds FROM OBJECTS 20798,20796,20793 s</syntaxhighlight>


The context menu has a `Copy > OQL Query` option which returns an OQL query representing the selected rows and appropriate column.

The report plug-in which converts result tables to HTML now uses the context menu name to match with the table column to
put HTML linksin the correct place across the columns in the table rather than always in the first column. This also applies for
other queries, so the system properties query when used in a report has in-place links for keys and values.

'''Question - should the context menu appear for all columns, in case a column has a heap object in rows other than the first. The context menu would then appear for non-object columns holding strings or numeric values. Is it confusing to offer a context menu for those, when no queries (apart from Copy Selection) can do anything.'''

=== Map processing ===

Map heap objects in the heap dump can be now accessed using array notation, returning Map.Entry items. Previously array access returned any Map.Entry heap objects in the heap dump for the map, which could then be used to find the key and value via the `key` and `value` fields. Not all maps have entry objects, so the new system means that `getKey()` and `getValue()` can be used to access the keys and values.

'''Question: when array access returns map entry objects, should those objects have a fake object ID of the actual map heap object, or of the Map.Entry heap object if one was available? This affects all collection extraction of maps, not just OQL. See [https://help.eclipse.org/2019-09/topic/org.eclipse.mat.ui.help/doc/org/eclipse/mat/inspections/collectionextract/IMapExtractor.EntryObject.html]'''

<syntaxhighlight lang="tsql">SELECT h AS map, (SELECT e.getKey() AS key, e.getValue() AS value FROM OBJECTS ${h}[0:-1] e ) AS kv FROM java.util.HashMap h WHERE (h[0:-1].size() > 0)</syntaxhighlight>

<pre>
map                              |kv
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
java.util.HashMap [id=0x22f49970]|[{key=java.lang.String [id=0x22f44658], value=java.lang.String [id=0x22f44670]}, {key=java.lang.String [id=0x22f44688], value=java.lang.String [id=0x22f446a0]}]
java.util.HashMap [id=0x22f49948]|[{key=java.lang.String [id=0x22f44628], value=java.lang.String [id=0x22f44640]}]
java.util.HashMap [id=0x22f49920]|[{key=java.lang.String [id=0x22f445c8], value=java.lang.String [id=0x22f445e0]}, {key=java.lang.String [id=0x22f445f8], value=java.lang.String [id=0x22f44610]}]
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
</pre>

Extracts the map and a list of key / values pairs.

'''Question: Should MAT use the select clause or the AS column name as the key for the RowMap and subsequent access, or should it just use a simple name if given as part of the select, and a generated term such as `Expr1000` or `EXPR$0` (Apache Calcite) if more complex? Is an autogenerated term required in the case of duplicated select items or column names?'''

==== Flattening ====

The second column is a list of key/value pairs. It would be nice to process these further, and auto-flattening  is one way to achieve this. If a sub-select returns a RowMap containing values which are lists or arrays, then auto-flattening splits that RowMap into multiple RowMaps, one for each entry of the list or array. Other objects are just repeated in the RowMap. If there are multiple lists or arrays of different lengths then items beyond the end of the list or array are replaced by null.

'''Question: This is very experimental and is intended to be a basic alternative to SQL JOIN. Is auto-flattening of sub-selects the way to go, or should it operated on FROM method calls as well or an alternative. Should there be a `flatten((SELECT ...))` function, or a `flatten()` method on the result table?'''

'''Question: A SELECT as a SELECT item can with OQL return multiple rows or columns. SQL expects at most one row and one column. Should a SELECT with one column be dequalified? E.g. <syntaxhighlight lang="tsql">SELECT z, z.st.v FROM OBJECTS ( SELECT (SELECT s.value AS v FROM java.lang.String s ) AS st, t FROM java.lang.Long t  ) z</syntaxhighlight> the z.st.v needs several levels of qualification. As there is just one column, should this be just 'z.st'?

The aim is to achieve a result such as
<pre>
Map                              |Key                             |Value
----------------------------------------------------------------------------------------------------------------------------------------------------------------
java.util.HashMap [id=0xe35e96a8]|java.lang.String [id=0xe35ef478]|com.sun.management.internal.PlatformMBeanProviderImpl$4 [id=0xe35cbf68]
java.util.HashMap [id=0xe35e96a8]|java.lang.String [id=0xe35e96d8]|jdk.management.jfr.internal.FlightRecorderMXBeanProvider$SingleMBeanComponent [id=0xe35e98b0]
java.util.HashMap [id=0xe35ce190]|java.lang.String [id=0xe35c1950]|java.lang.Object [id=0xe0c145f0]
java.util.HashMap [id=0xe35ce190]|java.lang.String [id=0xe35c1900]|java.lang.Object [id=0xe0c145f0]
java.util.HashMap [id=0xe35cbde0]|java.lang.String [id=0xe35cbc70]|java.lang.Object [id=0xe0c145f0]
----------------------------------------------------------------------------------------------------------------------------------------------------------------
</pre>


#<syntaxhighlight lang="tsql">SELECT z.map as Map, z.kv.key as Key, z.kv.value as Value FROM OBJECTS ( SELECT h AS map, (SELECT e.getKey() AS key, e.getValue() AS value FROM OBJECTS ${h}[0:-1] e ) AS kv FROM java.util.HashMap h WHERE (h[0:-1].size() > 0) ) z</syntaxhighlight>
#<syntaxhighlight lang="tsql">SELECT z.map AS Map, z.kv.key AS Key, z.kv.value AS Value FROM OBJECTS (eval(( SELECT h AS map, (SELECT e.getKey() AS key, e.getValue() AS value FROM OBJECTS ${h}[0:-1] e ) AS kv FROM java.util.HashMap h WHERE (h[0:-1].size() > 0))) ) z</syntaxhighlight>
#<syntaxhighlight lang="tsql">SELECT z.map AS Map, z.kv.key AS Key, z.kv.value AS Value FROM OBJECTS (flatten((SELECT h AS map, (SELECT e.getKey() AS key, e.getValue() AS value FROM OBJECTS ${h}[0:-1] e ) AS kv FROM java.util.HashMap h WHERE (h[0:-1].size() > 0))) ) z</syntaxhighlight>
#<syntaxhighlight lang="tsql">SELECT z.map AS Map, z.kv.key AS Key, z.kv.value AS Value FROM OBJECTS (eval(( SELECT h AS map, (SELECT e.getKey() AS key, e.getValue() AS value FROM OBJECTS ${h}[0:-1] e ) AS kv FROM java.util.HashMap h WHERE (h[0:-1].size() > 0)).flatten()) ) z</syntaxhighlight>

'''Question: How should flattening handle lists or arrays with no items? Should the row be omitted, or should a row with no entries for the array be generated as a null for the item, or should the empty array be left unchanged? It is hard to add back a row later, but it is possible to filter rows with null or an empty list. It is hard to tell an empty list/array from one which contains one entry which is the same as the default value OQL chose (null or an empty list/array).'''


Here is another example of flattening - a query to see if any child of a parent does not have a back reference to the parent.

<syntaxhighlight lang="tsql">SELECT group AS Group, thread AS Thread 
FROM OBJECTS ( SELECT t AS group, t.threads[0:-1] AS thread FROM java.lang.ThreadGroup t  ) 
WHERE ((thread != null) and (thread.group != group))</syntaxhighlight>

This selects all the java.lang.ThreadGroup objects and then generates rows with two columns, the group and a list of the child threads. This is then flattened to a rows of the group and a single child thread, where the select then checks for a non-null child Thread and a child which does not point back to the ThreadGroup. Note here the omission of the alias name before the 'WHERE' as it is not necessary - 'group' and 'thread' in the outer select do not need to be qualified with an alias name.

=== GROUP BY ===

SQL GROUP BY can be simulated in the following fashion:

<syntaxhighlight lang="tsql">SELECT s.sz AS Size, 
(SELECT OBJECTS m FROM java.util.HashMap m WHERE (m[0:-1].size() = s.sz)) AS Maps 
FROM OBJECTS ( SELECT DISTINCT h[0:-1].size() AS sz FROM java.util.HashMap h  ) s</syntaxhighlight>

# This first obtains a list of things to group by, which here is a list of sizes.
# The sizes are then returned to the next phase as a sub-select.
# Then the select items clause only chooses objects which match the current GROUP BY value.
# Then the results are converted to an object list, which appears in the columns as an int[] array, which can then be used as a context menu.

Another example grouping by number of inbounds:

<syntaxhighlight lang="tsql">SELECT s.sz AS Size, 
(SELECT OBJECTS m FROM INSTANCEOF java.lang.Object m WHERE (inbounds(m).@length = s.sz)) AS Objects 
FROM OBJECTS ( SELECT DISTINCT inbounds(h).@length AS sz FROM INSTANCEOF java.lang.Object h  ) s</syntaxhighlight>

=== COUNT ===

SQL COUNT can be simulated in the following fashion using the @length attribute on arrays, or on a list or by converting an array to a list and then using size().

<syntaxhighlight lang="tsql">SELECT z.size AS Size, 
z.maps AS Maps, 
z.maps.@length AS "Count", 
z.maps[0:-1].size() AS "Count (another way)" 
FROM OBJECTS ( eval((
SELECT 
s.sz AS size, 
(SELECT OBJECTS m FROM java.util.HashMap m WHERE (m[0:-1].size() = s.sz)) AS maps 
FROM OBJECTS ( SELECT DISTINCT h[0:-1].size() AS sz FROM java.util.HashMap h  ) s
)) ) z</syntaxhighlight>

# This first obtains a list of things to group by, which here is a list of sizes.
# The sizes are then returned to the next phase as a sub-select.
# Then the select items clause only chooses objects which match the current GROUP BY value.
# Then the results are converted to an object list, which appears in the columns as an int[] array
# The select is then wrapped by an eval() so that the outer select does not flatten it
# The outer select then generates the result, with the size, the maps, and two ways of counting the elements in the map array, once using @length and one using size().

Another example:

<syntaxhighlight lang="tsql">SELECT z.size AS Size, 
z.objects AS Objects,
z.objects.@length AS "Count", 
z.objects[0:-1].size() AS "Count (another way)" 
FROM OBJECTS ( eval((
SELECT s.sz AS size, 
(SELECT OBJECTS m FROM INSTANCEOF java.lang.Object m WHERE (inbounds(m).@length = s.sz)) AS objects 
FROM OBJECTS ( SELECT DISTINCT inbounds(h).@length AS sz FROM INSTANCEOF java.lang.Object h  ) s
)) ) z</syntaxhighlight>

=== JOIN operations ===

OQL does not have SQL-style JOIN operations apart from UNION. With flattening it is possible to simulate some of these operations, but the statements required are more complex.

Consider a JOIN on `java.lang.Integer` and `java.lang.Long` based on the value fields of both.

==== CROSS JOIN ====

This operation generates every combination of the left and right tables (sets of objects), so can generate a huge sized result table.

<syntaxhighlight lang="tsql">SELECT z.i AS Integer, z.i.value AS "Integer value", z.lv.l AS Long, z.lv.l.value as "Long value" 
FROM OBJECTS ( SELECT i, (SELECT l FROM java.lang.Long l ) AS lv FROM java.lang.Integer i  ) z</syntaxhighlight>

This selects all the `java.lang.Integer` heap objects, then for each Integer heap object then generates a row with the object and a list of all the java.lang.Long objects, then flattens the rows. Each flattened row has one Integer heap object (accessed via 'z.i' or 'i') and one Long heap object (accessed via 'z.lv.l' or 'lv.l'). The objects in one row do not necessarily match in value.

Compare to MAT Calcite (SQL):
<syntaxhighlight lang="sql">select i.this,i.this['value'] as "Integer value", l.this,l.this['value'] as "Long value" 
from "java.lang.Integer" i CROSS JOIN "java.lang.Long" l</syntaxhighlight>

==== LEFT JOIN / LEFT OUTER JOIN ====

This operation generates every row from the left table (set of objects) and includes in that row any corresponding row from the right table. The result table is the same size as the left. 

<syntaxhighlight lang="tsql">SELECT z.i AS Integer, z.i.value AS "Integer value", z.lv.l AS Long, z.lv.l.value as "Long value"  
FROM OBJECTS ( SELECT i, (SELECT l FROM java.lang.Long l WHERE (l.value = i.value)) AS lv FROM java.lang.Integer i  ) z</syntaxhighlight>

This selects all the `java.lang.Integer` objects, then for each Integer then generates a row with the object and a list of all the java.lang.Long objects with the same value, then flattens the rows. Each flattened row has one Integer heap object (accessed via 'z.i' or 'i') and possibly one matching Long heap object or null (accessed via 'z.lv.l' or 'lv.l').

Compare to MAT Calcite (SQL):
<syntaxhighlight lang="sql">select i.this,i.this['value'] as "Integer value", l.this,l.this['value'] as "Long value" 
from "java.lang.Integer" i LEFT JOIN "java.lang.Long" l on i.this['value']+0 = l.this['value']+0</syntaxhighlight>

==== INNER JOIN ====

This operation generates every row from the left table (set of objects) which has a matching row in the right table (set of objects). The result table is no bigger than the smaller of the left and right tables. 

<syntaxhighlight lang="tsql">SELECT z.i AS Integer, z.i.value AS "Integer value", z.lv.l AS Long, z.lv.l.value as "Long value" 
FROM OBJECTS ( SELECT i, (SELECT l FROM java.lang.Long l WHERE (l.value = i.value)) AS lv FROM java.lang.Integer i  ) z 
WHERE (z.lv != null)</syntaxhighlight>

This selects all the `java.lang.Integer` objects, then for each Integer then generates a row with the object and a list of all the java.lang.Long objects with the same value, then flattens the rows and excludes any row without a java.lang.Long value. 

<syntaxhighlight lang="tsql">SELECT z.iv.i AS Integer, z.iv.i.value AS "Integer value", z.l AS Long, z.l.value as "Long value" 
FROM OBJECTS ( SELECT (SELECT i FROM java.lang.Integer i WHERE (i.value = l.value)) AS iv, l FROM java.lang.Long l  ) z 
WHERE (z.iv != null)</syntaxhighlight>

This selects all the `java.lang.Long` objects, then for each Long then generates a row with the object and a list of all the java.lang.Integer objects with the same value, then flattens the rows and excludes any row without a java.lang.Integer value. Each flattened row has one Integer heap object (accessed via 'z.i' or 'i') and one Long heap object (accessed via 'z.lv.l' or 'lv.l') which matches by the 'WHERE (i.value = l.value)' clause.

Compare to MAT Calcite (SQL):
<syntaxhighlight lang="sql">select i.this,i.this['value'] as "Integer value", l.this,l.this['value'] as "Long value" 
from "java.lang.Integer" i INNER JOIN "java.lang.Long" l on i.this['value']+0 = l.this['value']+0</syntaxhighlight>

==== RIGHT JOIN / RIGHT OUTER JOIN ====

This operation generates every row from the right table (set of objects) and includes in that row any corresponding row from the left table which matches. The result table is the same size as the right. 

<syntaxhighlight lang="tsql">SELECT z.iv.i AS Integer, z.iv.i.value AS "Integer value", z.l AS Long, z.l.value as "Long value" 
FROM OBJECTS ( SELECT (SELECT i FROM java.lang.Integer i WHERE (i.value = l.value)) AS iv, l FROM java.lang.Long l  ) z</syntaxhighlight>

This selects all the `java.lang.Long` objects, then for each Long then generates a row with the object and a list of all the java.lang.Integer objects with the same value, then flattens the rows. Each flattened row has one Long heap object (accessed via 'z.l' or 'l') and possibly one matching Integer heap object or null (accessed via 'z.iv.i' or 'iv.i').

Compare to MAT Calcite (SQL):
<syntaxhighlight lang="sql">select i.this,i.this['value'] as "Integer value", l.this,l.this['value'] as "Long value" 
from "java.lang.Integer" i RIGHT JOIN "java.lang.Long" l on i.this['value']+0 = l.this['value']+0</syntaxhighlight>

==== FULL OUTER JOIN ====

This operation generates has a row for every row from the left table (set of objects) and every row from the right table (set of objects), but when the left table has a row with the same value as a row from right table they will be included in the same output row. The result table is at least as big as the bigger of the left table and right table. 

<syntaxhighlight lang="tsql">SELECT z.i AS Integer, z.i.value AS "Integer value", z.lv.l AS Long, z.lv.l.value as "Long value" 
FROM OBJECTS ( SELECT i, (SELECT l FROM java.lang.Long l WHERE (l.value = i.value)) AS lv FROM java.lang.Integer i  ) z 
UNION (
SELECT z.iv.i AS Integer, z.iv.i.value AS "Integer value", z.l AS Long, z.l.value as "Long value" 
FROM OBJECTS ( SELECT (SELECT i FROM java.lang.Integer i WHERE (i.value = l.value)) AS iv, l FROM java.lang.Long l  ) z 
WHERE (z.iv = null)
)</syntaxhighlight>

This does a [[MemoryAnalyzer/OQL#LEFT_JOIN_.2F_LEFT_OUTER_JOIN|LEFT JOIN / LEFT OUTER JOIN]] then combines the rows with a list of all java.lang.Long objects which do not have a corresponding java.lang.Integer object. Each row contains either an Integer heap object or a Long heap object or both.

Compare to MAT Calcite (SQL):
<syntaxhighlight lang="sql">select i.this,i.this['value'] as "Integer value", l.this,l.this['value'] as "Long value" 
from "java.lang.Integer" i FULL OUTER JOIN "java.lang.Long" l on i.this['value']+0 = l.this['value']+0</syntaxhighlight>

=== Bug fixes ===

* When there is a union queries with a select which returns no items, that select was then omitted from the command window.
<syntaxhighlight lang="tsql">SELECT s FROM java.lang.String s UNION (SELECT s FROM java.lang.Missing s)</syntaxhighlight> this was then redisplayed as <syntaxhighlight lang="tsql">SELECT s FROM java.lang.String s</syntaxhighlight>
* Context dependency fix. OQL processing optimizes some queries by detecting that some parts are not context dependent and will evaluate the same each time, so can just be evaluated once. This processing was not correct for sub-select. Now a sub-select in a select item will be correctly re-evaluated if required.
* Progress monitoring has been improved so the progress monitor bar graph better shows how much more work needs to be done to complete a query. Also, cancelling a long running OQL query works more swiftly.

== Writing Queries ==

Writing queries can be a bit of an art, with some trial and error required.
For example, consider this problem:

"I would like to find all the unreachable objects of a particular type."

Normally, Memory Analyzer discards unreachable objects and the only thing visible are some totals in the
unreachable objects histogram. This is not useful for OQL, so we need the <code>-keep_unreachable_objects</code> option. The unreachable objects will not then be discarded, but will be
retained by some artificially inserted <code>UNREACHABLE_OBJECT</code> garbage collection roots.

Let us try some examples:

The <code>snapshot</code> has some interesting methods, including <code>getGCRoots()</code>. 

<syntaxhighlight lang="tsql">
SELECT * FROM OBJECTS ${snapshot}.getGCRoots() r
</syntaxhighlight>

As it is a no-argument method starting with get we can access it using a bean attribute.

<syntaxhighlight lang="tsql">
SELECT * FROM OBJECTS ${snapshot}.@GCRoots r
</syntaxhighlight>

<pre>[21800, 21801, 21802, 21803, 21804, 21805,</pre>
This returns an array of integers - the MAT object IDs.

Treat them as values:
<syntaxhighlight lang="tsql">
SELECT r FROM OBJECTS ${snapshot}.@GCRoots r
</syntaxhighlight>
<pre>
      r
--------
 21,800
 21,801
 21,802
--------
</pre>
Treat them as objects:
<syntaxhighlight lang="tsql">
SELECT OBJECTS r FROM OBJECTS ${snapshot}.@GCRoots r
</syntaxhighlight>
<pre>
Class Name                                                        | Shallow Heap | Retained Heap
-------------------------------------------------------------------------------------------------
class java.lang.IllegalArgumentException @ 0x2b75f188 System Class|          104 |           104
class java.lang.NumberFormatException @ 0x2b75f1e8 System Class   |          104 |           104
class java.text.CharacterIterator @ 0x2b760648 System Class       |          104 |           104
-------------------------------------------------------------------------------------------------
</pre>
This gives all the GC roots, but we only want the unreachable roots.

The snapshot getGCRootInfo(int id) method might help. Let use try it out using an object ID
above that is a GC root.

<syntaxhighlight lang="tsql">
SELECT * FROM OBJECTS ${snapshot}.getGCRootInfo(21800) t
</syntaxhighlight>

<pre>
[org.eclipse.mat.parser.model.XGCRootInfo@2b963bbc]
</pre>
so it returns an array of XGCRootInfo.

Let OQL look at each element:
<syntaxhighlight lang="tsql">
SELECT t FROM OBJECTS ${snapshot}.getGCRootInfo(21800) t
</syntaxhighlight>
<pre>
r
-------------------------------------------------
org.eclipse.mat.parser.model.XGCRootInfo@2b963bbc
-------------------------------------------------
</pre>
Using the MAT API reference we can see that this type has a <code>getType()</code> method, so
access it using as a bean.
<syntaxhighlight lang="tsql">
SELECT t,t.@type FROM OBJECTS ${snapshot}.getGCRootInfo(21800) t 
</syntaxhighlight>
<pre>
t                                                | t.@type
-----------------------------------------------------------
org.eclipse.mat.parser.model.XGCRootInfo@2b963bbc|       2
-----------------------------------------------------------
</pre>
Using the API reference we can see that <code>GCRootInfo.Type.UNREACHABLE</code> has a value of <2048>
so we can select just the GCRootInfo objects with that value as:
<syntaxhighlight lang="tsql">
SELECT t,t.@type FROM OBJECTS ${snapshot}.getGCRootInfo(21800) t WHERE t.@type = 2048
</syntaxhighlight>
This does not return anything as object ID 21800 is a SYSTEM root. However, we can use it to
select from all the GC roots, relying on this sub SELECT clause being null if it does not find an
UNREACHABLE root. We can simplify the select item to <code>*</code> as it is not important.

<syntaxhighlight lang="tsql">
SELECT OBJECTS r FROM OBJECTS ${snapshot}.getGCRoots() r
 WHERE (SELECT * FROM OBJECTS ${snapshot}.getGCRootInfo(r) t WHERE t.@type = 2048) != null
</syntaxhighlight>
<pre>
Class Name                                          | Shallow Heap | Retained Heap
-----------------------------------------------------------------------------------
int[11] @ 0x22ef4c58 Unreachable                    |           56 |            56
int[9] @ 0x22ed2ad8 Unreachable                     |           48 |            48
int[7] @ 0x22ed2b28 Unreachable                     |           40 |            40
int[17] @ 0x22ed3150 Unreachable                    |           80 |            80
java.lang.ref.SoftReference @ 0x22f52cd8 Unreachable|           32 |           400
-----------------------------------------------------------------------------------
</pre>
This is looking promising - we have a list of objects, all of which are UNREACHABLE GC roots.
We now need the retained set to find everything that is normally discarded, but is now only
retained via these artificial roots.
<syntaxhighlight lang="tsql">
SELECT AS RETAINED SET r FROM OBJECTS ${snapshot}.getGCRoots() r
 WHERE (SELECT * FROM OBJECTS ${snapshot}.getGCRootInfo(r) t WHERE t.@type = 2048) != null
</syntaxhighlight>
<pre>
r
--------------------------------------------------------------------

java.lang.Class [id=0x26970198;name=java.lang.Throwable[]]
java.lang.Class [id=0x26970370;name=java.lang.Error[]]
java.lang.Class [id=0x26970558;name=java.lang.VirtualMachineError[]]
--------------------------------------------------------------------
</pre>
Some of the output is above.
Alternatively to see them as a tree, not a table.
<syntaxhighlight lang="tsql">
SELECT AS RETAINED SET * FROM OBJECTS ${snapshot}.getGCRoots() r
 WHERE (SELECT * FROM OBJECTS ${snapshot}.getGCRootInfo(r) t WHERE t.@type = 2048) != null
</syntaxhighlight>
<pre>
Class Name                                                        | Shallow Heap | Retained Heap
-------------------------------------------------------------------------------------------------
int[33] @ 0x22e5be20 Unreachable                                  |          144 |           144
int[27] @ 0x22e5bed0 Unreachable                                  |          120 |           120
int[17] @ 0x22e5bf60 Unreachable                                  |           80 |            80
sun.reflect.NativeConstructorAccessorImpl @ 0x22e5c5d8 Unreachable|           24 |           184
sun.reflect.NativeConstructorAccessorImpl @ 0x22e5c5f0 Unreachable|           24 |           184
-------------------------------------------------------------------------------------------------
</pre>
If we are quickly interested in the types then the 'Show as Histogram' button in the top right of the toolbar will show the totals by class. If we are interested in the subclasses then the 'Group by superclass' option does that.

However, we might want to do it entirely by OQL - for example as part of another query or in batch mode.
We now need to choose just the objects of the type we are interested in. There are
several ways. 
# We could list all the objects of the type we are possibly interested in, and keep those in the retained set.
# We could look at each of the retained objects and see if each one is in the list of objects we are interested in.
# We could look at each of the retained objects, find its class, then its class name and see if the class name matches the one we are interested in.

<syntaxhighlight lang="tsql">
SELECT * FROM java.util.ArrayList o
</syntaxhighlight>

<syntaxhighlight lang="tsql">
SELECT * FROM java.util.ArrayList o WHERE o in
(SELECT AS RETAINED SET * FROM OBJECTS ${snapshot}.getGCRoots() r
 WHERE (SELECT * FROM OBJECTS ${snapshot}.getGCRootInfo(r) t WHERE t.@type = 2048) != null)
</syntaxhighlight>
<pre>
Class Name                      | Shallow Heap | Retained Heap
---------------------------------------------------------------
java.util.ArrayList @ 0x22efe620|           24 |           320
java.util.ArrayList @ 0x22efe608|           24 |           320
java.util.ArrayList @ 0x22efe5a8|           24 |           320
java.util.ArrayList @ 0x22efcfd8|           24 |           384
java.util.ArrayList @ 0x22efb998|           24 |           320
java.util.ArrayList @ 0x22efb928|           24 |           320
---------------------------------------------------------------
</pre>

or

<syntaxhighlight lang="tsql">
SELECT * FROM OBJECTS (SELECT AS RETAINED SET * FROM OBJECTS ${snapshot}.getGCRoots() r
 WHERE (SELECT * FROM OBJECTS ${snapshot}.getGCRootInfo(r) t WHERE t.@type = 2048) != null) u
 WHERE u in (SELECT * FROM java.util.ArrayList o)
</syntaxhighlight>
<pre>
Class Name                      | Shallow Heap | Retained Heap
---------------------------------------------------------------
java.util.ArrayList @ 0x22efb928|           24 |           320
java.util.ArrayList @ 0x22efb998|           24 |           320
java.util.ArrayList @ 0x22efcfd8|           24 |           384
java.util.ArrayList @ 0x22efe5a8|           24 |           320
java.util.ArrayList @ 0x22efe608|           24 |           320
java.util.ArrayList @ 0x22efe620|           24 |           320
---------------------------------------------------------------
</pre>

or

<syntaxhighlight lang="tsql">
SELECT * FROM OBJECTS (SELECT AS RETAINED SET * FROM OBJECTS ${snapshot}.getGCRoots() r
 WHERE (SELECT * FROM OBJECTS ${snapshot}.getGCRootInfo(r) t WHERE t.@type = 2048) != null) u
 WHERE u.@clazz.@name = "java.util.ArrayList"
</syntaxhighlight>
<pre>
Class Name                      | Shallow Heap | Retained Heap
---------------------------------------------------------------
java.util.ArrayList @ 0x22efb928|           24 |           320
java.util.ArrayList @ 0x22efb998|           24 |           320
java.util.ArrayList @ 0x22efcfd8|           24 |           384
java.util.ArrayList @ 0x22efe5a8|           24 |           320
java.util.ArrayList @ 0x22efe608|           24 |           320
java.util.ArrayList @ 0x22efe620|           24 |           320
---------------------------------------------------------------
</pre>

If we wanted to know if about objects which extended <code>java.util.AbstractCollection</code> then
use:

<syntaxhighlight lang="tsql">
SELECT * FROM INSTANCEOF java.util.AbstractCollection o WHERE o in
(SELECT AS RETAINED SET * FROM OBJECTS ${snapshot}.getGCRoots() r
 WHERE (SELECT * FROM OBJECTS ${snapshot}.getGCRootInfo(r) t WHERE t.@type = 2048) != null)
</syntaxhighlight>
<pre>
Class Name                      | Shallow Heap | Retained Heap
---------------------------------------------------------------
java.util.ArrayList @ 0x22efb928|           24 |           320
java.util.ArrayList @ 0x22efb998|           24 |           320
java.util.ArrayList @ 0x22efcfd8|           24 |           384
java.util.ArrayList @ 0x22efe5a8|           24 |           320
java.util.ArrayList @ 0x22efe608|           24 |           320
java.util.ArrayList @ 0x22efe620|           24 |           320
java.util.HashSet @ 0x22f2b600  |           16 |           136
java.util.Vector @ 0x22f2b5e8   |           24 |            80
java.util.Vector @ 0x22f2b638   |           24 |            80
---------------------------------------------------------------
</pre>

or

<syntaxhighlight lang="tsql">
SELECT * FROM OBJECTS (SELECT AS RETAINED SET * FROM OBJECTS ${snapshot}.getGCRoots() r
 WHERE (SELECT * FROM OBJECTS ${snapshot}.getGCRootInfo(r) t WHERE t.@type = 2048) != null) u
 WHERE u in (SELECT * FROM INSTANCEOF java.util.AbstractCollection o)
</syntaxhighlight>
<pre>
Class Name                      | Shallow Heap | Retained Heap
---------------------------------------------------------------
java.util.ArrayList @ 0x22efb928|           24 |           320
java.util.ArrayList @ 0x22efb998|           24 |           320
java.util.ArrayList @ 0x22efcfd8|           24 |           384
java.util.ArrayList @ 0x22efe5a8|           24 |           320
java.util.ArrayList @ 0x22efe608|           24 |           320
java.util.ArrayList @ 0x22efe620|           24 |           320
java.util.Vector @ 0x22f2b5e8   |           24 |            80
java.util.HashSet @ 0x22f2b600  |           16 |           136
java.util.Vector @ 0x22f2b638   |           24 |            80
---------------------------------------------------------------
</pre>

or

<syntaxhighlight lang="tsql">
SELECT * FROM OBJECTS (SELECT AS RETAINED SET * FROM OBJECTS ${snapshot}.getGCRoots() r
 WHERE (SELECT * FROM OBJECTS ${snapshot}.getGCRootInfo(r) t WHERE t.@type = 2048) != null) u
 WHERE u.@clazz.doesExtend("java.util.AbstractCollection")
</syntaxhighlight>
<pre>
Class Name                      | Shallow Heap | Retained Heap
---------------------------------------------------------------
java.util.ArrayList @ 0x22efb928|           24 |           320
java.util.ArrayList @ 0x22efb998|           24 |           320
java.util.ArrayList @ 0x22efcfd8|           24 |           384
java.util.ArrayList @ 0x22efe5a8|           24 |           320
java.util.ArrayList @ 0x22efe608|           24 |           320
java.util.ArrayList @ 0x22efe620|           24 |           320
java.util.Vector @ 0x22f2b5e8   |           24 |            80
java.util.HashSet @ 0x22f2b600  |           16 |           136
java.util.Vector @ 0x22f2b638   |           24 |            80
---------------------------------------------------------------
</pre>

== Extracting Thread information ==

With a recent level of MAT this should work:

<syntaxhighlight lang="tsql">
SELECT u.Thread AS Thread, u.Frame.@text AS Frame 
  FROM OBJECTS ( 
    SELECT t AS Thread, ${snapshot}.getThreadStack(t.@objectId).@stackFrames AS Frame 
      FROM java.lang.Thread t  ) u 
</syntaxhighlight>

The inner select

<syntaxhighlight lang="tsql">
SELECT t AS Thread, ${snapshot}.getThreadStack(t.@objectId).@stackFrames AS Frame 
  FROM java.lang.Thread t
</syntaxhighlight>
    
extracts each thread and an array of stack frames. The outer select then flattens that array with the same thread reference for each of its stack frames.

<pre>
Thread                           |Frame
--------------------------------------------------------------------------------------------------------------------------------------------------
java.lang.Thread [id=0x7b6a1e7f0]|at java.lang.Object.wait(J)V (Native Method)
java.lang.Thread [id=0x7b6a1e7f0]|at java.lang.Object.wait(JI)V (Unknown Source)
java.lang.Thread [id=0x7b6a1e7f0]|at com.squareup.okhttp.ConnectionPool.performCleanup()Z (ConnectionPool.java:305)
java.lang.Thread [id=0x7b6a1e7f0]|at com.squareup.okhttp.ConnectionPool.runCleanupUntilPoolIsEmpty()V (ConnectionPool.java:242)
java.lang.Thread [id=0x7b6a1e7f0]|at com.squareup.okhttp.ConnectionPool.access$000(Lcom/squareup/okhttp/ConnectionPool;)V (ConnectionPool.java:54)
java.lang.Thread [id=0x7b6a1e7f0]|at com.squareup.okhttp.ConnectionPool$1.run()V (ConnectionPool.java:97)
--------------------------------------------------------------------------------------------------------------------------------------------------
</pre>


You can even extract each local from each frame using another select.

<syntaxhighlight lang="tsql">
    SELECT v.Thread as Thread, toString(v.Thread) AS Name, v.Frame AS Frame, ${snapshot}.getObject(v.Objs) AS Local 
      FROM OBJECTS ( 
        SELECT u.Thread AS Thread, u.Frame.@text AS Frame, u.Frame.@localObjectsIds AS Objs 
          FROM OBJECTS ( 
            SELECT t AS Thread, ${snapshot}.getThreadStack(t.@objectId).@stackFrames AS Frame 
              FROM java.lang.Thread t
          ) u
      ) v 
      WHERE (v.Objs != null)
</syntaxhighlight>

<pre>
v.Thread                         |Name                 |Frame                                                                                                           |Local
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
java.lang.Thread [id=0x7b6a1e7f0]|OkHttp ConnectionPool|at com.squareup.okhttp.ConnectionPool.performCleanup()Z (ConnectionPool.java:305)                               |java.util.ArrayList [id=0x7bb402eb8]
java.lang.Thread [id=0x7b6a1e7f0]|OkHttp ConnectionPool|at com.squareup.okhttp.ConnectionPool.performCleanup()Z (ConnectionPool.java:305)                               |com.squareup.okhttp.ConnectionPool [id=0x6c556ccb0]
java.lang.Thread [id=0x7b6a1e7f0]|OkHttp ConnectionPool|at com.squareup.okhttp.ConnectionPool.runCleanupUntilPoolIsEmpty()V (ConnectionPool.java:242)                   |com.squareup.okhttp.ConnectionPool [id=0x6c556ccb0]
java.lang.Thread [id=0x7b6a1e7f0]|OkHttp ConnectionPool|at com.squareup.okhttp.ConnectionPool.access$000(Lcom/squareup/okhttp/ConnectionPool;)V (ConnectionPool.java:54)|com.squareup.okhttp.ConnectionPool [id=0x6c556ccb0]
java.lang.Thread [id=0x7b6a1e7f0]|OkHttp ConnectionPool|at com.squareup.okhttp.ConnectionPool$1.run()V (ConnectionPool.java:97)                                         |com.squareup.okhttp.ConnectionPool$1 [id=0x6c556ccd8]
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
</pre>