--To check the free memory of the shared pool
SELECT * FROM v$sgastat WHERE NAME = 'free memory';
--To flush the shared pool
Alter system flush shared_pool;
Tuning
select * from v$sqlarea;
set autotrace traceonly explain
SQLPlus DBMS_XPLAN
EXPLAIN PLAN SET STATEMENT_ID = 'R1' FOR
SELECT * FROM USER_TABLES;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null,'R1','typical'));
Autotrace defination:
recursive calls = basically sql performed on behalf of your sql. So, if we had to PARSE
the query for example, we might have had to run some other queries to get data dictionary
info. that would be recursive calls.
db block gets = blocks gotten in "current" mode. That is, blocks gotten as they exist
right now. You'll see these for full table scans (segment headers are read in current
mode) and modification statements (we modify the block as it exists "right now")
consistent gets = blocks gotten in consistent read mode. This is the mode we read blocks
in with a select for example. Also, when you do a searched UPDATE/DELETE, we read the
blocks in consistent read mode and then get the block in current mode to actually do the
modification. A select for update will do this as well.
physical reads = self explanatory, physical IO
redo size = self explanatory -- amount of redo generated
sorts (memory)/(disk) -- sorts done.
LISTAGG (measure_expr [, 'delimiter_expr']) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
with rb as(
select 500 population, 'China' nation ,'Guangzhou' city from dual union all
select 1500 population, 'China' nation ,'Shanghai' city from dual union all
select 500 population, 'China' nation ,'Beijing' city from dual union all
select 1000 population, 'USA' nation ,'New York' city from dual union all
select 500 population, 'USA' nation ,'Bostom' city from dual union all
select 500 population, 'Japan' nation ,'Tokyo' city from dual
)
select population,
nation,
city,
listagg(city,',') within GROUP (order by city) over (partition by nation) rank
from rb;
with temp as(
select 'China' nation ,'Guangzhou' city from dual union all
select 'China' nation ,'Shanghai' city from dual union all
select 'China' nation ,'Beijing' city from dual union all
select 'USA' nation ,'New York' city from dual union all
select 'USA' nation ,'Bostom' city from dual union all
select 'Japan' nation ,'Tokyo' city from dual
)
select nation,listagg(city,',') within GROUP (order by city)
from temp
group by nation ;
clean session cache
ALTER SESSION SET events 'immediate trace name flush_cache';
Get the store procedure body
select text from user_source where name ='xx';
List the procedures
select * from User_Procedures;
select * from sys.aux_stats$;
Indexes
select index_name, index_type from user_indexes where index_name='XX';
select * from all_ind_columns where index_name =
Database parameters
select * from v$parameter where upper(name) like upper('parallel_max_servers');
select * from v$parameter where upper(name) like upper('processes');
select * from v$parameter where upper(name) like upper('parallel_force_local');
select * from v$parameter where upper(name) like upper('open_cursors');
select * from v$parameter where upper(name) like upper('MEMORY_MAX_TARGET');
select * from v$parameter where upper(name) like upper('MEMORY_TARGET');
select * from v$parameter where upper(name) like upper('db_keep_cache_size');
select * from v$parameter where upper(name) like upper('parallel_max_servers');
select * from v$parameter where upper(name) like upper('processes');
select * from v$parameter where upper(name) like upper('parallel_force_local');
select * from v$parameter where upper(name) like upper('open_cursors');
select * from v$parameter where upper(name) like upper('MEMORY_MAX_TARGET');
select * from v$parameter where upper(name) like upper('MEMORY_TARGET');
select * from v$parameter where upper(name) like upper('db_keep_cache_size');
show parameter process
select * from v$process;
show parameter process;
select * from user_indexes where table_name = '' ;
select * from user_indexes where index_name = 'IDX_SY_DELEGATION_USR_FUN$1';
describe bj_sysadmin;
select * from user_indexes where upper(table_name) like upper('bj_sysadmin');
select POOL, round(bytes/1024/1024,0) FREE_MB
from v$sgastat
where name like '%free memory%';
select POOL, round(bytes/1024/1024,0) FREE_MB
from v$sgastat
where name like '%free memory%';
Date format
DD-MON-RR HH.MI.SSXFF AM
Cursor
/* check max cluster */
SELECT t.machine, t.user_name, t.sql_id, sql.sql_fulltext, t.total
FROM (
SELECT s.machine, oc.user_name, oc.sql_id, count(1) AS total
FROM v$open_cursor oc, v$session s
WHERE oc.sid = s.sid
GROUP BY user_name, oc.sql_id, machine,oc.sql_id
HAVING COUNT(1) > 2
) t
LEFT JOIN v$sql sql ON t.sql_id = sql.sql_id
WHERE sql.sql_fulltext like '%PAL_ADMIN%'
ORDER BY total DESC;
/* checking the cursor number and cursor SQL - start */
select o.sid, osuser, machine, count(*) num_curs
from v$open_cursor o, v$session s
where o.sid=s.sid
group by o.sid, osuser, machine
order by machine, num_curs desc;
select q.sql_text, q.LAST_ACTIVE_TIME
from v$open_cursor o, v$sql q
where q.hash_value=o.hash_value and o.sid in (397, 493)
order by q.LAST_ACTIVE_TIME desc;
/* checking the cursor number and cursor SQL - end */
Check session lock
/*check lock */
SELECT
(SELECT username FROM gv$session WHERE gv$session.sid = a.sid and rownum = 1) AS blocker,
a.sid,
sa.machine,
sqla.sql_id,
sqla.sql_fulltext,
sqla.elapsed_time,
'is blocking',
(SELECT username FROM gv$session WHERE gv$session.sid = b.sid and rownum = 1) AS blockee,
b.sid,
sb.machine,
sqlb.sql_id,
sqlb.sql_fulltext,
sqlb.elapsed_time
FROM gv$lock a
INNER JOIN gv$lock b ON a.id1 = b.id1 AND a.id2 = b.id2
INNER JOIN gv$session sa ON sa.sid = a.sid
INNER JOIN gv$session sb ON sb.sid = b.sid
LEFT JOIN gv$sqlarea sqla ON sa.sql_hash_value = sqla.hash_value AND sa.sql_address = sqla.address
LEFT JOIN gv$sqlarea sqlb ON sb.sql_hash_value = sqlb.hash_value AND sb.sql_address = sqlb.address
WHERE a.block = 1
AND b.request > 0
ORDER BY a.sid ASC, b.sid ASC;
invalid hex number
utl_raw.cast_to_raw('(BLOB)')
check constraint
/*constraint*/
SELECT
cons.constraint_name AS constraint_name,
source.table_name AS src_table,
source.column_name AS src_column,
destination.table_name AS dest_table,
destination.column_name AS dest_column
FROM user_constraints cons
LEFT JOIN user_cons_columns source ON source.constraint_name = cons.constraint_name
LEFT JOIN user_cons_columns destination ON destination.constraint_name = cons.r_constraint_name
WHERE cons.constraint_type = 'R'
AND (source.table_name = UPPER('rp_run_param') OR destination.table_name = UPPER('rp_run_param'))
ORDER BY cons.constraint_name ASC, source.table_name ASC,
source.column_name ASC, destination.table_name ASC, destination.column_name ASC;
AWR report Generation
SELECT * FROM dba_hist_snapshot ORDER BY begin_interval_time DESC;
set pagesize 0;
set echo off heading on underline on;
set lines 1500;
set termout on;
column inst_num heading "Inst Num" format 99999;
column inst_name heading "Instance" format a12;
column dbid heading "DB Id" format 9999999999 just c;
SPOOL "C:/temp_awr/CLNTSPH2_1_20153108_1698_1699.HTML"
SELECT output
FROM TABLE(dbms_workload_repository.awr_report_HTML(2640918804,1,1698,1699));
SPOOL OFF
https://www.krenger.ch/blog/ora-00845-memory_target-not-supported-on-this-system/
https://www.kernel.org/doc/Documentation/filesystems/tmpfs.txt