1# PerfettoSQL Built-ins 2 3These are functions built into C++ which reduce the amount of boilerplate which 4needs to be written in SQL. 5 6## Profile Functions 7 8### STACK_FROM_STACK_PROFILE_FRAME 9 10`STACK_FROM_STACK_PROFILE_FRAME(frame_id)` 11 12#### Description 13 14Creates a stack with just the frame referenced by `frame_id` (reference to the 15[stack_profile_frame](sql-tables.autogen#stack_profile_frame) table) 16 17#### Return Type 18 19`BYTES` 20 21#### Arguments 22 23Argument | Type | Description 24-------- | ---- | ----------- 25frame_id | StackProfileFrameTable::Id | reference to the [stack_profile_frame](sql-tables.autogen#stack_profile_frame) table 26 27### STACK_FROM_STACK_PROFILE_CALLSITE 28 29`STACK_FROM_STACK_PROFILE_CALLSITE(callsite_id)` 30 31#### Description 32 33Creates a stack by taking a `callsite_id` (reference to the 34[stack_profile_callsite]](sql-tables.autogen#stack_profile_callsite) table) and 35generating a list of frames (by walking the 36[stack_profile_callsite]](sql-tables.autogen#stack_profile_callsite) table) 37 38#### Return Type 39 40`BYTES` 41 42#### Arguments 43 44Argument | Type | Description 45-------- | ---- | ----------- 46callsite_id | StackProfileCallsiteTable::Id | reference to the [stack_profile_callsite]](sql-tables.autogen#stack_profile_callsite) table 47 48### CAT_STACKS 49 50`CAT_STACKS(([root [[,level_1 [, ...]], leaf]])` 51 52#### Description 53 54Creates a Stack by concatenating other Stacks. Also accepts STRING values for 55which it generates a fake Frame. Null values are just ignored. 56 57#### Return Type 58 59`BYTES` 60 61#### Arguments 62 63Argument | Type | Description 64-------- | ---- | ----------- 65root | BYTES or STRING | Stack or STRING for which a fake Frame is generated 66... | BYTES or STRING | Stack or STRING for which a fake Frame is generated 67leaf | BYTES or STRING | Stack or STRING for which a fake Frame is generated 68 69### EXPERIMENTAL_PROFILE 70 71`EXPERIMENTAL_PROFILE(stack [,sample_type, sample_units, sample_value]*)` 72 73#### Description 74 75Aggregation function that generates a profile in 76[pprof](https://github.com/google/pprof) format from the given samples. 77 78#### Return Type 79 80`BYTES` ([pprof](https://github.com/google/pprof) data) 81 82#### Arguments 83 84Argument | Type | Description 85-------- | ---- | ----------- 86stack | BYTES | Stack or string for which a fake Frame is generated 87sample_type | STRING | Type of the sample value (e.g. size, time) 88sample_units | STRING | Units of the sample value (e.g. bytes, count) 89sample_value | LONG | Value for the sample 90 91Multiple samples can be specified. 92 93If only the `stack` argument is present, a `"samples"`, `"count"`, and `1` are 94used as defaults for `sample_type`, `sample_units`, and `sample_value` 95 respectively. 96 97#### Example 98 99CPU profile 100 101```sql 102SELECT 103 perf_session_id, 104 EXPERIMENTAL_PROFILE( 105 STACK_FROM_STACK_PROFILE_CALLSITE(callsite_id), 106 'samples', 107 'count', 108 1) AS profile 109FROM perf_sample 110GROUP BY perf_session_id 111``` 112 113Heap profile 114 115```sql 116SELECT 117 EXPERIMENTAL_PROFILE( 118 CAT_STACKS(heap_name, STACK_FROM_STACK_PROFILE_CALLSITE(callsite_id)), 119 'count', 120 'count', 121 count, 122 'size', 123 'bytes', 124 size) AS profile 125FROM heap_profile_allocation 126WHERE size >= 0 AND count >= 0 127```