Carlos Sierra's Tools and Tips

Version v1711 of eDB360 invites eAdam 3.0 to the party. What does it mean? We recently learned that eDB360 v1706 introduced the eDB360 repository, which materialized the content of 26 core DBA_HIST views into a staging repository made of heap tables. This in order to expedite the execution of eDB360 on a database with an enlarged AWR. New version v1711 expands the list from 26 views to a total of 219. And these 219 views include now DBA_HIST, DBA, GV$ and V$.

Expanding existing eDB360 repository 8.4x from 26 to 219 views is not what is key on version v1710. The real impact of the latest version of eDB360 is that now it benefits of eAdam 3.0, providing the same benefits of the 219 views of the eDB360 heap-tables repository, but using External Tables, which are easily transported from a source database to a target database. This simple fact opens many doors.

Using the eAdam 3.0 repository from eDB360, we can now extract the metadata from a production database, then restore it on a staging database where we can produce the eDB360 report for the source database. We could also use this new external repository for: finer-granularity data mining; capacity planning; sizing for potential hardware refresh; provisioning tools; to estimate candidate segments for partitioning or for offloading into Hadoop; etc.

With the new external-tables eAdam 3.0 repository, we could easily build a permanent larger heap-table permanent repository for multiple databases (multiple tenants), or for multiple time versions of the same database. Thus, now that eDB360 has met eAdam 3.0, the combination of these two enables multiple innovative future features (custom or to be packaged and shipped with eDB360).

eDB360 recap

eDB360 is a free tool that gives a 360-degree view of an Oracle database. It installs nothing on the database, and it runs on 10g to 12c Oracle databases. It is designed for Linux and UNIX, but runs well on Windows as well (you may want to install first UNIX Utilities UnxUtils and a zip program, else a few OS commands may not properly work on Windows). This repository-less version is the default way to use eDB360, and is the right method for most cases. But now, in addition to the default use, eDB360 can also make use of one of two repositories.

eDB360 takes time to execute (several hours). It is designed to time-out after 24 hours by default. First reason for long execution times is the intentional serial-processing method used, with sequential execution of query after query, while consuming little resources. Such serial execution, plus the fact that it is common to have the tool execute on large databases where the state of AWR is suboptimal, causes the execution to take long.  We often discover that source AWR repositories lack expected partitioning, and in many cases they hold years of data instead of expected 8 to 31 days. Therefore, the nature of serial-execution combined with enlarged and suboptimal AWR repositories, usually cause eDB360 to execute for many hours more than expected. With 8 to 31 days of AWR data, and when such reasonable history is well partitioned, eDB360 usually executes in less than 6 hours.

To overcome the undesired extended execution times of eDB360, and besides the obvious (partition and purge AWR), the tool provides the capability to execute in multiple threads splitting its content by column. And now, in addition to the divide-and-conquer approach of column execution, eDB360 provides 2 repositories with different objectives in mind:

1. eDB360 repository: Use this method to create a staging repository based on heap tables inside the source database. This method helps to expedite the execution of eDB360 by at least 10x. Repository heap-tables are created and consumed inside the same database.
2. eAdam3 repository: Use this method to generate a repository based on external tables on the source database. Such external-tables repository can be moved easily to a remote target system, allowing to efficiently generate the eDB360 report there. This method helps to reduce computations in the source database, and enables potential data mining on the external repository without any resources impact on the source database. This method also helps to build other functions on top of the 219-tables repository.

Views included on both eDB360 and eAdam3 repositories:

1. dba_2pc_neighbors
2. dba_2pc_pending
3. dba_all_tables
4. dba_audit_mgmt_config_params
5. dba_autotask_client
6. dba_autotask_client_history
7. dba_cons_columns
8. dba_constraints
9. dba_data_files
10. dba_db_links
11. dba_extents
12. dba_external_tables
13. dba_feature_usage_statistics
14. dba_free_space
15. dba_high_water_mark_statistics
16. dba_hist_active_sess_history
17. dba_hist_database_instance
18. dba_hist_event_histogram
19. dba_hist_ic_client_stats
20. dba_hist_ic_device_stats
21. dba_hist_interconnect_pings
22. dba_hist_memory_resize_ops
23. dba_hist_memory_target_advice
24. dba_hist_osstat
25. dba_hist_parameter
26. dba_hist_pgastat
27. dba_hist_resource_limit
28. dba_hist_seg_stat
29. dba_hist_service_name
30. dba_hist_sga
31. dba_hist_sgastat
32. dba_hist_snapshot
33. dba_hist_sql_plan
34. dba_hist_sqlstat
35. dba_hist_sqltext
36. dba_hist_sys_time_model
37. dba_hist_sysmetric_history
38. dba_hist_sysmetric_summary
39. dba_hist_sysstat
40. dba_hist_system_event
41. dba_hist_tbspc_space_usage
42. dba_hist_wr_control
43. dba_ind_columns
44. dba_ind_partitions
45. dba_ind_statistics
46. dba_ind_subpartitions
47. dba_indexes
48. dba_jobs
49. dba_jobs_running
50. dba_lob_partitions
51. dba_lob_subpartitions
52. dba_lobs
53. dba_obj_audit_opts
54. dba_objects
55. dba_pdbs
56. dba_priv_audit_opts
57. dba_procedures
58. dba_profiles
59. dba_recyclebin
60. dba_registry
61. dba_registry_hierarchy
62. dba_registry_history
63. dba_registry_sqlpatch
64. dba_role_privs
65. dba_roles
66. dba_rsrc_consumer_group_privs
67. dba_rsrc_consumer_groups
68. dba_rsrc_group_mappings
69. dba_rsrc_io_calibrate
70. dba_rsrc_mapping_priority
71. dba_rsrc_plan_directives
72. dba_rsrc_plans
73. dba_scheduler_job_log
74. dba_scheduler_jobs
75. dba_scheduler_windows
76. dba_scheduler_wingroup_members
77. dba_segments
78. dba_sequences
79. dba_source
80. dba_sql_patches
81. dba_sql_plan_baselines
82. dba_sql_plan_dir_objects
83. dba_sql_plan_directives
84. dba_sql_profiles
85. dba_stat_extensions
86. dba_stmt_audit_opts
87. dba_synonyms
88. dba_sys_privs
89. dba_tab_cols
90. dba_tab_columns
91. dba_tab_modifications
92. dba_tab_partitions
93. dba_tab_privs
94. dba_tab_statistics
95. dba_tab_subpartitions
96. dba_tables
97. dba_tablespace_groups
98. dba_tablespaces
99. dba_temp_files
100. dba_triggers
101. dba_ts_quotas
102. dba_unused_col_tabs
103. dba_users
104. dba_views
105. gv$active_session_history
106. gv$archive_dest
107. gv$archived_log
108. gv$asm_disk_iostat
109. gv$database
110. gv$dataguard_status
111. gv$event_name
112. gv$eventmetric
113. gv$instance
114. gv$instance_recovery
115. gv$latch
116. gv$license
117. gv$managed_standby
118. gv$memory_current_resize_ops
119. gv$memory_dynamic_components
120. gv$memory_resize_ops
121. gv$memory_target_advice
122. gv$open_cursor
123. gv$osstat
124. gv$parameter
125. gv$pga_target_advice
126. gv$pgastat
127. gv$pq_slave
128. gv$pq_sysstat
129. gv$process
130. gv$process_memory
131. gv$px_buffer_advice
132. gv$px_process
133. gv$px_process_sysstat
134. gv$px_session
135. gv$px_sesstat
136. gv$resource_limit
137. gv$result_cache_memory
138. gv$result_cache_statistics
139. gv$rsrc_cons_group_history
140. gv$rsrc_consumer_group
141. gv$rsrc_plan
142. gv$rsrc_plan_history
143. gv$rsrc_session_info
144. gv$rsrcmgrmetric
145. gv$rsrcmgrmetric_history
146. gv$segstat
147. gv$services
148. gv$session
149. gv$session_blockers
150. gv$session_wait
151. gv$sga
152. gv$sga_target_advice
153. gv$sgainfo
154. gv$sgastat
155. gv$sql
156. gv$sql_monitor
157. gv$sql_plan
158. gv$sql_shared_cursor
159. gv$sql_workarea_histogram
160. gv$sysmetric
161. gv$sysmetric_summary
162. gv$sysstat
163. gv$system_parameter2
164. gv$system_wait_class
165. gv$temp_extent_pool
166. gv$undostat
167. gv$waitclassmetric
168. gv$waitstat
169. v$archive_dest_status
170. v$archived_log
171. v$ash_info
172. v$asm_attribute
173. v$asm_client
174. v$asm_disk
175. v$asm_disk_stat
176. v$asm_diskgroup
177. v$asm_diskgroup_stat
178. v$asm_file
179. v$asm_template
180. v$backup
181. v$backup_set_details
182. v$block_change_tracking
183. v$cell_config
184. v$cell_state
185. v$controlfile
186. v$database
187. v$database_block_corruption
188. v$datafile
189. v$flashback_database_log
190. v$flashback_database_stat
191. v$instance
192. v$io_outlier
193. v$iostat_file
194. v$kernel_io_outlier
195. v$lgwrio_outlier
196. v$log
197. v$log_history
198. v$logfile
199. v$mystat
200. v$nonlogged_block
201. v$option
202. v$parallel_degree_limit_mth
203. v$parameter
204. v$pdbs
205. v$recovery_area_usage
206. v$recovery_file_dest
207. v$restore_point
208. v$rman_backup_job_details
209. v$rman_output
210. v$segstat
211. v$spparameter
212. v$standby_log
213. v$sys_time_model
214. v$sysaux_occupants
215. v$system_parameter2
216. v$tablespace
217. v$tempfile
218. v$thread
219. v$version

Instructions to use eDB360 and eAdam3 repositories

Both repositories are implemented under the edb360-master/repo subdirectory. Each has a readme file, which explains how to create the repository, consume it and drop it. The eAdam3 repository also includes instructions how to clone an external-table-based eAdam repository into a heap-table-based eDB360 repository.

Executing eDB360 on the eDB360 repository is faster than executing it on the eAdam3 repository, while avoiding new bug 25802477. This new Oracle database bug inflicts compressed external-tables like the ones used by the eAdam3 repository.

If you use eDB360 or eAdam3 repositories and have questions or concerns, do not hesitate to contact the author.