No question at this time
DBA Top 10
1 B. Vroman 14600
2 M. Cadot 11000
3 J. Schnackenberg 8200
4 T. Boles 7950
5 A. Kavsek 6200
6 M. Hidayathullah ... 2200
7 G. Lambregts 1100
8 T. P 1000
9 P. Wisse 900
10 B. Derous 500
10 . Lauri 500
10 R. Pattyn 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48286
Total active users1591
Act. users last 24h3
Act. users last hour0
Registered user hits last week242
Registered user hits last month1121
Go up

select from v$sql hangs
Next thread: Tidying up after Critical Patch Updates
Prev thread: rman backup failures

Message Score Author Date
Hi, I have a server with a "melting pot" databa...... Alain Bourgeois Jul 03, 2018, 17:22
Hello Alain, - have a look at https://community...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Bruno Vroman Jul 04, 2018, 08:12
2. upgrade to 12: not sure all apps will be compat...... Alain Bourgeois Jul 04, 2018, 11:37
<pre> SQL> select * from v$memory_dynamic_compone...... Alain Bourgeois Jul 04, 2018, 11:37
Recently learned about v$sqlstats. (available sind...... Rob Pattyn Jul 05, 2018, 11:05

Follow up by mail Click here


Subject: select from v$sql hangs
Author: Alain Bourgeois, Belgium
Date: Jul 03, 2018, 17:22, 138 days ago
Os info: Linux 2.6.32-696.13.2.el6.x86_64
Oracle info: 11.2.0.4.0 standard edition
Message: Hi,

I have a server with a "melting pot" database: import from EE tablespaces, import from EE exports, database links to XE - and other -databases, ...
Load is fine (<1.5). But users sometime complain about slowlyness.

Problem:
in sqlplus / as sysdba on server, when I run:
select * from (
SELECT disk_reads/executions, executions, buffer_gets, disk_reads, disk_reads/executions as cost, first_load_time,s.sql_fulltext
FROM v$sql s
WHERE EXECUTIONS>0
ORDER BY disk_reads/executions desc)
where rownum<50;

* No answer for 1 hour now,
* load goes to 100%


Any hint??????

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: select from v$sql hangs
Author: Bruno Vroman, Belgium
Date: Jul 04, 2018, 08:12, 137 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hello Alain,

- have a look at https://community.oracle.com/thread/3879893 "Slow access on v$sql"

- time to upgrade to 12?

Best regards,

Bruno Vroman
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: select from v$sql hangs
Author: Alain Bourgeois, Belgium
Date: Jul 04, 2018, 11:37, 137 days ago
Message: 2. upgrade to 12: not sure all apps will be compatible. It is a production system. And se2 limits the cores available to oracle.
1. I looked at the document.
Memory allocated is different

SQL> select name, bytes from v$sgastat
where pool = 'shared pool' and bytes > 30000000
order by bytes desc;

NAME BYTES
-------------------------- ----------
SQLA 4764189480
free memory 4319622352
KGLH0 3472699776
KGLHD 753626816
db_block_hash_buckets 186650624
KGLDA 181800752
kglsim object batch 104022744
kglsim heap 61583360

8 rows selected.
SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0

SQL> show sga

Total System Global Area 4,2758E+10 bytes
Fixed Size 2262656 bytes
Variable Size 1,8388E+10 bytes
Database Buffers 2,4293E+10 bytes
Redo Buffers 74420224 bytes
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: select from v$sql hangs
Author: Alain Bourgeois, Belgium
Date: Jul 04, 2018, 11:37, 137 days ago
Message:

SQL> select * from v$memory_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPE GRANULE_SIZE
---------------------------------------------------------------- ------------ ---------- ---------- ------------------- ---------- ------------- --------- -------- ------------
shared pool 1,4093E+10 1,4093E+10 1,4093E+10 0 0 STATIC 134217728
large pool 402653184 402653184 402653184 0 0 STATIC 134217728
java pool 402653184 402653184 402653184 0 0 STATIC 134217728
streams pool 134217728 134217728 134217728 0 0 STATIC 134217728
SGA Target 3,9594E+10 3,9594E+10 3,9594E+10 0 0 STATIC 134217728
DEFAULT buffer cache 2,3757E+10 2,3757E+10 2,3757E+10 0 0 INITIALIZING 134217728
KEEP buffer cache 0 0 0 0 0 STATIC 134217728
RECYCLE buffer cache 0 0 0 0 0 STATIC 134217728
DEFAULT 2K buffer cache 0 0 0 0 0 STATIC 134217728
DEFAULT 4K buffer cache 0 0 0 0 0 STATIC 134217728
DEFAULT 8K buffer cache 0 0 0 0 0 STATIC 134217728
DEFAULT 16K buffer cache 0 0 0 0 0 STATIC 134217728
DEFAULT 32K buffer cache 0 0 0 0 0 STATIC 134217728
Shared IO Pool 536870912 536870912 536870912 536870912 0 STATIC 134217728
PGA Target 3355443200 3355443200 3355443200 0 0 STATIC 134217728
ASM Buffer Cache 0 0 0 0 0 STATIC 134217728


granule size is 134M.


The lowest allocations are:
kglsim heap 61583360
Fixed Size 2262656 bytes


There is no %NO ACCESS% in any pool (no matter size) in v$sgastat
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: select from v$sql hangs
Author: Rob Pattyn, Belgium
Date: Jul 05, 2018, 11:05, 136 days ago
Message: Recently learned about v$sqlstats. (available sinds oracle 10?)
https://artofdba.com/oracle-performance-tuning-vsqlstats/
Might be faster than v$sql
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here