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
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