Coskan’s Approach to Oracle

June 30, 2009

How to explain plan on views when you don’t have access on base tables

Filed under: PL/SQL, Tips — coskan @ 11:28 am

One of my friends asked, how can developers see the explain plan on views without having access to the base tables.

After a small search in Oracle Forums I found the solution of John Spencer.

Basically he creates a procedure to run explain plan and gives the execute access to that procedure.

This is the procedure

CREATE OR REPLACE PROCEDURE explain_it (p_plan_id IN VARCHAR2,
p_sql IN VARCHAR2,
p_plan OUT my_types.expl_cur) AS
l_sqlstr VARCHAR2(32767);
BEGIN
l_sqlstr := 'SELECT LPAD('' '',2*(LEVEL-1))||OPERATION||'' ''||OPTIONS||'' ''||OBJECT_NAME||'' ''||';
l_sqlstr := l_sqlstr||'DECODE(ID,0,''COST = ''||POSITION) "QUERY PLAN" FROM plan_table ';
l_sqlstr := l_sqlstr||'START WITH ID = 0 AND STATEMENT_ID = :b1 ';
l_sqlstr := l_sqlstr||'CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = :b2';

EXECUTE IMMEDIATE 'EXPLAIN PLAN SET statement_id = '''||p_plan_id||''' FOR '||p_sql;

OPEN p_plan FOR l_sqlstr USING p_plan_id, p_plan_id;
END;
/

or to use with dbms_xplan.display


CREATE OR REPLACE PROCEDURE explain_it (
p_sql IN VARCHAR2,
p_plan OUT sys_refcursor) AS
l_sqlstr VARCHAR2(32767);
BEGIN
l_sqlstr := 'select * from table (dbms_xplan.display)';
EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR '||p_sql;
OPEN p_plan FOR l_sqlstr ;
END;
/

and this is how it works

SQL> create user read_only identified by read_only
  2  ;

User created.

SQL> grant create session to read_only;

Grant succeeded.

SQL> create table exp_test as select * from user_objects where rownum<1000;

Table created.

SQL> create view v_exp_test as select * from exp_test;

View created.

SQL> grant select on v_exp_test to read_only;

Grant succeeded.

SQL> grant execute on explain_it to read_only;

Grant succeeded.

----connect with read_only

SQL> connect read_only/read_only
Connected.

SQL> select count(*) from hr.v_exp_test;

  COUNT(*)
----------
       156

SQL> explain plan for select count(*) from hr.v_exp_test;
explain plan for select count(*) from hr.v_exp_test
                                         *
ERROR at line 1:
ORA-01039: insufficient privileges on underlying objects of the view

---TRY WITH NEW PROCEDURE

SQL> var my_cur refcursor;
SQL> EXEC hr.explain_it('SELECT * FROM hr.v_exp_test',:my_cur);

PL/SQL procedure successfully completed.

SQL> print my_cur;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
-------------------------------------------------
Plan hash value: 3488715207

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   156 | 29640 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EXP_TEST |   156 | 29640 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

12 rows selected.

June 26, 2009

Blogroll Report 19/06/2009 – 26/06/2006

Filed under: Blogroll Report — coskan @ 8:07 pm

<—- Blogroll Report 12/06/09 – 19/06/09

Here are  technical highlights from Oracle Blogosphere with my keywords,

1- ORA-38029 for index creation

Randolf Geist – Locked table statistics and subsequent create index

2- How to start RAC instance in non-RAC  mode

Chandra Pabba – ORA-29702 – Starting RAC instance in non-rac mode

3- How to set cardinality for pipelined and table functions

Adrian Billington – setting cardinality for pipelined and table functions

4- How to format index tree dump by SQL and RegEXP

Dion Cho – Simple formatting on index tree dump

5- Restrictions for aggregate materialized views

Rob Van Wijk – Fast refreshable materialized view errors, part three: aggregate MV’s

6- dbms_stats.copy_table_stats min/high value alteration bug

Amit Bansal – dbms_stats.copy_table_stats does not alter low/high value

7- Automatic top subheap dumping with heapdump

Tanel Poder Oracle memory troubleshooting, Part 3: Automatic top subheap dumping with heapdump

8- Views in Explain Plan output – no_merge

Jonathan Lewis – Explain View

9-  Importance of Size  for  Autoextend on Next

Hemant K Chitale  - AUTOEXTEND ON Next Size

10- Partition pruning problem for MIN/MAX of partition key column

Jeff Moss – No pruning for MIN/MAX of partition key column

11- ORA-02070 with VPD  and DBMS_SCHEDULER

Amardeep Sidhu – DBMS_SCHEDULER, DBMS_RLS and SYS_CONTEXT

12- How to check RAC option in Oracle Binary

Surachart Opun – check RAC Option in Oracle Binary

13- Diagnosing paralel query and interconnect  performance on RAC with UDPSNOOP

Riyaj Shamsudeen – RAC, parallel query and udpsnoop

Oracle Forums

1- Cache Fusion and Past Image

http://forums.oracle.com/forums/thread.jspa?threadID=917350

2-CBC Latch and Buffer Busy wait on same table.

http://forums.oracle.com/forums/thread.jspa?threadID=917866

3-Latch problem

http://forums.oracle.com/forums/thread.jspa?threadID=918767

Blogroll Report 26/06/09 – 03/07/09 —- >

June 23, 2009

Blogroll Report 12/06/09 – 19/06/09

Filed under: Blogroll Report — coskan @ 11:23 am

<—- Blogroll Report 06/06/09 – 12/06/09

Last 5 days, I was at Hurricane Festival enjoying Hard Rock, without any internet access  and I couldn’t keep my promise to write blogroll report on time :(

After 4 days of tiring camping conditions, I am now ready to write last weeks highlights.

1-What is “consistent gets” and “db block gets”

Jonathan Lewis- Consistent gets

2-How to remap_tablespace on original export import

3-  How to track bind variables with errorstack  dump

Dion Cho - Tracking the bind value with errorstack dump

4- What is cleanout in Oracle

Jonathan Lewis-Clean it up

5-Q/A on  Oracle Temporary Tablespace Groups

Randolf Geist - Temporary Tablespace Groups

6- How to record DDL statements in alert log of  Logical Standby

Uwe Hesse - Record DDL Statements in DBA_LOGSTDBY_EVENTS & alert.log

7- How to kill a session on other node in Oracle 11G

Chandra Pabba - Oracle11g – Killing session in RAC (in remote instance)

8-How to remap_tablespace with original export import

Chen Shapira -  Did You Know You Can Rename Tablespaces?

9 -CBO and INDEXES for beginners Part – 2

Richard Foote - The CBO and Indexes: Introduction Continues …

10- How to dinagnose PGA with oradebug (new oradebug parameter on 11g unit_test_nolog)

Miladin Madrakovic - Memory Diagnostics – PGA Part 1

11 –  SMON and Tablespace name problem

Jonathan Lewis – Tablespaces

12-  How to resolve corruption issue with file_hdrs dump

Riyaj Shamsudeen - Resolving corruption issue with file_hdrs dump

13 – Pure SQL version for OSTAKPROF of Tanel Poder

Dion Cho - Pure SQL version of ostakprof(from Tanel Poder)

14- Re-writing query instead of creating index

Karen Morton – You dont really need that index (or that SQL )

15 – SQL Trace anomality for LOB columns

Dion Cho - LOB vs. SQL*Trace

Oracle Forums

1- What happens when you Flush buffer cache

http://forums.oracle.com/forums/thread.jspa?threadID=913831&start=15&tstart=0

2- Histograms and Explain plan differences

http://forums.oracle.com/forums/thread.jspa?threadID=914152&start=30&tstart=0

3- db file sequential read

http://forums.oracle.com/forums/thread.jspa?threadID=916549&messageID=3562867

Blogroll Report 19/06/09 – 26/06/09 —->

June 12, 2009

Blogroll Report 06/06/09 – 12/06/09

Filed under: Blogroll Report — coskan @ 7:32 pm

As you see from the blogroll page I follow over 140 Oracle DBA related blogs and all blog aggregators from orana.info. I try to read updates from all of them but  this huge amount of different source of information about different topics started to blow my mind. I start not to remember what I read and which one was useful in technical context, and most of the blog headers do not match with my future search criteria. For these reasons, I decide to write my own weekly blogroll report just for indexing technical oracle blog posts with my own keywords. ( Log Buffers of Pyhtan Group are very nice but not enough) . I am planning to write every Friday night. If you find anything useful please leave a comment so the post will be complete.

Here is this weeks selection.

1-How to deal with memory leaks by using memory dumps. (PGA leak bug)
Jonathan Lewis – PGA Leaks
Dion Cho – Troubleshooting PGA leak with PGA heap dump

2- How to fix ASM disk header

Jason Arneil – Fixing up ASM Disk Header Corruption

3- How to use Perfsheet and TPT tools for Diagnosing RAC wait events

Karl Arao -Diagnosing and Resolving “gc block lost”

4- How to use  XPLAN  tool of Alberto Dell’era

Alberto Dell’era – Optimizing SQL statements with xplan

5- How CBO use Dynamic Sampling on partitioned tables

Randolf Geist – Dynamic sampling and partitioned tables

6- How to limit query time with Resource Manager

Martin Ruthner – Limiting Query Runtime Without Killing the Session

7- How Oracle choose tablespaces for interval partitioning

Yasin Baskan – Tablespace selection in interval partitioning

8- DB_CACHE_ADVICE  bug causes simulator lru latch

Don Seiler – Turn Off db_cache_advice To Avoid Latch Contention Bugs

9- CBO and INDEXES for beginners Part – 1

Richard Foote – The CBO and Indexes: An Introduction (Absolute Beginners)

10 – How to unload  data with Data Pump and External Tables – How to move data between servers with DBMS_FILE_TRANSFER  packages

Jean-Pierre Dijcks – Unloading data using External Table and Data Pump

11- How to exclude scan on single paths with ASM multipathing

Surachart Opun – Check the device ASMLib on multi-path

12- PL/SQL Functions and their CBO costing

Adrian Billington – pl/sql functions and cbo costing

13- Tutorial for grouping rows with group by

Joe Fuda – Grouping Rows with GROUP BY

Oracle Forums – (The ones I caught)

1- db writer processes and working data sets

http://forums.oracle.com/forums/thread.jspa?threadID=909105&messageID=3528668#3528668

2- Performance tuning issue on parallel query

http://forums.oracle.com/forums/thread.jspa?threadID=912434&start=0&tstart=0

3- What should be the value of Optimizer_index_cost_adj in oracle 9i    (they are  back :) )

http://forums.oracle.com/forums/thread.jspa?threadID=912028&messageID=3535406#3535406

These are all for this week. Hope it will be helpfull to find the right blog posts for you

Blogroll Report 12/06/09 – 19/06/09—–>

June 11, 2009

How to use SYSMAN schema without EM

Filed under: Basics, Tips — coskan @ 4:18 pm

As you all know Oracle Enterprise Manager can provide incredible amount of information for the database by gathering information from the database. This information is not just in V$ / DBA_ or any internal table, there are also other tables under SYSMAN schema for providing information to EM repository.

on 11.1.0.6 Windows XP version for single instance EM Repository, my DB shows 681 tables under SYSMAN schema

on 10.2.0.4 HP-UX box for single instance EM Repository, my DB shows 341 tables under SYSMAN schema

As you can see it doubled up on 11G. New features brings new tables to this schema.

I could not find good documentation about the tables under SYSMAN schema but the structure of the table names are meaningfull enough to understand what they can be used for. For most of them The naming is like MGMT_X_Y. X stands for the generic name like HC (in my understanding Hardware Components/Configuration or something else ) and Y is for information under the generic root like HARDWARE_MASTER. Table full name is MGMT_HC_HARDWARE_MASTER

Lets start with this table and see what we can get from the table

SQL> @printtab "select * from sysman.MGMT_HC_HARDWARE_MASTER"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
VENDOR_NAME                   : Dell Inc.
SYSTEM_CONFIG                 : Latitude D620
MACHINE_ARCHITECTURE          : x86
CLOCK_FREQ_IN_MHZ             : 133
MEMORY_SIZE_IN_MB             : 2038
LOCAL_DISK_SPACE_IN_GB        : 74.53
CPU_COUNT                     : 2
CPU_BOARD_COUNT               :
IOCARD_COUNT                  : 1
FAN_COUNT                     :
POWER_SUPPLY_COUNT            :
-----------------

As you can see Tables under SYSMAN schema can give you very nice information, which you can’t find in any V$ view or DBA_ table especially for Host system.

Lets see what else we can get under MGMT_HC_%

SQL> select table_name from dba_tables where table_name like 'MGMT_HC%';

TABLE_NAME
------------------------------
MGMT_HC_SYSTEM_SUMMARY
MGMT_HC_HARDWARE_MASTER
MGMT_HC_CPU_DETAILS
MGMT_HC_IOCARD_DETAILS
MGMT_HC_NIC_DETAILS
MGMT_HC_OS_SUMMARY
MGMT_HC_OS_PROPERTIES
MGMT_HC_OS_COMPONENTS
MGMT_HC_FS_MOUNT_DETAILS
MGMT_HC_VENDOR_SW_SUMMARY
MGMT_HC_VENDOR_SW_COMPONENTS

We can get Operating system summary from MGMT_HC_OS_SUMMARY (this is my favourite on windows because it is hard to find 32bit 64 bit information even from OS itself)

SQL> @printtab "select * from sysman.MGMT_HC_OS_SUMMARY"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : Microsoft Windows XP Workstation
VENDOR_NAME                   : Microsoft Corporation
BASE_VERSION                  : 5.1
UPDATE_LEVEL                  : Service Pack 2
DISTRIBUTOR_VERSION           : N/A
MAX_SWAP_SPACE_IN_MB          : 3933
ADDRESS_LENGTH_IN_BITS        : 32-bit
PATCHES                       : 152
-----------------

PL/SQL procedure successfully completed.

on HP-UX same table shows

SQL>  @printtab "select * from sysman.MGMT_HC_OS_SUMMARY"
SNAPSHOT_GUID                 : 66E304347A471561E044000000000000
NAME                          : HP-UX
VENDOR_NAME                   : Hewlett-Packard Co.
BASE_VERSION                  : B.11.11
UPDATE_LEVEL                  : U
DISTRIBUTOR_VERSION           : N/A
MAX_SWAP_SPACE_IN_MB          : 15598.066
ADDRESS_LENGTH_IN_BITS        : 64
-----------------

Do you want to learn which operating system fixes are installed on the box, then MGMT_HC_OS_COMPONENTS is the right table for you

SQL> @printtab "select * from sysman.MGMT_HC_OS_COMPONENTS where rownum<4"
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : KB867282
TYPE                          : Patch
VERSION                       :
DESCRIPTION                   : Windows XP Hotfix - KB867282
INSTALLATION_DATE             :
-----------------
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : KB873333
TYPE                          : Patch
VERSION                       :
DESCRIPTION                   : Windows XP Hotfix - KB873333
INSTALLATION_DATE             :
-----------------
SNAPSHOT_GUID                 : B4E96AB2F8F9436E8A15B562B90E12B5
NAME                          : KB873339
TYPE                          : Patch
VERSION                       :
DESCRIPTION                   : Windows XP Hotfix - KB873339
INSTALLATION_DATE             :
-----------------

PL/SQL procedure successfully completed.

As you see these tables are there and ready to be used. There are too many other tables under SYSMAN schema, which might have valuable info for your needs. I think my duty is finished here, remaining is homework for you, to discover the information under the tables listed via this sql command


select table_name from dba_tables where owner='SYSMAN' and num_rows>0;

June 3, 2009

Oracle Tips by David Fitzjarrell

Filed under: Blogroll — coskan @ 9:58 pm

I just discovered the Oracle Tips blog of David Fitzjarrell, by the help of this post from Jonathan Lewis .

I could’t take myself from reading his blog journey from the very beginning. All of his posts are nice, clear and interesting. If you are an Oracle lover, I highly recommend you follow this highly technical blog

Too many trace files on 11G

Filed under: Tips — coskan @ 9:30 am

On 11G user and system traces are on the same directory (%DIAGNOSTIC_DEST%\diag\rdbms\SID\trace on my machine) and automatic health monitoring  looks like a bit more active than 10G. On 10G I wasn’t seeing automatic trace generation for user sessions, however on 11.1.0.6 Oracle nearly creates trace for every session and  it realy drives me mad when I am working on 10046 traces.  I checked unsupported parameters and I found _disable_health_check parameter. When you set this parameter to TRUE a Oracle stops generating trace files for every user session. Parameter needs restart of database.

It looks like there are 32 other diagnostic related parameters on 11G but _disable_health_check
was enough to solve my problem.

I can see this parameter is available on 10GR2 and 11GR1 but not available at least on 9.2.0.4

!!!!! As you see, it is unsupported dont use it on any production system without asking Oracle Support.

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 199 other followers