SQL:占用CPU资源最多的SQL语句
Command:
select * from (select sql_text,buffer_gets,disk_reads,sorts,cpu_time/1000000 cpu_sec,executions,rows_processed from v$sqlstats order by cpu_[……]
Adamhuan's Data Center - 【逻辑驱动数据】
数据玩物、代码屋、1/0游戏:(零和博弈)/ 禅宗意志 / 规则战争 / 解放数据力量 / 技术的飞速发展并没有改变这个世界,因为,这个世界从没有变,它只是越来越趋近于它本来的模样。
Command:
select * from (select sql_text,buffer_gets,disk_reads,sorts,cpu_time/1000000 cpu_sec,executions,rows_processed from v$sqlstats order by cpu_[……]
今天看一本书的时候,看到原来Linux的别名还可以这么使用,… 作者的想法比较独特。
具体如下:
1.准备命令:
通过Linux的别名(ALIAS)制作常用命令的快捷方式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[oracle@ora12c - allah:~]$ alias tail_alert_log_allah='tail -f /u01/app/oracle/diag/rdbms/allah/allah/trace/alert_allah.log' [oracle@ora12c - allah:~]$ alias call_sqlplus='sqlplus / as sysdba' [oracle@ora12c - allah:~]$ alias start_database='echo "startup;" | call_sqlplus' [oracle@ora12c - allah:~]$ alias shutdown_database='echo "shutdown immediate;" | call_sqlplus' [oracle@ora12c - allah:~]$ alias list_oracle_process='ps -ef | grep --color ora_' [oracle@ora12c - allah:~]$ alias list_remote_connect_lsnr_process='ps -ef | grep --color LOCAL=NO' [oracle@ora12c - allah:~]$ [oracle@ora12c - allah:~]$ alias alias call_sqlplus='sqlplus / as sysdba' alias l.='ls -d .* --color=tty' alias list_oracle_process='ps -ef | grep --color ora_' alias list_remote_connect_lsnr_process='ps -ef | grep --color LOCAL=NO' alias ll='ls -l --color=tty' alias ls='ls --color=tty' alias shutdown_database='echo "shutdown immediate;" | call_sqlplus' alias start_database='echo "startup;" | call_sqlplus' alias tail_alert_log_allah='tail -f /u01/app/oracle/diag/rdbms/allah/allah/trace/alert_allah.log' alias which='alias | /usr/bin/which --tty-only --read-alias --show-dot --show-tilde' [oracle@ora12c - allah:~]$ [oracle@ora12c - allah:~]$ |
2.使用:
关闭数据库:
[crayon-6[……]
除了Linux的命令提示符可以自定义之外,Oracle的SQL*Plus的命令提示符也是可以自定义的。
关于它的设定方法有两种:
一、通过SQL*Plus的自定义提示符变量设置
具体配置如下所示:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[oracle@ora12c - allah:~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 28 18:41:37 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select name from v$database; NAME --------- ALLAH SQL> show user USER is "SYS" SQL> set sqlprompt '&_USER.@&_CONNECT_IDENTIFIER. &_PRIVILEGE:&_DATE> ' SYS@allah AS SYSDBA:28-OCT-14> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ora12c - allah:~]$ |
正如上面的LOG所示:s[……]
如题所示,这其实是一个非常常见的需求。
具体配置如下所示:
1 2 3 4 5 |
[root@ora12c ~]# su - oracle [oracle@ora12c ~]$ export PS1='[\u@\h - ${ORACLE_SID}:\w]$ ' [oracle@ora12c - yayu:~]$ cd /u01 [oracle@ora12c - yayu:/u01]$ export ORACLE_SID=amy [oracle@ora12c - amy:/u01]$ |
————————————————
Ending。[……]
通过下面的SQL可以查看到SGA中各个组成部分的细节,Oracle的SGA的结构随着不同的Oracle版本一直在或多或少的变化。
下面的样例所在的数据库版本为:Oracle 12.1.0.2.0。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SQL> desc v$sgastat; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- POOL VARCHAR2(12) NAME VARCHAR2(26) BYTES NUMBER CON_ID NUMBER SQL> select pool,count(*) from v$sgastat group by pool order by pool; POOL COUNT(*) ------------ ---------- java pool 1 large pool 2 shared pool 1117 4 SQL> |
——————————————[……]