Notice: Undefined index: HTTP_ACCEPT_ENCODING in C:\server\web\www.showyounger.com\s-system.php on line 2
Oracle top sql | 小样儿(ShowYounger)
小样儿,顶呱呱!!![http://www.showyounger.com]
-
- select buffer_gets, sql_text
- from (select sql_text,
- buffer_gets,
- dense_rank() over
-
- (order by buffer_gets desc) buffer_gets_rank
- from v$sql)
- where buffer_gets_rank <= 100;
-
- select disk_reads, sql_text
- from (select sql_text,
- disk_reads,
- dense_rank() over
-
- (order by disk_reads desc) disk_reads_rank
- from v$sql)
- where disk_reads_rank <= 100;
-
- select sql_text, executions
- from (select sql_text,
- executions,
- rank() over(order
-
- by executions desc) exec_rank
- from v$sql)
- where exec_rank <= 10;
-
-
- select b.username username,
- a.disk_reads reads,
- a.executions
-
- exec,
- a.disk_reads / decode(a.executions, 0, 1, a.executions) rds_exec_ratio,
- a.sql_text statement
- from v$sqlarea a, dba_users b
- where a.parsing_user_id = b.user_id
- and a.disk_reads > 100000
- order by a.disk_reads desc;
-
-
- SELECT EXECUTIONS,
- DISK_READS,
- BUFFER_GETS,
- ROUND((BUFFER_GETS - DISK_READS) / BUFFER_GETS, 2) Hit_radio,
- ROUND(DISK_READS / EXECUTIONS, 2) Reads_per_run,
- SQL_TEXT
- FROM V$SQLAREA
- WHERE EXECUTIONS > 0
- AND BUFFER_GETS > 0
- AND (BUFFER_GETS - DISK_READS) / BUFFER_GETS < 0.8
- ORDER BY 4 DESC;
-
-
- select sql_text,
- operation_type,
- policy,
- (last_memory_used / 1024 / 1024),
- last_execution,
- last_tempseg_size
- from v$sql i, v$sql_workarea a
- where i.hash_value = a.hash_value
- order by 4 desc;
-
-
- select username,sid,opname,
- round(sofar*100 / totalwork,0) || '%' as progress,
- time_remaining,sql_text
- from v$session_longops , v$sql
- where time_remaining <> 0
- and sql_address = address
- and sql_hash_value = hash_value ;
-
-
-
- select * /*sid,
- p1,
- p1raw,
- p2,
- p2raw,
- p3,
- p3raw,
- wait_time,
- seconds_in_wait,
- state,
- event*/
- from v$session_wait
- where event not in
- ('AQ Proxy Cleanup Wait', 'ASM background timer', 'DIAG idle wait',
- 'EMON idle wait', 'KSV master wait', 'LNS ASYNC archive log',
- 'LNS ASYNC dest activation', 'LNS ASYNC end of log',
- 'LogMiner: client waiting for transaction',
- 'LogMiner: slave waiting for activate message',
- 'LogMiner: wakeup event for builder',
- 'LogMiner: wakeup event for preparer',
- 'LogMiner: wakeup event for reader', 'Null event',
- 'PX Deq Credit: need buffer', 'PX Deq Credit: send blkd',
- 'PX Deq: Execute Reply', 'PX Deq: Execution Msg',
- 'PX Deq: Par Recov Execute', 'PX Deq: Signal ACK',
- 'PX Deq: Table Q Normal', 'PX Deq: Table Q Sample', 'PX Deque wait',
- 'PX Idle Wait', 'Queue Monitor Shutdown Wait',
- 'Queue Monitor Slave Wait', 'Queue Monitor Wait',
- 'SQL*Net message from client', 'SQL*Net message to client',
- 'SQL*Net more data from client',
- 'STREAMS apply coord waiting for slave message',
- 'STREAMS apply slave idle wait',
- 'STREAMS apply slave waiting for coord message',
- 'STREAMS capture process filter callback wait for ruleset',
- 'STREAMS fetch slave waiting for txns',
- 'STREAMS waiting for subscribers to catch up',
- 'Streams AQ: RAC qmn coordinator idle wait',
- 'Streams AQ: deallocate messages from Streams Pool',
- 'Streams AQ: delete acknowledged messages',
- 'Streams AQ: qmn coordinator idle wait',
- 'Streams AQ: qmn slave idle wait',
- 'Streams AQ: waiting for messages in the queue',
- 'Streams AQ: waiting for time management or cleanup tasks',
- 'Streams fetch slave: waiting for txns', 'class slave wait',
- 'client message', 'dispatcher timer', 'gcs for action',
- 'gcs remote message', 'ges remote message', 'i/o slave wait',
- 'jobq slave wait', 'knlqdeq', 'lock manager wait for remote message',
- 'master wait', 'null event', 'parallel query dequeue', 'pipe get',
- 'pmon timer', 'queue messages', 'rdbms ipc message', 'slave wait',
- 'smon timer', 'virtual circuit status', 'wait for activate message',
- 'wait for unread message on broadcast channel',
- 'wakeup event for builder', 'wakeup event for preparer',
- 'wakeup event for reader', 'wakeup time manager');
-
- select sql_text
- from v$sqltext_with_newlines st, v$session se
- where st.address = se.sql_address
- and st.hash_value = se.sql_hash_value
- and se.sid =1646
- order by piece;
-
- select * from dba_hist_snapshot a order by a.snap_id desc;
-
-
- select * from v$database;
-
-
- SELECT dbms_advisor.get_task_report('turning02', 'TEXT', 'ALL')
- FROM DUAL;
-
-
- begin
- dbms_workload_repository.create_snapshot('TYPICAL');
- end;
- /
-
-
- DECLARE task_name VARCHAR2(30) := 'turning02';
- task_desc VARCHAR2(30) := 'turning02';
- task_id NUMBER;
- BEGIN
- dbms_advisor.create_task('ADDM', task_id, task_name, task_desc, null);
- dbms_advisor.set_task_parameter(task_name, 'START_SNAPSHOT', 快照id-开始);
- dbms_advisor.set_task_parameter(task_name, 'END_SNAPSHOT', 快照id-结束);
- dbms_advisor.set_task_parameter(task_name, 'INSTANCE', 1);
- dbms_advisor.set_task_parameter(task_name, 'DB_ID', ******);
- dbms_advisor.execute_task(task_name);
- END;
- /
-
-
- DECLARE my_task_name VARCHAR2(30);
- my_sqltext CLOB;
- BEGIN
- my_sqltext := '你的sql语句';
- my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => my_sqltext,
- user_name => 'STAT_GW',
- scope => 'COMPREHENSIVE',
- time_limit => 60,
- task_name => 'tuning_sql_test',
- description => 'Task to tune a query ');
- DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'tuning_sql_test');
- END;
- /