Monday, July 28, 2008

AWR top sql quarries

FOR EXECUTION : EXECUTIONS_DELTA
FOR CPU TIME: CPU_TIME_DELTA
FOR DISK READ: DISK_READS_DELTA
FOR BUFFER GETS: BUFFER_GETS_DELTA


SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHS.SQL_ID SQL_ID,SUM(DHS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHS
WHERE DHS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('07/20/2008','MM/DD/YYYY')
AND END_INTERVAL_TIME<=TO_DATE('07/27/2008','MM/DD/YYYY'))
GROUP BY DHS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY X.CPU_TIME DESC;

Sunday, July 27, 2008

user: SYS vs SYSTEM

SYS

When any database is created, the user SYS is automatically created and granted the DBA role.

All of the base tables and views for the database's data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by Oracle. They should never be modified by any user or database administrator, and no one should create any tables in the schema of user SYS. (However, you can change the storage parameters of the data dictionary settings if necessary.)

Ensure that most database users are never able to connect using the SYS account.

SYSTEM

When a database is created, the user SYSTEM is also automatically created and granted the DBA role.

The SYSTEM username is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle options and tools. Never create in the SYSTEM schema tables of interest to individual users.

SYS has the SYSDBA privilege, SYSTEM doesn't.