SQL> column OBJECT_NAME format a30
SQL> column EVENT format a30
select dba_objects.object_name,
dba_objects.object_type,
active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) ttl_wait_time
from v$active_session_history active_session_history,
dba_objects
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.current_obj# = dba_objects.object_id
group by dba_objects.object_name, dba_objects.object_type, active_session_history.event
order by 4 desc;
10.22.2008
5. What SQL is currently using the most resources?
select active_session_history.user_id,
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username
order by 4 desc;
dba_users.username,
sqlarea.sql_text,
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history,
v$sqlarea sqlarea,
dba_users
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.sql_id = sqlarea.sql_id
and active_session_history.user_id = dba_users.user_id
group by active_session_history.user_id,sqlarea.sql_text, dba_users.username
order by 4 desc;
4. What user is waiting the most?
Select sesion.sid,
sesion.username,
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3;
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history,
v$session sesion
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
and active_session_history.session_id = sesion.sid
group by sesion.sid, sesion.username
order by 3;
3. Last 30 minutes result those resources that are in high demand on your system.
select active_session_history.event,
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
group by active_session_history.event
order by 2;
sum(active_session_history.wait_time +
active_session_history.time_waited) total_wait_time
from v$active_session_history active_session_history
where active_session_history.sample_time between sysdate - 60/2880 and sysdate
group by active_session_history.event
order by 2;
2. Identify heavy SQL (Get the SQL with heavy DISK_READS)
Select sql_text ,executions ,disk_reads ,buffer_gets
from v$sqlarea
where decode(executions ,0,disk_reads,disk_reads/executions)
> (select avg(decode(executions,0,disk_reads,disk_reads/executions))
+ stddev(decode(executions,0,disk_reads,disk_reads/executions))
from v$sqlarea)
and parsing_user_id !=3D;
from v$sqlarea
where decode(executions ,0,disk_reads,disk_reads/executions)
> (select avg(decode(executions,0,disk_reads,disk_reads/executions))
+ stddev(decode(executions,0,disk_reads,disk_reads/executions))
from v$sqlarea)
and parsing_user_id !=3D;
1. Identify heavy SQL (Get the SQL with heavy BUFFER_GETS)
Select sql_text ,executions ,disk_reads ,buffer_gets
from v$sqlarea
where decode(executions,0,buffer_gets,buffer_gets/executions)
> (select avg(decode(executions,0,buffer_gets,buffer_gets/executions))
+ stddev(decode(executions,0,buffer_gets,buffer_gets/executions))
from v$sqlarea) and parsing_user_id !=3D;
where decode(executions,0,buffer_gets,buffer_gets/executions)
> (select avg(decode(executions,0,buffer_gets,buffer_gets/executions))
+ stddev(decode(executions,0,buffer_gets,buffer_gets/executions))
from v$sqlarea) and parsing_user_id !=3D;
10.20.2008
Difference between Host variable and Bind Variable
HOST VARIABLES:
BIND VARIABLES:
- Host variables are the key to communication between your host program and Oracle.
- Typically, a precompiler program inputs data from a host variable to Oracle, and Oracle outputs data to a host variable in the program.
- Oracle stores input data in database columns, and stores output data in program host variables.
BIND VARIABLES:
- A host variable can be any arbitrary C expression that resolves to a scalar type. But, a host variable must also be an lvalue. Bind variables are used in SQL and PL/SQL statements for holding data or result sets.
- They are commonly used in SQL statements to optimize statement perrformance.
- A statement with a bind variable may be re-executed multiple times without needing to be re-parsed.
- Their values can be set and referenced in PL/SQL blocks.
- They can be referenced in SQL statements bind variable references should be prefixed with a colon
Execute to parse ratio
Execute to parse ratio:
Execute to parse ratio is a measure of how many times you execute a sql statement versus parse it.
This 'ratio' will go towards 100 as the number of executes goes up and up and up, while the number of parses remains the same.
This will go to zero as the number of parses and executes are equal.
This will go negative if you parse more than you execute.
That percentage is computed as: round(100*(1-:prse/:exe),2)
Your developers have all of the control here - we cannot change this ratio without touching the way the application interacts with the database.
When Execute to Parse values is too low:
That means, someone is parsing statements highly, but not executing properly. They are just chewing up your CPU, latching the shared pool, killing your performance.
If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1%.
How could parse be bigger than execution? Or, Why Execute to Parse values become too low?
(http://www.dba-oracle.com/m_parse_to_execute_ratio.htm)
There may be tools that parse a statement but never execute it. Or they parse it to discover what inputs/outputs it takes and then later on parse it again to execute it (so the parse/execute is 2:1).
It is commonly referred to as "inefficient coding techniques". They parse like there is no tomorrow -- works great in a single user environment -- starts to stink with two users (or more).
Not using bind variable in query can cause such low value.
Some applications (generic apps typically) parse a "select * from T" to describe a table, never execute the cursor -- their parses exceed their executes.
Oracle does what it is told to do. Oracle is told by you to PARSE. If you do not tell Oracle to execute the statement, it won't. That is how you have parse but no execute.
How I can identify those SQL's, Which PARSE but never EXECUTE:
v$sql -- look at the parse and execute counts.
Example:
select PARSE_CALLS,EXECUTIONS,SQL_TEXT
from v$sql
where executions = 0 and parse_calls > 0
order by parse_calls asc;
Select PARSE_CALLS, EXECUTIONS, SQL_TEXT
from v$sql
where executions < PARSE_CALLS
order by parse_calls;
How they can be eliminated or efficiently coded ?
By only parsing a statment ONCE per session,
not once per execution
not once to "describe a table"
not once to "describe the ith column in a table"
When Parse is very high and execution is very low, Check in coding --
cursor-sharing=force must be on?
**Make cursor-sharing=similar. That will reduce hard parse.
(http://asktom.oracle.com/pls/asktom/f?p=100:11:2626091586804596::::P11_QUESTION_ID:4032595293314)
SQL> show parameter cursor_sharing
Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared.
Some Oracle databases with high ad-hoc query activity (Crystal Reports, Business Objects cannot avoid in-line literals inside the SQL, and that's why Oracle introduced the cursor_sharing parameter. Cursor_sharing=similar allows for bind variable "peeking", and in my opinion, it's too buggy to use until Oracle 11i, when you get adaptive cursor sharing. (http://www.dba-oracle.com/t_cursor_sharing_similar.htm)
If you have a routine that will be executed many times to generate a page, you should definitely parse once per page and execute it over and over, closing the cursor if necessary before your connection is returned to the connection pool.
It is 100% controlled by the client code and cannot be affected by the server settings at all. It is 100% a function of the number of times the client parses a sql statement and how many times they execute it.
How a Statement given for parsing cannot Execute?
Example:
dbms_sql.parse ( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns ( l_theCursor, l_colCnt, l_descTbl );
That just parsed the query, described it (to find the number of columns, data types, names, sizes, etc).
It never executed it -- If you closed the cursor, you would have a PARSE without an execute.
cursor sharing and parse:
Cursor sharing cannot reduce the NUMBER of parse calls (only your coders can do that! really - that is the ONLY way to reduce parse calls, they developers must call PARSE less often!)
cursor sharing can reduce the NUMBER OF HARD PARSES, that is all.
Hard parse:
A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small or when you have non-reusable SQL statements without host variables and bind variables.
http://www.ooug.org/2005slides/0720/ToolsIuse/trace_no.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:2626091586804596::::P11_QUESTION_ID:4032595293314
Execute to parse ratio is a measure of how many times you execute a sql statement versus parse it.
This 'ratio' will go towards 100 as the number of executes goes up and up and up, while the number of parses remains the same.
This will go to zero as the number of parses and executes are equal.
This will go negative if you parse more than you execute.
That percentage is computed as: round(100*(1-:prse/:exe),2)
Your developers have all of the control here - we cannot change this ratio without touching the way the application interacts with the database.
When Execute to Parse values is too low:
That means, someone is parsing statements highly, but not executing properly. They are just chewing up your CPU, latching the shared pool, killing your performance.
If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1%.
How could parse be bigger than execution? Or, Why Execute to Parse values become too low?
It is possible that the application is not using shareable SQL, or the database has sub-optimal parameters that are reducing the effectiveness of cursor sharing. A problem like excessive parsing is likely to manifest itself as additional network traffic between the application server and clients. The additional parse activity may also show up as a marked increase in CPU consumption on the database server.
(http://www.dba-oracle.com/m_parse_to_execute_ratio.htm)
There may be tools that parse a statement but never execute it. Or they parse it to discover what inputs/outputs it takes and then later on parse it again to execute it (so the parse/execute is 2:1).
It is commonly referred to as "inefficient coding techniques". They parse like there is no tomorrow -- works great in a single user environment -- starts to stink with two users (or more).
Not using bind variable in query can cause such low value.
Some applications (generic apps typically) parse a "select * from T" to describe a table, never execute the cursor -- their parses exceed their executes.
Oracle does what it is told to do. Oracle is told by you to PARSE. If you do not tell Oracle to execute the statement, it won't. That is how you have parse but no execute.
How I can identify those SQL's, Which PARSE but never EXECUTE:
v$sql -- look at the parse and execute counts.
Example:
select PARSE_CALLS,EXECUTIONS,SQL_TEXT
from v$sql
where executions = 0 and parse_calls > 0
order by parse_calls asc;
Select PARSE_CALLS, EXECUTIONS, SQL_TEXT
from v$sql
where executions < PARSE_CALLS
order by parse_calls;
How they can be eliminated or efficiently coded ?
By only parsing a statment ONCE per session,
not once per execution
not once to "describe a table"
not once to "describe the ith column in a table"
When Parse is very high and execution is very low, Check in coding --
cursor-sharing=force must be on?
SQL> show parameter cursor_sharing
Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared.
Some Oracle databases with high ad-hoc query activity (Crystal Reports, Business Objects cannot avoid in-line literals inside the SQL, and that's why Oracle introduced the cursor_sharing parameter. Cursor_sharing=similar allows for bind variable "peeking", and in my opinion, it's too buggy to use until Oracle 11i, when you get adaptive cursor sharing. (http://www.dba-oracle.com/t_cursor_sharing_similar.htm)
If you have a routine that will be executed many times to generate a page, you should definitely parse once per page and execute it over and over, closing the cursor if necessary before your connection is returned to the connection pool.
It is 100% controlled by the client code and cannot be affected by the server settings at all. It is 100% a function of the number of times the client parses a sql statement and how many times they execute it.
How a Statement given for parsing cannot Execute?
Example:
dbms_sql.parse ( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns ( l_theCursor, l_colCnt, l_descTbl );
That just parsed the query, described it (to find the number of columns, data types, names, sizes, etc).
It never executed it -- If you closed the cursor, you would have a PARSE without an execute.
cursor sharing and parse:
Cursor sharing cannot reduce the NUMBER of parse calls (only your coders can do that! really - that is the ONLY way to reduce parse calls, they developers must call PARSE less often!)
cursor sharing can reduce the NUMBER OF HARD PARSES, that is all.
Hard parse:
A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small or when you have non-reusable SQL statements without host variables and bind variables.
http://www.ooug.org/2005slides/0720/ToolsIuse/trace_no.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:2626091586804596::::P11_QUESTION_ID:4032595293314
10.19.2008
Oracle DBA scripts - Partition related
set pages 999 lines 100
col table_name format a40
col high_value format a20
col tablespace_name format a20
1. List partitioned tables
select table_name, partitioning_type type, partition_count partitions
from dba_part_tables
where owner = '&owner'
order by 1;
2. List a tables partitions
select partition_name , tablespace_name , high_value
from dba_tab_partitions
where table_owner = '&owner' and table_name = '&table_name'
order by partition_position;
col table_name format a40
col high_value format a20
col tablespace_name format a20
1. List partitioned tables
select table_name, partitioning_type type, partition_count partitions
from dba_part_tables
where owner = '&owner'
order by 1;
2. List a tables partitions
select partition_name , tablespace_name , high_value
from dba_tab_partitions
where table_owner = '&owner' and table_name = '&table_name'
order by partition_position;
9.23.2008
ORA-30041
I was connected as sysdba and when I tried to assign quota on temporary tablespace I got an error:
SQL> ALTER USER r_nahar QUOTA UNLIMITED ON TEMP;
ALTER USER r_nahar
*
ERROR at line 1:
ORA-30041: Cannot grant quota on the tablespace
Why this error:
It is is expected behavior in oracle 10.2g.
In version 10.2 oracle does not support quota on temporary tablespaces.
If your version is earlier than 10.2g then this is a bug.
Solution:
Don't try to assign quota on the temporary tablespaces if your database version is 10.2 as it is expected behavior.
SQL> ALTER USER r_nahar QUOTA UNLIMITED ON TEMP;
ALTER USER r_nahar
*
ERROR at line 1:
ORA-30041: Cannot grant quota on the tablespace
Why this error:
It is is expected behavior in oracle 10.2g.
In version 10.2 oracle does not support quota on temporary tablespaces.
If your version is earlier than 10.2g then this is a bug.
Solution:
Don't try to assign quota on the temporary tablespaces if your database version is 10.2 as it is expected behavior.
Subscribe to:
Posts (Atom)