![OS_FILE_CACHE_BUFFERING option behavior change [CR 487762]](arrow-left.gif){kind=icon}
![Results returned in a case insensitive database [CR 472049]](arrow-right.gif){kind=icon}
Indexes are often created to provide optimization metadata and to enforce uniqueness and primary/foreign key relationships. Once an index is created, however, DBAs face the challenge of quantifying benefits that the index provides.
Tables are often created in the IQ Main Store for the temporary storage of data that must be accessed by multiple connections or over a long period. These tables might be forgotten while they continue to use valuable disk space. Moreover, the number of these tables in a data warehouses is too large and the workloads are too complex to manually analyze usage.
Thus, unused indexes and tables waste disk space, increase backup time, and degrade DML performance.
In ESD #3, Sybase IQ offers tools for collecting and analyzing statistics for a defined workload. DBAs can quickly determine which database objects are being referenced by queries and should be kept. Unused tables, columns, and indexes can be dropped to reduce wasted space, improve DML performance, and decrease backup time.
Workload monitoring is implemented using stored procedures, which control the collection and report detailed usage of table, column, and, index information. These procedures complement INDEX_ADVISOR functionality, which generates messages suggesting additional column indexes that may improve performance of one or more queries Once recommended indexes have been added, their usage can be tracked to determine if they are worth keeping.
For more information, see “INDEX_ADVISOR option” and “sp_iqindexadvice procedure,” in the Sybase IQ Reference Manual.
Controls collection of workload monitor usage information and reports monitoring collection status.
sp_iqworkmon [ ‘action’ ] [ , ‘mode’ ]
action = ‘start’ , ‘stop’ , ’status’ , ‘reset’
mode = ‘index’ , ‘table’ , ‘column’ , ‘all’
For example:
sp_iqworkmon ‘start’ , ‘all’
If one argument is specified, it can only be action. For example:
sp_iqworkmon ‘stop’
DBA authority required. Users without DBA authority must be granted EXECUTE permission in order to run the stored procedure.
action Specifies the control action to apply. A value of start starts monitoring for the specified mode immediately. A value of stop stops monitoring immediately. A value of reset clears all collected usage information. A value of status (the default) displays the current status without changing state.
mode Specifies the type of monitoring to control. The INDEX, TABLE, and COLUMN keywords individually control monitoring of index usage, table usage, and column usage respectively. The default ALL keyword controls monitoring of all usage monitoring features simultaneously.
A result set always displays when sp_iqworkmon is executed. If a specific mode is specified (such as index), only the row for that mode displays.
Usage is collected only for SQL statements containing a FROM clause; for example, SELECT, UPDATE, and DELETE.
Column name |
Description |
|---|---|
MonMode |
Table, index, or column |
Status |
Started or stopped |
Rowcount |
Current number of rows collected |
The following table illustrates sample output from the sp_iqworkmon procedure.
MonMode Status Rowcount
index started 15
table started 10
column started 31
Reports detailed usage information for columns accessed by the workload.
sp_iqcolumnuse
DBA authority required. Users without DBA authority must be granted EXECUTE permission in order to run the stored procedure.
Columns from tables created in SYSTEM are not reported.
Column name |
Description |
|---|---|
TableName |
Table name |
ColumnName |
Column name |
Owner |
Username of column owner |
UID** |
Column Unique Identifier |
LastDT |
Date/time of last access |
NRef |
Number of query references |
**UID is a number assigned by the system that uniquely identifies the instance of the column (where instance is defined when an object is created).
The following table illustrates sample output from the sp_iqcolumnuse procedure.
TableName ColumnName Owner UID LastDT NRef orders o_orderdate DBA 151 20070917 22:41:22.. 13 orders o_shippriority DBA 154 20070917 22:41:22.. 13 lineitem l_orderkey DBA 186 20070917 22:41:22.. 13 lineitem l_extendedp.. DBA 191 20070917 22:41:22.. 13 lineitem l_discount DBA 192 20070917 22:41:22.. 13 lineitem l_shipdate DBA 196 20070917 22:41:22.. 13 #tmp1 expression DBA 10000000001218 20070917 22:57:36.. 1 #tmp1 expression DBA 10000000001222 20070917 22:41:58.. 1 ...
The long numbers in the example above are temporary
IDs.
Reports detailed usage information for secondary (non-FP) indexes accessed by the workload.
sp_iqindexuse
DBA authority required. Users without DBA authority must be granted EXECUTE permission in order to run the stored procedure.
Each secondary index accessed by the workload displays a row. Indexes that have not been accessed are not displayed. Index usage is broken down by optimizer, constraint, and query usage.
Indexes from tables created in SYSTEM are not reported.
Column name |
Description |
|---|---|
IndexName |
Index name |
TableName |
Table name |
Owner |
Username of index owner |
UID** |
Index Unique Identifier |
Type |
Index type |
LastDT |
Date/time of last access |
NOpt |
Number of metadata/uniqueness accesses |
NQry |
Number of Query accesses |
NConstraint |
Number of accesses for Unique or RI checks |
**UID is a number assigned by the system that uniquely identifies the instance of the index (where instance is defined when an object is created).
The following table illustrates sample output from the sp_iqindexuse procedure.
IndexName TableName Owner UID Type LastDT NOpt NQry NConstraint n_nationkey_hg nation DBA 29 HG 20070917 22:08:06~ 12 0 12 n_regionkey_hg nation DBA 31 HG 20070917 22:08:06~ 12 0 0 r_regionkey_hg region DBA 47 HG 20070917 22:08:06~ 12 0 12 s_suppkey_hg supplier DBA 64 HG 20070917 22:08:06~ 12 0 12 p_partkey_hg part DBA 87 HG 20070917 22:08:06~ 6 0 6 s_suppkey_hg supplier DBA 64 HG 20070917 22:08:06~ 12 0 12 ...
Reports detailed usage information for tables accessed by the workload.
sp_iqtableuse
DBA authority required. Users without DBA authority must be granted EXECUTE permission in order to run the stored procedure.
Tables created in SYSTEM are not reported.
Column name |
Description |
|---|---|
TableName |
Table name |
Owner |
Username of table owner |
UID** |
Table Unique Identifier |
LastDT |
Date/time of last access |
NRef |
Number of query references |
**UID is a number assigned by the system that uniquely identifies the instance of the table (where instance is defined when an object is created).
Reports IQ columns that were not referenced by the workload.
sp_iqunusedcolumn
DBA authority required. Users without DBA authority must be granted EXECUTE permission in order to run the stored procedure.
Columns from tables created in SYSTEM or local temporary tables are not reported.
Column name |
Description |
|---|---|
TableName |
Table name |
ColumnName |
Column name |
Owner |
Username of column owner |
The following table illustrates sample output from the sp_iqunusedcolumn procedure.
TableName ColumnName Owner
sales_order id DBA
sales_order cust_id DBA
sales_order order_date DBA
sales_order fin_code_id DBA
sales_order region DBA
sales_order sales_rep DBA
sales_order_items id DBA
sales_order_items line_id DBA
sales_order_items prod_id DBA
sales_order_items quantity DBA
sales_order_items ship_date DBA
contact id DBA
contact last_name DBA
contact first_name DBA
contact title DBA
contact street DBA
contact city DBA
contact state DBA
contact zip DBA
contact phone DBA
contact fax DBA
customer id DBA
...
Reports IQ secondary (non-FP) indexes that were not referenced by the workload.
sp_iqunusedindex
DBA authority required. Users without DBA authority must be granted EXECUTE permission in order to run the stored procedure.
Indexes from tables created in SYSTEM or local temporary tables are not reported.
Column name |
Description |
|---|---|
IndexName |
Index name |
TableName |
Table name |
Owner |
Username of index owner |
IndexType |
Index type |
The following table illustrates sample output from the sp_iqunusedindex procedure.
IndexName TableName Owner IndexType
ASIQ_IDX_T450_I7_HG sales_order DBA HG
ASIQ_IDX_T450_C6_HG sales_order DBA HG
ASIQ_IDX_T450_C4_HG sales_order DBA HG
ASIQ_IDX_T450_C2_HG sales_order DBA HG
ASIQ_IDX_T451_I6_HG sales_order_items DBA HG
ASIQ_IDX_T451_C3_HG sales_order_items DBA HG
ASIQ_IDX_T451_C1_HG sales_order_items DBA HG
ASIQ_IDX_T452_I11_HG contact DBA HG
ASIQ_IDX_T453_I10_HG customer DBA HG
ASIQ_IDX_T454_I4_HG fin_code DBA HG
ASIQ_IDX_T455_I5_HG fin_data DBA HG
ASIQ_IDX_T455_C3_HG fin_data DBA HG
ASIQ_IDX_T456_I8_HG product DBA HG
ASIQ_IDX_T457_I4_HG department DBA HG
ASIQ_IDX_T457_C3_HG department DBA HG
ASIQ_IDX_T458_I21_HG employee DBA HG
ASIQ_IDX_T458_C5_HG employee DBA HG
ASIQ_IDX_T459_I7_HG alt_sales_order DBA HG
ASIQ_IDX_T460_I6_HG alt_sales_order_items
DBA HG
Reports IQ tables that were not referenced by the workload.
sp_iqunusedtable
DBA authority required. Users without DBA authority must be granted EXECUTE permission in order to run the stored procedure.
Tables created in SYSTEM and local temporary tables are not reported.
Column name |
Description |
|---|---|
TableName |
Table name |
Owner |
Username of table owner |
The following table illustrates sample output from the sp_iqunusedtable procedure.
TableName Owner
fin_code DBA
contact DBA
employee DBA
emp1 DBA
alt_sales_order_items DBA
sales_order DBA
fin_data DBA
department DBA
alt_sales_order DBA
sales_order_items DBA
product DBA
iq_dummy DBA
customer DBA
sale DBA
Block type reporting is enhanced to report temporary storage used by the workload monitor. Additional block types are defined in the following table.
Identifier |
Block Type |
|---|---|
T |
Table use |
U |
Index use |
N |
Column use |
