Are system queries exclusive to the sysquery resource pool?
Out of curiosity, is running a query against a system table (such as
dual) supposed to exclusively acquire resources from the sysquery pool? It appears to be also dipping into the general pool:
dbadmin=> select * from query_requests where session_id = 'v7101n2-1244:0xaa6' and statement_id = 2;
-[ RECORD 1 ]-------+-------------------------------------------------- node_name | v_vmart_node0002 user_name | dbadmin session_id | v7101n2-1244:0xaa6 request_id | 2 transaction_id | 49539595901136378 statement_id | 2 request_type | QUERY request | select 1 from dual; request_label | search_path | "$user", public, v_catalog, v_monitor, v_internal memory_acquired_mb | 100 success | t error_count | start_timestamp | 2014-12-22 13:22:08.802066-06 end_timestamp | 2014-12-22 13:22:08.813208-06 request_duration_ms | 11 is_executing | f dbadmin=> select * from dc_resource_acquisitions where transaction_id = '49539595901136378' and statement_id = 2; -[ RECORD 1 ]----+------------------------------ time | 2014-12-22 13:22:08.803435-06 node_name | v_vmart_node0002 transaction_id | 49539595901136378 statement_id | 2 request_type | Acquire pool | 45035996273720462 pool_name | general priority | 0 memory_kb | 102400 filehandles | 0 threads | 0 queries | 0 start_time | 2014-12-22 13:22:08.802436-06 succeeded | t result | Granted failing_resource | is_required | t -[ RECORD 2 ]----+------------------------------ time | 2014-12-22 13:22:08.811576-06 node_name | v_vmart_node0002 transaction_id | 49539595901136378 statement_id | 2 request_type | Reserve pool | 45035996273720464 pool_name | sysquery priority | 110 memory_kb | 4231 filehandles | 0 threads | 4 queries | 1 start_time | 2014-12-22 13:22:08.811543-06 succeeded | t result | Granted failing_resource | is_required | t
0
Comments
The system query does take some resources from the user pool (which in this case is general) for some initial planning. These resources are released before the actual execution starts and the actual execution of the query takes resources from the sysquery pool.
Hope that helps.
- Shivani