|
|
|
| | Found 22 tips, Displaying 1 - 10 | |
|
|
| | When and what's been analyzed
select a.TABLE_NAME, a.PARTITION_NAME, a.SUBPARTITION_NAME, a.NUM_ROWS, a.SAMPLE_SIZE, a.LAST_ANALYZED, a.STATTYPE_LOCKED, a.STALE_STATS, b.MONITORING, b.TEMPORARY, c.default_directory_name
from dba_tab_statistics a, dba_tables b, dba_external_tables c
where a.owner = '[SCHEMA]'
and (a.owner = b.OWNER and a.TABLE_NAME = b.TABLE_NAME)
and (c.OWNER (+)= a.OWNER and c.TABLE_NAME (+)= a.TABLE_NAME)
order by a.LAST_ANALYZED asc; | | More...
10/11/11 | | | | |
| | | | Find I/O intensive SQL statements
--
-- Determine the heaviest sql statements, sorted by disk reads and then by executions
--
SELECT executions, buffer_gets, disk_reads, first_load_time, sql_text
FROM v$sqlarea
ORDER BY disk_reads desc, executions desc; | | More...
01/25/11 | | | | |
| | | | View wait events for a session
select a.sid, a.event, a.total_waits, a.time_waited, a.average_wait, b.username, b.osuser, b.machine, b.program
from v$session_event a, v$session b
where time_waited > 0
and a.sid = b.sid
and a.sid = 283
order by time_waited; | | More...
01/13/09 | | | | |
| | | Get buffer cache hit ratio / miss ratio
SQL> SELECT name, 1-(physical_reads / (consistent_gets + db_block_gets )) "HIT_RATIO"
FROM V$BUFFER_POOL_STATISTICS
WHERE ( consistent_gets + db_block_gets ) !=0
/
NAME HIT_RATIO
-------------------- ----------
DEFAULT .817011081
-- ... | | More...
06/24/08 | | | | |
| | | View what sql user is running
-- View SQL text for user session
-- Replace DB_USER with the actuall database username and os_user with the OS username.
select a.sql_text, b.username, b.osuser, b.logon_time, b.program,
b.machine, b.sid, b.serial#
from v$sql a, v$session b
where a.address = b.sql_address
and b.username = 'DB_USER'
and b.osuser = 'os_user'; | | 
More...
11/01/07 | | | | |
| | | How to save (export) table stats
Before analyzing a table you may want to save the old stats (i.e. if the table is very large and analyzing takes a while just to have a backup of previous stats). Here's the syntax:
SQL> exec dbms_stats.export_table_stats('SCHEMA_OWNER', 'TABLE_NAME', NULL, 'STATS_TABLE_NAME', 'STATID', TRUE, 'SCHEMA_OF_STATS_TABLE'); | | 
More...
06/27/07 | | | | |
| | | Script to analyze table subpartitions
This script will analyze specified subpartitions in a loop, useful when some partitions / subpartitions in the table have been modified or re-built.
BEGIN
FOR rec IN
(SELECT subpartition_name
FROM all_tab_subpartitions
WHERE table_owner = 'SCHEMA_OWNER'
... | | More...
06/27/07 | | | | |
| | | How to ANALYZE partitioned tables with VALIDATE STRUCTURE
If you got the error:
ORA-14508: specified VALIDATE INTO table not found
This may be because you are trying to analyze a partitioned table and INVALID_ROWS table is not found in the current schema.
To validate structure (checks objects for logical corruption) for partitioned tables you first need to ensure that you’ve got ... | | 
More...
01/24/07 | | | | |
|
Page 1 | 2 | 3 >> |
|
