Oracle 23c New Features for DBA'sBefore to 12c, we have a problem to revert back to a question, how much time does the statistics operation will run?
This is common problem, as we cannot anticipate the time and its depend on the various factors that will take place at time of statistics job execution.
To accomplish this we will have to use queries written here
Now, from 12c onwards there are reporting feasibility with statistics jobs , rather running actual you can run in reporting mode and see the details and revert back to the question above.
The following are sub programs for dbms_stats package where you can run the respective statistics collection in reporting mode.
For example, If I want to know the report for Schema OE statistics job, what all it will collect including histograms.
Scroll to right, you can see the full report.
variable myreport clob;
set long 1000000
begin
:myreport := dbms_stats.report_gather_schema_stats
(ownname =>'OE',
detail_level=>'ALL',
format=>'TEXT');
end;
/
SQL> SQL> print myreport MYREPORT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Operation Id | Operation | Target | Start Time | End Time | Status | Total Tasks | Successful | Failed Tasks | Active Tasks | Job Name | Session Id | Additional Info | | | | | | | | | Tasks | | | | | | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 711 | gather_schema_stats | OE | | | | 47 | | | | | | Parameters: [block_sample: | | | (reporting mode) | | | | | | | | | | | FALSE] [cascade: NULL] | | | | | | | | | | | | | | [concurrent: FALSE] [degree: | | | | | | | | | | | | | | ] [estimate_percent: | | | | | | | | | | | | | | DBMS_STATS.AUTO_SAMPLE_SIZE] | | | | | | | | | | | | | | [force: FALSE] | | | | | | | | | | | | | | [gather_fixed: FALSE] | | | | | | | | | | | | | | [gather_temp: FALSE] | | | | | | | | | | | | | | [granularity: AUTO] | | | | | | | | | | | | | | [method_opt: FOR ALL COLUMNS | | | | | | | | | | | | | | SIZE AUTO] [no_invalidate: | | | | | | | | | | | | | | NULL] [options: GATHER] | | | | | | | | | | | | | | [ownname: OE] | | | | | | | | | | | | | | [reporting_mode: TRUE] | | | | | | | | | | | | | | [statid: ] [statown: ] | | | | | | | | | | | | | | [stattab: ] [stattype: DATA] | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | T A S K S | | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | Target | Type | Start | End | Status | Rank | Job | Estimated | Batching | Histogram Columns | Extended | Additional | | | | | | Time | Time | | | Name | Cost | Info | | Stats | Info | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.ACTION_TABLE | TABLE | | | | 1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.SYS_C009916 | INDEX | | | | 1/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.CATEGORIES_TAB | TABLE | | | | 2 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.SYS_C009921 | INDEX | | | | 2/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.SYS_C009922 | INDEX | | | | 2/2 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.SYS_C009923 | INDEX | | | | 2/3 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.SYS_C009924 | INDEX | | | | 2/4 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.CUSTOMERS | TABLE | | | | 3 | | N/A | N/A | (3) SYS_NC00008$;SYS_NC00009$;ACCOUNT_MGR_ID; | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.CUST_ACCOUNT_MANAGER_IX | INDEX | | | | 3/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.CUST_LNAME_IX | INDEX | | | | 3/2 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.CUST_EMAIL_IX | INDEX | | | | 3/3 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.CUST_UPPER_NAME_IX | INDEX | | | | 3/4 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.CUSTOMERS_PK | INDEX | | | | 3/5 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.INVENTORIES | TABLE | | | | 4 | | N/A | N/A | (2) PRODUCT_ID;WAREHOUSE_ID; | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.INV_PRODUCT_IX | INDEX | | | | 4/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.INVENTORY_IX | INDEX | | | | 4/2 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.LINEITEM_TABLE | TABLE | | | | 5 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.SYS_C009917 | INDEX | | | | 5/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.ORDERS | TABLE | | | | 6 | | N/A | N/A | (3) ORDER_MODE;CUSTOMER_ID;SALES_REP_ID; | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.ORD_SALES_REP_IX | INDEX | | | | 6/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.ORD_CUSTOMER_IX | INDEX | | | | 6/2 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.ORD_ORDER_DATE_IX | INDEX | | | | 6/3 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.ORDER_PK | INDEX | | | | 6/4 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.ORDER_ITEMS | TABLE | | | | 7 | | N/A | N/A | (4) ORDER_ID;PRODUCT_ID;UNIT_PRICE;QUANTITY; | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.ITEM_ORDER_IX | INDEX | | | | 7/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.ITEM_PRODUCT_IX | INDEX | | | | 7/2 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.ORDER_ITEMS_PK | INDEX | | | | 7/3 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.ORDER_ITEMS_UK | INDEX | | | | 7/4 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.PRODUCT_DESCRIPTIONS | TABLE | | | | 8 | | N/A | N/A | (1) PRODUCT_ID; | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.PRD_DESC_PK | INDEX | | | | 8/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.PROD_NAME_IX | INDEX | | | | 8/2 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.PRODUCT_INFORMATION | TABLE | | | | 9 | | N/A | N/A | (4) | | | | | | | | | | | | | | | PRODUCT_NAME;CATEGORY_ID;LIST_PRICE;MIN_PRICE; | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.PROD_SUPPLIER_IX | INDEX | | | | 9/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.PRODUCT_INFORMATION_PK | INDEX | | | | 9/2 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.PRODUCT_REF_LIST_NESTEDTAB | TABLE | | | | 10 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.SYS_FK0000091701N00007$ | INDEX | | | | 10/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.PROMOTIONS | TABLE | | | | 11 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.PROMO_ID_PK | INDEX | | | | 11/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.PURCHASEORDER | TABLE | | | | 12 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.LINEITEM_TABLE_MEMBERS | INDEX | | | | 12/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.ACTION_TABLE_MEMBERS | INDEX | | | | 12/2 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.SYS_C009920 | INDEX | | | | 12/3 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.SUBCATEGORY_REF_LIST_NESTEDTAB | TABLE | | | | 13 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.SYS_FK0000091701N00009$ | INDEX | | | | 13/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.WAREHOUSES | TABLE | | | | 14 | | N/A | N/A | (1) LOCATION_ID; | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.WHS_LOCATION_IX | INDEX | | | | 14/1 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | OE.WAREHOUSES_PK | INDEX | | | | 14/2 | | N/A | N/A | | | | | | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | | | | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SQL> SQL>
Follow Me!!!