Oracle Archives » Page 9 of 15 » Musings of an IT Implementor

Use Oracle 10g Segment Advisor Usage

Generally, the Oracle 10g Segment Advisor collection job runs automatically out-of-the-box in an Oracle 10g install.
It’s useful to run the advisor sometimes on large indexes as it should be able to report whether the index is efficiently storing index records, or if it could be re-built.
Oracle Enterprise Manager is already capable of pulling Space Advisor information from Oracle database.

Here are some links to the Oracle docs:

Oracle Doc: 10g Advisors
Oracle Doc: 10g Segment Advisor

Oracle Doc: Manually Running the Segment Advisor to Reclaim Wasted Space

An excellent whitepaper on pro-actively managing space in the Oracle 10g database.

The collector job (Automatic Segment Advisor) analyses segments in the database and compares to AWR reports to produce recommendations which can be reported on.
You can check the last run of the job using the SQL below:

— Check the DBA SEGMENT ADVISOR job is collecting data.
COL actual_start_date FORMAT A20
COL run_duration FORMAT A15
COL job_name FORMAT A30
SELECT TO_CHAR(actual_start_date,’DD-MM-YY HH24:MI:SS’) start_date,
run_duration,
job_name
FROM dba_scheduler_job_run_details
WHERE owner=’SYS’
AND job_name = ‘AUTO_SPACE_ADVISOR_JOB’
ORDER BY actual_start_date;

Once the collector is running, you can query the database using the DBMS_ADVISOR package to create analysis tasks that will query the recommendations and produce a recommendation report.

NOTE: The collector job does not analyse every object.

Below is the process I used to create a simple task to analyse a specific table and an index:

— Create some variables to hold our task details.
VARIABLE TASK_ID NUMBER;
VARIABLE TASK_NAME VARCHAR2(100);
VARIABLE OBJECT_ID NUMBER;

— Create a new empty task, which will populate the variables just defined.
EXEC DBMS_ADVISOR.CREATE_TASK(‘Segment Advisor’, :TASK_ID, :TASK_NAME);

— Assign a table and index check to the new task (replace <TABLE NAME> and <INDEX NAME>).
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, ‘TABLE’, ‘<SCHEMA>’, ‘<TABLE NAME>’, NULL, NULL, :OBJECT_ID);
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, ‘INDEX’, ‘<SCHEMA>’, ‘<INDEX NAME>’, NULL, NULL, :OBJECT_ID);

— Execute the task.
EXEC DBMS_ADVISOR.EXECUTE_TASK(:TASK_NAME);

NOTE: On a 40GB table this took approximately 10 minutes.

— Check the results in two ways:
— Query the tables DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_ACTIONS.

— or use DBMS_SPACE package (recommended).

SELECT
RECOMMENDATIONS RECOMMENDATION,
C1 ACTION1,
C2 ACTION2,
C3 ACTION3
FROM
TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS)
WHERE
TASK_ID = :TASK_ID;

— Delete the task at the end.
EXEC DBMS_ADVISOR.DELETE_TASK(:TASK_NAME);

ORA-01114 IO Error But What Is The Oracle File

Whilst creating a new 10GB index on a large 40GB table I was experiencing the
“ORA-01114: IO error writing block to file <file#> (block # <block#>)” error.

The main vital piece of information is the file number (<file#>). This can be found in the DBA_DATA_FILES or DBA_TEMP_FILES views as the FILE_ID column.

So, you construct a query to go find the file name:

SELECT file_id, file_name
FROM dba_data_files
WHERE file_id = <the number>
UNION ALL
SELECT file_id, file_name
FROM dba_temp_files
WHERE file_id = <the number>;

NOTE: I query both just in case. I’ve worked on systems where the TEMP tablespace has not been created as “TEMPORARY”!

After running the query, you get “0 rows returned”.
What!

Well, in this case, the TEMP tablespace was a proper TEMPORARY tablespace as the file_id was returned from the DBA_TEMP_FILES view.

I knew that the file numbers were somehow linked to the number of permitted files in the database. This is controlled by the DB_FILES database parameter.
Looking at the Oracle documentation (for 10g) here: https://docs.oracle.com/cd/B19306_01/server.102/b14231/dfiles.htm#i1206149

You will see that Oracle states that:
“…the DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance.”

In short this means that the DB_FILES parameter only applies to datafiles and not tempfiles.
Therefore, all other files will be outside this parameter range.

My parameter DB_FILES was set to 1024.
My reported file# in the ORA-01114 error was greater than 1024.
If you look at the file_id values for the DBA_TEMP_FILES view, you’ll notice that the file_id numbers conflict with the file_id values in DBA_DATA_FILES.
Since the tempfiles exist outside the range of DB_FILES, I subtracted the value of parameter “DB_FILES” from the reported file#, to achieve the file_id for tempfiles belonging to temporary tablespaces.
Voila!

Sure enough, when I subtracted 1024 (my DB_FILES value) from my reported file id in the ORA-01114, I got 2, which equated to the second tempfile in the TEMP tablespace.
The partition that the tempfile was sitting in was 95% full, but the tempfile couldn’t expand by the requested next extent size as it would be more than the partition free space. Increasing the partition size fixed my problem.

Is there another way of viewing the online file# vs the DBA_x_FILES file_id field? I don’t know, but if I find one, I’ll post it here.

What’s the Current Size of AWR in the Oracle Database?

If you have to size a new Oracle 10g database, or you just want to increase the AWR snapshot interval, you may want to know how much database space is being used by the current AWR setup.

This is where V$SYSAUX_OCCUPANTS comes in.
SQL> set linesize 300 SQL> col OCCUPANT_NAME format A7 SQL> SELECT OCCUPANT_NAME, OCCUPANT_DESC, SPACE_USAGE_KBYTES FROM v$sysaux_occupants where OCCUPANT_NAME ='SM/AWR';

OCCUPAN OCCUPANT_DESC SPACE_USAGE_KBYTES ------- ----------------------------------------------------- ------------------ SM/AWR Server Manageability - Automatic Workload Repository 107008

The above was taken from a system with a default AWR setup (retention of 7 days, snapping every hour).
This is approximately 14MB per day.

If you were thinking you were going to keep 60 days, you would expect about 900MB of data in SYSAUX.

Checking Segment High Water Mark (HWM) in Oracle 10g

The High Water Mark is equal to “Total Blocks” – “Unused Blocks”.

Substitute “<<<A USER>>>” and “<<<A TABLE>>>” with the real values.
Thanks to this post here.

set serveroutput on size 100000;
DECLARE
alc_bks NUMBER;
alc_bts NUMBER;
unsd_bks NUMBER;
unsd_bts NUMBER;
luefi NUMBER;
luebi NUMBER;
lub NUMBER;
BEGIN
FOR c1_row IN (SELECT table_name
FROM dba_tables where table_name = ‘<<<A TABLE>>>’) LOOP
DBMS_SPACE.UNUSED_SPACE (
segment_owner => ‘<<<A USER>>>’ ,
segment_name => c1_row.table_name ,
segment_type => ‘TABLE’ ,
total_blocks => alc_bks ,
total_bytes => alc_bts ,
unused_blocks => unsd_bks ,
unused_bytes => unsd_bts ,
last_used_extent_file_id => luefi ,
last_used_extent_block_id => luebi ,
last_used_block => lub);
DBMS_OUTPUT.PUT_LINE( ‘Data for ‘|| c1_row.table_name);
DBMS_OUTPUT.PUT_LINE( RPAD(‘*’,LENGTH(c1_row.table_name) + 10,’*’));
DBMS_OUTPUT.PUT_LINE( ‘Total Blocks……………..’|| alc_bks );
DBMS_OUTPUT.PUT_LINE( ‘Total Bytes………………’|| alc_bts );
DBMS_OUTPUT.PUT_LINE( ‘Unused Blocks…………….’|| unsd_bks );
DBMS_OUTPUT.PUT_LINE( ‘Unused Bytes……………..’|| unsd_bts );
DBMS_OUTPUT.PUT_LINE( ‘Last Used Ext File Id……..’|| luefi );
DBMS_OUTPUT.PUT_LINE( ‘Last Used Ext Block Id…….’|| luebi );
DBMS_OUTPUT.PUT_LINE( ‘Last Used Block…………..’|| lub );
DBMS_OUTPUT.NEW_LINE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/

“Fix” Control and ORA-00940: invalid ALTER command

Upon startup of an Oracle 10.2.0.4.0 database I received Oracle error:
ORA-00940: invalid ALTER command

I eventually managed to narrow this down to a “_fix_control” setting.

The database is for an SAP system and the init.ora was updated after following SAP note 830576 (Parameter recommendations for Oracle 10g).
I noticed that I had misread the note and applied a “_fix_control” parameter setting for a slightly (only by a patchset update) higher version of Oracle.

Take care when reading this note and make sure you test the startup and shutdown of the database before going home!

UPDATE: This is a bug. See here.