SHARE
TWEET

Untitled

a guest May 23rd, 2019 69 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #### Create RESOURCE PLAN testrp;
  2. ```
  3. create RESOURCE PLAN testrp;
  4. CREATE POOL testrp.default.c1 WITH ALLOC_FRACTION=0.3, QUERY_PARALLELISM=3, SCHEDULING_POLICY='fair';
  5. CREATE POOL testrp.default.c2 WITH ALLOC_FRACTION=0.5, QUERY_PARALLELISM=1, SCHEDULING_POLICY='fair';
  6. SELECT * FROM SYS.WM_POOLS;
  7. ALTER RESOURCE PLAN testrp VALIDATE;
  8. ALTER RESOURCE PLAN testrp ENABLE ACTIVATE;
  9. ```
  10.  
  11.  
  12. #### This will fail with error - Sum of children pools' alloc fraction should be less than 1 got: 1.0 for pool: default
  13. ```
  14. create RESOURCE PLAN testrp1;
  15. CREATE POOL testrp1.default.c1 WITH ALLOC_FRACTION=0.3, QUERY_PARALLELISM=3, SCHEDULING_POLICY='fair';
  16. CREATE POOL testrp1.default.c2 WITH ALLOC_FRACTION=0.7, QUERY_PARALLELISM=1, SCHEDULING_POLICY='fair';
  17. SELECT * FROM SYS.WM_POOLS;
  18. ALTER RESOURCE PLAN testrp1 VALIDATE;
  19. ALTER RESOURCE PLAN testrp1 ENABLE ACTIVATE;
  20. ```
  21.  
  22. #### This will SUCCEEDED
  23. ```
  24. create RESOURCE PLAN testrp2;
  25. CREATE POOL testrp2.default.c1 WITH ALLOC_FRACTION=0.3, QUERY_PARALLELISM=3, SCHEDULING_POLICY='fair';
  26. CREATE POOL testrp2.default.c2 WITH ALLOC_FRACTION=0.6, QUERY_PARALLELISM=2, SCHEDULING_POLICY='fair';
  27. SELECT * FROM SYS.WM_POOLS;
  28. ALTER RESOURCE PLAN testrp2 VALIDATE;
  29. ALTER RESOURCE PLAN testrp2 ENABLE ACTIVATE;
  30. ```
  31.  
  32. #### Disable resource plan
  33. ```
  34. ALTER RESOURCE PLAN testrp2 DISABLE;
  35. ```
  36. #### Disable workload management
  37. ```
  38. DISABLE WORKLOAD MANAGEMENT
  39. ```
  40. #### Show Resource Plan
  41. ```
  42. SHOW RESOURCE PLANS;
  43. SELECT * FROM SYS.WM_RESOURCEPLANS;
  44. ```
  45.  
  46. ### Show pools
  47. ```
  48. SELECT * FROM SYS.WM_POOLS;
  49. ```
  50.  
  51. ### Alter pools
  52. ```
  53. ALTER POOL plan_2.default.c2 SET SCHEDULING_POLICY='fair';
  54. ALTER POOL plan_2.default.c2 UNSET SCHEDULING_POLICY;
  55. ALTER POOL plan_2.default SET path = def;
  56. SELECT * FROM SYS.WM_POOLS;
  57.  
  58.  
  59. ALTER POOL testrp2.default SET  QUERY_PARALLELISM = 2;
  60. ALTER POOL testrp2.default.c1 SET  QUERY_PARALLELISM = 1;
  61. ALTER POOL testrp2.default.c2 SET  QUERY_PARALLELISM = 1;
  62.  
  63. DROP POOL plan_2.default;
  64. SELECT * FROM SYS.WM_POOLS;
  65.  
  66.  
  67. ```
  68.  
  69. #### Create Trigger
  70. ```CREATE TRIGGER testrp2.trigger_1 WHEN ELAPSED_TIME > 300 DO KILL```
  71.  
  72. #### ADD trigger to the pool
  73. ```ALTER POOL testrp2.default ADD TRIGGER trigger_1```
  74.  
  75. #### Drop Trigger
  76. ```DROP TRIGGER plan_1.trigger_1;```
  77.  
  78.  
  79. #### Show Triggers on pool
  80. ```SELECT * FROM SYS.WM_POOLS_TO_TRIGGERS;```
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top