Vertica Tableau Report

Hi ,
In most of the cases , this can be improved by defining the right projections for the right queries !

You need to define projections for the SQLs generated by Tableau .

You can query  the query_requests systen table in order  to collect the relevent queries .

You can used DBD with query specific mode  see http://h71056.www7.hp.com/bdc/documents/training/Database-Designer.pdf  , the idea is to   provide to it a list of SQLs in a text file , DBD will analyze the file and based on that he will recommend to you the right projection setup to address your query use case .

I hope you will find it useful

Thanks 

Hi Eli Revach,

Actually I have changed  the memory size as following but despite of changing it as following I still see the performance issue only on some of the reports but not all.

 Changing MEMORY SIZE on the following pool :

1) Etlpool 60% 60%
2) Bipool 30 G 60G
3) Adhocpool 40% 40%
 

By the way is there any online url which provide steps with command in order to follow your foot steps. Kindly please advise ? Thought to share this too.

There is no resource rejections:

dbadmin=>
dbadmin=> select * from resource_rejections where last_rejected_timestamp >= (sysdate -1) limit 15;
node_name | pool_id | pool_name | reason | resource_type | rejection_count | first_rejected_timestamp | last_rejected_timestamp | last_rejected_value
-----------+---------+-----------+--------+---------------+-----------------+--------------------------+-------------------------+---------------------
(0 rows)

dbadmin=>

There are no queue.

dbadmin=>
dbadmin=> select * from resource_queues;
node_name | transaction_id | statement_id | pool_name | memory_requested_kb | priority | position_in_queue | queue_entry_timestamp
-----------+----------------+--------------+-----------+---------------------+----------+-------------------+-----------------------
(0 rows)


While view the report it uses the xyzbi usrs.

dbadmin=>
dbadmin=> select * from users where user_name='xyzbi';
user_id | user_name | is_super_user | profile_name | is_locked | lock_time | resource_pool | memory_cap_kb | temp_space_cap_kb | run_time_cap | all_roles | default_roles | search_path
-------------------+-----------+---------------+--------------+-----------+-----------+---------------+---------------+-------------------+--------------+-----------+---------------+-------------------------------------------------
45035997405778228 | xyzbi | f | default | f | | bipool | unlimited | unlimited | unlimited | | | xyz, public, v_catalog, v_monitor, v_internal
(1 row)

dbadmin=>

Thanks

Ujjwal

Hi
My recommendations above focus on starting with creating the right projections to the relevant queries . Do you validate that you have the right projections ?

No I haven't Check Database Designer Projections

However I have come up with top 10 slow query. Attached is the email here with where you can see REQUEST_DURATION_MS

Hi Emoreno,

Thanks for the url link. I went through pdf file and inside pdf file is see following content at page 36

The following query returns the 10 slowest queries from the system:

=> SELECT request FROM v_monitor.query_requests
WHERE request_type = 'QUERY' ORDER BY request_duration_ms
DESC LIMIT 10;
The following query returns the 10 most frequently

The output of this file is attached here with in excel file. However if you look at the excel file then most of the slow comand are from INSERT statement but i wanted to see  10 slowest queries from the system having only SELECT statement. Can you help me please ? What is the sql statement to check 10 slowest queries from the system having only SELECT statement. However i tried following statement but that did not work. select statment is there too but could not display the exact output which i am looking for.

SELECT request, request_duration_ms FROM v_monitor.query_requests
WHERE request_type = 'QUERY' and request ilike '%select%' ORDER BY request_duration_ms
DESC LIMIT 10;

Please help

did you tried by removing the % before the select ? that will bring you only the transaction that start with Select. 

Hope that helps. 

Eugenia