really *reuse* a CREATE statement,
because corresponding object has to be dropped before, causing dependent
library cache structures invalidated.
Btw, in v$sqlarea, there is also one more
option for optimizer_mode - MULTIPLE CHILDS
PRESENT which states that you should go to v$sql to check
I noticed in an 9.2 instance that a number of entries in V$SQL, V$SQLAREA
showed up with OPTIMIZER_MODE=NONE [there were others with CHOOSE]
I can understand that it might be NONE if someone has done an ANALYZE or
DBMS_STATS
or executed DDL and the SQLs are invalidated.
But do you normally
alidated.
Btw, in v$sqlarea, there is also one more "option"
for optimizer_mode - "MULTIPLE CHILDS PRESENT" which states that you should
go to v$sql to check individual optimizer modes (I prefer v$sql over v$sqlarea
anyway due performance reasons and better granularity...)
For c
Thanks Mladen.
So, I was looking at the wrong place. Since I was referring to the column
elapsed_time in v$sqlarea, I expected to find it documented under
v$sqlarea. I did not have the foresight or the hindsight to look at v$sql :
-)
Thanks
Raj
--
Please see the official ORACLE-L FAQ: http
This is a good time to mention that it's a lot better idea to query
V$SQL than to query V$SQLAREA when you really only need what's in V$SQL.
It's less workload to use V$SQL, and therefore it's more scalable.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos
What's elapsed_time in v$sqlarea? I believe this was introduced in Oracle
9i. What is the unit for it? Strangely, the Oracle 9i Server Reference
manual seems to have missed this altogether.
I executed a query, the execution time of which was approximately 9
seconds. But when I look up v$sqlarea
All my e-mail was sys$hosed last night so could you please resend any and
all responses to this since last night.
Anybody run into a situation where you see optimizer_mode equal
multiple_children_present when the optimizer_mode is set to choose in the
init*.ora file ?
If so, did you follow up
What's elapsed_time in v$sqlarea? I believe this was introduced in Oracle
9i. What is the unit for it? Strangely, the Oracle 9i Server Reference
manual seems to have missed this altogether.
I executed a query, the execution time of which was approximately 9
seconds. But when I look up v$sqlarea
. This is not
the hash value for the SQL statement. If CURSOR_SHARING is not used, the value
is 0.
LAST_LOAD_TIME
VARCHAR2(19)
On 2003.08.01 00:44, [EMAIL PROTECTED] wrote:
What's elapsed_time in v$sqlarea? I believe this was introduced in Oracle
9i. What
I know it is not usual, but because ASCII version of the note looked very
ugly, I decided to attach HTML. You can start with stoning as soon as I
say Jehova.
On 2003.07.31 17:44, [EMAIL PROTECTED] wrote:
What's elapsed_time in v$sqlarea? I believe this was introduced in Oracle
9i. What is the unit
Anybody run into a situation where you see optimizer_mode equal
multiple_children_present when the optimizer_mode is set to choose in the
init*.ora file ?
If so, did you follow up on it and try and determine why this was the case
and your results ? There is very little information on
I'm suffering from a senior moment.
The question is at the every bottom.
SQL select sql_text from v$sqlarea sa where buffer_gets 1
SQL_TEXT
SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P
bottom.
SQL select sql_text from v$sqlarea sa where buffer_gets 1
SQL_TEXT
--
--
SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID
= :p1 AN
D P.ID = RP.PART AND RP.ACTIVE = 'Y
Join sql_address from v$session to address from v$sqlarea .
Regards,
Denny
Quoting [EMAIL PROTECTED]:
I'm suffering from a senior moment.
The question is at the every bottom.
SQL select sql_text from v$sqlarea sa where buffer_gets 1
SQL_TEXT
Perhaps this is what you're looking for?
select sql_text ,sid, username, osuser, logon_time
from v$sqlarea sa, v$session ss
where sa.buffer_gets 1-- that's a lot of gets!
and sa.hash_value = ss.sql_hash_value;
HTH! GL! :)
Rich
Rich JesseSystem
How about:
SELECT a.sid,
a.username,
c.disk_reads,
c.buffer_gets,
c.sorts,
c.executions,
c.rows_processed,
c.sql_text
FROM v$session a,
v$sqlarea c
WHERE a.sid = sessid
AND a.sql_address = c.address
-- AND a.SQL_HASH_VALUE
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, March 12, 2003 9:18 AM
I'm suffering from a senior moment.
The question is at the every bottom.
SQL select sql_text from v$sqlarea sa where buffer_gets 1
SQL_TEXT
FROM v$sqlarea sa
4 --, v$session s
5 where buffer_gets 1
6 --sa.address = s.sql_address
7 --and sa.hash_value = s.sql_hash_value
8*--and s.sid = 173
[EMAIL PROTECTED] /
ADDRESS HASH_VALUE BUFFER_GETS
-- ---
SQL_TEXT
[EMAIL PROTECTED] wrote:
I'm suffering from a senior moment.
The question is at the every bottom.
SQL select sql_text from v$sqlarea sa where buffer_gets 1
SQL_TEXT
SELECT RP.RELPART FROM
How about:
SELECT a.sid,
a.username,
c.disk_reads,
c.buffer_gets,
c.sorts,
c.executions,
c.rows_processed,
c.sql_text
FROM v$session a,
v$sqlarea c
WHERE a.sid = sessid
AND a.sql_address = c.address
-- AND a.SQL_HASH_VALUE
Join sql_address from v$session to address from v$sqlarea .
Regards,
Denny
Quoting [EMAIL PROTECTED]:
I'm suffering from a senior moment.
The question is at the every bottom.
SQL select sql_text from v$sqlarea sa where buffer_gets 1
SQL_TEXT
THANKS AK!
1 select oc.sid, ss.serial#, user_name, osuser, sa.sql_text
2 from v$open_cursor oc, v$session ss, v$sqlarea sa
3 where oc.sid = ss.sid
4 and oc.address = sa.address
5 and oc.hash_value = sa.hash_value
6* and buffer_gets 1
SQL /
SIDSERIAL
Just as a passing note - when you
query v$sqlarea like this it thrashes
the heck out of the library cache latch.
(And v$sqlarea is also an aggregate
view of x$kglob - so for a large shared
pool you could end up with a massive
sort and thrash of the temporary tablespace
as the view
.
Also...
- Currently pinned ( accessed )objects will NOT be flushed (even with
flush command)
- Objects KEPT using DBMS_SHARED_POOL.KEEP procedure to pin the objects to
the Shared Pool for the life of the instance will NOT be flushed out of the
Sqlarea, and hence cannot be seen thru the v$sqlarea
Dear All,
I wonder how long the life time of data in v$sqlarea. Is it a cumulative data
collecting in v$sqlarea since last database startup?or do the data in v$sqlarea only
reflect some recently data in Least Recetly List?
Why do I ask this is after I used alter system flush shared_pool, I
It will show you the SQL atatements that are residing in the shared pool.
Once you flush shared pool all the statements cached there gets flushed, and
so V$SQLAREA will only show information about the queries which are currently
held by open cursors and are not flushed.
Regards
Naveen
Hi List.
does anybody know what the OPTIMIZER_MODE = NONE
mean in v$sqlarea view ?
I've found this document Doc ID: 48131.996 in
metalink, but it seems that I don't have access to it.
The problem I'm facing here is that I am getting this
information when analyzing v$sqlarea
Hi List.
does anybodyknow
whatthe OPTIMIZER_MODE = NONE mean in v$sqlarea view ?
I've foundthis
documentDoc
ID: 48131.996in metalink, but it seems that I don't have
access to it.
The problem I'm facing here is thatI am
getting this information when analyzing v$sqlarea. What I'm
Hi,
You should not compare tkprof outputs with V$SQL,V$SQLAREA. Because,
recursive/child statistics are included in their parent statements in these
views. But, tkprof substructs recursive statistics. I mean tkprof reports real
values for statements, but dictionary doesn't.
regards...
Orr
) When I run tkprof on the query it shows a full table scan ignoring the
index... that's what I want 'cause it's cached.
4) But when I look at V$SQLAREA the DISK_READS column is incremented.
5) This query is executed very often so my StatsPack report lists it as one
of the most expensive queries
look at V$SQLAREA the DISK_READS column is incremented.
5) This query is executed very often so my StatsPack report lists it as one
of the most expensive queries in terms of physical reads.
It appears that Oracle is counting full table scans of cached tables as
DISK_READS in V$SQLAREA. Seems like
What version of Oracle?
1) I have a 400,000 row table table which is cached.
How has the table been cached? Alter table XXX cache? Or with a KEEP
buffer?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Greg Moore
INET: [EMAIL PROTECTED]
Fat City Network
but it's there so ho hum...)
3) When I run tkprof on the query it shows a full table scan ignoring the
index... that's what I want 'cause it's cached.
4) But when I look at V$SQLAREA the DISK_READS column is incremented.
5) This query is executed very often so my StatsPack report lists
the index since the table is cached but it's there so ho hum...)
3) When I run tkprof on the query it shows a full table scan ignoring the
index... that's what I want 'cause it's cached.
4) But when I look at V$SQLAREA the DISK_READS column is incremented.
5) This query is executed very often so
the table is cached but it's there so ho hum...)
3) When I run tkprof on the query it shows a full table scan ignoring the
index... that's what I want 'cause it's cached.
4) But when I look at V$SQLAREA the DISK_READS column is incremented.
5) This query is executed very often so my StatsPack
Can a statement be in V$SQLAREA and not in V$SQLTEXT at the same time?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Orr, Steve
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public
Not sure - but could it be that if the statement fits entirely in to the
V$sqlarea.sql_text field (varchar2(1000))?
-Original Message-
Sent: 18 June 2002 17:21
To: Multiple recipients of list ORACLE-L
Can a statement be in V$SQLAREA and not in V$SQLTEXT at the same time?
--
Please see
Did you join these tables by HASH_VALUE to other tables (or to each other)
and discover that quirky bug relating to the 32 bit arithmetic?
HASH_VALUE in v$sqltext is signed whereas HASH_VALUE in v$sqlarea is
unsigned.
This can cause joins to fail. Check for negative HASH_VALUE in v$sqltext
Well I'm unclear about the inconsistent wording in the docs:
V$SQLTEXT
This view contains the text of SQL statements belonging to shared SQL
cursors in the SGA.
V$SQLAREA
This view lists statistics on shared SQL area and contains one row per
SQL string. It provides statistics on SQL
I want to understand the difference between v$sql and v$sqlarea. Apparently
they are both views of the same x$ table. Does anyone know where I can view
the code that creates these two views?
Thanks.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Greg Moore
INET
Title: RE: v$sql and v$sqlarea
-Original Message-
From: Greg Moore [mailto:[EMAIL PROTECTED]]
I want to understand the difference between v$sql and
v$sqlarea. Apparently
they are both views of the same x$ table. Does anyone know
where I can view
the code that creates
check out V$FIXED_VIEW_DEFINITION
--- Greg Moore [EMAIL PROTECTED] wrote:
I want to understand the difference between v$sql
and v$sqlarea. Apparently
they are both views of the same x$ table. Does
anyone know where I can view
the code that creates these two views?
Thanks
are looking at the statements
contained in the v$sqlarea.
The first looks at statements with a high number of
buffer gets and the
other looks at the statements with a high number of
disk reads. Some of the
statements appear in both lists, but some in only
one. If all of the disk
reads are moving
to identify the most harmful
statements
in an application. From
the Oracle Performance and Tuning Tips and
Techniques book, I found two
statements. Both are looking at the statements
contained in the v$sqlarea.
The first looks at statements with a high number
of
buffer gets and the
other
I am trying to identify the most harmful statements in an application. From
the Oracle Performance and Tuning Tips and Techniques book, I found two
statements. Both are looking at the statements contained in the v$sqlarea.
The first looks at statements with a high number of buffer gets
.
hth
Deepak:
--- Erik Williams [EMAIL PROTECTED] wrote:
I am trying to identify the most harmful statements
in an application. From
the Oracle Performance and Tuning Tips and
Techniques book, I found two
statements. Both are looking at the statements
contained in the v$sqlarea.
The first
book, I found two
statements. Both are looking at the statements
contained in the v$sqlarea.
The first looks at statements with a high number of
buffer gets and the
other looks at the statements with a high number of
disk reads. Some of the
statements appear in both lists, but some in only
Beatriz,
Take a look at the column FIRST_LOAD_TIME in the V$SQLAREA view..
HTH
Mark
-Original Message-
Martinez Jimenez
Sent: Tuesday, September 25, 2001 16:05
To: Multiple recipients of list ORACLE-L
Hello,
Is there any way to see the dates in which the 'sql' statements from
v
--- Raj Gopalan [EMAIL PROTECTED]
wrote:
Thanks Chris.
The problem I am facing is 100% CPU usage and memory
paging out at times. I
tought the starting point is v$sqlarea. Purchasing
of Precise SQL or SQL
Vision can not happen here immediately. The RAM size
is 512MB and SGA
Thanks Chris.
The problem I am facing is 100% CPU usage and memory paging out at times. I
tought the starting point is v$sqlarea. Purchasing of Precise SQL or SQL
Vision can not happen here immediately. The RAM size is 512MB and SGA is
210MB. The buffer cache hit ratio is 99%.
I was just
On Tuesday 05 June 2001 05:25, Raj Gopalan wrote:
Thanks Chris.
The problem I am facing is 100% CPU usage and memory paging out at times. I
tought the starting point is v$sqlarea. Purchasing of Precise SQL or SQL
Vision can not happen here immediately. The RAM size is 512MB and SGA is
210MB
the reason
I've usually seen for it.
Keeps disk access down though. :)
Jared
On Tuesday 05 June 2001 05:25, Raj Gopalan wrote:
Thanks Chris.
The problem I am facing is 100% CPU usage and memory paging out at
times. I
tought the starting point is v$sqlarea. Purchasing of Precise SQL or
SQL
DBAs
The statistics in v$sqlarea is getting flushed very often.
In the morning I found the a query which has more than 10,000 disk reads as
the top one. But now the top most query with disk reads has not more than
100 disk reads. I have not bouncd the DB or flushed the shared pool. The
overall
Title: v$session_longops join to v$sqlarea
Hi,
I'm trying to identify some of the longops queries I'm seeing:
sofar totalwork message
7.2058E+16 7.2058E+16 29-MAR-01 0 417865044 1.8447E+19 : : 72057594037927936 out of 72057594037927936 done
I'm joining on the sqladdress, but I
54 matches
Mail list logo
