- Schema:
For guidance on query performance when using organization-wide usage views, see Performance (Organization Usage).
AGGREGATE_
Important
This view is only available in the organization account. For more information, see Premium views in the organization account.
Organization Usage performance
When you query a specific view in the SNOWFLAKE.ORGANIZATION_USAGE schema, follow the organization-wide guidance in
Performance (Organization Usage): bound every scan on history views, list
columns explicitly, and use the time filter column table plus worked SQL and anti-patterns there.
This Organization Usage view enables you to monitor and track the execution of statements over time across the accounts in your organization. It contains similar data to the QUERY_HISTORY view but is aggregated in one-minute intervals for repeated SQL statements.
This view contains the same data as the AGGREGATE_QUERY_HISTORY view in the ACCOUNT_USAGE schema, additionally aggregated at the organization level. For details about how the data is aggregated, the fields returned by the OBJECT columns, and example queries, see the AGGREGATE_QUERY_HISTORY view in the ACCOUNT_USAGE schema.
This view is available only in the organization account. Users with the GLOBALORGADMIN role, or users granted the SNOWFLAKE.ORGANIZATION_GOVERNANCE_VIEWER application role, can access it. For details, see Accessing the ORGANIZATION_USAGE schema.
Columns¶
Organization-level columns
| Column Name | Data Type | Description |
|---|---|---|
| ORGANIZATION_NAME | VARCHAR | Name of the organization. |
| ACCOUNT_LOCATOR | VARCHAR | System-generated identifier for the account. |
| ACCOUNT_NAME | VARCHAR | User-defined identifier for the account. |
Additional columns
| Column Name | Data Type | Description |
|---|---|---|
| CALLS | NUMBER | Number of times the statement (query + query plan) was executed in the aggregation interval. |
| INTERVAL_START_TIME | TIMESTAMP_LTZ | Start time of the window of measurement (in the local time zone). |
| INTERVAL_END_TIME | TIMESTAMP_LTZ | End time of the window of measurement (in the local time zone). |
| QUERY_PARAMETERIZED_HASH | TEXT | Unique ID to identify identical parameterized queries. See QUERY_PARAMETERIZED_HASH column. |
| QUERY_TEXT | TEXT | Sample text of the SQL statement. |
| DATABASE_ID | NUMBER | Internal/system-generated identifier for the database that was in use. |
| DATABASE_NAME | TEXT | Database that was in use at the time of the query. |
| SCHEMA_ID | NUMBER | Internal/system-generated identifier for the schema that was in use. |
| SCHEMA_NAME | TEXT | Schema that was in use at the time of the query. |
| QUERY_TYPE | TEXT | DML, query, etc. If the query failed, then the query type may be UNKNOWN. |
| SESSION_ID | NUMBER | Session that executed the statement. |
| USER_NAME | TEXT | User who issued the query. |
| ROLE_NAME | TEXT | Role that was active in the session at the time of the query. |
| ROLE_TYPE | TEXT | Specifies APPLICATION, DATABASE_ROLE, or ROLE that executed the query. |
| WAREHOUSE_ID | NUMBER | Internal/system-generated identifier for the warehouse that was used. |
| WAREHOUSE_NAME | TEXT | Warehouse that the query executed on, if any. |
| WAREHOUSE_SIZE | TEXT | Size of the warehouse when this statement executed. |
| WAREHOUSE_TYPE | TEXT | Type of the warehouse when this statement executed. |
| QUERY_TAG | TEXT | Query tag set for this statement through the QUERY_TAG session parameter. |
| IS_CLIENT_GENERATED_STATEMENT | BOOLEAN | Indicates whether the query was client-generated. |
| RELEASE_VERSION | TEXT | Release version in the format of major_release.minor_release.patch_release. |
| ERRORS | ARRAY | List of error codes and messages that occurred during the aggregation interval. Each error is in the format of {"code": "code1", "message": "msg1", "count": 10}. |
| TOTAL_ELAPSED_TIME | OBJECT | Elapsed time (in milliseconds). |
| BYTES_SCANNED | OBJECT | Number of bytes scanned by this statement. |
| PERCENTAGE_SCANNED_FROM_CACHE | OBJECT | The percentage of data scanned from the local disk cache. The value ranges from 0.0 to 1.0. Multiply by 100 to get a true percentage. |
| BYTES_WRITTEN | OBJECT | Number of bytes written (for example, when loading into a table). |
| BYTES_WRITTEN_TO_RESULT | OBJECT | Number of bytes written to a result object. For example, select * from . . . would produce a set of results in tabular format representing each field in the selection. In general, the results object represents whatever is produced as a result of the query, and BYTES_WRITTEN_TO_RESULT represents the size of the returned result. |
| BYTES_READ_FROM_RESULT | OBJECT | Number of bytes read from a result object. |
| ROWS_PRODUCED | OBJECT | Number of rows produced by this statement. |
| ROWS_INSERTED | OBJECT | Number of rows inserted by the query. |
| ROWS_UPDATED | OBJECT | Number of rows updated by the query. |
| ROWS_DELETED | OBJECT | Number of rows deleted by the query. |
| ROWS_UNLOADED | OBJECT | Number of rows unloaded during data export. |
| BYTES_DELETED | OBJECT | Number of bytes deleted by the query. |
| PARTITIONS_SCANNED | OBJECT | Number of micro-partitions scanned. |
| PARTITIONS_TOTAL | OBJECT | Total micro-partitions of all tables included in this query. |
| BYTES_SPILLED_TO_LOCAL_STORAGE | OBJECT | Volume of data spilled to local disk. |
| BYTES_SPILLED_TO_REMOTE_STORAGE | OBJECT | Volume of data spilled to remote disk. |
| BYTES_SENT_OVER_THE_NETWORK | OBJECT | Volume of data sent over the network. |
| COMPILATION_TIME | OBJECT | Compilation time (in milliseconds). |
| EXECUTION_TIME | OBJECT | Execution time (in milliseconds). |
| QUEUED_PROVISIONING_TIME | OBJECT | Time (in milliseconds) spent in the warehouse queue, waiting for the warehouse compute resources to provision, due to warehouse creation, resume, or resize. |
| QUEUED_REPAIR_TIME | OBJECT | Time (in milliseconds) spent in the warehouse queue, waiting for compute resources in the warehouse to be repaired. |
| QUEUED_OVERLOAD_TIME | OBJECT | Time (in milliseconds) spent in the warehouse queue, due to the warehouse being overloaded by the current query workload. |
| TRANSACTION_BLOCKED_TIME | OBJECT | Time (in milliseconds) spent blocked by a concurrent DML. |
| OUTBOUND_DATA_TRANSFER_CLOUD | TEXT | Target cloud provider for statements that unload data to another region and/or cloud. |
| OUTBOUND_DATA_TRANSFER_REGION | TEXT | Target region for statements that unload data to another region and/or cloud. |
| OUTBOUND_DATA_TRANSFER_BYTES | OBJECT | Number of bytes transferred in statements that unload data to another region and/or cloud. |
| INBOUND_DATA_TRANSFER_CLOUD | TEXT | Source cloud provider for statements that load data from another region and/or cloud. |
| INBOUND_DATA_TRANSFER_REGION | TEXT | Source region for statements that load data from another region and/or cloud. |
| INBOUND_DATA_TRANSFER_BYTES | OBJECT | Number of bytes transferred in a replication operation from another account. The source account could be in the same region or a different region than the current account. |
| LIST_EXTERNAL_FILES_TIME | OBJECT | Time (in milliseconds) spent listing external files. |
| CREDITS_USED_CLOUD_SERVICES | OBJECT | Number of credits used for cloud services. |
| EXTERNAL_FUNCTION_TOTAL_INVOCATIONS | OBJECT | Aggregate number of times that this query called remote services. |
| EXTERNAL_FUNCTION_TOTAL_SENT_ROWS | OBJECT | Total number of rows that this query sent in all calls to all remote services. |
| EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS | OBJECT | Total number of rows that this query received from all calls to all remote services. |
| EXTERNAL_FUNCTION_TOTAL_SENT_BYTES | OBJECT | Total number of bytes that this query sent in all calls to all remote services. |
| EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES | OBJECT | Total number of bytes that this query received from all calls to all remote services. |
| QUERY_LOAD_PERCENT | OBJECT | The approximate percentage of active compute resources in the warehouse for this query execution. |
| QUERY_ACCELERATION_BYTES_SCANNED | OBJECT | Number of bytes scanned by the query acceleration service. |
| QUERY_ACCELERATION_PARTITIONS_SCANNED | OBJECT | Number of partitions scanned by the query acceleration service. |
| QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR | OBJECT | Upper limit scale factor that a query would have benefited from. |
| CHILD_QUERIES_WAIT_TIME | OBJECT | Time (in milliseconds) to complete the cached lookup when calling a memoizable function. |
| HYBRID_TABLE_REQUESTS_THROTTLED_COUNT | NUMBER | Number of hybrid table queries that were throttled. |
For columns with the OBJECT data type, the object contains statistical fields (such as sum, avg, and percentiles) computed across all executions within the aggregation interval. For a description of these fields, see the AGGREGATE_QUERY_HISTORY view in the ACCOUNT_USAGE schema.
Usage notes¶
- Latency for the view may be up to 24 hours.
- The data is retained for 365 days (1 year).
Examples¶
For example queries, see the AGGREGATE_QUERY_HISTORY view in the ACCOUNT_USAGE schema. To find organization-level information, replace SNOWFLAKE.ACCOUNT_USAGE with SNOWFLAKE.ORGANIZATION_USAGE in the queries.