|
|
XMLType is an abstract type for programming, whose storage model can be unstructured storage or structure storage.
Oracle XML DB uses XML schema as a mechanism for defining how the contents of an XMLType should be stored inside the database. Currently Oracle XML DB
provides two options:
- Unstructured storage. The content of the XMLType is persisted as XML text using a CLOB datatype. This option is available for non-schema-based and schema-based XML content. When the XML is to be stored and retrieved as complete documents, unstructured storage may be the best solution as it offers the fastest rates of throughput when storing and retrieving XML content.
- Structured storage. The content of the XMLType is persisted as a set of SQL objects. The structured storage option is only available when the XMLType table or column has been constrained to a global element defined by XML schema. If there is a need to extract or update sections of the document, perform XSL transformation on the document, or work through the DOM API, then structured storage may be the preferred storage type. Structured storage allows all these operations to take place more efficiently but at a greater overhead when storing and retrieving the entire document.
Let’s go to the index topic. This is the range of structured storage. Similar to the regular index, we can create an index when we create the table, or we can create an index using create index statement. Below is an example using create index.
CREATE INDEX ipurchaseorder_rejectedby
ON purchaseorder (extractValue(OBJECT_VALUE, '/PurchaseOrder/Reject/User'));
One limitation is that this require the element to index must occur only once, as extractValue can’t return multiple values.
If the elements occurs multiple times, the index must be create when creating the table using nested table for the index element, one such example is as below:
CREATE TABLE purchaseorder OF XMLType
XMLSCHEMA "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"
ELEMENT "PurchaseOrder"
VARRAY "XMLDATA"."ACTIONS"."ACTION"
STORE AS TABLE action_table
((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
ORGANIZATION INDEX OVERFLOW)
VARRAY "XMLDATA"."LINEITEMS"."LINEITEM"
STORE AS TABLE lineitem_table
((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
ORGANIZATION INDEX OVERFLOW);
In this way, the indexes for action and lineitem is setup when creating the table.
This CREATE TABLE statement above is equivalent to the CREATE TABLE statement automatically generated by Oracle XML DB when the schema annotation storeVarrayAsTable="true" is included in the root element of the PurchaseOrder XML schema (and genTables="true" is set during schema registration). When this annotation is used, the nested tables generated by the XML schema registration process are given system-generated names, which can be difficult to work with. You can give them more meaningful names using the SQL statement RENAME TABLE.
The elementFormDefault can be qualified or unqualified, unqualified is the default.
"qualified" means that all the local elements(such as shipTo below) must be qualified, either through specified namespace or default namespace.
Example:
po.xsd as:
<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:po="http://www.example.com/PO1"
targetNamespace="http://www.example.com/PO1"
elementFormDefault="qualified"
attributeFormDefault="qualified">
<element name="purchaseOrder" type="po:PurchaseOrderType"/>
<complexType name="PurchaseOrderType">
<sequence>
<element name="shipTo" type="string"/>
</sequence>
</complexType>
</schema>
Corresponding xml can be:
<?xml version="1.0"?>
<prx:purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org/P01 po2.xsd"
xmlns:prx="http://www.example.com/PO1">
<prx:shipTo >
</prx:shipTo>
</prx:purchaseOrder>
OR
<?xml version="1.0"?>
<purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org/P01 po2.xsd"
xmlns="http://www.example.com/PO1">
<shipTo >
</shipTo>
</purchaseOrder>
"unqualified" means that all the local elements mustn’t be qualified, or to say, must have no namespace.
po.xsd as:
<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:po="http://www.example.com/PO1"
targetNamespace="http://www.example.com/PO1"
elementFormDefault="unqualified"
attributeFormDefault="unqualified">
<element name="purchaseOrder" type="po:PurchaseOrderType"/>
<complexType name="PurchaseOrderType">
<sequence>
<element name="shipTo" type="string"/>
</sequence>
</complexType>
</schema>
Corresponding xml can be:
<?xml version="1.0"?>
<purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org/P01 po.xsd"
xmlns="http://www.example.com/PO1">
<shipTo xmlns="">
</shipTo>
</purchaseOrder>
OR
<?xml version="1.0"?>
<prx:purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org/P01 po.xsd"
xmlns:prx="http://www.example.com/PO1">
<shipTo>
</shipTo>
</prx:purchaseOrder>
You can test the above examples in the XML-editor, such as JDeveloper, validating the grammar automatically.
Also, the references below can be useful if you want the further understanding.
And Recently, My colleague Corey clarify this concept too in another blog, whose URL is elementFormDefault attribute, attributeFormDefault attribute and form attribute in XML Schema , also referencing to the above documents:)
SQL> startup
ORACLE instance started.
Total System Global Area 202868968 bytes
Fixed Size 731368 bytes
Variable Size 167772160 bytes
Database Buffers 33554432 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: ‘/opt/oracle/product/9.2/dbs/datafile.dbf’
1. The datafile does exist, but Oracle cannot find it.
2. The datafile does not exist or unsuable by Oracle. The datafile has been physically removed or damaged to an extent that Oracle cannot recognize it anymore.
a. Recreate the tablespace that the datafile belongs to.
1. If the database is down, mount it. SVRMGR> STARTUP MOUNT PFILE=’<location_of_pfile>’;
2. Offline drop the datafile. SVRMGR> ALTER DATABASE DATAFILE ‘<full_path_file_name>’ OFFLINE DROP;
3. If the database is at mount point, open it. SVRMGR> ALTER DATABASE OPEN;
4. Drop the user tablespace. SVRMGR> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;
Note: The users can stop with this step if they do not want the tablespace anymore in the database.
5. Recreate the tablespace. SVRMGR> CREATE TABLESPACE <tablespace_name> DATAFILE ‘<datafile_full_path_name’> SIZE <required_size>;
6. Recreate all the previously existing objects in the tablespace.
b. Recover the datafile using normal recovery procedures.
1. Restore the lost file from a backup.
2. If the database is down, mount it.
SQL>STARTUP MOUNT PFILE=<location_of_pfile>;
3. Issue the following query:
SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#,
FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;
This will list all your online redolog files and their respective sequence and first change numbers.
4. issue the query:
SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;
This query will list the files needing to recover.
5. use rman to restore and recover the datafile.
3. Operating Systems (OS) Tempfiles missing:
When using TEMPORARY tablespaces with tempfiles, the absence of the tempfile at the OS level can cause ORA-1157.
Since Oracle does not checkpoint tempfiles, the database can be opened even with missing tempfiles.
The solution in this case would be to drop the logical tempfile and add a new one.
For example:
SQL> select * from dba_objects order by object_name;
select * from dba_objects order by object_name;
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1026 - see DBWR trace file
ORA-01110: data file 1026: ‘/Oracle/oradata/V901/temp2_01.tmp’
SQL> alter database tempfile ‘/Oracle/oradata/V901/temp2_01.tmp’ drop;
SQL> select tablespace_name, file_name from dba_temp_files;
SQL> alter tablespace temp2
add tempfile ‘/Oracle/oradata/V901/temp2_01.tmp’ size 5m;
The article is mainly from metalink Note:184327.1
1. corrupt the data file corresponding to the table
For this purpose, a dd command is generate from the Oracle views.
set heading off
set lines 113
SELECT 'dd of=' || f.file_name || ' bs=8192 conv=notrunc seek=' ||
to_number(S.HEADER_BLOCK + 1) || ' << EOF',
'CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT corrupt CORRUPT CORRUPT CORRUPT CORRUPT corrupt corrupt ',
'EOF'
FROM DBA_SEGMENTS S, dba_data_files f, dba_tables t
WHERE f.tablespace_name = t.tablespace_name
and S.SEGMENT_NAME = t.table_name
and t.table_name = 'BTEST'
and S.OWNER = t.owner
and t.owner = 'SYS';
After executing this dd comand, the storage space of table sys.btest will be corrupted.
2. flush the buffer cache, so the next querying statement will read data from storage.
alter system flush buffer_cache;
3. select should be error.
sys@ORCL> select * from btest;
select * from btest
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 16, block # 12)
ORA-01110: data file 16: '/opt/oracle/oradata/orcl/ORCL/datafile/o1_mf_broken_2vjk5vss_.dbf'
4. After shutdown and startup, the database functions normal, but the btest will be unavailable.
DFO means "Data Flow Operator". Actually, “queries” don’t run in parallel, it’s "data flow operations" (DFOs) that run in parallel, and a single query can be made up of several data flow operations. DFO tree is composite with DFOs, usually one query have one DFO tree. such as
select /*+ parallel(t1 4) parallel(t2 4) */ rownum, t1.id from t1, t2 where t1.id = t2.id;
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 600 | 5 (20)| 00:00:01 | | | |
| 1 | COUNT | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 100 | 600 | 5 (20)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 4 | HASH JOIN BUFFERED | | 100 | 600 | 5 (20)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX RECEIVE | | 100 | 300 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 6 | PX SEND HASH | :TQ10000 | 100 | 300 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 7 | PX BLOCK ITERATOR | | 100 | 300 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| T1 | 100 | 300 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 9 | PX RECEIVE | | 100 | 300 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 10 | PX SEND HASH | :TQ10001 | 100 | 300 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 11 | PX BLOCK ITERATOR | | 100 | 300 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 12 | TABLE ACCESS FULL| T2 | 100 | 300 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------
The result from v$pq_tqstat:
hlr@ORCL> select * from v$pq_tqstat order by dfo_number , tq_id , server_type;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES OPEN_TIME AVG_LATENCY WAITS TIMEOUTS PROCESS INSTANCE
---------- ---------- --------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------
1 0 Consumer 28 192 0 0 10 1 P002 1
1 0 Consumer 26 184 0 0 9 1 P001 1
1 0 Consumer 19 156 0 0 10 2 P000 1
1 0 Consumer 27 188 0 0 10 3 P003 1
1 0 Producer 0 80 0 0 0 0 P007 1
1 0 Producer 0 80 0 0 0 0 P005 1
1 0 Producer 100 480 0 0 2 1 P004 1
1 0 Producer 0 80 0 0 0 0 P006 1
1 1 Consumer 28 192 0 0 10 1 P002 1
1 1 Consumer 26 184 0 0 9 1 P001 1
1 1 Consumer 19 156 0 0 10 3 P000 1
1 1 Consumer 27 188 0 0 10 2 P003 1
1 1 Producer 100 480 0 0 1 0 P004 1
1 1 Producer 0 80 0 0 0 0 P007 1
1 1 Producer 0 80 0 0 0 0 P005 1
1 1 Producer 0 80 0 0 0 0 P006 1
1 2 Consumer 100 480 0 0 0 0 QC 1
1 2 Producer 27 128 0 0 0 0 P003 1
1 2 Producer 26 124 0 0 0 0 P001 1
1 2 Producer 28 132 0 0 0 0 P002 1
1 2 Producer 19 96 0 0 0 0 P000 1
So, there is on DFO tree, three TQ(table queue), and P000,P001,P002 not only acted as Consumer, but also Producer. The DFO tree can be visualized(the rectangle stands for table queue, and ellipse stands for servers/slaves :
There are quite a lot of queries that have multiple DFO trees, just a demonstration innovated from Jonathan’s blog
select /*+ parallel(t1 4) parallel(v1 8 ) */
t1.id
from t1,
(select /*+ parallel(t1 4) parallel(t2 4) */
rownum, t1.id
from t1, t2
where t1.id = t2.id) v1
where t1.id = v1.id;
Execution plan:
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 600 | 7 (15)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ20002 | 100 | 600 | 7 (15)| 00:00:01 | Q2,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 100 | 600 | 7 (15)| 00:00:01 | Q2,02 | PCWP | |
| 4 | PX RECEIVE | | 100 | 300 | 2 (0)| 00:00:01 | Q2,02 | PCWP | |
| 5 | PX SEND HASH | :TQ20001 | 100 | 300 | 2 (0)| 00:00:01 | Q2,01 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 100 | 300 | 2 (0)| 00:00:01 | Q2,01 | PCWC | |
| 7 | TABLE ACCESS FULL | T1 | 100 | 300 | 2 (0)| 00:00:01 | Q2,01 | PCWP | |
| 8 | BUFFER SORT | | | | | | Q2,02 | PCWC | |
| 9 | PX RECEIVE | | 100 | 300 | 5 (20)| 00:00:01 | Q2,02 | PCWP | |
| 10 | PX SEND HASH | :TQ20000 | 100 | 300 | 5 (20)| 00:00:01 | | S->P | HASH |
| 11 | VIEW | | 100 | 300 | 5 (20)| 00:00:01 | | | |
| 12 | COUNT | | | | | | | | |
| 13 | PX COORDINATOR | | | | | | | | |
| 14 | PX SEND QC (RANDOM) | :TQ10002 | 100 | 600 | 5 (20)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 15 | HASH JOIN BUFFERED | | 100 | 600 | 5 (20)| 00:00:01 | Q1,02 | PCWP | |
| 16 | PX RECEIVE | | 100 | 300 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 17 | PX SEND HASH | :TQ10000 | 100 | 300 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 18 | PX BLOCK ITERATOR | | 100 | 300 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 19 | TABLE ACCESS FULL| T1 | 100 | 300 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 20 | PX RECEIVE | | 100 | 300 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 21 | PX SEND HASH | :TQ10001 | 100 | 300 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 22 | PX BLOCK ITERATOR | | 100 | 300 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 23 | TABLE ACCESS FULL| T2 | 100 | 300 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------
Information from v$pq_tqstat;
hlr@ORCL> select * from v$pq_tqstat order by dfo_number , tq_id , server_type;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES OPEN_TIME AVG_LATENCY WAITS TIMEOUTS PROCESS INSTANCE
---------- ---------- --------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------
1 0 Consumer 27 128 0 0 14 8 P003 1
1 0 Consumer 19 96 0 0 12 6 P000 1
1 0 Consumer 26 124 0 0 12 6 P001 1
1 0 Consumer 28 132 0 0 14 8 P002 1
1 1 Consumer 27 188 0 0 13 7 P003 1
1 1 Consumer 26 184 0 0 14 9 P001 1
1 1 Consumer 28 192 0 0 11 6 P002 1
1 1 Consumer 19 156 0 0 14 9 P000 1
1 1 Producer 0 80 0 0 0 0 P006 1
1 1 Producer 100 480 0 0 1 0 P004 1
1 1 Producer 0 80 0 0 0 0 P005 1
1 1 Producer 0 80 0 0 0 0 P007 1
1 2 Producer 26 124 0 0 0 0 P001 1
1 2 Producer 19 96 0 0 0 0 P000 1
1 2 Producer 28 132 0 0 0 0 P002 1
1 2 Producer 27 128 0 0 0 0 P003 1
2 0 Consumer 26 184 0 0 14 7 P009 1
2 0 Consumer 19 156 0 0 15 8 P008 1
2 0 Consumer 28 192 0 0 13 7 P010 1
2 0 Consumer 27 188 0 0 13 8 P011 1
2 0 Producer 100 480 0 0 2 1 P016 1
2 0 Producer 0 80 0 0 0 0 P013 1
2 0 Producer 100 480 0 0 4294967242 4294967282 QC 1
2 0 Producer 0 80 0 0 0 0 P014 1
2 0 Producer 0 80 0 0 0 0 P012 1
2 1 Consumer 19 156 0 0 14 8 P008 1
2 1 Consumer 27 188 0 0 14 8 P011 1
2 1 Consumer 28 192 0 0 16 7 P010 1
2 1 Consumer 26 184 0 0 13 7 P009 1
2 1 Producer 100 480 0 0 1 0 P016 1
2 1 Producer 0 80 0 0 0 0 P014 1
2 1 Producer 0 80 0 0 0 0 P012 1
2 1 Producer 0 80 0 0 0 0 P013 1
2 2 Consumer 100 480 0 0 2 1 QC 1
2 2 Consumer 100 480 0 0 4 2 QC 1
2 2 Producer 28 132 0 0 0 0 P010 1
2 2 Producer 27 128 0 0 0 0 P011 1
2 2 Producer 19 96 0 0 0 0 P008 1
2 2 Producer 26 124 0 0 0 0 P009 1
SO, there are two DFO trees. Also, the DFO trees can be visualized:
Questions is: there are two QC in TQ(2,2), which have different waits and timeouts in v$pq_tqstat. And the TQ(1,0) have no producers in the v$pq_tqstat, I just add it for completion.
After the parallel execution, this is the best chance to query the execution information.
For Example, to analyze the execution of following parallel SQL statement.
select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c hash, hash)*/
c.unit_price
from products p, costs c
where c.prod_id = p.prod_id;
After execution,
hlr@ORCL> select * from v$pq_sesstat;
STATISTIC LAST_QUERY SESSION_TOTAL
------------------------------ ---------- -------------
Queries Parallelized 1 13
DML Parallelized 0 0
DDL Parallelized 0 0
DFO Trees 1 13
Server Threads 6 0
Allocation Height 3 0
Allocation Width 1 0
Local Msgs Sent 362 303740
Distr Msgs Sent 0 0
Local Msgs Recv'd 368 303782
Distr Msgs Recv'd 0 0
11 rows selected.
hlr@ORCL> select DFO_NUMBER, TQ_ID, SERVER_TYPE, NUM_ROWS ,BYTES,process from v$pq_tqstat order by dfo_number , tq_id , server_type;
DFO_NUMBER TQ_ID SERVER_TYPE NUM_ROWS BYTES PROCESS
---------- ---------- --------------- ---------- ---------- --------
1 0 Consumer 17151 100454 P001
1 0 Consumer 17242 100969 P002
1 0 Consumer 17257 101058 P000
1 0 Producer 1971 9955 P004
1 0 Producer 29565 174989 P005
1 0 Producer 20114 117537 P003
1 1 Consumer 893 8107 P002
1 1 Consumer 2914 26341 P001
1 1 Consumer 0 60 P000
1 1 Producer 611 5494 P003
1 1 Producer 2593 23493 P005
1 1 Producer 603 5521 P004
1 2 Consumer 3807 19040 QC
1 2 Producer 2914 14545 P001
1 2 Producer 893 4475 P002
1 2 Producer 0 20 P000
16 rows selected.
hlr@ORCL>
Obviously, from the v$pq_tqstat, the DOP is downgrade to 3, and have two slave sets. From the v$tq_sesstat, we can see that what’s the process. How these processes cooperated together to generated the result. In this sample, P003, P004,P005 generate the data and feed to P000,P001,P002, totally twice. Then P000,P001,P002 feed the data to QC, the query coordinator. Also, we can find that P000 at the first time received 17257 rows, but at the second time it received 0 rows, and lastly, it product 0 rows to QC. Why?
Let’s look at the execution plan for further investigation.
hlr@ORCL> explain plan for select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c hash, hash)*/ c.unit_price from products p, costs c where c.prod_id = p.prod_id;
Explained.
hlr@ORCL> @utlxplp
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1177066807
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3867 | 147K| 13 (8)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 3867 | 147K| 13 (8)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 3867 | 147K| 13 (8)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 53332 | 677K| 7 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 53332 | 677K| 7 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 53332 | 677K| 7 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| PRODUCTS | 53332 | 677K| 7 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX RECEIVE | | 3867 | 98K| 5 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | 3867 | 98K| 5 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 3867 | 98K| 5 (0)| 00:00:01 | Q1,01 | PCWC | |
| 11 | TABLE ACCESS FULL| COSTS | 3867 | 98K| 5 (0)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."PROD_ID"="P"."PROD_ID")
Note
-----
- dynamic sampling used for this statement
27 rows selected.
hlr@ORCL>
This is a hash-hash parallel distribute, using hash joining. firstly, Slave set(Q1,00) scan products table and send the results to slave set(Q1,02), and slave set(Q1,01) scan the costs table and send results to slave set(Q1,02), which slave in slave set(Q1,02) to send to is dependent on the hash value of join key. as the table costs only have two distinct values for prod_id, so the rows in costs only have two target slave to send to, resulting one slave of the slave set (Q1,02) receive 0 rows. After the slaves in (Q1,02) received rows from the other two slave sets, they hash join the rows and send the result to QC, obviously, one slave will have no rows to process.
This is why 0 rows in the above output.
Also, you can enable the trace for parallel execution, through this way:
alter session set events '10132 trace name context forever';
As a summary, we can monitor the parallel executions through v$ views, execution plan and trace event.
The PQ_DISTRIBUTE hint instructs the optimizer how to distribute rows of joined tables among producer and consumer query servers. Such distribution can improve the performance of parallel join operations.
tablespec is the name or alias of a table to be used as the inner table of a join.
outer_distribution is the distribution for the outer table.
inner_distribution is the distribution for the inner table.
The values of the distributions are HASH, BROADCAST, PARTITION, and NONE. Only six combinations table distributions are valid, as described in the table below:
| Distribution |
Description |
| HASH, HASH |
The rows of each table are mapped to consumer query servers, using a hash function on the join keys. When mapping is complete, each query server performs the join between a pair of resulting partitions. This distribution is recommended when the tables are comparable in size and the join operation is implemented by hash-join or sort merge join.
|
| BROADCAST, NONE |
All rows of the outer table are broadcast to each query server. The inner table rows are randomly partitioned. This distribution is recommended when the outer table is very small compared with the inner table. As a general rule, use this distribution when the inner table size multiplied by the number of query servers is greater than the outer table size.
|
| NONE, BROADCAST |
All rows of the inner table are broadcast to each consumer query server. The outer table rows are randomly partitioned. This distribution is recommended when the inner table is very small compared with the outer table. As a general rule, use this distribution when the inner table size multiplied by the number of query servers is less than the outer table size.
|
| PARTITION, NONE |
The rows of the outer table are mapped using the partitioning of the inner table. The inner table must be partitioned on the join keys. This distribution is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers.
Note: The optimizer ignores this hint if the inner table is not partitioned or not equijoined on the partitioning key.
|
| NONE, PARTITION |
The rows of the inner table are mapped using the partitioning of the outer table. The outer table must be partitioned on the join keys. This distribution is recommended when the number of partitions of the outer table is equal to or nearly equal to a multiple of the number of query servers; for example, 14 partitions and 15 query servers.
Note: The optimizer ignores this hint if the outer table is not partitioned or not equijoined on the partitioning key.
|
| NONE, NONE |
Each query server performs the join operation between a pair of matching partitions, one from each table. Both tables must be equipartitioned on the join keys.
|
For example, given two tables r and s that are joined using a hash join, the following query contains a hint to use hash distribution:
SELECT /*+ORDERED PQ_DISTRIBUTE(s HASH, HASH) USE_HASH (s)*/ column_list
FROM r,s
WHERE r.c=s.c;
To broadcast the outer table r, the query is:
SELECT /*+ORDERED PQ_DISTRIBUTE(s BROADCAST, NONE) USE_HASH (s) */ column_list
FROM r,s
WHERE r.c=s.c;
This is just reference from Oracle 10g online document.
My examples to demonstrate these concepts
system@ORCL> create table products as select rownum prod_id from all_objects;
Table created.
system@ORCL> create table costs as select mod(rownum,2)+1 prod_id, rownum unit_price from all_tables;
Table created.
system@ORCL> set autot trace expl;
system@ORCL> select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c hash, hash)*/ c.unit_price from products p, costs c where c.prod_id = p.prod_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1177066807
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1790 | 69810 | 9 (12)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| PRODUCTS | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX RECEIVE | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 11 | TABLE ACCESS FULL| COSTS | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."PROD_ID"="P"."PROD_ID")
Note
-----
- dynamic sampling used for this statement
system@ORCL> select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c broadcast, none)*/ c.unit_price from products p, costs c where c.prod_id = p.prod_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1844745828
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1790 | 69810 | 9 (12)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND BROADCAST | :TQ10000 | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 6 | PX BLOCK ITERATOR | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| PRODUCTS | 51613 | 655K| 7 (15)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX BLOCK ITERATOR | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 9 | TABLE ACCESS FULL | COSTS | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."PROD_ID"="P"."PROD_ID")
Note
-----
- dynamic sampling used for this statement
system@ORCL> select /*+ ordered use_hash(p c) parallel(p 4) parallel(c 4) pq_distribute(c none broadcast)*/ c.unit_price from products p, costs c where c.prod_id = p.prod_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 2231521328
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1790 | 69810 | 9 (12)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 1790 | 69810 | 9 (12)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 51613 | 655K| 7 (15)| 00:00:01 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | PRODUCTS | 51613 | 655K| 7 (15)| 00:00:01 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 9 | PX BLOCK ITERATOR | | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| COSTS | 1790 | 46540 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C"."PROD_ID"="P"."PROD_ID")
Note
-----
- dynamic sampling used for this statement
Two execution plans.
Disable optimizer_dynamic_sampling firstly.
alter session set optimizer_dynamic_sampling = 0;
Then, create two simple tables;
CREATE TABLE t1 ( c1 NUMBER, c2 NUMBER );
CREATE TABLE t2 ( c1 NUMBER, c2 NUMBER );
hr@ORCL> SELECT /*+ parallel(t1,2) */ t1.c2,t2.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
Execution Plan
----------------------------------------------------------
Plan hash value: 869483324
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 4264 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 82 | 4264 | 5 (20)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 82 | 4264 | 5 (20)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 9 | PX BLOCK ITERATOR | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 10 | TABLE ACCESS FULL| T2 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
hr@ORCL> SELECT /*+ parallel(t1,3) */ t1.c2,t2.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3683239666
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 4264 | 5 (20)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 82 | 4264 | 5 (20)| 00:00:01 | Q1,02 | P->S | QC (RAND) |
|* 3 | HASH JOIN BUFFERED | | 82 | 4264 | 5 (20)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| T1 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | PX RECEIVE | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
| 9 | PX SEND HASH | :TQ10001 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 11 | TABLE ACCESS FULL| T2 | 82 | 2132 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
Why the PQ distribution change from Broadcast to Hash when DOP(degree of parallel) changes from 2 to 3(or above)?
Note that TQ(Table Queue) also change from 2 to 3.
But let’s change to another principle questions, how many PQ distribute way exists? Can I change them by my own? Refer to my next article for answers.
Another principle question: how to monitor the parallel execution except the execution plan? how about the trace, V$ views, or other things? Refer to my next next article for answers!
So why the optimizer chose different execution plan according to different DOP? Simple to say, this is based on the statistics on the tables. And here, remember, the statistics for these tables here are all "default" statistics, which is not suitable for parallel execution actually. Where to use a specific PQ distribute method could be another deep topic.
Create two partitioned tables:
CREATE TABLE t1(c1 NUMBER, c2 NUMBER)
PARTITION BY RANGE (c1)
(
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (30),
PARTITION p4 VALUES LESS THAN (40)
);
CREATE TABLE t2(c1 NUMBER, c2 NUMBER)
PARTITION BY RANGE (c1)
(
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (30),
PARTITION p4 VALUES LESS THAN (40)
);
hr@ORCL> SELECT /*+ PARALLEL (t1,1) */ t1.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1604022418
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 328 | 12792 | 8 (25)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL| | 328 | 12792 | 8 (25)| 00:00:01 | 1 | 4 |
|* 2 | HASH JOIN | | 328 | 12792 | 8 (25)| 00:00:01 | | |
| 3 | TABLE ACCESS FULL| T1 | 328 | 8528 | 3 (0)| 00:00:01 | 1 | 4 |
| 4 | TABLE ACCESS FULL| T2 | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."C1"="T2"."C1")
hr@ORCL> SELECT /*+ PARALLEL (t1,2) */ t1.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2042620038
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 328 | 12792 | 6 (17)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 328 | 4264 | 3 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST LOCAL| :TQ10000 | 328 | 4264 | 3 (0)| 00:00:01 | | | | S->P | BCST LOCAL |
| 9 | PARTITION RANGE ALL | | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | |
| 10 | TABLE ACCESS FULL | T2 | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
hr@ORCL> SELECT /*+ PARALLEL (t1,4) */ t1.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2117927142
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 328 | 12792 | 6 (17)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 328 | 4264 | 3 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND BROADCAST | :TQ10000 | 328 | 4264 | 3 (0)| 00:00:01 | | | | S->P | BROADCAST |
| 9 | PARTITION RANGE ALL| | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | |
| 10 | TABLE ACCESS FULL | T2 | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
hr@ORCL> SELECT /*+ PARALLEL (t1,5) */ t1.c2 FROM t1,t2 WHERE t1.c1 = t2.c1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2042834191
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 328 | 12792 | 6 (17)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
|* 3 | HASH JOIN | | 328 | 12792 | 6 (17)| 00:00:01 | | | Q1,01 | PCWP | |
| 4 | PX PARTITION RANGE ALL | | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWC | |
| 5 | TABLE ACCESS FULL | T1 | 328 | 8528 | 2 (0)| 00:00:01 | 1 | 4 | Q1,01 | PCWP | |
| 6 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 7 | PX RECEIVE | | 328 | 4264 | 3 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX SEND PARTITION (KEY)| :TQ10000 | 328 | 4264 | 3 (0)| 00:00:01 | | | | S->P | PART (KEY) |
| 9 | PARTITION RANGE ALL | | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | |
| 10 | TABLE ACCESS FULL | T2 | 328 | 4264 | 3 (0)| 00:00:01 | 1 | 4 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."C1"="T2"."C1")
hr@ORCL>
Also the PQ distribution change from "Broadcast Local" to "Broadcast" to "Part(Key)" when DOP(degree of parallel) changes from 2 to 4 to 5(or above).
Why? Maybe, in the further, I will use an more realistic case to study and investigate why.
Compiling from source is not an productive way to install software, the binary distribution will cease a lot of trouble when installing. It will be perfect will one distribution vendor bundle all of the necessary development software together and make the common configuration and optimization, and make the management easy to combine some management tools.
Zend Core for Oracle come out! "Zend Core is a Certified PHP distribution that includes a stable and reliable environment for managing PHP. This environment improves PHP development by removing the current hardship in manually building and setting up an entire PHP environment. Zend Core also acts as an "efficiency facilitator" to improve the PHP implementation and management process for managing PHP development and production environments.
Zend Core does not require any complicated configurations or settings in order to run. After installation, Zend Core is ready to run. The only thing left to do is to login to the application with the password that was created in the installation process."
The URL to download the latest version: http://www.oracle.com/technology/tech/php/zendcore/index.html
Just download it and execute the install scripts, remember to install the additional components, including Zend Framework, Mysql, PHPMyAdmin.
After installing, the directory layout(in /usr/local/Zend) is like:
|– Core
| |– GUI
| |– bin
| |– cgi-bin
| |– etc
| |– include
| |– lib
| |– logs
| |– modules
| |– mysql
| |– sbin
| |– setup
| `– share
|– ZendFramework
| |– INSTALL.txt
| |– LICENSE.txt
| |– NEWS.txt
| |– README.txt
| |– VERSION.txt
| |– demos
| |– library
| `– tests
`– apache2
|– LICENSE
|– bin
|– build
|– cgi-bin
|– conf
|– error
|– htdocs
|– icons
|– include
|– lib
|– logs
|– man
|– manual
`– modules
And the Web page is like this:
The Mysql link to PHPMyAdmin:
All is ready for you to explore!
For example, let’s test the OCI functionalities:
create the file conn.php as last article, and output is like this:
Actually, the traditional way is compiling source from scratch. Below is information from OTN:
Installing Apache, PHP, JDeveloper, and the PHP Extension on Linux
Apache 1.3
- Login as Oracle user.
- Copy/download apache_1.3.31.tar.gz
- tar -zxf apache_1.3.31.tar.gz
- cd apache_1.3.31
- Configure:
./configure --enable-module=so --prefix=$HOME/apache --with-port=8888
- Build: make
- Install: make install
- Start Apache: $HOME/apache/bin/apachectl start
- Go to a browser and check that http://localhost:8888/ returns the default "Powered by Apache" page.
- Stop Apache: $HOME/apache/bin/apachectl stop
PHP 4.3
Before you proceed, make sure that the packages "bison" and "flex" are installed, because PHP needs them.
- Copy/download php-4.3.9.tar.gz
- tar -zxf php-4.3.9.tar.gz
- Set environment variable ORACLE_SID
- Set environment variable ORACLE_HOME
- Set environment variable LD_LIBRARY_PATH to $ORACLE_HOME/lib:${LD_LIBRARY_PATH}
- Configure (make sure to enter this as one long line):
./configure --prefix=$HOME/php --with-apxs=$HOME/apache/bin/apxs
--with-config-file-path=$HOME/apache/conf
--with-oci8=$ORACLE_HOME --enable-sigchild
- Build: make
- Install: Make install
- cp php.ini-recommended $HOME/apache/conf/php.ini
- Edit $HOME/apache/conf/httpd.conf and add:
AddType application/x-httpd-php .php
AddType application/x-httpd-php-source .phps
- Restart Apache
- create $HOME/apache/htdocs/phpinfo.php as:
1: <?php
2: phpinfo();
3: ?>
- In a browser load http://localhost:8888/phpinfo.php. Check that the OCI module is listed.
Following exactly the steps above, you will get PHP + Apache running on Linux.
NOTES:–with-apxs in step 6 when installing PHP is important, this let the installation process to compile the OCI library for the apache and make it available to Apache.
For testing the OCI support in PHP, create $HOME/apache/htdocs/connect.php as:
1: <?php
2:
3: define('ORA_CON_UN', 'scott');
4: define('ORA_CON_PW', 'tiger');
5: define('ORA_CON_DB', 'MYSID');
6:
7: $conn = OCILogon(ORA_CON_UN, ORA_CON_PW , ORA_CON_DB );
8:
9: if (!$conn) {
10: exit;
11: }
12:
13: echo OCIServerVersion($conn) ."<br>\n";
14: echo "Connected as ".ORA_CON_UN."</br>\n";
15: echo date('Y-m-d H:i:s')."<br><br>\n";
16:
17: $query = 'select * from emp';
18:
19: $stid = OCIParse($conn, $query);
20: OCIExecute($stid, OCI_DEFAULT);
21: print "<table border='1'>\n";
22: while ($succ = OCIFetchInto($stid, $row, OCI_RETURN_NULLS)) {
23: print "<tr>\n";
24: foreach ($row as $item) {
25: print '<td>';
26: print isset($item)?htmlentities($item):'?';
27: print "</td>\n";
28: }
29: print "</tr>\n";
30: }
31: print "</table>\n";
32:
33: OCILogoff($conn);
34: ?>
In a browser load http://localhost:8888/connect.php. Check that the OCI driver works.
|
Disclaimer The views or opinions expressed on this blog are my own and do not necessarily reflect the views or opinions of Oracle Corporation. The views or opinions expressed by visitors on this blog are theirs solely and may not reflect mine.
|
