tuning_recommendations and QUERY_REPO

[Timbo]

Hi,
Just starting to get an understand on reviewing database performance.
Have switched on the query repoisitoy:-
SELECT SET_CONFIG_PARAMETER('QueryRepositoryEnabled', '1');

and run
SELECT ANALYZE_WORKLOAD('', true);

One of the recommendations is :-
tuning_description | run database designer on table public.equityindexoptionkeys

Don't I need to capture any queries running against table public.equityindexoptionkeys to use in the database designer tool or can it be run just against a table?

Will the following query identify the sql using the table:-
select query from SYS_DBA.QUERY_REPO where query like '%equityindexoptionkeys%';

This currently returns no rows.

Regards
Tim

[JimKnicely]

Hi,

Maybe you can run DD against the query "SELECT * FROM public.equityindexoptionkeys;"

FYI, the only table I could find that contains the complete text of a query is the dc_requests_issued table which contains data from the Data Collector.

So if you've enabled DC, you could try this to find queries against your table:

Code: Select all

dbadmin=> select request from dc_requests_issued where request like '%equityindexoptionkeys%';
                                       request
--------------------------------------------------------------------------------------
 select request from dc_requests_issued where request like '%equityindexoptionkeys%';
(1 row)

Warning from the Admin Guide:

Caution! Data Collector tables (prefixed by dc_) reside in the V_INTERNAL schema and are provided for informational purposes only. They are provided as-is and are subject to change, or be removed, without notice. If you use these tables in scripts or monitoring tools, you may need to change your scripts and tools after a Vertica upgrade. Vertica recommends that you use the Enterprise Edition Workload Analyzer (page 360) instead of accessing the Data Collector tables directly.

[scutter]

Whether you query QUERY_REPO or dc_requests_issued, you should use ilike instead of like so that you catch any lowercase/uppercase combo of the table name.

Commenting on the original post - if DBD hasn't been run at all, then start with a comprehensive design with a full set of key queries. If you have a good comprehensive design and want to further optimize a single query then run a query-specific design.

[JimKnicely]

Scutter - "I like" your recommendation of using ilike instead like! Good catch!

viewtopic.php?f=48&t=104&p=148&hilit=ilike#p148