Oracle refresh statistics on owner/table   

Check is it needed update of statistics

If you want to check for a single table are statistic refreshed you can also use SQL developer:

• Go to connections( the left window)
• Find your owner
• List tables and find your one
• Open statistics

If you see information like below your statistics were analyzed:

Needs to be analyzed:

You can use also SQL Tunning Advisor (a tool from SQL Developer) in order to check is it needed a refresh of statistics.

Oracle refresh statistics of single table

EXECUTE dbms_stats.gather_table_stats(ownname => 'MYOWNER', tabname => 'MYTABLE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Oracle refresh statistics of owner all objects - PL/SQL

This PL/SQL script will refresh statistics of all tables for several owners.
In order to run it with SQL Developer:

• Replace 'MYOWNER','OWNER1' with your owner.
• Run it (including the "/" or you need to save it as .sql file)
• the output should be:
• anonymous block completed

BEGIN
    FOR obj IN (SELECT * 
                FROM all_users
                WHERE username in ('MYOWNER','OWNER1'))
    LOOP
        dbms_stats.gather_schema_stats(obj.username);
    END LOOP;
END;
/

Oracle refresh statistics of all owners all objects - PL/SQL

Refresh Oracle statistics for all owners and all objects except the system ones.

BEGIN
    FOR obj IN (SELECT * 
                FROM all_users
                WHERE username NOT IN ('SYS','SYSDBA'))
    LOOP
        dbms_stats.gather_schema_stats(obj.username);
    END LOOP;
END;
/

Oracle refresh statistics of all owners only tables - PL/SQL

Refresh Oracle statistics for all owners only tables except the system ones.

BEGIN
    FOR obj IN (SELECT * 
                FROM all_tables
                WHERE owner NOT IN ('SYS','SYSDBA'))
    LOOP
        dbms_stats.gather_table_stats(obj.owner, obj.table_name);
    END LOOP;
END;
/

Oracle refresh statistics of a table using PL/SQL

Refresh statistics for a single table with PL/SQL.

begin
    DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'MYOWNER',
       TABNAME=>'MYTABLE',  
       ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,
       METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 1',
       DEGREE=>4);
end;
/

Oracle auto-refresh statistics of a table using PL/SQL

You can leave the decision of the statistics refresh for the Oracle. In order to do it you can run this PL/SQL script.

begin
    DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'MY OWNER',
       OPTIONS => 'GATHER AUTO',  
       ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,
       DEGREE=>4);
end;
/

How to run PL/SQL script

In order to run queries with PL/SQL (query block with begin/end, loops and other stuff) you need to add "/" at the end of your code or save it as .sql file:

• SQL plus (or SQL Developer without .sql file)

begin
  dbms_output.put_line('Hello World');
end;
/

• SQL Developer - open the file

• Create .sql file and save you code there

• Open the .sql file with SQL Developer

• Run it

• SQL Developer - without opening the file(in case of bigger files)

• Create .sql file and save the below in it

• Open new SQL worksheet

• place your file path : @C:\user\plssqlcode.sql

• Run it