performance
Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
| performance [2018/12/06 21:05] – created 91.177.234.129 | performance [2026/01/09 15:47] (current) – external edit 127.0.0.1 | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Performance | + | * [[https:// |
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[http:// | ||
| + | * [[https:// | ||
| + | * [[http:// | ||
| * [[https:// | * [[https:// | ||
| + | * [[https:// | ||
| * [[https:// | * [[https:// | ||
| * [[http:// | * [[http:// | ||
| * [[http:// | * [[http:// | ||
| * [[http:// | * [[http:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[http:// | ||
| + | * [[https:// | ||
| + | * [[http:// | ||
| + | * [[https:// | ||
| + | * [[http:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[http:// | ||
| + | * [[http:// | ||
| + | * [[http:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| * [[http:// | * [[http:// | ||
| * [[Tuning]] | * [[Tuning]] | ||
| * [[http:// | * [[http:// | ||
| - | =====Tanel Poder' | + | Scripts |
| - | * [[http:// | + | * [[https:// |
| - | =====Show long-running SQL queries / statements===== | + | * [[https:// |
| + | * [[Snippets]] | ||
| + | * [[Handy Scripts]] | ||
| + | * [[Tuning]] | ||
| + | * [[http:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | |||
| + | |||
| + | ==== Why was my query fast yesterday and super slow today? ==== | ||
| + | * [[https:// | ||
| + | |||
| + | Maybe the stats are stale, maybe something happened to global temporary tables, maybe something else happened but now the query that was super fast yesterday is now running like a pig. Could be that the execution plan has changed. | ||
| + | I'm not going to rehash all that was said by the AntiKyte so I'll just put the main points here in case his article is removed. | ||
| + | < | ||
| + | sho parameter awr | ||
| + | |||
| + | NAME TYPE VALUE | ||
| + | --------------------------- ------- ----- | ||
| + | awr_pdb_autoflush_enabled | ||
| + | awr_pdb_max_parallel_slaves integer 10 | ||
| + | awr_snapshot_time_offset | ||
| + | </ | ||
| + | Allow autoflush | ||
| + | < | ||
| + | alter system set awr_pdb_autoflush_enabled = true; | ||
| + | </ | ||
| + | |||
| + | Adjust the snapshot interval | ||
| + | < | ||
| + | select * from cdb_hist_wr_control; | ||
| + | </ | ||
| + | < | ||
| + | begin | ||
| + | dbms_workload_repository.modify_snapshot_settings( | ||
| + | interval => 10, | ||
| + | topnsql => ' | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | Make a big table | ||
| + | < | ||
| + | create table chunky as select * from dba_objects; | ||
| + | |||
| + | begin | ||
| + | for i in 1..100 loop | ||
| + | insert into chunky | ||
| + | select * from dba_objects; | ||
| + | -- commit after each iteration as we're a bit tight | ||
| + | -- on resources | ||
| + | commit; | ||
| + | end loop; | ||
| + | end; | ||
| + | / | ||
| + | |||
| + | create index chunky_owner on chunky(owner); | ||
| + | |||
| + | exec dbms_stats.gather_table_stats(user, | ||
| + | |||
| + | </ | ||
| + | |||
| + | Start a new snapshot | ||
| + | < | ||
| + | exec dbms_workload_repository.create_snapshot; | ||
| + | </ | ||
| + | Find the sql_id from the the v$sql table | ||
| + | < | ||
| + | select sql_id, sql_text | ||
| + | from v$sql | ||
| + | where sql_text like ' | ||
| + | and sql_text not like ' | ||
| + | / | ||
| + | </ | ||
| + | Create another snapshot and ennsure a different plan is generated by hiding the index | ||
| + | < | ||
| + | exec dbms_workload_repository.create_snapshot; | ||
| + | alter index chunky_owner invisible; | ||
| + | </ | ||
| + | Rerun the query. They should appear in different snapshots. | ||
| + | |||
| + | Check the awr history tables to see if we can see more than one plan | ||
| + | < | ||
| + | select | ||
| + | snap.snap_id, | ||
| + | snap.instance_number, | ||
| + | begin_interval_time, | ||
| + | sql_id, | ||
| + | plan_hash_value, | ||
| + | nvl(executions_delta, | ||
| + | (elapsed_time_delta/ | ||
| + | (buffer_gets_delta/ | ||
| + | from dba_hist_sqlstat stat, dba_hist_snapshot snap | ||
| + | where sql_id = '& | ||
| + | and snap.snap_id = stat.snap_id | ||
| + | and snap.instance_number = stat.instance_number | ||
| + | and executions_delta > 0 | ||
| + | order by 3 | ||
| + | / | ||
| + | </ | ||
| + | List the plans | ||
| + | < | ||
| + | select * | ||
| + | from dba_hist_sql_plan | ||
| + | where sql_id = '& | ||
| + | / | ||
| + | </ | ||
| + | or | ||
| + | < | ||
| + | select * | ||
| + | from table ( dbms_xplan.display_workload_repository ( sql_id | ||
| + | , plan_hash_value => & | ||
| + | ) | ||
| + | ); | ||
| + | </ | ||
| + | |||
| + | ==== Top Program-Module-Action ==== | ||
| + | From [[https:// | ||
| + | < | ||
| + | set lines 200 pages 100 | ||
| + | col program for a42 | ||
| + | col module | ||
| + | col action | ||
| + | select program | ||
| + | , module | ||
| + | , action | ||
| + | , count(*) cnt | ||
| + | , 100*trunc(ratio_to_report(count(*)) over (),4) " | ||
| + | from | ||
| + | where 1=1 | ||
| + | and sample_time > sysdate-1/ | ||
| + | group by program | ||
| + | , module | ||
| + | , action | ||
| + | order by count(*) desc | ||
| + | fetch first 50 rows only | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== Top user activity ==== | ||
| + | < | ||
| + | set lines 200 pages 100 | ||
| + | col username for a20 | ||
| + | select du.username | ||
| + | , count(*) cnt | ||
| + | , 100*trunc(ratio_to_report(count(*)) over (),4) " | ||
| + | from | ||
| + | join | ||
| + | where 1=1 | ||
| + | and vash.sample_time > sysdate-1/ | ||
| + | group by du.username | ||
| + | order by count(*) desc | ||
| + | fetch first 20 rows only | ||
| + | / | ||
| + | </ | ||
| + | ==== Top Events ==== | ||
| + | set lines 200 pages 100 | ||
| + | col wait_class for a20 | ||
| + | < | ||
| + | select nvl(event,' | ||
| + | , nvl(wait_class,' | ||
| + | , count(*) | ||
| + | , 100*trunc(ratio_to_report(count(*)) over (),4) " | ||
| + | from | ||
| + | where 1=1 | ||
| + | and sample_time > sysdate-1/ | ||
| + | group by event | ||
| + | , wait_class | ||
| + | order by count(*) desc | ||
| + | fetch first 20 rows only | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | === Top SQL ==== | ||
| + | set lines 200 pages 100 | ||
| + | col sql_opname for a20 | ||
| + | < | ||
| + | select vash.sql_id | ||
| + | , vash.sql_opname | ||
| + | , count(*) cnt | ||
| + | , 100*trunc(ratio_to_report(count(*)) over (),4) " | ||
| + | , vs.sql_text | ||
| + | from | ||
| + | left join v$sqlarea vs on vash.sql_id = vs.sql_id | ||
| + | where 1=1 | ||
| + | and sample_time > sysdate-10/ | ||
| + | and vash.sql_id is not null | ||
| + | group by vash.sql_id | ||
| + | , vash.sql_opname | ||
| + | , vs.sql_text | ||
| + | order by count(*) desc | ||
| + | fetch first 20 rows only | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== Top accessed objects ==== | ||
| + | set lines 200 pages 100 | ||
| + | col owner for a32 | ||
| + | col object_name for a32 | ||
| + | < | ||
| + | select vash.current_obj# | ||
| + | , do.owner | ||
| + | , do.object_name | ||
| + | , do.object_type | ||
| + | , count(*) cnt | ||
| + | , 100*trunc(ratio_to_report(count(*)) over (),4) " | ||
| + | from | ||
| + | left join dba_objects do on vash.current_obj# | ||
| + | where sample_time > sysdate-1/ | ||
| + | and current_obj# | ||
| + | group by vash.current_obj# | ||
| + | , do.owner | ||
| + | , do.object_name | ||
| + | , do.object_type | ||
| + | order by count(*) desc | ||
| + | fetch first 30 rows only | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== How much hard parsing ==== | ||
| + | set lines 200 pages 100 | ||
| + | col in_parse for a9 | ||
| + | col in_hard_parse for a14 | ||
| + | < | ||
| + | select in_parse | ||
| + | , in_hard_parse | ||
| + | , count(*) cnt | ||
| + | , 100*trunc(ratio_to_report(count(*)) over (),4) " | ||
| + | from | ||
| + | where 1=1 | ||
| + | and sample_time > sysdate-30/ | ||
| + | group by in_parse | ||
| + | , in_hard_parse | ||
| + | order by count(*) desc | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== Everything the database did in the past n minutes ==== | ||
| + | < | ||
| + | select vash.sample_time | ||
| + | , vash.sql_id | ||
| + | , vash.top_level_sql_id | ||
| + | , nvl(vash.event,' | ||
| + | , in_parse | ||
| + | , in_hard_parse | ||
| + | , vash.force_matching_signature | ||
| + | , t.exact_matching_signature | ||
| + | , t.sql_text | ||
| + | from | ||
| + | left join v$sqlarea t on vash.sql_id=t.sql_id | ||
| + | where 1=1 | ||
| + | and vash.top_level_sql_id is not null | ||
| + | --and vash.top_level_sql_id=' | ||
| + | and sample_time > sysdate-(& | ||
| + | order by sample_time desc | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== Create a snapshot/ | ||
| + | Ref [[https:// | ||
| + | * No connections posible to the database except for sysdba. | ||
| + | * Nothing suspicious in the alertlog. | ||
| + | * Filesystems not full. | ||
| + | Before restarting, create hang analysis and systemstate trace files with the current state so that Oracle Support can have a look and see what is wrong. | ||
| + | Use -prelim flag if connections using sysdba are not even possible | ||
| + | * Collect Hang Analysis 1 | ||
| + | < | ||
| + | sqlplus [-prelim] / as sysdba | ||
| + | oradebug setmypid | ||
| + | oradebug unlimit | ||
| + | oradebug tracefile_name | ||
| + | oradebug hanganalyze 3 | ||
| + | </ | ||
| + | Wait 1 minute.... | ||
| + | * Collect Hang Analysis 2 | ||
| + | < | ||
| + | sqlplus / as sysdba | ||
| + | oradebug setmypid | ||
| + | oradebug unlimit | ||
| + | oradebug tracefile_name | ||
| + | oradebug hanganalyze 3 | ||
| + | </ | ||
| + | * Collect Systemstate dump | ||
| + | < | ||
| + | sqlplus / as sysdba | ||
| + | oradebug setmypid | ||
| + | oradebug unlimit | ||
| + | oradebug dump systemstate 258 | ||
| + | or | ||
| + | oradebug dump systemstate 266 | ||
| + | oradebug tracefile_name | ||
| + | </ | ||
| + | |||
| + | * Generate and Check ADDM report, implement findings, re-test | ||
| + | < | ||
| + | SQL> @?/ | ||
| + | </ | ||
| + | ==== Explain plan for a recently statement statement ==== | ||
| + | This example is from [[https:// | ||
| + | |||
| + | In order to collect execution statistics for the SQL plan, the hint gather_plan_statistics is used | ||
| + | < | ||
| + | select | ||
| + | --+ gather_plan_statistics | ||
| + | -- | ||
| + | | ||
| + | from | ||
| + | | ||
| + | | ||
| + | where | ||
| + | | ||
| + | | ||
| + | ; | ||
| + | </ | ||
| + | Find SQL_ID and child number of executed SQL statement | ||
| + | < | ||
| + | select | ||
| + | | ||
| + | | ||
| + | | ||
| + | from | ||
| + | | ||
| + | where | ||
| + | | ||
| + | | ||
| + | </ | ||
| + | With the sql_id and child number queried in the previous statement, we can now execute dbms_xplan.display_cursor | ||
| + | < | ||
| + | select | ||
| + | * | ||
| + | from | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | )); | ||
| + | </ | ||
| + | The data that is formatted by dbms_xplan.display_cursor is found in v$sql_plan_statistics_all, | ||
| + | |||
| + | ==== Trace all SQL in the current session, sending output to a tracefile (.trc) ==== | ||
| + | < | ||
| + | set lines 1000 pages 0 | ||
| + | col stmt for a500 | ||
| + | col sid new_value sid | ||
| + | col ser new_value ser | ||
| + | |||
| + | select sid sid | ||
| + | , serial# ser | ||
| + | from | ||
| + | where sid in (select distinct sid from sys.v_$mystat) | ||
| + | / | ||
| + | |||
| + | begin | ||
| + | dbms_monitor.session_trace_enable ( | ||
| + | session_id => &sid, | ||
| + | serial_num => &ser, | ||
| + | waits => true, | ||
| + | binds => true, | ||
| + | plan_stat | ||
| + | end; | ||
| + | / | ||
| + | |||
| + | |||
| + | select vr.value | ||
| + | -- | ||
| + | -- | ||
| + | -- | ||
| + | | ||
| + | | ||
| + | | ||
| + | from | ||
| + | , v$parameter vr | ||
| + | , v$process | ||
| + | , v$database | ||
| + | , v$instance | ||
| + | where vr.name = ' | ||
| + | and vs.sid | ||
| + | and vp.addr = vs.paddr | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== SQLTXPLAIN ==== | ||
| + | SQLTXPLAIN (SQLT) Tool that helps to diagnose SQL statements performing poorly [[https:// | ||
| + | ==== Trace an SQL query ==== | ||
| + | Useful additional information [[https:// | ||
| + | < | ||
| + | alter session set tracefile_identifier=' | ||
| + | alter session set timed_statistics = true; | ||
| + | alter session set statistics_level=all; | ||
| + | alter session set max_dump_file_size = unlimited; | ||
| + | alter session set events '10053 trace name context forever, level 1'; | ||
| + | |||
| + | explain plan for select count(' | ||
| + | |||
| + | alter session set events '10053 trace name context off'; | ||
| + | </ | ||
| + | |||
| + | < | ||
| + | alter session set tracefile_identifier=' | ||
| + | alter session set timed_statistics = true; | ||
| + | alter session set statistics_level=all; | ||
| + | alter session set max_dump_file_size = unlimited; | ||
| + | alter session set events '10046 trace name context forever, | ||
| + | |||
| + | rem execute the query for 30 minutes, then stop it | ||
| + | select count(' | ||
| + | |||
| + | select ' | ||
| + | alter session set events '10046 trace name context off'; | ||
| + | exit; | ||
| + | </ | ||
| + | Then find the trc files in the trace directory, probably in | ||
| + | < | ||
| + | ls -altr $ORACLE_BASE/ | ||
| + | </ | ||
| + | ==== set event 10046 ==== | ||
| + | < | ||
| + | alter system set events ' | ||
| + | / | ||
| + | </ | ||
| + | With this event set every time this sqlid is executed it will be traced, Supply TRUE for binds and waits means they will be shown in the tracefiles as well. To disable the event, run | ||
| + | < | ||
| + | alter system set events ' | ||
| + | / | ||
| + | </ | ||
| + | tkprof the tracefile | ||
| + | < | ||
| + | tkprof trace.trc / | ||
| + | </ | ||
| + | sys=y means that it will include all sql executed, also what is called recursive sql. Recursive sql is sql executed on behalf of the executed sql. | ||
| + | |||
| + | |||
| + | ==== How to Diagnose Slow TNS Listener / Connection Performance ==== | ||
| + | Ref: [[https:// | ||
| + | * [[Listener]] | ||
| + | |||
| + | ==== Tracing the Cost Based Optimiser (CBO) ==== | ||
| + | This is the Cost-based Optimizer trace. This trace really tells you ‘why did the CBO process this explain plan’…it goes into considerable detail on the hard parsing process | ||
| + | |||
| + | This is the syntax to start and stop this trace. The trace files are all created in the ‘BACKGROUND_DUMP_DEST’ location | ||
| + | < | ||
| + | alter session set events '10053 trace name context forever'; | ||
| + | </ | ||
| + | Run sql here | ||
| + | < | ||
| + | alter session set events '10053 trace name context off'; | ||
| + | </ | ||
| + | ==== Get the total amount of memory currently in use by databases on the server ==== | ||
| + | < | ||
| + | ./all_db_do " | ||
| + | </ | ||
| + | ==== Tanel Poder' | ||
| + | * [[http:// | ||
| + | ==== More from Tanel Poder ==== | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | |||
| + | ==== Set trace in another session using DBMS_SYSTEM ==== | ||
| + | First lets set trace in SCOTT' | ||
| + | < | ||
| + | exec dbms_system.set_bool_param_in_session(10, | ||
| + | exec dbms_system.set_int_param_in_session(10, | ||
| + | exec dbms_system.set_sql_trace_in_session(10, | ||
| + | exec dbms_system.set_sql_trace_in_session(10, | ||
| + | </ | ||
| + | A second way to set trace in another session - This time setting trace level as well\\ | ||
| + | Events and trace levels: | ||
| + | * Level 0 = No statistics generated | ||
| + | * Level 1 = standard trace output including parsing, executes and fetches plus more. | ||
| + | * Level 2 = Same as level 1. | ||
| + | * Level 4 = Same as level 1 but includes bind information | ||
| + | * Level 8 = Same as level 1 but includes wait's information | ||
| + | * Level 12 = Same as level 1 but includes binds and waits | ||
| + | Turn on tracing | ||
| + | < | ||
| + | exec dbms_system.set_ev(10, | ||
| + | </ | ||
| + | Turn off tracing | ||
| + | < | ||
| + | exec dbms_system.set_ev(10, | ||
| + | </ | ||
| + | Yet another way - with dbms_support package\\ | ||
| + | If not installed, do so with @? | ||
| + | < | ||
| + | exec dbms_support.start_trace_in_session(10, | ||
| + | exec dbms_support.stop_trace_in_session(10, | ||
| + | </ | ||
| + | |||
| + | === Shared pool loaded objects ==== | ||
| + | < | ||
| + | set lines 1000 pages 100 | ||
| + | col owner for a20 | ||
| + | col name for a30 | ||
| + | col type for a12 | ||
| + | select owner | ||
| + | , name | ||
| + | , type | ||
| + | , round(sharable_mem/ | ||
| + | , loads | ||
| + | , kept | ||
| + | , executions | ||
| + | , locks | ||
| + | , pins | ||
| + | from | ||
| + | where type in (' | ||
| + | order by sharable_mem desc | ||
| + | / | ||
| + | </ | ||
| + | ==== See the state of the SGA (which components are shrinking and growing) ==== | ||
| + | < | ||
| + | set lines 1000 pages 100 | ||
| + | col component | ||
| + | col oper_type | ||
| + | col oper_mode | ||
| + | col parameter | ||
| + | col status | ||
| + | col start_time for a18 | ||
| + | col end_time | ||
| + | |||
| + | select component | ||
| + | , oper_type | ||
| + | , oper_mode | ||
| + | , parameter | ||
| + | , initial_size/ | ||
| + | , target_size/ | ||
| + | , final_size/ | ||
| + | , status | ||
| + | , start_time | ||
| + | , end_time | ||
| + | from | ||
| + | / | ||
| + | set lines 80 | ||
| + | |||
| + | </ | ||
| + | On a system in trouble, errors will be seen when trying to grow a component... | ||
| + | < | ||
| + | COMPONENT | ||
| + | ------------------------------ ------------- --------- -------------------- ---------- ---------- ---------- --------- ------------------ ------------------ | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | DEFAULT buffer cache | ||
| + | DEFAULT buffer cache | ||
| + | DEFAULT buffer cache | ||
| + | DEFAULT buffer cache | ||
| + | DEFAULT buffer cache | ||
| + | DEFAULT buffer cache | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | shared pool GROW IMMEDIATE shared_pool_size | ||
| + | |||
| + | </ | ||
| + | On a freshly started system everything is good... | ||
| + | < | ||
| + | COMPONENT | ||
| + | ------------------------- ------------- --------- ------------------------- ---------- ---------- ---------- --------- ------------------ ------------------ | ||
| + | shared pool | ||
| + | In-Memory Area STATIC | ||
| + | large pool STATIC | ||
| + | RECYCLE buffer cache STATIC | ||
| + | KEEP buffer cache | ||
| + | java pool | ||
| + | streams pool STATIC | ||
| + | DEFAULT buffer cache STATIC | ||
| + | ASM Buffer Cache STATIC | ||
| + | DEFAULT buffer cache INITIALIZING | ||
| + | DEFAULT 2K buffer cache | ||
| + | DEFAULT 4K buffer cache | ||
| + | DEFAULT 8K buffer cache | ||
| + | DEFAULT 16K buffer cache STATIC | ||
| + | DEFAULT 32K buffer cache STATIC | ||
| + | DEFAULT buffer cache SHRINK | ||
| + | DEFAULT buffer cache GROW DEFERRED | ||
| + | large pool SHRINK | ||
| + | |||
| + | </ | ||
| + | |||
| + | ==== How to create an ADDM report quickly ==== | ||
| + | From [[http:// | ||
| + | < | ||
| + | set lines 1000 pages 100 | ||
| + | select a.execution_end, | ||
| + | ' | ||
| + | ' | ||
| + | ' | ||
| + | From dba_advisor_tasks a, dba_advisor_findings b, | ||
| + | | ||
| + | Where a.owner=b.owner and a.task_id=b.task_id | ||
| + | And b.task_id=d.task_id and b.finding_id=d.finding_id | ||
| + | And a.task_id=c.task_id and d.rec_id=c.rec_Id | ||
| + | And a.task_name like ' | ||
| + | and a.execution_end > sysdate - 1 | ||
| + | Order by a.execution_end desc, rank desc; | ||
| + | </ | ||
| + | ==== See how evenly the physical reads and writes are spread over the datafiles ==== | ||
| + | From [[http:// | ||
| + | The Physical design of the database assures optimal performance for DISK I/O. Storing the datafiles in different filesystems (Disks) is a good technique to minimize disk contention for I/O | ||
| + | < | ||
| + | set lines 1000 | ||
| + | col perc_writes for a12 | ||
| + | col perc_reads | ||
| + | col name for a55 | ||
| + | select name | ||
| + | , phyrds physical_reads | ||
| + | , round((ratio_to_report(phyrds) over ())*100, 2)|| ' | ||
| + | , phywrts physical_writes | ||
| + | , round((ratio_to_report(phywrts) over ())*100, 2)|| ' | ||
| + | , phyrds + phywrts total | ||
| + | from | ||
| + | , v$filestat fs | ||
| + | where df.file# = fs.file# | ||
| + | order by phyrds desc | ||
| + | / | ||
| + | </ | ||
| + | ==== How I/O is spread per filesystem ==== | ||
| + | < | ||
| + | select filesystem | ||
| + | , round((ratio_to_report(reads) over ())*100, 2) || ' | ||
| + | , round((ratio_to_report(writes) over ())*100, 2) || ' | ||
| + | , round((ratio_to_report(total) over ())*100, 2) || ' | ||
| + | from ( | ||
| + | | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | | ||
| + | select substr(name, | ||
| + | , phyrds | ||
| + | , round((ratio_to_report(phyrds) over ())*100, 2)|| ' | ||
| + | , phywrts | ||
| + | , round((ratio_to_report(phywrts) over ())*100, 2)|| ' | ||
| + | , phyrds + phywrts | ||
| + | from | ||
| + | , v$filestat fs | ||
| + | where df.file# = fs.file# | ||
| + | order by phyrds desc | ||
| + | ) a | ||
| + | | ||
| + | ) b | ||
| + | order by round((ratio_to_report(total) over ())*100, 2) desc; | ||
| + | </ | ||
| + | ==== How I/O is spread for the datafiles of a specific tablespace ==== | ||
| + | < | ||
| + | select df.name | ||
| + | , phyrds physical_reads | ||
| + | , round((ratio_to_report(phyrds) over ())*100, 2)|| ' | ||
| + | , phywrts physical_writes | ||
| + | , round((ratio_to_report(phywrts) over ())*100, 2)|| ' | ||
| + | , phyrds + phywrts total | ||
| + | from | ||
| + | , v$filestat fs | ||
| + | , ts$ t | ||
| + | where df.file# = fs.file# | ||
| + | and df.ts# = t.ts# | ||
| + | and t.name = ' | ||
| + | order by phyrds desc; | ||
| + | </ | ||
| + | ==== Spotting I/O intensive SQL statements ==== | ||
| + | From [[https:// | ||
| + | < | ||
| + | select * | ||
| + | from ( | ||
| + | | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | | ||
| + | | ||
| + | ) | ||
| + | where rownum < 11 | ||
| + | / | ||
| + | </ | ||
| + | ==== Enable and disable session tracing using PL/SQL package ==== | ||
| + | See also trace of a particular session_id in [[Datapump]] section. | ||
| + | < | ||
| + | exec dbms_monitor.session_trace_enable; | ||
| + | exec dbms_monitor.session_trace_disable; | ||
| + | |||
| + | exec dbms_monitor.database_trace_enable; | ||
| + | exec dbms_monitor.database_trace_disable; | ||
| + | |||
| + | exec dbms_monitor.client_id_trace_enable; | ||
| + | exec dbms_monitor.client_id_trace_disable; | ||
| + | |||
| + | exec dbms_monitor.serv_mod_act_trace_enable; | ||
| + | exec dbms_monitor.serv_mod_act_trace_disable; | ||
| + | |||
| + | </ | ||
| + | ==== Show database resource limits and current usage ==== | ||
| + | < | ||
| + | set lines 1000 pages 100 | ||
| + | select resource_name | ||
| + | , current_utilization | ||
| + | , max_utilization | ||
| + | , limit_value | ||
| + | from | ||
| + | / | ||
| + | </ | ||
| + | ==== Show long-running SQL queries / statements ==== | ||
| Long running means more than 10 minutes if last_call_et > 600 (adjust as necessary!) | Long running means more than 10 minutes if last_call_et > 600 (adjust as necessary!) | ||
| - | < | + | < |
| + | set pages 1000 headi on underline off lines 1000 | ||
| + | col username for a15 | ||
| + | col program | ||
| + | col osuser | ||
| + | select distinct machine | ||
| + | , nvl(s.osuser,' | ||
| + | , s.username | ||
| + | , s.sid sid | ||
| + | , s.serial# | ||
| + | , (s.last_call_et/ | ||
| + | , s.last_call_et | ||
| + | , s.program | ||
| + | , s.sql_id | ||
| + | , q.sql_text | ||
| + | from | ||
| + | , v$sql q | ||
| + | where s.sql_id | ||
| + | and status | ||
| + | and type != ' | ||
| + | and last_call_et > 600 | ||
| + | order by sid | ||
| + | , serial# | ||
| + | / | ||
| + | </ | ||
| - | =====Is stats gathering enabled?===== | + | ==== Enable block change tracking (BCT) ==== |
| - | < | + | < |
| + | alter database enable block change tracking; | ||
| + | </ | ||
| - | =====Check when statistics were last gathered===== | + | ==== Disable block change tracking (BCT) ==== |
| - | < | + | < |
| - | =====Gather more accurate statistics===== | + | alter database disable block change tracking; |
| + | </ | ||
| + | |||
| + | ==== Where is the block change tracking file? ==== | ||
| + | < | ||
| + | select filename from v$block_change_tracking | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== Check if block change tracking (BCT) is enabled ==== | ||
| + | < | ||
| + | select status from v$block_change_tracking | ||
| + | / | ||
| + | </ | ||
| + | or | ||
| + | < | ||
| + | ps -ef | grep [c]twr | ||
| + | </ | ||
| + | ==== Show CPU Usage for Active Sessions ==== | ||
| + | < | ||
| + | SET PAGESIZE 60 | ||
| + | SET LINESIZE 300 | ||
| + | |||
| + | COLUMN username FORMAT A30 | ||
| + | COLUMN sid FORMAT 999, | ||
| + | COLUMN serial# FORMAT 999, | ||
| + | COLUMN "cpu usage (seconds)" | ||
| + | |||
| + | SELECT | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | FROM | ||
| + | | ||
| + | | ||
| + | | ||
| + | WHERE | ||
| + | | ||
| + | AND | ||
| + | NAME like '%CPU used by this session%' | ||
| + | AND | ||
| + | t.SID = s.SID | ||
| + | AND | ||
| + | | ||
| + | AND | ||
| + | | ||
| + | GROUP BY username, | ||
| + | /</ | ||
| + | ==== Show the Bind Variables for a Given sqlid ==== | ||
| + | < | ||
| + | SET PAGESIZE 60 | ||
| + | SET LINESIZE 300 | ||
| + | |||
| + | COLUMN sql_text FORMAT A120 | ||
| + | COLUMN sql_id FORMAT A13 | ||
| + | COLUMN bind_name FORMAT A10 | ||
| + | COLUMN bind_value FORMAT A26 | ||
| + | |||
| + | SELECT | ||
| + | sql_id, | ||
| + | t.sql_text sql_text, | ||
| + | b.name bind_name, | ||
| + | b.value_string bind_value | ||
| + | FROM | ||
| + | v$sql t | ||
| + | JOIN | ||
| + | v$sql_bind_capture b using (sql_id) | ||
| + | WHERE | ||
| + | b.value_string is not null | ||
| + | AND | ||
| + | sql_id='& | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== List the Most Resource Hungry SQL Statements ==== | ||
| + | < | ||
| + | SET PAGESIZE 60 | ||
| + | SET LINESIZE 300 | ||
| + | |||
| + | COLUMN sql_text FORMAT A50 | ||
| + | COLUMN reads_per_execution FORMAT 999, | ||
| + | COLUMN buffer_gets FORMAT 999, | ||
| + | COLUMN disk_reads FORMAT 999, | ||
| + | COLUMN executions FORMAT 999, | ||
| + | COLUMN sorts FORMAT 999, | ||
| + | |||
| + | SELECT * | ||
| + | FROM | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | FROM | ||
| + | ORDER BY 2 DESC) | ||
| + | WHERE rownum <= & | ||
| + | / | ||
| + | </ | ||
| + | ==== Is stats gathering enabled? ==== | ||
| + | < | ||
| + | column client_name format A55 | ||
| + | SELECT client_name, | ||
| + | </ | ||
| + | |||
| + | ==== Check when the statistics advisor tasks ran ==== | ||
| + | This shows the details of various advisor jobs | ||
| + | < | ||
| + | col name for a50 | ||
| + | col owner_name for a10 | ||
| + | col name for a40 | ||
| + | select name | ||
| + | , ctime | ||
| + | , how_created | ||
| + | , owner_name | ||
| + | , name | ||
| + | from | ||
| + | where 1=1 | ||
| + | --and owner_name = ' | ||
| + | --and name in (' | ||
| + | order by 2 | ||
| + | |||
| + | </ | ||
| + | gives something like... | ||
| + | < | ||
| + | NAME CTIME | ||
| + | ----------------------------------- --------------------------- ------------------------------ ---------- ----------------------------------- | ||
| + | SYS_AUTO_SPCADV339041814082021 | ||
| + | SYS_AUTO_SPCADV216002216082021 | ||
| + | ADDM: | ||
| + | SYS_AUTO_SPCADV522010021082021 | ||
| + | ADDM: | ||
| + | ADDM: | ||
| + | ADDM: | ||
| + | ADDM: | ||
| + | ADDM: | ||
| + | ADDM: | ||
| + | ADDM: | ||
| + | ADDM: | ||
| + | SYS_AUTO_SPCADV151060126082021 | ||
| + | ADDM: | ||
| + | ADDM: | ||
| + | ... | ||
| + | ADDM: | ||
| + | SYS_AUTO_SPCADV951040912092021 | ||
| + | ADDM: | ||
| + | SYS_AUTO_SPCADV557041012092021 | ||
| + | ADDM: | ||
| + | SYS_AUTO_SPCADV155041112092021 | ||
| + | ADDM: | ||
| + | SYS_AUTO_SPCADV759041212092021 | ||
| + | ADDM: | ||
| + | SYS_AUTO_SPCADV302051312092021 | ||
| + | |||
| + | 958 rows selected. | ||
| + | </ | ||
| + | |||
| + | ==== Check when statistics were last gathered ==== | ||
| + | < | ||
| + | select owner, | ||
| + | </ | ||
| + | ==== Gather more accurate statistics ==== | ||
| default is 2 | default is 2 | ||
| - | < | + | < |
| - | =====Check for stale statistics===== | + | alter system set optimizer_dynamic_sampling = 4; |
| - | < | + | </ |
| + | ==== Check for stale statistics ==== | ||
| + | < | ||
| + | set lines 200 pages 100 | ||
| + | col table_owner for a15 | ||
| + | col table_name | ||
| + | select m.table_owner | ||
| + | , m.table_name | ||
| + | , m.timestamp | ||
| + | , sum(m.inserts) | ||
| + | , sum(m.updates) | ||
| + | , sum(m.deletes) | ||
| + | , t.num_rows | ||
| + | , t.last_analyzed | ||
| + | from | ||
| + | , dba_tables t | ||
| + | where m.table_owner = t.owner | ||
| + | and m.table_name | ||
| + | group by m.table_owner | ||
| + | , m.table_name | ||
| + | , m.timestamp | ||
| + | , t.num_rows | ||
| + | , t.last_analyzed | ||
| + | order by 1,2,3 | ||
| + | / | ||
| + | set lines 80 | ||
| + | |||
| + | </ | ||
| or | or | ||
| - | < | + | < |
| + | set lines 200 pages 200 | ||
| + | col owner_table | ||
| + | col partition_name for a20 | ||
| + | select dt.owner||' | ||
| + | , dtm.partition_name | ||
| + | , inserts+updates+deletes modified_rows, | ||
| + | , case when num_rows = 0 then null | ||
| + | else (inserts+updates+deletes) / num_rows * 100 | ||
| + | end percent_modified | ||
| + | from | ||
| + | join | ||
| + | on | ||
| + | order by 6 desc | ||
| + | / | ||
| + | </ | ||
| - | =====Gather | + | ==== Export dictionary and fixed statistics |
| + | < | ||
| + | begin | ||
| + | dbms_stats.create_stat_table ( stattab => ' | ||
| + | , ownname => ' | ||
| + | ); | ||
| + | dbms_stats.export_dictionary_stats ( stattab => ' | ||
| + | , statown => ' | ||
| + | ); | ||
| + | |||
| + | |||
| + | dbms_stats.create_stat_table ( stattab => ' | ||
| + | , ownname => ' | ||
| + | ); | ||
| + | dbms_stats.export_fixed_objects_stats ( stattab => ' | ||
| + | , statown => ' | ||
| + | ); | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | < | ||
| + | expdp userid=' | ||
| + | expdp userid=' | ||
| + | </ | ||
| + | |||
| + | ==== Delete existing fixed and dictionary stats and import new ones ==== | ||
| + | This can be done before importing stats from another | ||
| + | < | ||
| + | begin | ||
| + | dbms_stats.delete_dictionary_stats; | ||
| + | dbms_stats.delete_fixed_objects_stats; | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | < | ||
| + | impdp userid=' | ||
| + | impdp userid=' | ||
| + | </ | ||
| + | |||
| + | ==== Copy the statistics from a single query from one database to another ==== | ||
| + | * [[https:// | ||
| + | |||
| + | === Pack up the baseline === | ||
| + | < | ||
| + | set serveroutput on | ||
| + | prompt prepare a SQL Plan Baseline for transport | ||
| + | prompt | ||
| + | accept v_table_name | ||
| + | accept v_table_owner prompt 'enter the schema name where the staging table is to be created: ' | ||
| + | accept v_sql_handle | ||
| + | accept v_plan_name prompt 'enter the corresponding plan name: ' | ||
| + | |||
| + | declare | ||
| + | v_packed_baselines number; | ||
| + | begin | ||
| + | dbms_spm.create_stgtab_baseline( | ||
| + | table_name => '& | ||
| + | table_owner => '& | ||
| + | |||
| + | v_packed_baselines := dbms_spm.pack_stgtab_baseline( | ||
| + | table_name => '& | ||
| + | table_owner => '& | ||
| + | sql_handle => '& | ||
| + | plan_name => '& | ||
| + | dbms_output.put_line(v_packed_baselines || ' baselines have been staged in & | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | * Export the table, copy it over and import it into the destination database. | ||
| + | |||
| + | === Unpack the baseline === | ||
| + | < | ||
| + | var num_unpacked number | ||
| + | |||
| + | begin | ||
| + | : | ||
| + | table_name => '& | ||
| + | table_owner => '& | ||
| + | end; | ||
| + | / | ||
| + | |||
| + | print : | ||
| + | </ | ||
| + | |||
| + | * Check it is there | ||
| + | < | ||
| + | select sql_handle, plan_name, origin, enabled, accepted, fixed from dba_sql_plan_baselines; | ||
| + | </ | ||
| + | |||
| + | ==== Gather database stats ==== | ||
| Some helpful stuff on statistics gathering [[http:// | Some helpful stuff on statistics gathering [[http:// | ||
| - | < | + | < |
| - | =====Gather dictionary and fixed objects statistics===== | + | - !/ |
| - | How to Gather Statistics on Objects Owned by the ‘SYS’ User and ‘Fixed’ Objects ([[https:// | + | sqlplus / as sysdba << EOSQL |
| - | < | + | exec dbms_stats.gather_database_stats( estimate_percent=> |
| - | < | + | EOSQL |
| + | </ | ||
| + | |||
| + | ==== Setup a job to run gather statistics as a one-off job ==== | ||
| + | < | ||
| + | begin | ||
| + | dbms_scheduler.create_job ( job_name | ||
| + | , job_type | ||
| + | , job_action => 'begin dbms_stats.gather_fixed_object_stats; | ||
| + | , start_date => sysdate+7 | ||
| + | , auto_drop | ||
| + | , comments | ||
| + | ); | ||
| + | dbms_scheduler.enable ( name => '" | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== Gather dictionary and fixed objects statistics ==== | ||
| + | A common problem when certain queries take a long time - a particular culprit is dba_free_space.\\ | ||
| + | In this case also check the dba_recyclebin and purge it or disable it (at least on production) if not required.\\ | ||
| + | The problem stems from queries doing a full table scan on x$ktfbue (and executing gather_fixed_object_stats didn't help as this table is specifically excluded from the gather!). The full story can be read [[https:// | ||
| + | A rejigged query (using hints) was used in the past to speed up dba_free_space. This was provided by Oracle for 12.1.0.2 | ||
| + | < | ||
| + | create or replace view DBA_FREE_SPACE | ||
| + | (TABLESPACE_NAME, | ||
| + | BYTES, BLOCKS, RELATIVE_FNO) | ||
| + | as | ||
| + | select ts.name, fi.file#, f.block#, | ||
| + | f.length * ts.blocksize, | ||
| + | from sys.ts$ ts, sys.fet$ f, sys.file$ fi | ||
| + | where ts.ts# | ||
| + | and f.ts# = fi.ts# | ||
| + | and f.file# = fi.relfile# | ||
| + | and ts.bitmapped = 0 | ||
| + | union all | ||
| + | select /*+ ordered use_nl(f) use_nl(fi) */ | ||
| + | ts.name, fi.file#, f.ktfbfebno, | ||
| + | f.ktfbfeblks * ts.blocksize, | ||
| + | from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi | ||
| + | where ts.ts# = f.ktfbfetsn | ||
| + | and f.ktfbfetsn = fi.ts# | ||
| + | and f.ktfbfefno = fi.relfile# | ||
| + | and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0 | ||
| + | union all | ||
| + | select /*+ ordered use_nl(u) use_nl(fi) */ | ||
| + | ts.name, fi.file#, u.ktfbuebno, | ||
| + | u.ktfbueblks * ts.blocksize, | ||
| + | from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi | ||
| + | where ts.ts# = rb.ts# | ||
| + | and rb.ts# = fi.ts# | ||
| + | and u.ktfbuefno = fi.relfile# | ||
| + | and u.ktfbuesegtsn = rb.ts# | ||
| + | and u.ktfbuesegfno = rb.file# | ||
| + | and u.ktfbuesegbno = rb.block# | ||
| + | and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0 | ||
| + | union all | ||
| + | select ts.name, fi.file#, u.block#, | ||
| + | u.length * ts.blocksize, | ||
| + | from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb | ||
| + | where ts.ts# = u.ts# | ||
| + | and u.ts# = fi.ts# | ||
| + | and u.segfile# = fi.relfile# | ||
| + | and u.ts# = rb.ts# | ||
| + | and u.segfile# = rb.file# | ||
| + | and u.segblock# = rb.block# | ||
| + | and ts.bitmapped = 0 | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | Another table that struggles can be rman_backup_job_details. | ||
| + | |||
| + | Stats on the sys X$ tables under the V$ views are not automatically gathered. Furthermore, | ||
| + | Noticable performance issues on DBA_FREE_SPACE, | ||
| + | How to Gather Statistics on Objects Owned by the ‘SYS’ User and ‘Fixed’ Objects ([[https:// | ||
| + | Fixed Objects Statistics (GATHER_FIXED_OBJECTS_STATS) Considerations ([[https:// | ||
| + | Other relevant MOS notes relating to gathering fixed objest stats include: | ||
| + | |||
| + | NOTE: | ||
| + | NOTE: | ||
| + | BUG:7430745 - ORA-1422 DBMS_STATS.GATHER_TABLE_STATS ON X$KTFBUE ON 10.2.0.4\\ | ||
| + | NOTE: | ||
| + | BUG:5259025 - THE FIXED TABLE X$KTFBUE HAS NO STATISTICS\\ | ||
| + | BUG:5880432 - QUERYING V$ACCESS CONTENTS ON LATCH: LIBRARY CACHE DRAWBACKS PERFORMANCE\\ | ||
| + | NOTE: | ||
| + | NOTE: | ||
| + | BUG:5247609 - RMAN SLOW PERFORMANCE DURING REGISTER DATABASE/ | ||
| + | NOTE: | ||
| + | NOTE: | ||
| + | NOTE: | ||
| + | NOTE: | ||
| + | NOTE: | ||
| + | NOTE: | ||
| + | |||
| + | The current stats can be exported first in case the results of gathering are worse! | ||
| + | < | ||
| + | exec dbms_stats.drop_stat_table(' | ||
| + | exec dbms_stats.create_stat_table(' | ||
| + | exec dbms_stats.export_fixed_objects_stats ( statown => ' | ||
| + | |||
| + | expdp userid=system/ | ||
| + | </ | ||
| + | < | ||
| + | exec dbms_stats.gather_dictionary_stats; | ||
| + | </ | ||
| + | < | ||
| + | exec dbms_stats.gather_fixed_objects_stats; | ||
| + | </ | ||
| + | |||
| + | ==== Stats are not gathered on fixed table X$KTFBUE ==== | ||
| + | From a study by Jonathon Lewis... [[https:// | ||
| + | ... | ||
| + | Part of the problem, of course, is that x$ktfbue is one of the objects that Oracle skips when you gather “fixed object” stats – it can be a bit expensive for exactly the reason that querying it can be expensive, all those single block segment header reads. | ||
| + | ... | ||
| + | < | ||
| + | select table_name, num_rows, avg_row_len, | ||
| + | from | ||
| + | where owner = ' | ||
| + | and table_name = ' | ||
| + | / | ||
| + | |||
| + | begin | ||
| + | dbms_stats.gather_table_stats(' | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== Compress indexes to take up less space in the buffer cache ==== | ||
| + | < | ||
| + | select blocks from dba_segments where segment_name = '& | ||
| + | </ | ||
| + | < | ||
| + | select index_name, compression from dba_indexes where index_name = '& | ||
| + | </ | ||
| + | < | ||
| + | alter index & | ||
| + | </ | ||
| + | |||
| + | ==== Gather schema statistics ==== | ||
| + | < | ||
| + | begin | ||
| + | dbms_stats.gather_schema_stats ( ownname | ||
| + | , options | ||
| + | , estimate_percent => 100 | ||
| + | , degree | ||
| + | , method_opt | ||
| + | , cascade | ||
| + | ); | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== Gather table statistics ==== | ||
| + | < | ||
| + | BEGIN dbms_stats.gather_table_stats(ownname =>' | ||
| + | </ | ||
| + | ==== Show any events set in database ==== | ||
| + | < | ||
| + | set serveroutput on | ||
| + | declare | ||
| + | event_level number; | ||
| + | begin | ||
| + | dbms_output.enable(null); | ||
| + | dbms_output.put_line (' | ||
| + | dbms_output.put_line (' | ||
| + | for i in 10000..10999 loop | ||
| + | sys.dbms_system.read_ev(i, | ||
| + | if (event_level > 0) then | ||
| + | dbms_output.put_line(' | ||
| + | end if; | ||
| + | end loop; | ||
| + | end; | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== Show all Oracle hidden parameters ==== | ||
| + | < | ||
| + | set pages 100 lines 300 | ||
| + | col ksppinm | ||
| + | col ksppstvl for a50 | ||
| + | |||
| + | select ksppinm | ||
| + | , ksppstvl | ||
| + | from | ||
| + | , x$ksppsv b | ||
| + | where 1=1 | ||
| + | and a.indx=b.indx | ||
| + | and substr(ksppinm, | ||
| + | order by ksppinm | ||
| + | / | ||
| + | </ | ||
| + | ==== How Can we Run SQL Tuning Advisor For A SQL ID In Oracle Database? ==== | ||
| + | * [[https:// | ||
| + | Catch it while the statement is running...\\ | ||
| + | 1. Create Tuning Task | ||
| + | < | ||
| + | DECLARE | ||
| + | l_sql_tune_task_id VARCHAR2(100); | ||
| + | BEGIN | ||
| + | l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( | ||
| + | sql_id => ' | ||
| + | scope => DBMS_SQLTUNE.scope_comprehensive, | ||
| + | time_limit => 500, | ||
| + | task_name => ' | ||
| + | description => ' | ||
| + | DBMS_OUTPUT.put_line(' | ||
| + | END; | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | 2. Execute Tuning task: | ||
| + | < | ||
| + | EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ' | ||
| + | </ | ||
| + | |||
| + | 3. Get the Tuning advisor report. | ||
| + | |||
| + | < | ||
| + | set long 65536 | ||
| + | set longchunksize 65536 | ||
| + | set linesize 100 | ||
| + | select dbms_sqltune.report_tuning_task(' | ||
| + | </ | ||
| + | |||
| + | 4. Get list of tuning task present in database: | ||
| + | We can get the list of tuning tasks present in database from DBA_ADVISOR_LOG | ||
| + | |||
| + | < | ||
| + | SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME=' | ||
| + | </ | ||
| + | |||
| + | 5. Drop a tuning task: | ||
| + | < | ||
| + | execute dbms_sqltune.drop_tuning_task(' | ||
| + | |||
| + | |||
| + | Method when the sql is not running. We can get the information from the AWR snaps when the query was ran. | ||
| + | |||
| + | SQL_ID =4g5ah8zr6thnb | ||
| + | |||
| + | Find the begin snap and end snap of the sql_id. | ||
| + | |||
| + | select a.instance_number inst_id, a.snap_id, | ||
| + | executions_delta executions, round(ELAPSED_TIME_delta/ | ||
| + | where sql_id='& | ||
| + | and a.instance_number=b.instance_number | ||
| + | order by snap_id desc, a.instance_number; | ||
| + | From here we can get the begin snap and end snap of the sql_id. | ||
| + | |||
| + | begin_snap -> 6377 | ||
| + | end_snap -> 6380 | ||
| + | </ | ||
| + | |||
| + | 1. Create the tuning task: | ||
| + | |||
| + | < | ||
| + | DECLARE | ||
| + | l_sql_tune_task_id | ||
| + | BEGIN | ||
| + | l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( | ||
| + | begin_snap | ||
| + | end_snap | ||
| + | sql_id | ||
| + | scope => DBMS_SQLTUNE.scope_comprehensive, | ||
| + | time_limit | ||
| + | task_name | ||
| + | description => ' | ||
| + | DBMS_OUTPUT.put_line(' | ||
| + | END; | ||
| + | / | ||
| + | 2. Execute the tuning task: | ||
| + | |||
| + | EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ' | ||
| + | |||
| + | 3. Get the tuning task recommendation report | ||
| + | |||
| + | SET LONG 10000000; | ||
| + | SET PAGESIZE 100000000 | ||
| + | SET PAGESIZE 24 | ||
| + | SET LINESIZE 200 | ||
| + | SELECT DBMS_SQLTUNE.report_tuning_task(' | ||
| + | </ | ||
| + | ==== Find the sessions consuming a high amount Temporary space ==== | ||
| + | * [[https:// | ||
| + | Check the current status of the temporary tablespaces | ||
| + | < | ||
| + | select tablespace_name | ||
| + | , tablespace_size/ | ||
| + | , allocated_space/ | ||
| + | , free_space/ | ||
| + | from | ||
| + | / | ||
| + | </ | ||
| + | select s.sid | ||
| + | , s.username | ||
| + | , u.tablespace | ||
| + | , s.sql_hash_value||' | ||
| + | , u.segtype | ||
| + | , u.contents | ||
| + | , u.blocks | ||
| + | from | ||
| + | , v$tempseg_usage u | ||
| + | where s.saddr=u.session_addr | ||
| + | and u.tablespace=upper('& | ||
| + | order by u.blocks desc | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | Then for the sid and schema, run this. I wish I knew what it did! | ||
| + | < | ||
| + | select hash_value | ||
| + | , sorts | ||
| + | , rows_processed/ | ||
| + | from | ||
| + | where hash_value in (select hash_value from v$open_cursor where sid=& | ||
| + | and sorts > 0 | ||
| + | and parsing_schema_name=upper('& | ||
| + | order by rows_processed/ | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | |||
| + | ==== How much memory is being used by processes (PGA memory)? ==== | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | < | ||
| + | set lines 1000 pages 100 | ||
| + | col PGA_USED_MEGS for 999, | ||
| + | col PGA_ALLOC_MEGS for 999, | ||
| + | col PGA_FREEABLE_MEGS for 999, | ||
| + | col PGA_MAX_MEGS for 999, | ||
| + | compute sum of PGA_USED_MEGS on report | ||
| + | compute sum of PGA_ALLOC_MEGS on report | ||
| + | compute sum of PGA_MAX_MEGS on report | ||
| + | break on report | ||
| + | SELECT PROGRAM, (PGA_USED_MEM/ | ||
| + | FROM V$PROCESS | ||
| + | order by 2 | ||
| + | / | ||
| + | </ | ||
| + | ==== or PGA memory with a bit more surrounding info ...temporary tablespace usage also ==== | ||
| + | < | ||
| + | set lines 1000 pages 2000 trims on feed on newpa none | ||
| + | col username | ||
| + | col osuser | ||
| + | col sid_ser | ||
| + | col ospid head "O/S pid" | ||
| + | col logontime | ||
| + | col timenow | ||
| + | col program | ||
| + | col machine | ||
| + | col port head " | ||
| + | col status | ||
| + | col cpu_secs | ||
| + | col max_megs | ||
| + | col alloc_megs | ||
| + | col used_megs | ||
| + | col freeable_megs | ||
| + | col temp_used | ||
| + | |||
| + | col timenow nopri | ||
| + | |||
| + | compute sum of alloc_megs | ||
| + | compute sum of used_megs | ||
| + | compute sum of freeable_megs on report | ||
| + | break on report | ||
| + | |||
| + | with temp_usage as ( | ||
| + | select su.session_addr | ||
| + | , s.sid||' | ||
| + | , sum(su.blocks)*tbs.block_size/ | ||
| + | , su.tablespace | ||
| + | from | ||
| + | , v$sort_usage | ||
| + | , dba_tablespaces tbs | ||
| + | where 1=1 | ||
| + | and s.saddr | ||
| + | and su.tablespace = tbs.tablespace_name | ||
| + | group by su.session_addr | ||
| + | , s.sid | ||
| + | , s.serial# | ||
| + | , tbs.block_size | ||
| + | , su.tablespace | ||
| + | ) | ||
| + | SELECT nvl(s.username,' | ||
| + | , s.osuser | ||
| + | , s.sid||',' | ||
| + | , p.spid | ||
| + | , to_char(logon_time,' | ||
| + | , to_char(sysdate,' | ||
| + | , s.program | ||
| + | , s.machine | ||
| + | , s.port | ||
| + | , s.status | ||
| + | , round((ss.value/ | ||
| + | , round(pga_max_mem/ | ||
| + | , round(pga_alloc_mem/ | ||
| + | , round(pga_used_mem/ | ||
| + | , round(pga_freeable_mem/ | ||
| + | , tu.mb_used | ||
| + | from | ||
| + | , v$session | ||
| + | , v$sesstat | ||
| + | , v$statname sn | ||
| + | , temp_usage tu | ||
| + | where p.addr | ||
| + | and s.sid = ss.sid | ||
| + | and ss.statistic# | ||
| + | and sn.name | ||
| + | and s.saddr | ||
| + | order by pga_used_mem desc | ||
| + | / | ||
| + | |||
| + | set lines 80 | ||
| + | </ | ||
| + | |||
| + | ==== SGA and PGA historic usage per hour (or snapshot interval) ==== | ||
| + | < | ||
| + | set lines 1000 pages 5000 trims on | ||
| + | col instance_name head " | ||
| + | col sga head " | ||
| + | col pga head " | ||
| + | col tot head " | ||
| + | col datetime | ||
| + | |||
| + | select i.instance_name | ||
| + | , sga.allocated | ||
| + | , pga.allocated | ||
| + | , (sga.allocated+pga.allocated) | ||
| + | , ' | ||
| + | from ( | ||
| + | | ||
| + | , | ||
| + | , | ||
| + | | ||
| + | | ||
| + | , | ||
| + | | ||
| + | , ( | ||
| + | | ||
| + | , | ||
| + | , | ||
| + | | ||
| + | | ||
| + | , | ||
| + | | ||
| + | , dba_hist_snapshot sn | ||
| + | , v$instance | ||
| + | where sn.snap_id | ||
| + | and sn.instance_number = sga.instance_number | ||
| + | and sn.snap_id | ||
| + | and sn.instance_number = pga.instance_number | ||
| + | and sn.instance_number = i.instance_number | ||
| + | order by sn.end_interval_time desc | ||
| + | , sn.instance_number | ||
| + | / | ||
| + | |||
| + | set lines 80 | ||
| + | </ | ||
| + | |||
| + | ==== Tuning PGA_AGGREGATE_TARGET ==== | ||
| + | |||
| + | * [[https:// | ||
| + | |||
| + | < | ||
| + | SELECT ROUND(pga_target_for_estimate/ | ||
| + | | ||
| + | | ||
| + | FROM V$PGA_TARGET_ADVICE; | ||
| + | </ | ||
| - | =====Gather schema statistics===== | + | ==== Find the total PGA memory used by processes |
| - | < | + | < |
| + | select round(sum(pga_used_mem)/ | ||
| + | </ | ||
| + | ==== To calculate the amount of memory that you may need for PGA ==== | ||
| + | < | ||
| + | select * from v$pgastat; | ||
| + | select * from v$pga_target_advice; | ||
| + | </ | ||
| + | See [[https:// | ||
| + | See [[https:// | ||
| + | See [[https:// | ||
| + | < | ||
| + | col name for a40 | ||
| + | select b.name | ||
| + | , sum(a.value) value | ||
| + | from | ||
| + | , v$statname b | ||
| + | where a.statistic# | ||
| + | and b.name like ' | ||
| + | group by b.name | ||
| + | / | ||
| + | </ | ||
| + | < | ||
| + | NAME VALUE | ||
| + | ---------------------------------------- -------------------- | ||
| + | workarea executions - onepass | ||
| + | workarea executions - multipass | ||
| + | workarea executions - optimal | ||
| + | </ | ||
| + | The goal is to get rid of all passes and have everything optimal.\\ | ||
| + | One pass means that data has had to be swapped out to the temporary tablespace to get the job done. Multipass means data had to be swapped out several times to get the job done.\\ | ||
| + | These numbers are very high. | ||
| + | < | ||
| + | col pga_size_gb for 99999 | ||
| + | col estd_pga_cache_hit_percentage for 99999 head " | ||
| + | col estd_overalloc_count for 9999999999 head " | ||
| + | select round(pga_target_for_estimate/ | ||
| + | , estd_pga_cache_hit_percentage | ||
| + | , estd_overalloc_count | ||
| + | from | ||
| + | / | ||
| + | </ | ||
| + | < | ||
| + | ESTD_PGA_CACHE | ||
| + | PGA_SIZE_GB HIT_PERCENTAGE ALLOC_COUNT | ||
| + | ----------- -------------- ----------- | ||
| + | 0 | ||
| + | 1 | ||
| + | 2 | ||
| + | 2 | ||
| + | 3 | ||
| + | 4 | ||
| + | 4 | ||
| + | 5 | ||
| + | 5 | ||
| + | 6 | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | </ | ||
| + | Setting the pga_aggregate_target to 18Gb will allow all processing to remain in the pga and not get swapped out to the temp tablespaces. | ||
| - | =====Gather | + | ==== Check the table size and percentage of fragmentation |
| - | < | + | |
| - | =====Show any events set in database===== | + | < |
| - | < | + | select sum(bytes)/ |
| - | =====Top memory consumers in AIX===== | + | select table_name, |
| - | < | + | round((num_rows*avg_row_len/1024/ |
| - | =====Show all Oracle hidden parameters===== | + | round(((blocks*16/1024)-(num_rows*avg_row_len/ |
| - | < | + | (round(((blocks*16/ |
| - | =====How much memory is being used by processes | + | from all_tables WHERE table_name='& |
| - | < | + | </ |
| - | =====Enable Automatic Memory Management (AMM)===== | + | ==== Enable Automatic Memory Management (AMM) ==== |
| AMM is enabled by setting one or both of the following memory parameters: | AMM is enabled by setting one or both of the following memory parameters: | ||
| - | < | + | < |
| - | With these memory parameters set, AMM is enabled.<br /> | + | alter system set memory_max_target=2g scope=spfile; |
| - | Memory will now be allocated automatically to where it is needed.<br /> | + | alter system set memory_target=2g scope=spfile; |
| - | < | + | </ |
| - | If sga_target and/or pga_aggregate_target are set, these will be treated as minimum values.<br /> | + | With these memory parameters set, AMM is enabled.\\ |
| - | If only one of sga_target or pga_aggregate_target is set, the other will be set to (memory_target - the value set).<br /> | + | Memory will now be allocated automatically to where it is needed.\\ |
| - | <br /> | + | < |
| - | ====Disable AMM==== | + | alter system set sga_target=0 scope=spfile; |
| - | < | + | alter system set pga_aggregate_target=0 scope=spfile; |
| + | </ | ||
| + | If sga_target and/or pga_aggregate_target are set, these will be treated as minimum values.\\ | ||
| + | If only one of sga_target or pga_aggregate_target is set, the other will be set to (memory_target - the value set).\\ | ||
| - | ====Enable ASMM==== | + | === Disable AMM === |
| - | < | + | < |
| + | SQL> alter system reset memory_max_target scope=spfile | ||
| + | SQL> alter system reset memory_target | ||
| + | </ | ||
| - | ====Reboot database and verify that we have switched from AMM to ASMM==== | + | === Enable |
| - | < | + | < |
| - | --> AMM disabled | + | SQL> alter system set SGA_MAX_SIZE=1400m scope=spfile |
| + | SQL> alter system set SGA_TARGET=1000m scope=spfile | ||
| + | SQL> alter system set PGA_AGGREGATE_TARGET=480m scope=spfile | ||
| + | </ | ||
| - | < | + | === Reboot database and verify that we have switched from AMM to ASMM === |
| + | < | ||
| + | SQL> show parameter memory | ||
| + | NAME | ||
| + | ------------------------------------ ----------- ------------------------------ | ||
| + | memory_max_target | ||
| + | memory_target | ||
| + | </ | ||
| + | --> AMM disabled | ||
| + | |||
| + | < | ||
| + | SQL> show parameter sga | ||
| + | NAME | ||
| + | ------------------------ ----------- ------------------------------ | ||
| + | sga_max_size | ||
| + | sga_target | ||
| + | |||
| + | SQL> show parameter pga | ||
| + | NAME | ||
| + | ------------------------ ----------- ------------------------------ | ||
| + | pga_aggregate_target | ||
| + | </ | ||
| --> ASMM enabled ! | --> ASMM enabled ! | ||
| - | =====SGA | + | ==== Show size of the large pool ==== |
| - | Problem with 12c SGA growing too high. Database keeps crashing due to memory issues. Started at 2G, then to 4G and now at 5G.<br /> | + | < |
| + | select name, sum(bytes) from v$sgastat where upper(pool) = 'LARGE POOL' group by rollup (name); | ||
| + | </ | ||
| + | |||
| + | ==== Quick SGA sizing advice | ||
| + | The MMON background process gathers statistics about sga usage and updates the V$SGA_TARGET_ADVICE view. | ||
| + | < | ||
| + | --- DB_CACHE_ADVICE should be ON | ||
| + | |||
| + | SQL> show parameter db_cache_advice | ||
| + | |||
| + | NAME | ||
| + | ------------------------------------ -------------------------------- ------------------ | ||
| + | db_cache_advice | ||
| + | |||
| + | -- STATISTICS_LEVEL should be TYPICAL/ | ||
| + | |||
| + | SQL> show parameter statistics_level | ||
| + | |||
| + | NAME | ||
| + | ------------------------------------ -------------------------------- -------------------------- | ||
| + | statistics_level | ||
| + | </ | ||
| + | < | ||
| + | select sga_size | ||
| + | , sga_size_factor | ||
| + | , estd_physical_reads | ||
| + | , estd_db_time | ||
| + | from | ||
| + | order by sga_size; | ||
| + | </ | ||
| + | Note where the ESTD_PHYSICAL_READS drop | ||
| + | < | ||
| + | SGA_SIZE SGA_SIZE_FACTOR ESTD_PHYSICAL_READS ESTD_DB_TIME | ||
| + | ---------- --------------- ------------------- ------------ | ||
| + | 3756 .375 2.0808E+10 | ||
| + | 5008 .5 2.0808E+10 | ||
| + | 6260 .625 2.0808E+10 | ||
| + | 7512 | ||
| + | 8764 .875 2.0808E+10 | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | |||
| + | 14 rows selected. | ||
| + | </ | ||
| + | Suggestion would be to increase sga target by 1.25 as there is no longer a significant decrease in ESTD_PHYSICAL_READS for the increase in sga size. | ||
| + | |||
| + | ==== SGA tuning | ||
| + | Problem with 12c SGA growing too high. Database keeps crashing due to memory issues. Started at 2G, then to 4G and now at 5G.\\ | ||
| Raised SR with Oracle. This is the response | Raised SR with Oracle. This is the response | ||
| - | < | + | < |
| + | You need to query the advice views like V$MEMORY_TARGET_ADVICE, | ||
| + | Interpret the results and then make the changes to memory components as indicated by the SIZE column. | ||
| + | Please review | ||
| + | Note 1323708.1 : Tuning the SGA_TARGET using V$SGA_TARGET_ADVICE then follow the version specific advice. | ||
| + | |||
| + | Please note that Oracle recommend to specify a value for the each of the tunable parameters | ||
| + | i.e Shared Pool, Database Buffer Cache, Large Pool, Java Pool and Streams Pool even when AMM or ASMM is enabled (you are using AMM). | ||
| + | When minimum value for each of the tunable parameters is set, Oracle will make sure that the amount of memory being allocated for the corresponding pool will not shrink below the specified amount. | ||
| + | Best practices with auto-tuning are to include explicit, minimum settings for the various auto-tuned components. | ||
| + | This will help the auto-tuner make " | ||
| + | While it is not required to set explicit settings, the auto-tuner can get over aggressive with memory moves when auto-tuned components are set to 0. | ||
| + | |||
| + | The parameter _kghdsidx_count controls the number of subpools used. Setting this parameter to 2 will be the best. Currently you have this parameter set to 5. | ||
| + | |||
| + | SQL> alter system set " | ||
| + | or add this in the pfile | ||
| + | " | ||
| + | |||
| + | As a best practice for SGA components you can use the following recommendations: | ||
| + | |||
| + | - SHARED_POOL_SIZE >= 2G (because minimum 1G/subpool is recommended) | ||
| + | - LARGE_POOL_SIZE >= 150M (used for parallel executions) | ||
| + | - STREAMS_POOL_SIZE >= 150M (if you use datapump regularly) | ||
| + | - JAVA_POOL_SIZE >= 150M (if you use java) | ||
| + | - SGA_TARGET >= 3G (because it should be greater by 10% of the sum of it's components) | ||
| + | </ | ||
| or another one from an 11g incident... | or another one from an 11g incident... | ||
| - | < | + | < |
| + | ACTION PLAN | ||
| + | =========== | ||
| + | 1. We can see that your shared pool is divided intro 4 subpools. | ||
| + | Set _kghdsidx_count to 2 in order to be sure the shared pool will be divided only in 2 subpools and not more. You currently have 4 subpools which favor fragmentation. | ||
| + | |||
| + | connect / as sysdba | ||
| + | SQL > alter system set " | ||
| + | - restart the database | ||
| + | |||
| + | FYI: Starting with Oracle 9i the shared_pool will be divided into multiple sub-heaps. Until 9.0, the shared pool was always allocated in one large heap rather than multiple subheaps. The number of subpools even if produced a better performance, | ||
| + | could produce ora-4031 errors when there is not space in the subpool as a result of fragmentation. One process will use only a specific subpool and if an error is reported, it wont be migrated to other subpool. Oracle 9i implemented multiple subpools to avoid shared pool latch contention. But, the cost of the multiple pools is that the size of each subpool will be smaller and hence more susceptible to an ORA-4031. So, the parameter _kghdsidx_count can be used to override the default for the number of sub pools. | ||
| + | |||
| + | 2. Decrease the number of open_cursors from 3000 to 1000. | ||
| + | |||
| + | The open_cursors parameter is defined per session. Having many cursors open per session is too much and it means that the cursors are not correctly handled/ | ||
| + | 1000 cursors created per sessions should be enough for any application. We recommend in some cases to go as low as 600 which should still be enough. | ||
| + | |||
| + | 3. Apply the workaround to disable the use of durations by setting " | ||
| + | |||
| + | This will allow unpinned SQLA, KGLH0, and KGLHD memory to be freed to make room for new permanent allocations. | ||
| + | |||
| + | 4. Set a minimum for the SGA within the memory_target size using the parameter sga_target. For example set sga_target=13G. This will mean that at all times 13G of memory from memory_target will be reserved for the SGA. More memory can be allocated to it if necessary and available. | ||
| + | So please modify accordingly the parameters sga_max_size and sga_target. This will require a database restart. | ||
| + | </ | ||
| But.. From 1323708.1 ... | But.. From 1323708.1 ... | ||
| - | < | + | < |
| + | In Oracle11g (or higher), the information in the V$SGA_TARGET_ADVICE view is similar to that provided in the V$MEMORY_TARGET_ADVICE view for Automatic Shared Memory Management. | ||
| + | If MEMORY_TARGET is set, then SGA_TARGET should be set to 0. See Document 443746.1 for more details on this. | ||
| + | If MEMORY_TARGET is not set, use the same steps as for 10g. | ||
| + | </ | ||
| + | |||
| + | ==== Redo log file size and Database Performance ==== | ||
| + | * [[https:// | ||
| + | See how much time is spent writing redo logs and checkpointing | ||
| + | < | ||
| + | select /*+ ordered */ | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | order by type, time_waited; | ||
| + | </ | ||
| + | |||
| + | ==== How much redo was generated / How many archivelog switches have occurred per hour over the past week? ==== | ||
| + | < | ||
| + | set lines 200 pages 100 | ||
| + | col day for a9 hea " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | col " | ||
| + | alter session set nls_date_format=' | ||
| + | / | ||
| + | select * from ( | ||
| + | select trunc(first_time) day, | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | from | ||
| + | group by trunc(first_time) | ||
| + | order by trunc(first_time) desc | ||
| + | ) | ||
| + | where rownum < 8 | ||
| + | order by 1 | ||
| + | / | ||
| + | set lines 80 | ||
| - | =====How much redo was generated / How many archivelog switches have occurred per hour over the past week? | + | </ |
| - | < | + | ==== How much archive |
| - | =====How much archive has been generated per day===== | + | |
| Also how much has RMAN deleted | Also how much has RMAN deleted | ||
| - | < | + | < |
| + | set lines 1000 pages 100 | ||
| + | col day for a15 head " | ||
| + | select sum_arch.day | ||
| + | , round(sum_arch.generated_gb, | ||
| + | , round(sum_arch_del.archived_gb, | ||
| + | , round((sum_arch.generated_gb - sum_arch_del.archived_gb), | ||
| + | from ( | ||
| + | | ||
| + | , | ||
| + | | ||
| + | | ||
| + | group by to_char (completion_time, | ||
| + | , ( | ||
| + | | ||
| + | , | ||
| + | | ||
| + | | ||
| + | | ||
| + | where sum_arch.day = sum_arch_del.day(+) | ||
| + | order by to_date (day, ' | ||
| + | / | ||
| + | set lines 80 | ||
| + | </ | ||
| - | =====Top session activity===== | + | ==== Top session activity ==== |
| + | * [[https:// | ||
| Enter READS, EXECS or CPU to order session activity by that column | Enter READS, EXECS or CPU to order session activity by that column | ||
| - | < | + | < |
| + | set lines 500 pages 1000 verif off | ||
| - | =====Show current used undo blocks | + | col username |
| - | < | + | col machine |
| + | col module | ||
| + | col logon_time for a20 | ||
| + | col program | ||
| + | col killer | ||
| + | col osuser | ||
| - | =====Pinpoint which sessions are using lots of undo===== | + | prompt Enter CPU, READS or EXECS |
| - | < | + | prompt (Press Enter for CPU default) |
| - | =====How many blocks have been changed by sessions?===== | + | |
| - | High values indicate a session generating lots of redo<br /> | + | select nvl(a.username, |
| + | , a.osuser | ||
| + | , a.sid||',' | ||
| + | , c.value as && | ||
| + | , a.lockwait | ||
| + | , a.status | ||
| + | , a.module | ||
| + | , a.machine | ||
| + | , a.program | ||
| + | , to_char(a.logon_time,' | ||
| + | from | ||
| + | , v$sesstat c | ||
| + | , v$statname d | ||
| + | where 1=1 | ||
| + | and a.sid = c.sid | ||
| + | and c.statistic# | ||
| + | and d.name | ||
| + | ' | ||
| + | ' | ||
| + | 'CPU used by this session' | ||
| + | ) | ||
| + | order by c.value desc | ||
| + | / | ||
| + | |||
| + | undef TYPE | ||
| + | </ | ||
| + | |||
| + | * [[https:// | ||
| + | < | ||
| + | select s.ecid | ||
| + | , s.inst_id | ||
| + | , s.sid | ||
| + | , s.serial# | ||
| + | , p.spid | ||
| + | , s.status | ||
| + | , s.machine | ||
| + | , s.action | ||
| + | , s.module | ||
| + | , s.terminal | ||
| + | , s.sql_id | ||
| + | , s.last_call_et | ||
| + | , s.event | ||
| + | , s.client_info | ||
| + | , s.plsql_subprogram_id | ||
| + | , s.program | ||
| + | , s.client_identifier | ||
| + | , ( select max( substr( sql_text , 1, 40 )) FROM gv$sql sq WHERE sq.sql_id = s.sql_id ) | ||
| + | , ( select object_name | ||
| + | , ( select procedure_name | ||
| + | , ( select object_name | ||
| + | , ( select procedure_name | ||
| + | , 'alter system kill session ' || '''' | ||
| + | from | ||
| + | , gv$process p | ||
| + | where | ||
| + | --client_identifier like ' | ||
| + | --s.ecid like ' | ||
| + | -- sid=4361 | ||
| + | --p.spid=' | ||
| + | --s.program like ' | ||
| + | and p.addr=s.paddr | ||
| + | and p.inst_id = s.inst_id | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | |||
| + | ==== Show current used undo blocks for ongoing transactions ==== | ||
| + | < | ||
| + | select vs.sid | ||
| + | , vs.serial# | ||
| + | , vs.username | ||
| + | , vs.program | ||
| + | , vt.used_ublk | ||
| + | , vt.used_urec | ||
| + | from | ||
| + | , v$transaction vt | ||
| + | where vs.taddr = vt.addr | ||
| + | order by 5 desc, 6 desc | ||
| + | </ | ||
| + | |||
| + | ==== Pinpoint which sessions are using lots of undo ==== | ||
| + | < | ||
| + | select a.sid | ||
| + | , b.name | ||
| + | , a.value | ||
| + | from | ||
| + | , v$statname b | ||
| + | where a.statistic# | ||
| + | and a.statistic# | ||
| + | order by a.value desc | ||
| + | </ | ||
| + | ==== How many blocks have been changed by sessions? ==== | ||
| + | High values indicate a session generating lots of redo\\ | ||
| Use this query to check for programs generating lots of redo when these programs activate more than one transaction. | Use this query to check for programs generating lots of redo when these programs activate more than one transaction. | ||
| - | < | + | < |
| - | =====Top SQL===== | + | select vs.sid |
| + | , vs.serial# | ||
| + | , vs.username | ||
| + | , vs.program | ||
| + | , vi.block_changes | ||
| + | from | ||
| + | , v$sess_io vi | ||
| + | where vs.sid = vi.sid | ||
| + | order by 5 desc | ||
| + | , 1, 2, 3, 4; | ||
| + | </ | ||
| + | ==== Top SQL ==== | ||
| Shows the SQL statements that have caused the most disk reads per execution since the instance was last started | Shows the SQL statements that have caused the most disk reads per execution since the instance was last started | ||
| - | < | + | < |
| + | set lines 1000 pages 1000 | ||
| + | set verif off | ||
| + | col sql_text for a150 | ||
| + | |||
| + | select * | ||
| + | from ( | ||
| + | | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | | ||
| + | | ||
| + | ) | ||
| + | where rownum <= 11 | ||
| + | / | ||
| + | set lines 80 | ||
| + | </ | ||
| Top ten SQL statements with the greatest aggregate elapsed time | Top ten SQL statements with the greatest aggregate elapsed time | ||
| - | < | + | < |
| + | set lines 1000 pages 1000 | ||
| + | col sql_text for a150 | ||
| + | SELECT sql_id, | ||
| + | FROM (SELECT sql_id, child_number, | ||
| + | cpu_time, | ||
| + | RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank | ||
| + | FROM v$sql) | ||
| + | WHERE elapsed_rank <= 10 | ||
| + | / | ||
| + | set lines 80 | ||
| + | </ | ||
| See the explain plan of these SQL's by feeding the sql_id and child_no into this cracker! | See the explain plan of these SQL's by feeding the sql_id and child_no into this cracker! | ||
| - | < | + | < |
| + | SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (& | ||
| + | </ | ||
| - | =====Top waits===== | + | ==== Top waits ==== |
| - | Displays a list of the events currently being waited on by active sessions.<br /> | + | Displays a list of the events currently being waited on by active sessions.\\ |
| - | The meaning of the wait_time and seconds_in_wait columns varies depending on their values follows:<br /> | + | The meaning of the wait_time and seconds_in_wait columns varies depending on their values follows:\\ |
| - | * wait_time - A non-zero value represents the session’s last wait time, while a zero value indicates that the session is currently waiting.<br /> | + | * wait_time - A non-zero value represents the session’s last wait time, while a zero value indicates that the session is currently waiting.\\ |
| - | * seconds_in_wait - When the wait_time is zero, the seconds_in_wait value represents the seconds spent in the current wait condition.<br /> | + | * seconds_in_wait - When the wait_time is zero, the seconds_in_wait value represents the seconds spent in the current wait condition.\\ |
| - | When the wait_time is greater than zero, the seconds_in_wait value represents the seconds since the start of the last wait,<br /> | + | When the wait_time is greater than zero, the seconds_in_wait value represents the seconds since the start of the last wait,\\ |
| and (seconds_in_wait - wait_time / 100) is the active seconds since the last wait ended. | and (seconds_in_wait - wait_time / 100) is the active seconds since the last wait ended. | ||
| - | < | + | < |
| - | =====Session wait history===== | + | set lines 200 pages 1000 |
| + | col username | ||
| + | col event for a30 | ||
| + | col wait_class for a15 | ||
| + | |||
| + | select nvl(s.username, | ||
| + | , s.sid | ||
| + | , s.serial# | ||
| + | , sw.event | ||
| + | , sw.wait_class | ||
| + | , sw.wait_time | ||
| + | , sw.seconds_in_wait | ||
| + | , sw.state | ||
| + | from | ||
| + | , v$session s | ||
| + | where 1=1 | ||
| + | and s.sid = sw.sid | ||
| + | order by sw.seconds_in_wait desc | ||
| + | / | ||
| + | </ | ||
| + | and with the SQL... | ||
| + | < | ||
| + | select a.sid | ||
| + | , a.event | ||
| + | , a.wait_time | ||
| + | , round(c.physical_read_bytes/ | ||
| + | , round(c.physical_write_bytes/ | ||
| + | , c.sql_text | ||
| + | from | ||
| + | , v$session b | ||
| + | , v$sql c | ||
| + | where 1=1 | ||
| + | and a.sid = b.sid | ||
| + | and b.sql_id = c.sql_id | ||
| + | order by wait_time desc | ||
| + | / | ||
| + | </ | ||
| + | ==== Session wait history ==== | ||
| Once a session of interest has been identified, we can display the history of events associated with that session | Once a session of interest has been identified, we can display the history of events associated with that session | ||
| - | < | + | < |
| + | set lines 200 pages 1000 | ||
| + | set verif off | ||
| + | col username for a20 | ||
| + | col event for a40 | ||
| - | =====System waits===== | + | select nvl(s.username, |
| - | < | + | , s.sid |
| - | =====Oracle Log File Sync Wait Event===== | + | , s.serial# |
| - | Reference: [[http:// | + | , se.event |
| + | , se.total_waits | ||
| + | , se.total_timeouts | ||
| + | , se.time_waited | ||
| + | , se.average_wait | ||
| + | , se.max_wait | ||
| + | , round(se.time_waited_micro/ | ||
| + | from | ||
| + | , v$session s | ||
| + | where 1=1 | ||
| + | and s.sid = se.sid | ||
| + | and s.sid = &1 | ||
| + | order by se.time_waited desc | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== System waits ==== | ||
| + | < | ||
| + | select event | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | , | ||
| + | from v$system_event | ||
| + | order by event | ||
| + | / | ||
| + | </ | ||
| + | ==== ASH queries ==== | ||
| + | < | ||
| + | | ||
| + | | ||
| + | FROM | ||
| + | WHERE a.sample_time > SYSDATE - 5/(24*60) -- 5 mins | ||
| + | GROUP BY a.event | ||
| + | ORDER BY total_wait_time DESC; | ||
| + | </ | ||
| + | |||
| + | and | ||
| + | |||
| + | < | ||
| + | SELECT NVL(a.event, | ||
| + | | ||
| + | FROM | ||
| + | WHERE a.sample_time > SYSDATE - 1 | ||
| + | GROUP BY a.event | ||
| + | ORDER BY total_wait_time DESC; | ||
| + | </ | ||
| + | |||
| + | ==== Oracle Log File Sync Wait Event ==== | ||
| + | Reference: [[http:// | ||
| Snippet: | Snippet: | ||
| < | < | ||
| - | When a user session waits on the 'log file sync' event, it is actually waiting for the LGWR process to write the log buffer to the redo log file and<br /> | + | When a user session waits on the 'log file sync' event, it is actually waiting for the LGWR process to write the log buffer to the redo log file and\\ |
| - | return confirmation/ | + | return confirmation/ |
| number of waits is high, reduce the number of commits by batching (or committing after ' | number of waits is high, reduce the number of commits by batching (or committing after ' | ||
| If slow I/O, investigate the following: | If slow I/O, investigate the following: | ||
| - | | + | < |
| - | # Put log files on faster disks.<br /> | + | |
| - | # Put alternate redo logs on different disks to minimise the effect archive processes (log files switches).<br /> | + | # Put log files on faster disks.\\ |
| - | # Review application design, use NOLOGGING operations where appropriate, | + | # Put alternate redo logs on different disks to minimise the effect archive processes (log files switches).\\ |
| + | # Review application design, use NOLOGGING operations where appropriate, | ||
| + | </code> | ||
| If wait times are still significant, | If wait times are still significant, | ||
| </ | </ | ||
| - | =====EXPLAIN PLAN===== | + | ==== Shared Memory Problem (unable to allocate shared memory ...) - and how to avoid it using bind variables |
| - | ====Usage (old school)==== | + | * [[https:// |
| + | Just to give you a tiny idea of how huge of a difference this can make performance wise, you only need to run a very small test: | ||
| + | < | ||
| + | tkyte@TKYTE816> | ||
| + | System altered. | ||
| + | |||
| + | tkyte@TKYTE816> | ||
| + | 2 type rc is ref cursor; | ||
| + | 3 l_rc rc; | ||
| + | 4 l_dummy all_objects.object_name%type; | ||
| + | 5 l_start number default dbms_utility.get_time; | ||
| + | 6 begin | ||
| + | 7 for i in 1 .. 1000 | ||
| + | 8 loop | ||
| + | 9 open l_rc for | ||
| + | 10 ' | ||
| + | 11 from all_objects | ||
| + | 12 where object_id | ||
| + | 13 fetch l_rc into l_dummy; | ||
| + | 14 close l_rc; | ||
| + | 15 end loop; | ||
| + | 16 dbms_output.put_line | ||
| + | 17 ( round( (dbms_utility.get_time-l_start)/ | ||
| + | 18 ' seconds...' | ||
| + | 19 end; | ||
| + | 20 / | ||
| + | 14.86 seconds... | ||
| + | |||
| + | PL/SQL procedure successfully completed. | ||
| + | |||
| + | tkyte@TKYTE816> | ||
| + | 2 type rc is ref cursor; | ||
| + | 3 l_rc rc; | ||
| + | 4 l_dummy all_objects.object_name%type; | ||
| + | 5 l_start number default dbms_utility.get_time; | ||
| + | 6 begin | ||
| + | 7 for i in 1 .. 1000 | ||
| + | 8 loop | ||
| + | 9 open l_rc for | ||
| + | 10 ' | ||
| + | 11 from all_objects | ||
| + | 12 where object_id | ||
| + | 13 using i; | ||
| + | 14 fetch l_rc into l_dummy; | ||
| + | 15 close l_rc; | ||
| + | 16 end loop; | ||
| + | 17 dbms_output.put_line | ||
| + | 18 ( round( (dbms_utility.get_time-l_start)/ | ||
| + | 19 ' seconds...' | ||
| + | 20 end; | ||
| + | 21 / | ||
| + | 1.27 seconds... | ||
| + | |||
| + | PL/SQL procedure successfully completed. | ||
| + | </ | ||
| + | |||
| + | That is pretty dramatic. The fact is that not only does this execute much faster (we spent more time PARSING our queries then actually EXECUTING them!) it will let more users use your system simultaneously. | ||
| + | |||
| + | ==== EXPLAIN PLAN ==== | ||
| + | === Usage (old school) === | ||
| If no access to the system plan_table, create your own (also to keep the plans longer than a session), run $ORACLE_HOME/ | If no access to the system plan_table, create your own (also to keep the plans longer than a session), run $ORACLE_HOME/ | ||
| - | < | + | < |
| + | explain plan | ||
| + | [[set statement_id = ' | ||
| + | [[into table_name ]] | ||
| + | for sql_statement | ||
| + | </ | ||
| - | ====See the results==== | + | === See the results === |
| - | < | + | < |
| + | select rtrim(lpad(' | ||
| + | , cost | ||
| + | , cardinality | ||
| + | from | ||
| + | connect by prior id = parent_id | ||
| + | start with id = 0 | ||
| + | </ | ||
| - | ====Usage (new school)==== | + | === Usage (new school) === |
| 1. Explain plan for the most recent SQL statement executed in the session | 1. Explain plan for the most recent SQL statement executed in the session | ||
| - | < | + | < |
| + | set pages 0 | ||
| + | select * from table(dbms_xplan.display_cursor); | ||
| + | </ | ||
| 2. Execute an explain plan command on a SELECT statement | 2. Execute an explain plan command on a SELECT statement | ||
| - | < | + | < |
| - | 3. Explain plan on a previously executed statement<br /> | + | explain plan for |
| - | Find the SQL_ID<br /> | + | select * |
| - | <code>40@@</ | + | from |
| + | , dept d | ||
| + | where d.deptno = e.deptno | ||
| + | and e.ename | ||
| + | / | ||
| + | |||
| + | set pages 0 lines 150 | ||
| + | select * from table(dbms_xplan.display); | ||
| + | </ | ||
| + | 3. Explain plan on a previously executed statement\\ | ||
| + | Find the SQL_ID\\ | ||
| + | <code> | ||
| + | select sql_id, child_number | ||
| + | from | ||
| + | where sql_text like '%<something distinctive to find the SQL statement>%'; | ||
| + | </ | ||
| Get the explain_plan | Get the explain_plan | ||
| - | < | + | < |
| + | select * from table(dbms_xplan.display_cursor(('& | ||
| + | </ | ||
| - | =====Virtual Indexes===== | + | ==== Virtual Indexes ==== |
| An index created to see if the optimiser would use it without actually having to build it | An index created to see if the optimiser would use it without actually having to build it | ||
| - | < | + | < |
| + | SQL> ALTER SESSION SET " | ||
| + | Session altered. | ||
| + | |||
| + | SQL> CREATE INDEX sh.sales_vi1 ON sh.sales(quantity_sold) NOSEGMENT; | ||
| + | Index created. | ||
| + | </ | ||
| Now re-run the explain plan and see the difference. | Now re-run the explain plan and see the difference. | ||
| - | =====Statspack===== | + | ==== Statspack ==== |
| - | If you are using Standard Edition, you cannot use the Grid utilities or to debug performance issues. You need to use the " | + | If you are using Standard Edition, you cannot use the Grid utilities or to debug performance issues. You need to use the " |
| - | [[http:// | + | [[http:// |
| Reproduced here in case the page disappears... | Reproduced here in case the page disappears... | ||
| - | < | + | < |
| - | ====References==== | + | Overview |
| - | * [[http:// | + | |
| + | STATSPACK is a performance diagnosis tool, available since Oracle8i. STATSPACK can be considered BSTAT/ | ||
| + | |||
| + | Remember to set timed_statistics to true for your instance. Setting this parameter provides timing data which is invaluable for performance tuning. | ||
| + | |||
| + | The «more is better» approach is not always better! | ||
| + | |||
| + | The single most common misuse of STATSPACK is the «more is better» approach. Often STATSPACK reports spans hours or even days. The times between the snapshots (the collection points) should, in general, be measured in minutes, not hours and never days. | ||
| + | |||
| + | The STATSPACK reports we like are from 1 5-minute intervals during a busy or peak time, when the performance is at its worst. That provides a very focused look at what was going wrong at that exact moment in time. The problem with a very large STATSPACK snapshot window, where the time between the two snapshots is measured in hours, is that the events that caused serious performance issues for 20 minutes during peak processing don't look so bad when they' | ||
| + | |||
| + | «Having a history of the good times is just as important as having a history of the bad; you need both» | ||
| + | |||
| + | Another common mistake with STATSPACK is to gather snapshots only when there is a problem. That is fine to a point, but how much better would it be to have a STATSPACK report from when things were going good to compare it with when things are bad. A simple STATSPACK report that shows a tremendous increase in physical 1/0 activity or table scans (long tables) could help you track down that missing index. Or, if you see your soft parse percentage value went from 99% to 70%, you know that someone introduced a new feature into the system that isn't using bind variables (and is killing you). Having a history of the good times is just as important as having a history of the bad; you need both. | ||
| + | |||
| + | Architecture | ||
| + | |||
| + | To fully understand the STATSPACK architecture, | ||
| + | |||
| + | UTLBSTAT - UTLESTAT | ||
| + | |||
| + | The BSTAT-ESTAT utilities capture information directly from the Oracle' | ||
| + | |||
| + | insert into stats$begin_stats select * from v$sysstat; | ||
| + | insert into stats$end_stats select * from v$sysstat; | ||
| + | |||
| + | STATSPACK | ||
| + | |||
| + | When a snapshot is executed, the STATSPACK software will sample from the RAM in-memory structures inside the SGA and transfer the values into the corresponding STATSPACK tables. These values are then available for comparing with other snapshots. | ||
| + | |||
| + | Note that in most cases, there is a direct correspondence between the v$ view in the SGA and the corresponding STATSPACK table. For example, we see that the stats$sysstat table is similar to the v$sysstat view. | ||
| + | |||
| + | SQL> desc v$sysstat; | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | |||
| + | SQL> desc stats$sysstat; | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | |||
| + | It is critical to your understanding of the STATSPACK utility that you realize the information captured by a STATSPACK snapshot is accumulated values. The information from the V$VIEWS collects database information at startup time and continues to add the values until the instance is shutdown. In order to get a meaningful elapsed-time report, you must run a STATSPACK report that compares two snapshots as shown above. It is critical to understand that a report will be invalid if the database is shut down between snapshots. This is because all of the accumulated values will be reset, causing the second snapshot to have smaller values than the first snapshot. | ||
| + | |||
| + | Installing and Configuring STATSPACK | ||
| + | |||
| + | Create PERFSTAT Tablespace | ||
| + | |||
| + | The STATSPACK utility requires an isolated tablespace to obtain all of the objects and data. For uniformity, it is suggested that the tablespace be called PERFSTAT, the same name as the schema owner for the STATSPACK tables. It is important to closely watch the STATSPACK data to ensure that the stats$sql_summary table is not taking an inordinate amount of space. | ||
| + | |||
| + | SQL> CREATE TABLESPACE perfstat | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | |||
| + | Run the Create Scripts | ||
| + | |||
| + | Now that the tablespace exists, we can begin the installation process of the STATSPACK software. Note that you must have performed the following before attempting to install STATSPACK. | ||
| + | |||
| + | Run catdbsyn.sql as SYS | ||
| + | |||
| + | Run dbmspool.sql as SYS | ||
| + | |||
| + | $ cd $ORACLE_HOME/ | ||
| + | $ sqlplus "/ as sysdba" | ||
| + | SQL> start spcreate.sql | ||
| + | |||
| + | Choose the PERFSTAT user's password | ||
| + | ----------------------------------- | ||
| + | Not specifying a password will result in the installation FAILING | ||
| + | |||
| + | Enter value for perfstat_password: | ||
| + | |||
| + | Choose the Default tablespace for the PERFSTAT user | ||
| + | --------------------------------------------------- | ||
| + | Below is the list of online tablespaces in this database which can | ||
| + | store user data. Specifying the SYSTEM tablespace for the user' | ||
| + | default tablespace will result in the installation FAILING, as | ||
| + | using SYSTEM for performance data is not supported. | ||
| + | |||
| + | Choose the PERFSTAT users' | ||
| + | in which the STATSPACK tables and indexes will be created. | ||
| + | |||
| + | TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE | ||
| + | ------------------------------ --------- ---------------------------- | ||
| + | PERFSTAT PERMANENT | ||
| + | SYSAUX PERMANENT * | ||
| + | USERS PERMANENT | ||
| + | |||
| + | Pressing < | ||
| + | tablespace (identified by *) being used. | ||
| + | |||
| + | Enter value for default_tablespace: | ||
| + | |||
| + | Choose the Temporary tablespace for the PERFSTAT user | ||
| + | ----------------------------------------------------- | ||
| + | Below is the list of online tablespaces in this database which can | ||
| + | store temporary data (e.g. for sort workareas). Specifying the SYSTEM | ||
| + | tablespace for the user's temporary tablespace will result in the | ||
| + | installation FAILING, as using SYSTEM for workareas is not supported. | ||
| + | |||
| + | Choose the PERFSTAT user's Temporary tablespace. | ||
| + | |||
| + | TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE | ||
| + | ------------------------------ --------- -------------------------- | ||
| + | TEMP TEMPORARY * | ||
| + | |||
| + | Pressing < | ||
| + | tablespace (identified by *) being used. | ||
| + | |||
| + | Enter value for temporary_tablespace: | ||
| + | |||
| + | ..... | ||
| + | ..... | ||
| + | Creating Package STATSPACK... | ||
| + | |||
| + | Package created. | ||
| + | |||
| + | No errors. | ||
| + | Creating Package Body STATSPACK... | ||
| + | |||
| + | Package body created. | ||
| + | |||
| + | No errors. | ||
| + | |||
| + | NOTE: | ||
| + | SPCPKG complete. Please check spcpkg.lis for any errors. | ||
| + | |||
| + | Check the Logfiles: spcpkg.lis, spctab.lis, spcusr.lis | ||
| + | |||
| + | Adjusting the STATSPACK Collection Level | ||
| + | |||
| + | STATSPACK has two types of collection options, level and threshold. The level parameter controls the type of data collected from Oracle, while the threshold parameter acts as a filter for the collection of SQL statements into the stats$sql_summary table. | ||
| + | |||
| + | SQL> SELECT * FROM stats$level_description ORDER BY snap_level; | ||
| + | Level 0 \tThis level captures general statistics, including rollback segment, row cache, SGA, system events, background events, session events, system statistics, wait statistics, lock statistics, and Latch information. | ||
| + | Level 5 \tThis level includes capturing high resource usage SQL Statements, along with all data captured by lower levels. | ||
| + | Level 6 \tThis level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with all data captured by lower levels. | ||
| + | Level 7 \tThis level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits, along with all data captured by lower levels. | ||
| + | Level 10 \tThis level includes capturing Child Latch statistics, along with all data captured by lower levels. | ||
| + | |||
| + | You can change the default level of a snapshot with the statspack.snap function. The i_modify_parameter => ' | ||
| + | |||
| + | SQL> exec statspack.snap(i_snap_level => 6, i_modify_parameter => ' | ||
| + | |||
| + | Create, View and Delete Snapshots | ||
| + | |||
| + | sqlplus perfstat/ | ||
| + | SQL> exec statspack.snap; | ||
| + | SQL> select name, | ||
| + | " | ||
| + | |||
| + | NAME | ||
| + | --------- ---------- ------------------- | ||
| + | AKI1 4 14.11.2004: | ||
| + | AKI1 1 13.11.2004: | ||
| + | AKI1 2 13.11.2004: | ||
| + | AKI1 3 13.11.2004: | ||
| + | |||
| + | SQL> | ||
| + | Enter the Lower and Upper Snapshot ID | ||
| + | |||
| + | Create the Report | ||
| + | |||
| + | sqlplus perfstat/ | ||
| + | SQL> | ||
| + | |||
| + | Statspack at a Glance | ||
| + | |||
| + | What if you have this long STATSPACK report and you want to figure out if everything is running smoothly? Here, we will review what we look for in the report, section by section. We will use an actual STATSPACK report from our own Oracle 10g system. | ||
| + | |||
| + | Statspack Report Header | ||
| + | |||
| + | STATSPACK report for | ||
| + | |||
| + | DB Name DB Id Instance | ||
| + | ------------ ----------- ------------ -------- ----------- --- ---------------- | ||
| + | AKI1 2006521736 AKI1 1 10.1.0.2.0 | ||
| + | |||
| + | Snap Id Snap Time Sessions Curs/Sess Comment | ||
| + | --------- ------------------ -------- --------- ------------------- | ||
| + | Begin Snap: 5 14-Nov-04 11: | ||
| + | End Snap: 6 14-Nov-04 11: | ||
| + | | ||
| + | |||
| + | Cache Sizes (end) | ||
| + | ~~~~~~~~~~~~~~~~~ | ||
| + | | ||
| + | | ||
| + | |||
| + | Note that this section may appear slightly different depending on your version of Oracle. For example, the Curs/Sess column, which shows the number of open cursors per session, is new with Oracle9i (an 8i Statspack report would not show this data). | ||
| + | |||
| + | Here, the item we are most interested in is the elapsed time. We want that to be large enough to be meaningful, but small enough to be relevant (15 to 30 minutes is OK). If we use longer times, we begin to lose the needle in the haystack. | ||
| + | |||
| + | Statspack Load Profile | ||
| + | |||
| + | Load Profile | ||
| + | ~~~~~~~~~~~~ | ||
| + | | ||
| + | Redo size: 425, | ||
| + | Logical reads: | ||
| + | Block changes: | ||
| + | | ||
| + | Physical writes: | ||
| + | User calls: | ||
| + | | ||
| + | Hard parses: | ||
| + | Sorts: | ||
| + | | ||
| + | | ||
| + | | ||
| + | |||
| + | % Blocks changed per Read: 151.59 | ||
| + | | ||
| + | |||
| + | Here, we are interested in a variety of things, but if we are looking at a " | ||
| + | |||
| + | The Hard parses (we want very few of them) | ||
| + | Executes (how many statements we are executing per second / transaction) | ||
| + | Transactions (how many transactions per second we process). | ||
| + | |||
| + | This gives an overall view of the load on the server. In this case, we are looking at a very good hard parse number and a fairly light system load (1 - 4 transactions per second is low). | ||
| + | |||
| + | Statspack Instance Efficiency Percentage | ||
| + | |||
| + | Next, we move onto the Instance Efficiency Percentages section, which includes perhaps the only ratios we look at in any detail: | ||
| + | |||
| + | Instance Efficiency Percentages (Target 100%) | ||
| + | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | ||
| + | Buffer Nowait %: 100.00 | ||
| + | Buffer | ||
| + | Library Hit | ||
| + | | ||
| + | Parse CPU to Parse Elapsd %: | ||
| + | |||
| + | | ||
| + | | ||
| + | | ||
| + | % SQL with executions> | ||
| + | % Memory for SQL w/ | ||
| + | |||
| + | The three in bold are the most important: Library Hit, Soft Parse % and Execute to Parse. All of these have to do with how well the shared pool is being utilized. Time after time, we find this to be the area of greatest payback, where we can achieve some real gains in performance. | ||
| + | |||
| + | Here, in this report, we are quite pleased with the Library Hit and the Soft Parse % values. If the library Hit ratio was low, it could be indicative of a shared pool that is too small, or just as likely, that the system did not make correct use of bind variables in the application. It would be an indicator to look at issues such as those. | ||
| + | |||
| + | OLTP System | ||
| + | |||
| + | The Soft Parse % value is one of the most important (if not the only important) ratio in the database. For a typical OLTP system, it should be as near to 100% as possible. You quite simply do not hard parse after the database has been up for a while in your typical transactional / general-purpose database. The way you achieve that is with bind variables. In a regular system like this, we are doing many executions per second, and hard parsing is something to be avoided. | ||
| + | |||
| + | Data Warehouse | ||
| + | |||
| + | In a data warehouse, we would like to generally see the Soft Parse ratio lower. We don't necessarily want to use bind variables in a data warehouse. This is because they typically use materialized views, histograms, and other things that are easily thwarted by bind variables. In a data warehouse, we may have many seconds between executions, so hard parsing is not evil; in fact, it is good in those environments. | ||
| + | |||
| + | The moral of this is ... | ||
| + | |||
| + | ... to look at these ratios and look at how the system operates. Then, using that knowledge, determine if the ratio is okay given the conditions. If we just said that the execute-to-parse ratio for your system should be 95% or better, that would be unachievable in many web-based systems. If you have a routine that will be executed many times to generate a page, you should definitely parse once per page and execute it over and over, closing the cursor if necessary before your connection is returned to the connection pool. | ||
| + | |||
| + | Statspack Top 5 Timed Events | ||
| + | |||
| + | Moving on, we get to the Top 5 Timed Events section (in Oracle9i Release 2 and later) or Top 5 Wait Events (in Oracle9i Release 1 and earlier). | ||
| + | |||
| + | Top 5 Timed Events | ||
| + | ~~~~~~~~~~~~~~~~~~ | ||
| + | Event | ||
| + | -------------------------------------------- ------------ ----------- --------- | ||
| + | CPU time 122 | ||
| + | db file sequential read | ||
| + | db file scattered read 1,174 | ||
| + | log file sequential read 342 | ||
| + | control file parallel write | ||
| + | ------------------------------------------------------------- | ||
| + | Wait Events | ||
| + | |||
| + | -> s - second | ||
| + | -> cs - centisecond - 100th of a second | ||
| + | -> ms - millisecond - 1000th of a second | ||
| + | -> us - microsecond - 1000000th of a second | ||
| + | -> ordered by wait time desc, waits desc (idle events last) | ||
| + | |||
| + | This section is among the most important and relevant sections in the Statspack report. Here is where you find out what events (typically wait events) are consuming the most time. In Oracle9i Release 2, this section is renamed and includes a new event: CPU time. | ||
| + | |||
| + | CPU time is not really a wait event (hence, the new name), but rather the sum of the CPU used by this session, or the amount of CPU time used during the snapshot window. In a heavily loaded system, if the CPU time event is the biggest event, that could point to some CPU-intensive processing (for example, forcing the use of an index when a full scan should have been used), which could be the cause of the bottleneck. | ||
| + | |||
| + | Db file sequential read - This wait event will be generated while waiting for writes to TEMP space generally (direct loads, Parallel DML (PDML) such as parallel updates. You may tune the PGA AGGREGATE TARGET parameter to reduce waits on sequential reads. | ||
| + | |||
| + | Db file scattered read - Next is the db file scattered read wait value. That generally happens during a full scan of a table. You can use the Statspack report to help identify the query in question and fix it. | ||
| + | |||
| + | SQL ordered by Gets | ||
| + | |||
| + | Here you will find the most CPU-Time consuming SQL statements | ||
| + | |||
| + | SQL ordered by Gets DB/Inst: AKI1/ | ||
| + | -> Resources reported for PL/SQL code includes the resources used by all SQL | ||
| + | | ||
| + | -> End Buffer Gets Threshold: | ||
| + | -> Captured SQL accounts for 3.1% of Total Buffer Gets | ||
| + | -> SQL reported below exceeded | ||
| + | |||
| + | | ||
| + | Buffer Gets Executions | ||
| + | --------------- ------------ -------------- ------ -------- --------- ---------- | ||
| + | | ||
| + | Module: SQL*Plus | ||
| + | create table test as select * from all_objects | ||
| + | |||
| + | Tablespace IO Stats | ||
| + | |||
| + | Tablespace | ||
| + | ------------------------------ | ||
| + | | ||
| + | Reads Reads/s Rd(ms) Blks/ | ||
| + | -------------- ------- ------ ------- ------------ -------- ---------- ------ | ||
| + | TAB 1,643 | ||
| + | UNDO | ||
| + | SYSTEM | ||
| + | STATSPACK | ||
| + | SYSAUX | ||
| + | IDX | ||
| + | USER 18 | ||
| + | ------------------------------------------------------------- | ||
| + | |||
| + | Rollback Segment Stats | ||
| + | |||
| + | ->A high value for "Pct Waits" suggests more rollback segments may be required | ||
| + | ->RBS stats may not be accurate between begin and end snaps when using Auto Undo | ||
| + | managment, as RBS may be dynamically created and dropped as needed | ||
| + | |||
| + | Trans Table | ||
| + | RBS No Gets Waits | ||
| + | ------ -------------- ------- --------------- -------- -------- -------- | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | 10 9.0 0.00 | ||
| + | ------------------------------------------------------------- | ||
| + | |||
| + | Rollback Segment Storage | ||
| + | |||
| + | -> | ||
| + | |||
| + | RBS No Segment Size Avg Active | ||
| + | ------ --------------- --------------- --------------- --------------- | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | 10 | ||
| + | ------------------------------------------------------------- | ||
| + | |||
| + | Generate Execution Plan for given SQL statement | ||
| + | |||
| + | If you have identified one or more problematic SQL statement, you may want to check the execution plan. Remember the "Old Hash Value" from the report above (1279400914), | ||
| + | |||
| + | sqlplus perfstat/ | ||
| + | SQL> @?/ | ||
| + | Enter the Hash Value, in this example: 1279400914 | ||
| + | |||
| + | SQL Text | ||
| + | ~~~~~~~~ | ||
| + | create table test as select * from all_objects | ||
| + | |||
| + | Known Optimizer Plan(s) for this Old Hash Value | ||
| + | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | ||
| + | Shows all known Optimizer Plans for this database instance, and the Snap Id's | ||
| + | they were first found in the shared pool. A Plan Hash Value will appear | ||
| + | multiple times if the cost has changed | ||
| + | -> ordered by Snap Id | ||
| + | |||
| + | First First Plan | ||
| + | Snap Id Snap Time Hash Value Cost | ||
| + | --------- --------------- ------------ ---------- | ||
| + | 6 14 Nov 04 11:26 | ||
| + | |||
| + | Plans in shared pool between Begin and End Snap Ids | ||
| + | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | ||
| + | Shows the Execution Plans found in the shared pool between the begin and end | ||
| + | snapshots specified. | ||
| + | which existed at the time the first-ever snapshot captured this plan - these | ||
| + | values often change over time, and so may not be indicative of current values | ||
| + | -> Rows indicates Cardinality, | ||
| + | -> ordered by Plan Hash Value | ||
| + | |||
| + | -------------------------------------------------------------------------------- | ||
| + | | Operation | ||
| + | -------------------------------------------------------------------------------- | ||
| + | |CREATE TABLE STATEMENT | ||
| + | |LOAD AS SELECT | ||
| + | | VIEW | ||
| + | | FILTER | ||
| + | | HASH JOIN | | ||
| + | | TABLE ACCESS FULL | ||
| + | | TABLE ACCESS FULL | ||
| + | | TABLE ACCESS BY INDEX ROWID |IND$ | ||
| + | | INDEX UNIQUE SCAN | ||
| + | | | ||
| + | | INDEX RANGE SCAN |I_OBJAUTH1 | ||
| + | | FIXED TABLE FULL |X$KZSRO | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | FIXED TABLE FULL | ||
| + | | | ||
| + | | FAST DUAL | ||
| + | -------------------------------------------------------------------------------- | ||
| + | |||
| + | Resolving Your Wait Events | ||
| + | |||
| + | The following are 10 of the most common causes for wait events, along with explanations and potential solutions: | ||
| + | |||
| + | 1. DB File Scattered Read | ||
| + | |||
| + | This generally indicates waits related to full table scans. As full table scans are pulled into memory, they rarely fall into contiguous buffers but instead are scattered throughout the buffer cache. A large number here indicates that your table may have missing or suppressed indexes. Although it may be more efficient in your situation to perform a full table scan than an index scan, check to ensure that full table scans are necessary when you see these waits. Try to cache small tables to avoid reading them in over and over again, since a full table scan is put at the cold end of the LRU (Least Recently Used) list. | ||
| + | |||
| + | 2. DB File Sequential Read | ||
| + | |||
| + | This event generally indicates a single block read (an index read, for example). A large number of waits here could indicate poor joining orders of tables, or unselective indexing. It is normal for this number to be large for a high-transaction, | ||
| + | |||
| + | 3. Free Buffer | ||
| + | |||
| + | This indicates your system is waiting for a buffer in memory, because none is currently available. Waits in this category may indicate that you need to increase the DB_BUFFER_CACHE, | ||
| + | |||
| + | 4. Buffer Busy | ||
| + | |||
| + | This is a wait for a buffer that is being used in an unshareable way or is being read into the buffer cache. Buffer busy waits should not be greater than 1 percent. Check the Buffer Wait Statistics section (or V$WAITSTAT) to find out if the wait is on a segment header. If this is the case, increase the freelist groups or increase the pctused to pctfree gap. If the wait is on an undo header, you can address this by adding rollback segments; if it's on an undo block, you need to reduce the data density on the table driving this consistent read or increase the DB_CACHE_SIZE. If the wait is on a data block, you can move data to another block to avoid this hot block, increase the freelists on the table, or use Locally Managed Tablespaces (LMTs). If it's on an index block, you should rebuild the index, partition the index, or use a reverse key index. To prevent buffer busy waits related to data blocks, you can also use a smaller block size: fewer records fall within a single block in this case, so it's not as " | ||
| + | |||
| + | 5. Latch Free | ||
| + | |||
| + | Latches are low-level queuing mechanisms (they' | ||
| + | |||
| + | 6. Enqueue | ||
| + | |||
| + | An enqueue is a lock that protects a shared resource. Locks protect shared resources, such as data in a record, to prevent two people from updating the same data at the same time. An enqueue includes a queuing mechanism, which is FIFO (first in, first out). Note that Oracle' | ||
| + | |||
| + | 7. Log Buffer Space | ||
| + | |||
| + | This wait occurs because you are writing the log buffer faster than LGWR can write it to the redo logs, or because log switches are too slow. To address this problem, increase the size of the log files, or increase the size of the log buffer, or get faster disks to write to. You might even consider using solid-state disks, for their high speed. | ||
| + | |||
| + | 8. Log File Switch | ||
| + | |||
| + | All commit requests are waiting for " | ||
| + | |||
| + | 9. Log File Sync | ||
| + | |||
| + | When a user commits or rolls back data, the LGWR flushes the session' | ||
| + | |||
| + | 10. Idle Event. | ||
| + | |||
| + | There are several idle wait events listed after the output; you can ignore them. Idle events are generally listed at the bottom of each section and include such things as SQL*Net message to/from client and other background-related timings. Idle events are listed in the stats$idle_event table. | ||
| + | |||
| + | Remove STATSPACK from the Database | ||
| + | |||
| + | After a STATSPACK session you want to remove the STATSPACK tables. | ||
| + | |||
| + | sqlplus "/ as sysdba" | ||
| + | SQL> @?/ | ||
| + | SQL> DROP TABLESPACE perfstat INCLUDING CONTENTS AND DATAFILES; | ||
| + | </ | ||
| + | === References === | ||
| + | * [[http:// | ||
| + | |||
| + | ==== Help with Oracle Trace File Analyser (TFA) ==== | ||
| + | |||
| + | find current running sqls | ||
| + | |||
| + | Find active sessions in oracle database | ||
| + | Find waitevents in database | ||
| + | Find sessions generating undo | ||
| + | Find the temp usage of the sessions | ||
| + | Find sessions generating lot of redo | ||
| + | Monitor tablespace usage | ||
| + | Script to Monitor undo tablespace usage | ||
| + | Monitor TEMP tablespace usage | ||
| + | Find blocking sessions | ||
| + | Find long running operations | ||
| + | Find locks present in database | ||
| + | Find queries triggered from a procedure | ||
| + | Get sid from os pid | ||
| + | Kill all sessions of a sql_id | ||
| + | kill all session of a user | ||
| + | get parallel query detail | ||
| + | Kill snipped session in db | ||
| + | Top Query with high elapsed time | ||
| + | Monitor parallel queries | ||
| + | Find the locked objects | ||
| + | Check open cursors | ||
| + | Session login history from ASH | ||
| + | Buffer Cache hit ratio | ||
| + | Find top disk_reads by an user | ||
| + | Get os pid from sid | ||
| + | Get active sid of a pl/sql object | ||
| + | Find buffer cache usage | ||
| + | Monitor rollback transations | ||
| + | Find column usage statistics | ||
| + | Get background process details | ||
| + | oracle db is 32bit or 64 bit? | ||
| + | oracle license usage info | ||
| + | db optimizer processing rate | ||
| + | Purge recyclebin in database | ||
| + | DB MONITORING | ||
| + | |||
| + | xplain plan of sql_id from cursor | ||
| + | |||
| + | xplain plan of sql_id from AWR | ||
| + | Get sql_text from sid | ||
| + | xplain plan of a sql statement | ||
| + | xplain plan of a sql baseline | ||
| + | Get bind values of a sql_id | ||
| + | Flush a sql query from cursor | ||
| + | Enable trace for a sql_id | ||
| + | 10053 OPTIMIZER TRACE | ||
| + | Enable trace for a session | ||
| + | Tracing all session of a user | ||
| + | Enable tracing for a listener | ||
| + | execution detail of a sql_id in cursor | ||
| + | Pga usage by sessions | ||
| + | segments with high physical read | ||
| + | I/O usage of each tempfile | ||
| + | Current SGA usage | ||
| + | Top running queries from ASH | ||
| + | Find blocking sessions from ASH | ||
| + | Top cpu consuming sessions | ||
| + | Sessions holding library cache lock | ||
| + | Objects locked by library cache | ||
| + | Sessions accessing an object | ||
| + | Sqls doing full table scan | ||
| + | Dictionary cache hit ratio | ||
| + | Top sql queries using literal values | ||
| + | Objects causing flushing of shared pool | ||
| + | Latch type and sql hash value | ||
| + | Objects causing latch contention | ||
| + | Queries causing high physical read | ||
| + | Mutex sleep in database | ||
| + | Sql tuning advisor for sql_id from cursor | ||
| + | run sga target advisory | ||
| + | Run shared pool advisory | ||
| + | Generate addm report | ||
| + | DATABASE INFO | ||
| + | |||
| + | Get redo log member info | ||
| + | |||
| + | Get DDL of all tablespaces | ||
| + | Get DDL of all privileges granted to user | ||
| + | Get size of the database | ||
| + | View hidden parameter setting | ||
| + | Get ACL details in database | ||
| + | Archive generation per hour | ||
| + | Find active transactions in db | ||
| + | Find who locked your account | ||
| + | Find duplicate rows in table | ||
| + | Database growth per month | ||
| + | generate resize datafile script without ORA-03297 error | ||
| + | Get database uptime | ||
| + | Scn to timestamp and viceversa | ||
| + | Disable/ | ||
| + | Ger row_count of all the tables of a schema | ||
| + | Spool sql query output to HTML | ||
| + | Monitor index usage | ||
| + | Get installed sqlpatches in db | ||
| + | Cleanup orphaned datapump jobs | ||
| + | Get Alert log location in db | ||
| + | Installed RDBMS components | ||
| + | Characterset info of database | ||
| + | View/modify AWR retention | ||
| + | Find optimal undo retention size | ||
| + | Purge old awr snapshots | ||
| + | Modify moving window size | ||
| + | Open database link information | ||
| + | utilization of current redo log ( in % ) | ||
| + | Generate multiple AWR report | ||
| + | Table not having index on fk column | ||
| + | Get cpu memory info of db server | ||
| + | Get database incarnation info | ||
| + | View timezone info in db | ||
| + | </ | ||
| + | |||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | * [[https:// | ||
| + | |||
| + | * [[https:// | ||
| + | * [[https:// | ||
performance.1544130327.txt.gz · Last modified: 2018/12/06 21:05 by 91.177.234.129