|
Replies:
13
-
Pages:
1
-
Last Post:
Jul 10, 2009 12:46 PM
Last Post By: Randolf Geist
|
|
|
Posts:
18
Registered:
07/08/09
|
|
|
|
SQL Query statists?
Posted:
Jul 8, 2009 12:03 PM
|
|
|
|
I'd like to know if there is any easy, and convenient way, for someone to execute an SQL query an calculate, these measurments relative to the query:
1) The I/O performed
2)Number Read I/O
3) Number of Write out I/O
(such that 2+3 = 1
4) Number of buffered reads
5)Query Execution time
6) Query CPU usage
I've heard mention of such statisctis in views such as V$OSSTAT, etc.
But these views give the current values, and not the specific cumulative values. Such as: cummulative CPU usage time since start of the query; cumulative I/O since query begin, etc...
What is the right approach to this. Is it through the V$SESSION view? Would you about it by storing the V$SESSIOn values before the query, you run the query, and get the new V$SESSION values?
|
|
|
Posts:
1,430
Registered:
11/25/01
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 8, 2009 12:09 PM
in response to: sono99
|
|
|
|
You can start from autotrace
I wrote small blog post about how to configure it:
[http://projectenvision.com/blog/How-to-enable-AUTOTRACE-in-Oracle]
more detailed information about autotrace can be found in Oracle Documentation
With kind regards
Krystian Zieja
Edited by: krystian.zieja on Jul 8, 2009 9:09 PM
|
|
|
Posts:
4,389
Registered:
06/27/99
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 8, 2009 12:09 PM
in response to: sono99
|
|
|
SQL> set autotrace trace statistics
SQL> select sysdate from dual;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
415 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
|
|
|
Posts:
18
Registered:
07/08/09
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 9, 2009 3:08 AM
in response to: sb92075
|
|
|
First of, thanks for the quick replies.
I had read before about the trace, but it seems the statistics are kind of supperficial. Still, the physical writes attribute looks interesting.
I am, presently, trying to read the oracle guide about tunning.
They mention that they have a lot of views targeting different purposes. And that one common type of statistics if the cumulative statistic. That is, there is a statistic variable that increases as time passes.
So, given that their only tool to measure a query weight on the system is the trace, I was thinking of going with a different approach. I am trying to round up in these views the set of statistic varaibles that will give me a clear enough notion of what is happening.
So my approach would be:
1) Define the set of stats I want
2) Take a pseudo snapshot of the session environment before executing a query
3) Retake the same picture after the sql query
4) Calculate the stat differences.
That should be the impact of my query on the system.
For now I am writing this query but I am unsure as to what some of these stat values mean:
SELECT A.SID, N.STATISTIC#, N.NAME, N.CLASS, A.VALUE
FROM v$mystat A INNER JOIN v$statname N ON A.STATISTIC# = N.STATISTIC#
WHERE
(
N.NAME = 'CPU used by this session'
OR N.NAME = 'session logical reads'
OR N.NAME = 'session uga memory max'
OR N.NAME = 'session pga memory max'
OR N.NAME = 'consistent gets'
OR N.NAME = 'db block changes'
OR N.NAME = 'db block gets'
OR N.NAME = 'DBWR undo block writes'
OR N.NAME = 'redo blocks written'
OR N.NAME = 'redo size'
-- OR N.NAME = 'dirty buffers inspected'
OR N.NAME = 'physical reads'
-- OR N.NAME = 'physical reads cache'
-- OR N.NAME = 'physical reads direct'
OR N.NAME = 'physical writes'
-- OR N.NAME = 'physical writes from cache'
-- OR N.NAME = 'physical writes direct'
OR N.NAME = 'session connect time'
-- OR N.NAME = 'sorts (disk)'
-- OR N.NAME = 'sorts (memory)'
-- OR N.NAME = 'physical read IO requests'
-- What matters are the I/O reads/writes not the requests
)
ORDER BY N.CLASS;
But I have doubts.
1) Are all these stats only concerned with my session, or with the overall sessions running on the system? For example, the physical reads statistic: that describes the total number o database blocks that were read I/Oed for this section. Correct?
2) What is the correlation between db block gets and physical reads? Is there no correlation?
3) Shouldn't the DB block changes statistic be somewhat correlated to the number of physical writes? Right now this query of mine signals the existence of 4 db block changes and 0 physical writes.
4) Regarding physical writes: If i subtract the number of redo writes and DBWR undo block writes, do I get the actual number of updates/inserts/creates that were made to the system?
I would like to be advisited in terms of which statistic varaibles can be best used to describe:
1) CPU Processing time - i assume the CPU used by this section is enough;
2) Total execution time - maybe by making the difference of session time before the sql query and after i can calculate the total elapsed time
3) Total gets In porportion to Total hard Disk Reads (can i assume that each physical read is included in the session logical reads?) If so: physical reads / session logical reads = % the reads that incurred in I/O
4) In terms of writing activity, what can I use? Db Block changes as measure of the total number of updates? And can i relate this value to the physical writes in any way? For instance, my db block changes do not match my redo size (which is 796 bytes); a lot less than four blocks. But maybe those blocks have not been fully updated. Therefore number db block changes is less than changes size?
Are these the most relevant statistics?
Execution time - which for now i do not have a way to calculate,
cpu time;
and memory usage.
By the way, what can i conclude from analysing my session uga and pga max memory? Thanks! |
|
|
Posts:
1,669
Registered:
07/03/08
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 9, 2009 8:01 AM
in response to: sono99
|
 |
Helpful |
|
|
A quite convenient way to get a good overview of the resource consumption of your statement is to use SQL trace with the extended wait interface, which will show you:
* The time your execution has waited for something (like I/O, latches, locks etc.)
* The CPU and elapsed time
* The number of logical and physical I/O performed (no writes)
And the "Row Source Operations", which is the number of rows generated by each operation of your execution plan along with the consistent gets, physical reads, physical writes and time consumed, which is a very powerful information, because it let's you identify potentially inefficient steps of the execution plan (either because of high resource consumption and/or due to significant different number of rows generated compared to the optimizer's estimate).
From 10g on you can also use DBMS_XPLAN.DISPLAY_CURSOR, which covers similar information like above, but in addition it also shows the memory/temporary disk space consumption of workarea executions (like sorts or hash joins).
You need to use the GATHER_PLAN_STATISTICS hint or the STATISTICS_LEVEL = ALL to get that the extended information from DISPLAY_CURSOR though.
I've written a blog post some time ago that shows step-by-step instructions how to obtain both informations.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ |
|
|
Posts:
18
Registered:
07/08/09
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 9, 2009 10:20 AM
in response to: Randolf Geist
|
|
|
Hi randolf, thank you for your reply.
In this post I have a number of questions, and also i will ask for your opion on how i should approach my current problem.
The question topics are:
Topic 1) Oracle read consistency and my original approach. Handling oracle data dictionary views like, v$mystat, can be quite risky if done improperly i believe.
Topic 2) Testing the query cost of different SQL queries in clean way, possibly through java?
Topic 3) Your method. Presents two distinct approaches.
Topic 1)
First, regarding the read consistency. In oracle there seems to be a time stamp signature for Objects called SCN. Once an object becomes updated in a permanent way, that SCN value seems to be updated. And the old object images that may still exist in the UndoTablespace become temporary resources that will eventually get wiped out. That is what i grasped.
I am not quite sure as to what is the standard level of isolation in Oracle, if by the fault queries are Conflict Serializable. But what I seem to see as the most current trent is that Oracle tries to offer conflict seriability, through an alternative implementation that is not the two phase locking protocol.
What I seem to see happening in Oracle is whichever object exists in the system, anyone with adequate permissions can update it. So Image a Transaction T1 that has a cursors for a table A, with SCN 1. The table object dos not seem to be locked, any T2 can go there and update A, making SCN=2.
Now a the transaction T1 may eventually, most probably will because we are talking about a cursor, want to read T1 again. What oracle does is execute consitent reads. It searches for the table object with SCN 1. Eventually that after image becomes to old and your get consistent read no longer finds the consistent object and gives a snapshot too old error.
So, sooner or later, what is usually referred to as R-W (T1R,T2W,T1R - unprepeatble read), hapens. So, for me the snapshot too old error from oracle: is the same as an unrepeatable read in desguise.
Anyway why this conversation? Because, this has been scaring me regarding the handling of the Oracle views.
As it seems, these views are automatically incremented/updated by oracle inner processes. And it seems quite dangerous to query these objects with cursors or whatever.
The problem is, If i am use them to obtain the statistics of my SQl queries, I will to create a stored procedure that works something like this:
1) Open Cursors (SELECT * FROM interesting_mystats)
--- These would be my stats before teh SQL statement
2) Run the SQL statement
3) Create cursor with (SELECT * FROM interesting_mystats)
4)Process each cursor row, and return in end return to the invoker application an array with calculated statistic parameters.
The problem is: I imagine that in STEP 4, when try to access the static variables from the cursor i opened in 1. I will get my unrepeatable read error.
So I'd like to know, If I am predicting correctly my code problems and if I can avoid them by:
either storing those values of 1 in local procedural variables; or maybe by creat1 an array object with the constructor being an Select statement.
An opinion on this would be quite appreciated.
Topic 2)
I have a task to do for my University work that consists on the testing a number of queries, many of which doing the same thing but implemented in different manners.
The procedure can be quite painstaking, for each query that i am going to test, that will be something like
a Create table as SELECT FROM Query_Implementation_Solution, I have to:
1) Startup the database
2) Run the query
3) Store the statistics results in some file
4) Drop the created table
5) Shutdow the RDBMS
6) Repeat process 1 with a new quey implementation
So, in order to process these steps I was considering the use of Java scripts . What I would do, would be:
1) Install stored procedures In Oracle of the sort:
Create Stored procedure1
Collect Statistics
RUN QUERY_Impelmentation1
COLLECT STATIS
DROP QUERY OUTPUT TABLE;
RETURN STATISTIC TO JAVA;
INSTANCE SHUTWON;
Now, after your reply, I am unsure if this is a sensible approach. maybe I shouldn't be the one collecting these statistics at all, maybe i should just let oracle to that by trace.
This leads me to my third question, regarding your reply:
Topic 3)
Your method. Presents two distinct approaches.
I didn't quite understand, that concretely, which were my options.
Are they:
1)
A quite convenient way to get a good overview of the resource consumption of your statement is to use SQL trace with the extended wait interface, which will show you:
* The time your execution has waited for something (like I/O, latches, locks etc.)
* The CPU and elapsed time
* The number of logical and physical I/O performed (no writes) [/quote]
Ok, I saw your blog. So i assume you are referring to the:
The TKPROF output for this statement looks like the following:[/quote]
That had the following columns:
call count cpu elapsed disk query current rows
Moreover,
And the "Row Source Operations", which is the number of rows generated by each operation of your execution plan along with the consistent gets, physical reads, physical writes and time consumed, which is a very powerful information,
Something that is not clear for me, is if a physical real is also counted in the logical reads statistics (cosintent/current gets). I mean, if you query for a block that is not in the buffer cache, that block will be fetched but It will increase the physical statistic for reads. But should it not also be a logical read?
At the logical level, you never if what you ask from the Data buffer management system is there or not. I would assume that a logical read can, potentially, come from anywhere.
you mention the use of:
The DBMS_XPLAN.DISPLAY_CURSOR output:
DBMS_XPLAN.DISPLAY_CURSOR, which covers similar information like above, but in addition it also shows the memory/temporary disk space consumption of work area executions (like sorts or hash joins).
You need to use the GATHER_PLAN_STATISTICS hint or the STATISTICS_LEVEL = ALL to get that the extended information from DISPLAY_CURSOR though.[/quote]
Yes, I think that I need that additional information as well.
I am now going to try both the methods you mentioned. Am I correct in assuming that for the DBMS_XPLAN you do not need any tool to interpret the output? It is directly a text file?
I am sorry for showering you with so many questions!
My Best regards,
Nuno.
(By The way, I have tested the Oratrace with the script in part you give in your example. It is pretty nice, thanks. I will try tomorrow to follow up your part2 script. God, life is hard!)
A priceless blog entry.
Edited by: user10282047 on Jul 9, 2009 1:12 PM |
|
|
Posts:
4,389
Registered:
06/27/99
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 9, 2009 10:52 AM
in response to: sono99
|
|
|
|
Forgive me if I am restating the obvious, but I'm not sure you fully understand about SCN.
SCN only changes after COMMIT.
Even if you are doing DML as part of your "query testing", and you issue ROLLBACK, the SCN remains unchanged.
If you never COMMIT as part of your testing, you will never get ORA-01555 error.
HTH & YMMV
|
|
|
Posts:
1,669
Registered:
07/03/08
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 9, 2009 12:15 PM
in response to: sono99
|
|
|
Nuno,
Topic 1) Oracle read consistency and my original approach. Handling oracle data dictionary views like, v$mystat, can be quite risky if done improperly i believe.
The interesting point here is that the dynamic performance views (V$...) actually don't adhere to Oracle's read consistency model, since most of them are a representation of in-memory structures and it would be far too costly for Oracle to maintain read consistency on these memory structures.
So yes, it can happen and it actually happens that while you're reading from V$ views that the content is changing so that you get inconsistent information that never existed in that form.
So in order to minimize the risk, if you want to collect statistics yourself, you should choose a method that minimizes the time to process the data, i.e. bulk collect into memory or create a persistent copy of the data using a plain SQL approach (insert into ... select from).
If you want to see some sample code how to capture such statistics, have a look here at Jonathan Lewis' site: http://www.jlcomp.demon.co.uk/snapshots2.html
Topic 3) Your method. Presents two distinct approaches.
Just follow the blog post and the how-to steps. It shows you how to gather the information and how a sample output looks like. Both methods are valid and provide similar information. Some information, like the wait event information and the CPU time is only part of the SQL trace, other information like the memory requirements are only part of the DBMS_XPLAN.DISPLAY_CURSOR output. So if you're using both, you collect the most information.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ |
|
|
Posts:
18
Registered:
07/08/09
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 9, 2009 1:24 PM
in response to: Randolf Geist
|
|
|
I edited my previous post to tell you that I have been following your blog post, from start to end.
I have not reached the end. i stoped after tkprofing the trace file in the trace dump folder.
Tomorrow I'll try to see If i can get the feeling for your other approach.
In terms of updating the SCN value, I am aware that a modified data block only changes SCN when the thread that changed declares that it is finished/commits.
But I can not recall where I saw this, but i believe it was an ask tom kyte example, he showed how you can "screw yourlsef" when you use cursors over a data object that your yourself modifying.
The query went as something like:
Open Cursor for Table of indexes,
Drop index from table;
Fetch next index.
The point was: each time you dropped and Index, the SCN of the data object was chaning, and eventually your get consistent data block would yield an error. because that block was too old. The data object had a morre current signature.
In case of what I was talking about, I was not concerned about my updates: I never intended to update that statistics view. I am just aware that such an object is ever changing. So I was figuring that leting a long query run, would give me very bad results when i tried to manipulate a cursor I had opened in the initial statistics table. That cursor should be referencing a data object with very specific statistic row values. That would have changed and no longer exist.
Well anyway, it Is nine hours and 22 of the night here in portugal, and I am very very tired. Time is runing out for me, but I won't work more for today.
Thanks a lot for your feedback. It is speeding me alot along the way. |
|
|
Posts:
1,669
Registered:
07/03/08
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 9, 2009 1:53 PM
in response to: sono99
|
|
|
In terms of updating the SCN value, I am aware that a modified data block only changes SCN when the thread that changed declares that it is finished/commits.
But I can not recall where I saw this, but i believe it was an ask tom kyte example, he showed how you can "screw yourlsef" when you use cursors over a data object that your yourself modifying.
The query went as something like:
Open Cursor for Table of indexes,
Drop index from table;
Fetch next index.
The point was: each time you dropped and Index, the SCN of the data object was chaning, and eventually your get consistent data block would yield an error. because that block was too old. The data object had a morre current signature.
In case of what I was talking about, I was not concerned about my updates: I never intended to update that statistics view. I am just aware that such an object is ever changing. So I was figuring that leting a long query run, would give me very bad results when i tried to manipulate a cursor I had opened in the initial statistics table. That cursor should be referencing a data object with very specific statistic row values. That would have changed and no longer exist.
This note and your previous descriptions regarding read consistency: May be it's just a language issue (I'm not a native speaker either) but your understanding of Oracle's read consistency model seems to be questionable.
You might want to read through the Oracle Concepts manual: http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/consist.htm#g43931
Basically Oracle guarantees read consistency without blocking other writers (or readers) using a multi-version approach (the undo data to build previous versions of blocks, used by consistent gets if required).
Either Oracle is able to reconstruct a consistent (either consistent to the point in time your statement or your transaction began) version of a block using available undo data if necessary or you'll get the (in-)famous "ORA-01555: snapshot too old" error. You never read inconsistent data with Oracle under normal circumstances, and one of the exceptions to this rule are the V$ views you're attempting to read. There is no such thing like a consistent read on a V$ view in Oracle.
Tom Kyte's example usually is about showing that you can get into trouble with the read consistency (increase the possibility of the "ORA-01555: snapshot too old" error) if you attempt to fetch across commits or commit within a loop, because quite often people tend to think that committing often should help for various reasons (release locks, stop blocking other sessions, minimize the size of the undo segments etc. etc.), but doing so breaks the logical meaning of a transaction and increases the likelihood of the "snapshot too old" error. And due to the "multi-version" approach of Oracle it's simply unnecessary: In Oracle you should commit only when your transaction is logically complete and not earlier. No other session attempting to read the data modified will be blocked while the transaction is ongoing. Even sessions that attempt to write are not blocked in general, unless it attempts to modify the same data, or there are some other special circumstances (e.g. unindexed foreign key columns in case of parent table modifications).
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ |
|
|
Posts:
18
Registered:
07/08/09
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 9, 2009 2:38 PM
in response to: Randolf Geist
|
|
|
Well, actually i stayed here a little longer to try you part 2 of the manual.
It worked fine, following comes the output, originating from a spool file, of my first experiment:
Connected.
SQL> set timing on trimspool on linesize 250 pagesize 999
SQL>
SQL> -- system environment can be checked with:
SQL> -- show parameter statis
SQL> -- this show a series of parameters related to statistics
SQL>
SQL> -- this setting can influence your sorting
SQL> -- in particular if an index can satisfy your sort order
SQL> -- alter session set nls_language = 'AMERICAN';
SQL>
SQL>
SQL> rem Set the ARRAYSIZE according to your application
SQL> set arraysize 15 termout off
SQL>
SQL> spool diag2.log
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'))
;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------
SQL_ID b4j5rmwug3u8p, child number 0
-------------------------------------
SELECT USRID, FAVF FROM (SELECT ID as USRID, FAVF1, FAVF2, FAVF3,
FAVF4, FAVF5 FROM PROFILE) P UNPIVOT (FAVF FOR CNAME IN ( FAVF1,
FAVF2, FAVF3, FAVF4, FAVF5)) FAVFRIEND
Plan hash value: 888567555
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
Buffers |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 |
8 |
|* 1 | VIEW | | 1 | 5 | 5 |00:00:00.01 |
8 |
| 2 | UNPIVOT | | 1 | | 5 |00:00:00.01 |
8 |
| 3 | TABLE ACCESS FULL| PROFILE | 1 | 1 | 1 |00:00:00.01 |
8 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("unpivot_view_013"."FAVF" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement
26 rows selected.
Elapsed: 00:00:00.14
SQL>
SQL> spool off
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Pr
oduction
With the OLAP, Data Mining and Real Application Testing options
C:\Documents and Settings\Administrator\My Documents\scripts\oracle\99templates_
autotrace>my_part2_template.bat
C:\Documents and Settings\Administrator\My Documents\scripts\oracle\99templates_
autotrace>sqlplus /NOLOG @my_part2_template.sql
SQL*Plus: Release 11.1.0.7.0 - Production on Qui Jul 9 22:00:39 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected.
SQL> set timing on trimspool on linesize 250 pagesize 999
SQL>
SQL> -- system environment can be checked with:
SQL> -- show parameter statis
SQL> -- this show a series of parameters related to statistics
SQL>
SQL> -- this setting can influence your sorting
SQL> -- in particular if an index can satisfy your sort order
SQL> -- alter session set nls_language = 'AMERICAN';
SQL>
SQL>
SQL> rem Set the ARRAYSIZE according to your application
SQL> set arraysize 15 termout off
SQL>
SQL> spool diag2.log
SQL>
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'))
;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------
SQL_ID b4j5rmwug3u8p, child number 0
-------------------------------------
SELECT USRID, FAVF FROM (SELECT ID as USRID, FAVF1, FAVF2, FAVF3,
FAVF4, FAVF5 FROM PROFILE) P UNPIVOT (FAVF FOR CNAME IN ( FAVF1,
FAVF2, FAVF3, FAVF4, FAVF5)) FAVFRIEND
Plan hash value: 888567555
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
Buffers |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 |
8 |
|* 1 | VIEW | | 1 | 5 | 5 |00:00:00.01 |
8 |
| 2 | UNPIVOT | | 1 | | 5 |00:00:00.01 |
8 |
| 3 | TABLE ACCESS FULL| PROFILE | 1 | 1 | 1 |00:00:00.01 |
8 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("unpivot_view_013"."FAVF" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement
26 rows selected.
Elapsed: 00:00:00.01
SQL>
SQL> spool off
SQL>
SQL>
SQL> -- rem End of Part 2
SQL> show parameter statis
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_pending_statistics boolean FALSE
statistics_level string ALL
timed_os_statistics integer 5
timed_statistics boolean TRUE
SQL> quit
If you notice, at the end of the execution I print my statistics session environment. The statistics_level was set to ALL, as you advisied. But the output I obtained seems a lot more incomplete than the one I got from using the autotrace feature.
Am I missing something. Could it have something to do with the fact that I am running as system and not as sysdba? System shoul have enough permissions to access its session environment statistic values.
May be it's just a language issue (I'm not a native speaker either) but your understanding of Oracle's read consistency model seems to be questionable.
No, you could be right; my understanding is questionable indeed. I am familiar with general concepts of concurrency.
Things like: Read uncommited data:
T1 Writes A; T2 Reads A -> Here is a conflict
This enough for you to not be able to guarantee that the execution is serializable.
T1 Reads A, T2 Writes A and commits, T1 Reads A - You get another confli, the Unrepeatable read.
And so on.
I am also familiar with the different levels of atomicity that databse systems in general give you.
Conflict Serializable, normally implemented by using the strict phase locking mechanism.
Repeatable Reads, you lock the rows you access during a transaction. You are guaranteed that those data values you access do not change value; but other entires in the table could be put.
Unrepeatable reads. Only the data you modify is guaranteed to stay the same. Only you write locks are kept throughout the transaction. And so on.
But anyway...
What you explained in your post is more or less what I was saying. In you case much more clear than in mine.
For instance, if a thread T1 reads A; a thread T2 Writes on A
In oracle, you could have the thread T1 read A again without geting an Unrepeatable Read error. This is strange: in a normal system you directly get an exception telling you that your vision of the system is inconsistent. But in oracel you can do so, because oracle tries to fetch from the Undo Table Space that same data objects consistent with the view of the system you had when you first accessed it. It looks for a block with an an SCN older than the current version SCN. Or something like that. The only problem is that those modified blocks do not stay indefinitely there. Once a transaction commits you have a time bomb in your hands. That is, if you are working with that is not at its most current version.
But you are quite right, I have not read enough about Oracle concurrency. But I have a good enough understanding for mu current needs.
I can not know everything, nor do i want to  .
My memory is very limited.
My best regards, and deepest thanks for your time and attention.
Edited by: user10282047 on Jul 9, 2009 2:41 PM |
|
|
Posts:
1,669
Registered:
07/03/08
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 9, 2009 2:49 PM
in response to: sono99
|
|
|
If you notice, at the end of the execution I print my statistics session environment. The statistics_level was set to ALL, as you advisied. But the output I obtained seems a lot more incomplete than the one I got from using the autotrace feature.
Am I missing something. Could it have something to do with the fact that I am running as system and not as sysdba? System shoul have enough permissions to access its session environment statistic values.
You're confusing things here: What you're showing are initialization parameters that have the string "statis" in their name. But what you seem to be looking for are the execution statistics shown e.g. by AUTOTRACE. These are totally different things, one can be obtained e.g. from V$PARAMETER, whereas the others are based on data from V$SESSTAT etc.
By the way: Never run any tests connected as SYSDBA. Many exceptions to general behaviour apply when running with SYSDBA privileges. This should only be used for a very limited number of operations. Use a "normal" user (which could have the DBA role granted e.g., which is different from the special SYSDBA) for running test cases.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ |
|
|
Posts:
18
Registered:
07/08/09
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 10, 2009 2:56 AM
in response to: Randolf Geist
|
|
|
Hi Randolf,
What you're showing are initialization parameters that have the string "statis" in their name.
Yes, but I am showing those initialization parameters just to point out that when I run the query I had enabled the the ALL statistics level, as you said I should do in:
You need to use the GATHER_PLAN_STATISTICS hint or the STATISTICS_LEVEL = ALL to get that the extended information from DISPLAY_CURSOR though.
So, by printing those elements from v$parameter, I illustrated that the STATISTICS_LEVEL was set to ALL.
These are totally different things, one can be obtained e.g. from V$PARAMETER, whereas the others are based on data from V$SESSTAT etc.
Yes, those are different views. And Indeed I just want the statistics vom V$SESSIONSTAT.
But before I can get them, I was assuming that I had to affect the parameter STATISTICS_LEVEL from v$parameter.
Therefore, my doubt is: why weren't those statistics, that this method gathers from the V$sessionstat, printed by the end of the execution?
It must have done something wrong.
Or does this method also print them out into a trace file like the autotrace? i thought It didn't, that the methods were different.
Once more,
Thanks a lot.
(By the way: was I believe I was runing the query as the user SYSTEM. Not SYS as SYSDBA. ) But i might be wrong here.
---
Now I have been tampering a little more with the oracle trace, and the thing is quite confusing because you get all types of queries that are run by the system. And it seems that even when you start a new session and redo the process in a new trace file, the old traces shot again. Like, if do once the unpivot query. And later in following session I run the same query again. My later trace file will have to queries with Unpivot, and it is unclear which is the most recent one.
Edited by: user10282047 on Jul 10, 2009 3:58 AM |
|
|
Posts:
1,669
Registered:
07/03/08
|
|
|
|
Re: SQL Query statists?
Posted:
Jul 10, 2009 12:46 PM
in response to: sono99
|
|
|
Therefore, my doubt is: why weren't those statistics, that this method gathers from the V$sessionstat, printed by the end of the execution?
It must have done something wrong.
Or does this method also print them out into a trace file like the autotrace? i thought It didn't, that the methods were different.
The crucial information returned by DBMS_XPLAN.DISPLAY_CURSOR is shown in the columns of the output, e.g. A-Rows, A-Time, Buffers, OMem, Reads etc. etc.
There is no separate "statistics" section (as provided by AUTOTRACE) in the output, since it's already part of the "enhanced" execution plan shown by DBMS_XPLAN.DISPLAY_CURSOR.
(By the way: was I believe I was runing the query as the user SYSTEM. Not SYS as SYSDBA. ) But i might be wrong here.
That was just a hint that you should never attempt to do it, it didn't say you did but you were mentioning it in your note thinking about if using SYSDBA might produce the output you expected to see (but your expectation was incorrect, see above comment).
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
