Put your tips and tricks online - Share your knowledge! Login | Register
 
 
  Search     Advanced search
 

Home | Ask Question | Add tip | Questions | My tips | Recent tips & tricks | Suggest a category | FAQ | Forums

 
 
 
 Found 22 tips, Displaying 1 - 10 
 
Sort by 
 
 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
 
 
 Sessions, transactions and rollback segments query
select sess.sid, c.segment_name, b.used_ublk, b.log_io, b.phy_io, case when bitand(b.flag,power(2,7)) > then 'RB in Progress' else 'Not Rolling Back' ...
 More...
08/15/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
 
 
 Which sessions are using temporary tablespace, how much space is being used?
/* The query below will display which sessions are using TEMP tablespace and how much space is being used by each session: */ SELECT b.TABLESPACE , b.segfile# , b.segblk# , ROUND ( ...
     
More...

01/05/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 >>

Recent tips & tricks
Mobile Phones / Tablets > Android : smb not working with Samsung G...
Databases > Oracle > Performance Tuning : When and what's been analyzed
Databases > Oracle > Performance Tuning : Sessions, transactions and rol...
Databases > Oracle > ASM : ASM empty directory doesn't ge...

More categories
Databases | Programming | Hardware | Operating Systems | Networking | Internet | ERP / CRM | Games & Multimedia | Graphics & Design | Miscellaneous | Mobile Phones / Tablets | Office Software
 

Home |  FAQ |  Terms of Use |  Privacy Policy

© 2005 tiplib.com